Hi
Every third or so page loads take from 30-60 seconds to load. Could someone take a look at the forum server and see if the situation can be remedied. This has been going on for several days now…
Best regards,
Tommi
We noticed, thanks.
We’re getting through issues with the server and, from what i got from the discussions, there are some SQL queries issued by word press that stall the db. @Kwando and @Jayfella are actively working on it, to find a solution.
1 Like
LOL… not to mention timeouts yesterday non stop. I could barely load a page an hour, retrying every now and then…
ORLY? From SQL queries? Did you profile the SQL driver script to find the culprit? Coming from Wordpress, there must be quite a lot of unoptimized queries out there. Now imagine if somebody wants to do JME harm, they could spam the script to overload the server. We’d be better programming a forum from scratch, but it would take a lot of time and effort :-/
Did you consider looking at the TCP connections in real time? Maybe the culprit is there. It’s DDoS-like attacks. If our server is suffering from that, we could throttle/limit Apache queries by IP, it would be a good start and give everybody a fair chance of loading pages. Maybe use some firewall software like ConfigServer Security & Firewall. It’s open source and really helped me kill those spammers on mine. At one point I had more than 80,000 inbound connections tarpitting each SECOND eventually killing the whole machine. My server once crashed because of this and then I put a firewall rules together and found a sweet spot that acts like a connection throttler. I found that robots spamming are often trying for the port 25 for SMTP and so only by blacklisting IP’s having the wrong SMTP password 5x in less than 30 seconds will cut the used network ressources by HALF. So many web servers suffer from DDoS-like attacks nowadays, you have to keep a close eye on this every now and then. Robot techniques evolve each year.
We are not under a massive DDoS-attack. Turns out the way WP/bbpress queries “latest posts” from the database is to use a very inefficient query. Our forum software is forcing mysql to do a table scan on a large temporary table which is to large to handle in memory and will hit disk. The query also uses SQL_CALC_FOUND_ROWS which causes a table lock on the table containing all forums posts and every query needs to wait for the lock to be released.
The bad news is there is no easy fix for it, what we have is a combo of a large database and a bad database schema. The good news is that I might have monkey patch that will get rid of the table lock but it will not remove the bad query from “latest posts”-page.
Wait the query basically does select all topics latest posts and sort them or?
Is there no possibility to put some kind of LIMIT in it or is the schema to strange??
@kwando
Why don’t you simply remove the SQL_CALC_FOUND_ROWS and use the same query within COUNT() but only select using the primary column, this is in my experience MUCH faster than SQL_CALC_FOUND_ROWS, especially when SQL_CALC_FOUND_ROWS is selecting much data with a complex where that is missing some indices.
The query is generated by Wordpress/BBpress and “just remove SQL_CALC_FOUND_ROWS” isn’t that easy… I have a small patch that splits those kind of queries into one SELECT and one COUNT query. That is getting rid of the table lock, but the nasty join and sort on a LONGTEXT column without index still hurts performance.
I don’t know quite nothing about the WP/BP/SQL design, but would it be possible to add onCreate triggers for replys/threads and by that having a separate table containing the changes?
@zzuegg yeah, the trigger part would be easy… but the database query is generated by WP/BP so I’m not sure how viable it would be to replace that WP-library call with something custom.
@kwando said: The query is generated by Wordpress/BBpress and "just remove SQL_CALC_FOUND_ROWS" isn't that easy... I have a small patch that splits those kind of queries into one SELECT and one COUNT query. That is getting rid of the table lock, but the nasty join and sort on a LONGTEXT column without index still hurts performance.Ah alright, I've never worked with wordpress but I can understand how it can complicate things alot :p Anyways, I'm a web developer (mostly) and have some spare time, so if there's anything I can help with, I'd love to help out, its the least I could do to contribute back to this community for this engine I'll hopefully soon be using alot!