pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy very high CPU usage
4+ messages / 4 participants
[nested] [flat]
* Why very high CPU usage
@ 2002-05-10 19:38 Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-05-10 20:52 ` Re: [SQL] Why very high CPU usage Josh Berkus <josh@agliodbs.com>
2002-05-10 21:41 ` Re: Why very high CPU usage Shaun Thomas <sthomas@townnews.com>
0 siblings, 2 replies; 4+ messages in thread
From: Samuel J. Sutjiono @ 2002-05-10 19:38 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-sql
Hello,
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 ?
This is the SQL that I used:
SELECT COUNT(Catalog.CatalogSysID) as Rating, VendorName, ProductCategory,
ProductMfr, ProductModelName,
ProductDescr, ProductURL, ImageURL, Price
FROM Catalog, Keywords
WHERE (Catalog.CatalogSysID = Keywords.CatalogSysID) and
((Keywords.Keywords = SearchString1) or
(Keywords.Keywords = SearchString2) or
(Keywords.Keywords = SearchString3) or
(Keywords.Keywords = SearchString4) or
(Keywords.Keywords = SearchString5) or
(Keywords.Keywords = SearchString6) or
(Keywords.Keywords = SearchString7))
GROUP BY VendorName, ProductCategory, ProductMfr, ProductModelName,
ProductDescr, ProductURL, ImageURL, Price
ORDER BY CASE WHEN SortOrder = ''store'' Then VendorName
WHEN SortOrder = ''category'' Then ProductCategory
WHEN SortOrder = ''brand'' Then ProductMfr END
Note:
I use PostgreSQL version 7.2 runs on Compaq DL580 (2 CPUs, 2 gigabytes of RAM) and Red Hat Linux 7.2.
Postgresql.conf:
max_connections = 200
shared_buffers = 20000
sort_mem = 5120
Thanks in advance for your help.
Regards,
Samuel
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] Why very high CPU usage
2002-05-10 19:38 Why very high CPU usage Samuel J. Sutjiono <ssutjiono@wc-group.com>
@ 2002-05-10 20:52 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 4+ messages in thread
From: Josh Berkus @ 2002-05-10 20:52 UTC (permalink / raw)
To: Samuel J. Sutjiono <ssutjiono@wc-group.com>; pgsql-general@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Why very high CPU usage
2002-05-10 19:38 Why very high CPU usage Samuel J. Sutjiono <ssutjiono@wc-group.com>
@ 2002-05-10 21:41 ` Shaun Thomas <sthomas@townnews.com>
2002-05-10 23:19 ` Re: Why very high CPU usage grant <grant@amadensor.com>
1 sibling, 1 reply; 4+ messages in thread
From: Shaun Thomas @ 2002-05-10 21:41 UTC (permalink / raw)
To: Samuel J. Sutjiono <ssutjiono@wc-group.com>; +Cc: pgsql-sql
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 |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Why very high CPU usage
2002-05-10 19:38 Why very high CPU usage Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-05-10 21:41 ` Re: Why very high CPU usage Shaun Thomas <sthomas@townnews.com>
@ 2002-05-10 23:19 ` grant <grant@amadensor.com>
0 siblings, 0 replies; 4+ messages in thread
From: grant @ 2002-05-10 23:19 UTC (permalink / raw)
To: Shaun Thomas <sthomas@townnews.com>; +Cc: Samuel J. Sutjiono <ssutjiono@wc-group.com>; pgsql-general@postgresql.org; pgsql-sql
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);
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-05-10 23:19 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-05-10 19:38 Why very high CPU usage Samuel J. Sutjiono <ssutjiono@wc-group.com>
2002-05-10 20:52 ` Josh Berkus <josh@agliodbs.com>
2002-05-10 21:41 ` Shaun Thomas <sthomas@townnews.com>
2002-05-10 23:19 ` grant <grant@amadensor.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox