Re: random slow query

Well, this is going to be a bit redundant but:

On 6/30/09 11:22 AM, "Mike Ivanov" <mikei(at)activestate(dot)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.

I do not believe that setting means what you think it means. Here is the
same output for a machine with two quad-core cpus.

$ top -b -n 1
top - 12:43:06 up 264 days, 1:47, 5 users, load average: 0.24, 0.25, 0.71
Tasks: 253 total, 1 running, 252 sleeping, 0 stopped, 0 zombie
Cpu(s): 5.1%us, 0.5%sy, 0.0%ni, 93.9%id, 0.5%wa, 0.0%hi, 0.1%si,
0.0%st
Mem: 16432232k total, 13212684k used, 3219548k free, 5992k buffers
Swap: 2040244k total, 180k used, 2040064k free, 7775732k cached

From the man page:

When you see ¹Cpu(s):¹ in the summary area, the ¹1¹ toggle is On and all
cpu information is gathered in a single
line. Otherwise, each cpu is displayed separately as: ¹Cpu0,
Cpu1, ...¹

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

Load average is one of the more useless values to look at on a system unless
you are looking at a DELTA of the load average from one condition to
another. All alone, it doesn't say much.

The CPU was 60% idle, and ~35% in io wait. If those processes were waiting
on CPU resources to be available, the idle % would be very low. Or, the OS
scheduler is broken.

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

That's not rediculous at all. Modern OS's handle thousands of idle
processes just fine.

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

Or you can control the behavior with the following kenrnel params:
vm.swappiness
vm.dirty_ratio
vm.dirty_background ratio

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

Actually, no. When a process wakes up only the pages that are needed are
accessed. For most idle processes that wake up from time to time, a small
bit of work is done, then they go back to sleep. This initial allocation
does NOT come from the page cache, but from the "buffers" line in top. The
os tries to keep some ammount of free buffers not allocated to processes or
pages available, so that allocation demands can be met without having to
synchronously decide which buffers from page cache to eject.

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

There is no such thing as "cached swap". What would there be to cache? A
process' page is either in RAM or swap, and a file is either in buffer cache
or not.
That line entry is the size of the file page cache.
Read about 'free' and compare the values to top.

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

It says a ton of space is used caching files.

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

Not when it comes to CPU use percentage. The overlap with disk I/O and CPU
on linux shows up in time spent by the kernel (system time), and often
kswapd processor time (shows up as system time). Everything else is i/o
wait.

The OP has a I/O bottleneck. Suggestions other than new hardware:

* Put the xlogs on a separate partition and if ext3 mount with
data=writeback or use ext2.
* Use the deadline scheduler.

If queries are intermittently causing problems, it might be due to
checkpoints. Make sure that the kernel parameters for
dirty_background_ratio is 5 or less, and dirty_ratio is 10 or less.

Search this group for information about tuning postgres checkpoints.

If using a hardware RAID card with a battery back-up, make sure its cache
mode is set to write-back.

A larger shared_buffers size can help if sequential scans are infrequent and
kick out pages from the OS page cache.
Postgres does not let sequential scans kick out index pages or pages
accessed randomly from its buffer cache, but the OS (Linux) is more prone to
that.

Whether larger or smaller shared_buffers will help is HIGHLY load and use
case dependant.