Slow busy server
sebastien at aperghis.net
Thu Mar 7 12:32:18 CET 2013
Andrew Shitov wrote:
> During the last few days I experience problems with slow loading of
> the Act sites (mainly ye2013, of course, but others as well) and slow
> ssh connection. I looked at the top output and it says "load
> 18.60, 18.18, 18.80" and lists the number of postgres processes with
> big CPU usage numbers.
> Is it something to look at in sense of system administration?
Indeed. Tinita pinged me on IRC about that as well.
I took a look and tried different approaches, but I don't understand
yet what is the origin of the problem.
I first thought it was some crawlers, several were looking the wiki
diffs, which are known to be a bit costly (and probably hide some
deeper problems given they were a cause for crashing the Plack port).
So I completed the robots.txt to disallow this URL. No Effect.
Tinita suggested to completely disable the page when no logged in.
I live-added the few lines of code to do that. No effect (at least
I didn't break anything).
My PostgreSQL-fu has weaken over the years...
Ah! finally found again "SELECT * FROM pg_stat_activity;"
Lots of "SELECT COUNT(*) FROM participations ..."
That's a request from Act::Config::get_config()
Running EXPLAIN on the different sub-queries; seems like there are
already indexes at most of the places where it's needed.
Running a bit out of ideas except putting a Varnish in front of Apache
for a more aggressive cache. Won't do that right now obviously because
I need to get some $work done. Will try to do so tonight.
Close the world, txEn eht nepO.
More information about the Act