Re: 100% CPU Utilization when we run queries.

From: Tomas Vondra
Subject: Re: 100% CPU Utilization when we run queries.
Date: ,
Msg-id: 4E14B1AD.40306@fuzzy.cz
(view: Whole thread, Raw)
In response to: Re: 100% CPU Utilization when we run queries.  (bakkiya)
Responses: Re: 100% CPU Utilization when we run queries.  (Scott Marlowe)
Re: 100% CPU Utilization when we run queries.  (Craig Ringer)
Re: 100% CPU Utilization when we run queries.  (Robert Klemme)
List: pgsql-performance

Tree view

100% CPU Utilization when we run queries.  (bakkiya, )
 Re: 100% CPU Utilization when we run queries.  (Craig Ringer, )
  Re: 100% CPU Utilization when we run queries.  (bakkiya, )
   Re: 100% CPU Utilization when we run queries.  (Craig Ringer, )
    Re: 100% CPU Utilization when we run queries.  (bakkiya, )
     Re: 100% CPU Utilization when we run queries.  (bakkiya, )
      Re: 100% CPU Utilization when we run queries.  ("Kevin Grittner", )
      Re: 100% CPU Utilization when we run queries.  (Tomas Vondra, )
       Re: 100% CPU Utilization when we run queries.  (Scott Marlowe, )
       Re: 100% CPU Utilization when we run queries.  (Craig Ringer, )
        Re: 100% CPU Utilization when we run queries.  (bakkiya, )
       Re: 100% CPU Utilization when we run queries.  (Robert Klemme, )
 Re: 100% CPU Utilization when we run queries.  (Marti Raudsepp, )

Dne 6.7.2011 15:30, bakkiya napsal(a):
> Any help, please?

According to the EXPLAIN ANALYZE output (please, don't post it to the
mailing list directly - use something like explain.depesz.com, I've done
that for you this time: http://explain.depesz.com/s/HMN), you're doing a
UNIQUE over a lot of data (2 million rows, 1.5GB).

That is done by sorting the data, and sorting is very CPU intensive task
usually. So the fact that the CPU is 100% utilized is kind of expected
in this case. So that's a feature, not a bug.

In general each process is hitting some bottleneck. It might be an I/O,
it might be a CPU, it might be something less visible (memory bandwidth
or something like that).

But I've noticed one thing in your query - you're doing a UNIQUE in the
view (probably, we don't know the definition) and then once again in the
query (but using just one column from the view).

The problem is the inner sort does not remove any rows (1979735 rows
in/out). Why do you do the UNIQUE in the view? Do you really need it
there? I guess removing it might significantly improve the plan.

Try to do the query without the view - it seems it's just an union of
current tables and a history (both partitioned, so do something like this)

SELECT DISTINCT init_service_comp FROM (
  SELECT init_service_comp FROM events
  UNION
  SELECT init_service_comp FROM hist_events
)

or maybe even

SELECT DISTINCT init_service_comp FROM (
  SELECT DISTINCT init_service_comp FROM events
  UNION
  SELECT DISTINCT init_service_comp FROM hist_events
)

Let's see how that works - post EXPLAIN ANALYZE using explain.depesz.com

Tomas


pgsql-performance by date:

From: Craig Ringer
Date:
Subject: Re: 100% CPU Utilization when we run queries.
From: Clem Dickey
Date:
Subject: Re: GROUP BY with reasonable timings in PLAN but unreasonable execution time