Обсуждение: Why do I have reading from the swap partition?
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
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.
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
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 >