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