Re: Heavy queries not run by user application

2011/2/13, Ruben Blanco <rubenblan(at)gmail(dot)com>:
> 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