Re: Sudden Query slowdown on our Postgresql Server

On Fri, Mar 23, 2012 at 3:52 AM, Sebastian Melchior <webmaster(at)mailz(dot)de> wrote:
> unfortunately we cannot directly control the TRIM (i am not sure it even occurs) because the SSDs are behind an LSI MegaSAS 9260 Controller which does not allow access via smart. Even if some kind of TRIM command is the problem, shouldn't the iowait go up in this case?

Based on my recent benchmarking experiences, maybe not. Suppose
backend A takes a lock and then blocks on an I/O. Then, all the other
backends block waiting on the lock. So maybe one backend is stuck in
I/O-wait, but on a multi-processor system the percentages are averaged
across all CPUs, so it doesn't really look like there's much I/O-wait.
If you have 'perf' available, I've found the following quite helpful:

perf record -e cs -g -a sleep 30
perf report -g

Then you can look at the report and find out what's causing PostgreSQL
to context-switch out - i.e. block - and therefore find out what lock
and call path is contended. LWLocks don't show up in pg_locks, so you
can't troubleshoot this sort of contention that way.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company