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

Why do I have reading from the swap partition?

From
Ioana Danes
Date:
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
theplan 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
spaceon 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
to524288 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
restartso 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

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

From
Scott Marlowe
Date:
On Fri, Jul 22, 2011 at 12:19 PM, Ioana Danes <ioanasoftware@yahoo.ca> wrote:

> 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
spaceon disk. Once it starts swapping I still let it run for couple of minutes and the I stop it (CTRL+C). 

Are you running psql on the same machine?  My guess is that psql is
what's swapping.  Try running:

select count(*) from (select * from mybigfreakingtable);

and see if you start hitting swap like that.

Re: Why do I have reading from the swappartition?

From
Radosław Smogura
Date:
 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

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

From
Ioana Danes
Date:
Hi Scott,

Thank you for your answer, this is exactly what happens in this situation.


--- On Fri, 7/22/11, Scott Marlowe <scott.marlowe@gmail.com> wrote:

> From: Scott Marlowe <scott.marlowe@gmail.com>
> Subject: Re: [GENERAL] Why do I have reading from the swap partition?
> To: "Ioana Danes" <ioanasoftware@yahoo.ca>
> Cc: "PostgreSQL General" <pgsql-general@postgresql.org>
> Received: Friday, July 22, 2011, 3:24 PM
> On Fri, Jul 22, 2011 at 12:19 PM,
> Ioana Danes <ioanasoftware@yahoo.ca>
> wrote:
>
> > 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).
>
> Are you running psql on the same machine?  My guess is
> that psql is
> what's swapping.  Try running:
>
> select count(*) from (select * from mybigfreakingtable);
>
> and see if you start hitting swap like that.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>