Thread: Heavy queries not run by user application

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.

2011/2/13, Ruben Blanco <rubenblan@gmail.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

On 02/12/11 5:11 PM, Ruben Blanco wrote:
> 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.
>



there's nothing like that run by postgres itself automagically, it must
be some software at your end you're not aware of.

  in pg_stat_activity, check usename, client_addr and client_port, and
match this up against netstat or whatever activity to determine what
application is making these queries.

Thanks a lot, guys. There were two users running Navicat, and these killer queries are indeed run by this program.

Rubén.