News:

SMF 2.1.4 has been released! Take it for a spin! Read more.

Main Menu

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

Started by ondrreme, February 12, 2021, 07:25:24 AM

Previous topic - Next topic

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 hxxp:mem.id [nonactive]_member, hxxp:mem.is [nonactive]_activated - N AS new_value
  FROM members AS mem
  LEFT JOIN ban_items AS bi ON (bi.id_member = hxxp:mem.id [nonactive]_member OR hxxp:mem.email [nonactive]_address LIKE hxxp:bi.email [nonactive]_address)
  LEFT JOIN ban_groups AS bg ON (bg.id_ban_group = hxxp:bi.id [nonactive]_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 hxxp:bg.id [nonactive]_ban_group IS NULL)
  AND hxxp:mem.is [nonactive]_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.
Slava
Ukraini!
"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

How you can help SMF

Kindred

Слaва
Украинi

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

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?
Слaва
Украинi

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

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...
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

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.


Слaва
Украинi

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

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.
Слaва
Украинi

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

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

Quality first


Advertisement: