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
I removed your other topic as a duplicate, based on the version information in this one.
how many ban records do you have?? ?? ??
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)
That's a huge member count and a really huge ban count. Why would you need over 14,000 bans?
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...
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?
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.
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
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.
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. :)
https://www.simplemachines.org/community/index.php?topic=575212.msg4070909#msg4070909