Conversation
Notices
-
@lakwnikos @knuthollund Another thought:
3) Could this be a denial-of-service attack against quitter.se and quitter.no?? Or some misconfigured other instance flooding q.no and q.se??
Because: Some 3 weeks ago I told @knuthollund on #IRC about the issue and he said he would have a look. Soon after that the problem was completely gone and quitter.no worked flawlessly for at least the rest of the day! And moreover: quitter.se also worked flawlessly! But later it turned out @knuthollund didn't actually do anything at that point, not even restart anything.)
So I think that something on the fediverse network must have changed within those couple of hours. I think the cause of the problem is somewhere in the internet and that cause did stop its weird behaviour against quitter.se and quitter.no just for a while. Too bad the problem came back the next day.
I think one could check the logs of the webserver or use wireshark to look out for weird/excessive traffic.
!gnusocial-
@mcscx2 @lakwnikos don't think there is a ddos. A classical slow query is the following example (examine 3mill rows).
"# Query_time: 16.042286 Lock_time: 0.000140 Rows_sent: 104 Rows_examined: 3776090
# Rows_affected: 0
SET timestamp=1527533585;
SELECT id FROM notice
WHERE ( notice.created > "2018-01-14 23:36:01" ) AND ( notice.id IN (SELECT notice_id FROM reply WHERE profile_id=45991) OR notice.profile_id IN (SELECT subscribed FROM subscription WHERE subscriber=45991) OR notice.id IN (SELECT notice_id FROM group_inbox WHERE group_id IN (SELECT group_id FROM group_member WHERE profile_id=45991))OR notice.id IN (SELECT notice_id FROM attention WHERE profile_id=45991) )
ORDER BY notice.id DESC
LIMIT 0, 200;"
Also query's from the search field do a "LIKE '%yoursearch%" - which takes a long time - like does not use indexes. However, the select above (example 1) is the one reporting often as the slow queries.-
@knuthollund The query for the search field can be easily optimised with MariaDB by switching to the FULLTEXT type of search. It's not as good, but performance is a factor.
-
-
@mcscx2 @lakwnikos Ref previous comment - maybe "LIMIT ROWS EXAMIED" - should be implemented? - https://mariadb.com/kb/en/library/limit-rows-examined/
-