Why very high CPU usage
От | Samuel J. Sutjiono |
---|---|
Тема | Why very high CPU usage |
Дата | |
Msg-id | 395901c1f85a$38cf2850$110a010a@headquarters.wcgroup.com обсуждение исходный текст |
Ответы |
Re: Why very high CPU usage
Re: [GENERAL] Why very high CPU usage |
Список | 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
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
shared_buffers = 20000
sort_mem = 5120
Thanks in advance for your help.
Regards,
Samuel
В списке pgsql-sql по дате отправления: