[dancer-users] High CPU usage on DB

[John Stoffel]

Zahir> We have a fairly complex query we run from our web app and it
Zahir> normally runs just fine. However, we have noticed that after
Zahir> while the system slows down and we see the postgres server on
Zahir> 100% CPU. It is not out of memory and not thrashing the
Zahir> disk. We have 2 app servers each with 10 workers. We use the
Zahir> Dancer database module to create a connection, and so in this
Zahir> case we have 20 connections to the DB. When we see the
Zahir> performance issue, a restart of Starman cures the problem. We
Zahir> never have to do anything in the DB, just dropping those
Zahir> Starman connections is enough to resolve.

What happens if you just run the same query using a perl DBI module
directly against your DB?  Or run 10-20 of them in succession, but
never actually exiting?  Or can you run a cut down version of your app
and recreate the problem using the apache load testing tool while you
watch the system more closely?

Does the memory used by the starman process go up when this happens?
How about the PGsql process(es)?  Do they show increased memory use?

You could even do an 'strace' on your PG process(es) to see what
they're doing and if they're looping through a bunch of poll/select
calls or something.

As others have said, it sounds like you've got an open resource that's
not being closed properly.  

John