Slow busy server

[Sébastien Aperghis-Tramoni]

Andrew Shitov wrote:

> Hi,

Hello Andrew,

> 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
> averages:
> 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()
» https://github.com/book/Act/blob/master/lib/Act/Config.pm#L299

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.

-- 
Sébastien Aperghis-Tramoni

Close the world, txEn eht nepO.