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

2011-07-25 Thread Ioana Danes 
Hi Scott,

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


--- On Fri, 7/22/11, Scott Marlowe  wrote:

> From: Scott Marlowe 
> Subject: Re: [GENERAL] Why do I have reading from the swap partition?
> To: "Ioana Danes" 
> Cc: "PostgreSQL General" 
> Received: Friday, July 22, 2011, 3:24 PM
> On Fri, Jul 22, 2011 at 12:19 PM,
> Ioana Danes 
> 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
>

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2011-07-22 Thread Radosław Smogura 

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2011-07-22 Thread Scott Marlowe 
On Fri, Jul 22, 2011 at 12:19 PM, Ioana Danes  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