Обсуждение: Why very high CPU usage

Samuel,

> I ran a search query against two tables keywords and catalog.
> Keywords table has about 765,000 records and catalog hase about
> 147,000 records. The query returns 9,000 records. This query had 70 -
> 80% of CPU usage.  Are there any configuration parameter(s) that I
> need to check and change ?

And this is a problem because .... ?

High CPU usage is a *good* thing.  Under perfect tuning, Postgres would
hit about 97% CPU usage ... for about 0.1 seconds (or less, depending
on your processor).  The more CPU power used, the faster the query is
solved.

If you're having performaince problems, I suspect that they are located
somewhere other than your CPU.

-Josh Berkus

On Fri, 10 May 2002, Samuel J. Sutjiono wrote:

> This is the SQL that I used:

Can't really say much, not knowing the schema you're using.  By looking
at this query, I can probably guess it isn't pretty.  7 search columns
instead of a search table?  Putting a sort case in the query itself
instead of predetermining the sort order and dynmaically writing the
query?  That gigantic group-by isn't helping you, either.

My guess is that your schema could use an overhaul.  We of course are
going to ask you the standard questions.  I'll list them off, because
you asked your question without listing the obvious elements:

1.) Have you vacuumed/analyzed recently?
2.) What kind of indexes are you using?
3.) Can we see a schema to see what kind of column types you're using?
4.) Can you send an explain plan or two?
5.) Did you try turning off sequence scans?

Do not expect us to help you with something if you can't give us the
proper tools to do so.  We're not magical mind-readers.

--
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
| Shaun M. Thomas                INN Database Administrator           |
| Phone: (309) 743-0812          Fax  : (309) 743-0830                |
| Email: sthomas@townnews.com    AIM  : trifthen                      |
| Web  : www.townnews.com                                             |
|                                                                     |
|     "Most of our lives are about proving something, either to       |
|      ourselves or to someone else."                                 |
|                                           -- Anonymous              |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+

I have found turning off seq scans to be a mess at times.  Instead, I just
made them more expensive so that the system is very unlikely to use them.

______________________________________________________________________________

                          Your mouse has moved.
       You must restart Windows for your changes to take effect.

#!/usr/bin/perl
print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10);