Re: random slow query - Mailing list pgsql-performance

On Tue, Jun 30, 2009 at 12:22 PM, Mike Ivanov<mikei@activestate.com> wrote:
> 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.

Missed that.

>
>> 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.

No, it won't.  You can have 1000 cores, and if they're all waiting on
IO,  you'll have the same load.

>> 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.

But most of those processes are asleep and doing nothing.  My
production machine is an RHEL 5.2 machine doing only one thing really,
and it's got that many processes on it.  It's fine.


>> 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.

Close, but it'll use that memory for cache.  Large buffers are not
typical in linux, large kernel caches are.

>> 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.

Yep.

>> > 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

If that tutorial says that, then that tutorial is wrong.  I'm guessing
what that tutorial is talking about, and what top is saying are two
very different things though.

>>  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.

Sorry, but you are wrong here.  Look up a better tutorial on what the
cache entry for top means.  It's most assuredly not about swap cache,
it's kernel cache.

>> Yes, more hard drives / better caching RAID controller.
>>
>
> +1
>
> BTW, nearly full file system can be another source of problems.

Yeah, ran into that a while back, causes lots of fragmentation.