Topic: SMF forum not available/timeout after adding person on ban list

[ondrreme]

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

[Aleksi "Lex" Kilpinen]

I removed your other topic as a duplicate, based on the version information in this one.

[Kindred]

how many ban records do you have?? ?? ??

[ondrreme]

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)

[Kindred]

That's a huge member count and a really huge ban count. Why would you need over 14,000 bans?

[shawnb61]

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...

[ondrreme]

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?

[Kindred]

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.

[ondrreme]

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

[Kindred]

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.

[Matthias]

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. 

[efk]

https://www.simplemachines.org/community/index.php?topic=575212.msg4070909#msg4070909