Re: random slow query - Mailing list pgsql-performance

Hi Scott,

> Well, we can't be sure OP's only got one core.

In fact, we can, Sean posted what top -b -n 1 says. There was only one
CPU line.

> the number of cores, it's the IO subsystem is too slow for the load.
> More cores wouldn't fix that.
>

While I agree on the IO, more cores would definitely help to improve
~6.5 load average.

> My production PG server that runs ONLY pg has 222 processes on it.
> It's no big deal.  Unless they're all trying to get cpu time, which
> generally isn't the case.
>
222 / 8 cores = ridiculous 27 processes per core, while the OP has 239.

> More likely just a slow IO subsystem.  Like a single drive or
> something.  adding drives in a RAID-1 or RAID-10 etc usually helps.
>

Absolutely.

> This is kernel buffers, not pg buffers.  It's set by the OS
> semi-automagically.  In this case it's 325M out of 32 Gig, so it's
> well under 10%, which is typical.
>

You can control the FS buffers indirectly by not allowing running
processes to take too much memory. If you have like 40% free, there are
good chances the system will use that memory for buffers. If you let
them eat up 90% and swap out some more, there is no room for buffers and
the system will have to swap out something when it really needs it.

> Not true.  Linux will happily swap out seldom used processes to make
> room in memory for more kernel cache etc.  You can adjust this
> tendency by setting swappiness.
>

This is fine until one of those processes wakes up. Then your FS cache
is dumped.

> It's 30G btw,

Yeah, I couldn't believe my eyes :-)

> > 3G of cached swap
> and it's not swap that's cached, it's
> the kernel using extra memory to cache data to / from the hard drives.
>

Oh please.. it *is*:
http://www.linux-tutorial.info/modules.php?name=MContent&pageid=314

>  It's normal, and shouldn't worry anybody.  In fact it's a good sign
> that you're not using way too much memory for any one process.
>

It says exactly the opposite.

> Really?  I have eight cores on my production servers and many batch
> jobs I run put all 8 cores at 90% for extended periods.  Since that
> machine is normally doing a lot of smaller cached queries, it hardly
> even notices.
>

The OP's machine is doing a lot of write ops, which is different.

> Yes, more hard drives / better caching RAID controller.
>
+1

BTW, nearly full file system can be another source of problems.

Cheers,
Mike