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