Re: Why do I have reading from the swap partition?

On Fri, 22 Jul 2011 11:19:13 -0700 (PDT), Ioana Danes wrote:
> Hi Everyone,
>
> I am trying to debug a slowness that is happening on one of my
> production sites and I would like to ask you for some help.
>
> This is my environment:
> -----------------------
>
> Dedicated server running:
> SUSE Linux Enterprise Server 11 (x86_64):
> VERSION = 11
> PATCHLEVEL = 1
>
> RAM = 16GB
>
> Postgres 9.0.3:
>
> shared_buffers = 4GB
> work_mem = 2MB
> maintenance_work_mem = 2GB
> wal_buffers = 1MB
> checkpoint_segments =16
> effective_cache_size = 8GB
>
> And this is my scenario:
> ------------------------
> I have a table with 16 million records and few indexes for that
> table.
> I also have a query from that table (few filters no joins) that
> returns 6.000 records. I have the proper indexes and the plan looks
> good. I don't think the query or the table structure are important
> that is why I did not post them.
>
> I reboot the server and start postgres:
>
> I run a query first time and it takes ~ 2.5 seconds
> I run the same query for the second time and it takes < 1 sec
> (because it is cached)
> All good here.
>
> Now I reboot the server again and start postgres:
>
> I do a select * from a 8 GB table (a different one then the one used
> in the query). At a point it starts using swap space on disk. Once it
> starts swapping I still let it run for couple of minutes and the I
> stop it (CTRL+C).
>
> After that I have 14 GB free memory and in postgres I only have about
> 30000 buffers used in pg_buffercache, the rest up to 524288 being
> empty.
>
> If I run my query again then the query takes 60 seconds and I notice
> reads from the swap partition.
>
> Now my question is why would I have a read from the swap partition
> when using a table that was not accessed since restart so it is not
> cached and a have a bunch of free memory and shared buffers?
>
> Could this be a postgres issue?
>
> Thank you in advance,
> Ioana
Is this "big" read. This what I can image you read big bunch of data,
those data filled memory so other parts of applications ware swapped,
when you execute next query, system need to revoke those, as well If I
remember good SysV memory may be swapped to, so If you "ate" whole
anonymous memory to keep query result then rest of SysV buffers ware
swapped. Please bear in mind when You use sequence scan PG uses rings so
You will not "touch" all shared buffers.

Regards,
Radek