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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
