2011/2/13, Ruben Blanco <rubenblan@xxxxxxxxx>: > Hi: > > I'm running a Postgres database with a total disk occupation of 100Gb, > largest and most used table up to 40Gb (about 30.000.000 tuples). > > Overall performance degrades sometimes due to some queries that are not run > by the final user app. I guess they are run by Postgres itself. They use to > take up to 100% of CPU and delay user queries substantially. > > From 'pg_stat_activity', you can see this pattern in "current_query" column > for these queries: > > SELECT * FROM "public"."tablename" ORDER BY "column1", "column2"... > LIMIT 1000 OFFSET 144000 > > Sometimes with 'SET DATESTYLE = "ISO"'; before the SELECT. > > These are always SELECTs on random tables without conditions (WHERE) and > with 'ORDER BY' clause, what makes them -I guess- very heavy. > > I use to cancel these queries with "pg_cancel_backend" to recover database > functionality. > > So, what are these queries indeed? Is it advisable to cancel them? Is there > any way to prevent these situation to happen? > > I use PostgreSQL 8.4.4 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) > 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit. > > Thanks in advance for any help. > Ruben. > You check who is sending this queries. ------------ pasman -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
