Thread: Tuning 7.2? Different than 7.1.3?
I recently moved from 7.1.3 to 7.2. I am running on a Dual PIII900 Red Hat
7.1 server accessing Postgres exclusively through JDBC (JBoss app server is
on the same machine).
Since moving to 7.2, performance on part of my app really sucks. I'm sure it
is some setting or index that I'm missing...
Basically, certain queries run the CPU at 90% and I can't figure out why.
The tables are pretty simple...
Here's the scenario of a set of queries that peg the CPU:
DEBUG: query: SELECT product.rec_num FROM product , product_group AS pg
WHERE pg.name = 'Music' AND pg.rec_num = product.productgroup_products AND
product.active_status = true ORDER BY product_name
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: StartTransactionCommand
DEBUG: query: SELECT rec_num,product_name, product_id, description,
image_uri, active_status, ProductGroup_products FROM product WHERE
(rec_num=18) OR (rec_num=19)
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: StartTransactionCommand
DEBUG: query: SELECT product, rec_num FROM product_line_item WHERE
(product=18) OR (product=19)
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: StartTransactionCommand
DEBUG: query: SELECT product_id, price, description, discount, weight FROM
product_line_item WHERE (rec_num=19)
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: StartTransactionCommand
DEBUG: query: SELECT product_id, price, description, discount, weight FROM
product_line_item WHERE (rec_num=20)
The tables look like this:
Table "product"
Column | Type | Modifiers
-----------------------+-------------------+-----------
rec_num | integer | not null
active_status | boolean |
product_id | character varying |
product_name | character varying |
description | text |
image_uri | character varying |
productgroup_products | integer |
Primary key: pkproduct
Table "product_line_item"
Column | Type | Modifiers
-------------+-------------------+-----------
rec_num | integer | not null
product_id | character varying |
price | double precision |
description | text |
weight | double precision |
discount | double precision |
product | integer |
Primary key: pkproduct_line_item
Table "product_group"
Column | Type | Modifiers
---------+-------------------+-----------
rec_num | integer | not null
name | character varying |
Primary key: pkproduct_group
From this info, can anyone see why performance would really SUCK so bad? It
can take up to a minute for this to return...
product has 63 rows, product_line_item has 131 and product_group has 4.
Any help is appreciated.
Hunter
On Tue, 12 Mar 2002, Hunter Hillegas wrote: > I recently moved from 7.1.3 to 7.2. I am running on a Dual PIII900 Red Hat > 7.1 server accessing Postgres exclusively through JDBC (JBoss app server is > on the same machine). > > Since moving to 7.2, performance on part of my app really sucks. I'm sure it > is some setting or index that I'm missing... > > [...] > > >From this info, can anyone see why performance would really SUCK so bad? It > can take up to a minute for this to return... > > product has 63 rows, product_line_item has 131 and product_group has 4. > > Any help is appreciated. Have you run vacuum analyze, and what does explain show for the queries? Also, what do you have the settings in postgresql.conf (shared_buffers, etc)
vacuum (not vacuum analyze) is run every night... Just ran vacuum analyze and it showed some tables with very high "UnUsed", though I don't know what that means. Explain shows that it is indeed using the indexes I created... Is there a way to see which, if any, queries are taking a long time to execute? In postgresql.conf: max_connections = 200 shared_buffers = 400 Any other parameters in there that could affect this? Thanks, Hunter > From: Stephan Szabo <sszabo@megazone23.bigpanda.com> > Date: Tue, 12 Mar 2002 12:57:15 -0800 (PST) > To: Hunter Hillegas <lists@lastonepicked.com> > Cc: PostgreSQL <pgsql-general@postgresql.org> > Subject: Re: [GENERAL] Tuning 7.2? Different than 7.1.3? > > > On Tue, 12 Mar 2002, Hunter Hillegas wrote: > >> I recently moved from 7.1.3 to 7.2. I am running on a Dual PIII900 Red Hat >> 7.1 server accessing Postgres exclusively through JDBC (JBoss app server is >> on the same machine). >> >> Since moving to 7.2, performance on part of my app really sucks. I'm sure it >> is some setting or index that I'm missing... >> >> [...] >> >>> From this info, can anyone see why performance would really SUCK so bad? It >> can take up to a minute for this to return... >> >> product has 63 rows, product_line_item has 131 and product_group has 4. >> >> Any help is appreciated. > > Have you run vacuum analyze, and what does explain show for the > queries? Also, what do you have the settings in postgresql.conf > (shared_buffers, etc) > >
On Tue, 12 Mar 2002, Hunter Hillegas wrote: > vacuum (not vacuum analyze) is run every night... Just ran vacuum analyze > and it showed some tables with very high "UnUsed", though I don't know what > that means. > > Explain shows that it is indeed using the indexes I created... > > Is there a way to see which, if any, queries are taking a long time to > execute? Try explain analyze > In postgresql.conf: > > max_connections = 200 > shared_buffers = 400 That shared_buffer setting is pretty low for that number of connections. > Any other parameters in there that could affect this? I doubt sort_mem is likely to come up, but if there are sort steps in the query, possibly.
What value would you recommend? > From: Stephan Szabo <sszabo@megazone23.bigpanda.com> > Date: Tue, 12 Mar 2002 13:14:38 -0800 (PST) > To: Hunter Hillegas <lists@lastonepicked.com> > Cc: PostgreSQL <pgsql-general@postgresql.org> > Subject: Re: [GENERAL] Tuning 7.2? Different than 7.1.3? > >> In postgresql.conf: >> >> max_connections = 200 >> shared_buffers = 400 > > That shared_buffer setting is pretty low for that number of connections.
On Tue, 12 Mar 2002, Hunter Hillegas wrote: > What value would you recommend? It depends on the amount of RAM in the machine and usage patterns. I think the conventional wisdom I've seen expressed on list is that a few thousand is a good place to start. > > From: Stephan Szabo <sszabo@megazone23.bigpanda.com> > > Date: Tue, 12 Mar 2002 13:14:38 -0800 (PST) > > To: Hunter Hillegas <lists@lastonepicked.com> > > Cc: PostgreSQL <pgsql-general@postgresql.org> > > Subject: Re: [GENERAL] Tuning 7.2? Different than 7.1.3? > > > >> In postgresql.conf: > >> > >> max_connections = 200 > >> shared_buffers = 400 > > > > That shared_buffer setting is pretty low for that number of connections. >