Advertisement:

Author Topic: SMF forum not available/timeout after adding person on ban list  (Read 732 times)

Offline ondrreme

  • Newbie
  • *
  • Posts: 4
SMF forum not available/timeout after adding person on ban list
« on: February 12, 2021, 07:25:24 AM »
We are experiencing timeouts and unaccessibility of SMF based forum after adding new ban record.

The reason is waiting on the mysql to finish the query which takes 2 minutes.

---
Count: 123  Time=113.20s (13923s)  Lock=32.86s (4041s)  Rows_sent=771.0 (94833), Rows_examined=81046596.6 (9968731379), forum_avast_user[forum_avast_user]@localhost
  SELECT mem.id_member, mem.is_activated - N AS new_value
  FROM members AS mem
  LEFT JOIN ban_items AS bi ON (bi.id_member = mem.id_member OR mem.email_address LIKE bi.email_address)
  LEFT JOIN ban_groups AS bg ON (bg.id_ban_group = bi.id_ban_group AND bg.cannot_access = N AND (bg.expire_time IS NULL OR bg.expire_time > N))
  WHERE (bi.id_ban IS NULL OR bg.id_ban_group IS NULL)
  AND mem.is_activated >= N

---
We are running SMF 2.0.17 on mysqld 5.5.68-MariaDB

It seems it is caused by many LIKE quieries comming to the DB.

Did anyone experience same issues and have any hints how to resolve?

Thanks

Offline Aleksi "Lex" Kilpinen

  • A Peculiar Finn
  • Project Manager
  • SMF Super Hero
  • *
  • Posts: 19,935
  • Gender: Male
  • Don't worry, I'm n00b friendly
    • Aleksi.Kilpinen on Facebook
    • LexArma on GitHub
    • aleksi-kilpinen on LinkedIn
    • My Daily Dose Of Blasphemy
Re: SMF forum not available/timeout after adding person on ban list
« Reply #1 on: February 12, 2021, 08:32:13 AM »
I removed your other topic as a duplicate, based on the version information in this one.
A Finnish Project Manager (Support Specialist)
Happily running multiple SMF 2.x installations.
 Fooling around with an i7-10700 @ 2,90GHz-4.80GHz / 16Gb / RTX-2070 Super / 3840x2160 / Win 10 x64


How you can help SMF

"Before you allow people access to your forum, especially in an administrative position, you must be aware that that person can seriously damage your forum.
Therefore, you should only allow people that you trust, implicitly, to have such access." -Douglas

Offline Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 60,295
  • Gender: Male
    • Kindred-999 on GitHub
Re: SMF forum not available/timeout after adding person on ban list
« Reply #2 on: February 12, 2021, 12:49:00 PM »
how many ban records do you have?? ?? ??
Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

Offline ondrreme

  • Newbie
  • *
  • Posts: 4
Re: SMF forum not available/timeout after adding person on ban list
« Reply #3 on: February 15, 2021, 02:47:54 AM »
We have following number of ban records
MariaDB [forum]> select count(*) from ban_items;
+----------+
| count(*) |
+----------+
|    14312 |
+----------+
1 row in set (0.00 sec)
MariaDB [forum]> select count(*) from ban_groups;
+----------+
| count(*) |
+----------+
|     6178 |
+----------+
1 row in set (0.01 sec)

and also what could cause is the size of members

MariaDB [forum]> select count(*) from members;
+----------+
| count(*) |
+----------+
|  1515636 |
+----------+
1 row in set (1 min 37.44 sec)

Offline Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 60,295
  • Gender: Male
    • Kindred-999 on GitHub
Re: SMF forum not available/timeout after adding person on ban list
« Reply #4 on: February 15, 2021, 12:16:01 PM »
That's a huge member count and a really huge ban count. Why would you need over 14,000 bans?
Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

Offline shawnb61

  • Developer
  • SMF Hero
  • *
  • Posts: 3,146
    • sbulen on GitHub
Re: SMF forum not available/timeout after adding person on ban list
« Reply #5 on: February 15, 2021, 12:58:18 PM »
Yes, those are huge #s.  The query would need to be tuned differently for #s that large.  I would start with an index on the ban item email field...
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Offline ondrreme

  • Newbie
  • *
  • Posts: 4
Re: SMF forum not available/timeout after adding person on ban list
« Reply #6 on: February 16, 2021, 04:59:07 AM »
We have milions of customers so then the numbers of bans are the bad boys comming and spamming on our forum.

Is there a reason why there is LIKE statement and not equal comparison of emails?

Offline Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 60,295
  • Gender: Male
    • Kindred-999 on GitHub
Re: SMF forum not available/timeout after adding person on ban list
« Reply #7 on: February 16, 2021, 08:24:57 AM »
because you can ban based on patterns  (e.g. *@mail.ru)

also -- if you have that many bans for Spammers, then you are doing things wrong.
Stop the spammers BEFORE they get into your system -- that way you don't waste ban space on accounts which are probably use once and throw away anyway.


Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

Offline ondrreme

  • Newbie
  • *
  • Posts: 4
Re: SMF forum not available/timeout after adding person on ban list
« Reply #8 on: February 24, 2021, 10:07:58 AM »
Thanks. Do you have any recommendation who to prevent spammers to come to the forum at all which could lower the number of bans?
thanks

Offline Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 60,295
  • Gender: Male
    • Kindred-999 on GitHub
Re: SMF forum not available/timeout after adding person on ban list
« Reply #9 on: February 24, 2021, 10:56:47 AM »
Delete all the spammer accounts...


Add questions.   Add 20-30 GOOD questions and ask 1-2 during registration
(there are several threads around which indicate some suggestions on how to construct good questions -- but the core of it is: Ask questions that can not be answered by a simple google search, since the bots can actually do that these days (aka "what color is a blue bird" is a BAD question...)


You can also use some of the additional mods like Bad Behavior, HttpBL, and Stop Forum Spam/Stop Spammer

CAPTCHA in general is worthless....   reCAPTCHA is still about 60% effective.

basically, good questions are the answer. With a set of good questions, I have not had a single bot-spammer register in 3 years.
Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

Offline Matthias

  • Jr. Member
  • **
  • Posts: 164
  • Gender: Male
Re: SMF forum not available/timeout after adding person on ban list
« Reply #10 on: February 24, 2021, 06:20:26 PM »
A good way is like Kindred described.

Make a set of maybe 20-30 questions and ask 1-2 during registration. The questions could belong to the theme of your forum.

Another way is something like that:
Enter only digits: fngfeijf452mnhhj78
Enter only uppercase: kf49kdDCeieSE
...  ;)

There are millions of variations and you can change it from time to time. If spam bots get this resolved, it's all too late.  :)

Quality first