Thread: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
Hello great gurus of performance: Our 'esteemed' Engr group recently informed a customer that in their testing, upgrading to 8.2.x improved the performance of our J2EE application "approximately 20%", so of course, the customer then tasked me with upgrading them. We dumped their db, removed pgsql, installed the 8.2.4 rpms from postgresql.org, did an initdb, and the pg_restored their data. It's been about a week now, and the customer is complaining that in their testing, they are seeing a 30% /decrease/ in general performance. Of course, our Engr group is being less than responsive, and I have a feeling all they're doing is googling for answers, so I'm turning to this group for actual assistance :) I'd like to start by examining the poistgresql.conf file. Under 7.4.x, we had spent the better part of their 2 years as a customer tuning and tweaking setting. I've attached the file that was in place at the time of upgrade. I did some cursory googling of my own, and quickly realized that enough has changed in v8 that I'm not comfortable making the exact same modification to their new config file as some options are new, some have gone away, etc. I've attached the existing v8 conf file as well. I'd really like it if someone could assist me in determining which of the v8 options need adjusted to be 'functionally equivalent' to the v7 file. Right now, my goal is to get the customer back to the previous level of performance, and only then pursue further optimization. I can provide any and all information needed, but didn't know what to include initially, so I've opted to include the minimal :) The DB server in question does nothing else, is running CentOS 4.5, kernel 2.6.9-55.ELsmp. Hyperthreading is disabled in the BIOS and there are 2 Xeon 3.4Ghz cpus. There is 8Gb of RAM in the machine, and another 8Gb of swap. Thank you in advance for any and all assistance you can provide. -- Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778 http://doug.hunley.homeip.net Handy Guide to Modern Science: 1. If it's green or it wiggles, it's biology. 2. If it stinks, it's chemistry. 3. If it doesn't work, it's physics.
Attachment
Douglas J Hunley wrote: Hello > The DB server in question does nothing else, is running CentOS 4.5, kernel > 2.6.9-55.ELsmp. Hyperthreading is disabled in the BIOS and there are 2 Xeon > 3.4Ghz cpus. There is 8Gb of RAM in the machine, and another 8Gb of swap. > After a very quick read of your configuration files, I found some paramaters that need to be change if your server has 8GB of RAM. The values of these parameters depend a lot of how much RAM you have, what type of database you have (reading vs. writing) and how big the database is. I do not have experience with 8.2.x yet, but with 8.1.x we are using as defaults in out 8GB RAM servers these values in some of the paramaters (they are not the only ones, but they are the minimum to change): 25% of RAM for shared_buffers 2/3 of ram for effective_cache_size 256MB for maintenance_work_mem 32-64MB for work_mem 128 checkpoint_segments 2 random_page_cost And the most important of all: fsync should be ***ON*** if you appreciate your data. It looks like you are using default values .... > > #shared_buffers = 32MB # min 128kB or max_connections*16kB > #work_mem = 1MB # min 64kB > #maintenance_work_mem = 16MB # min 1MB > fsync = off # turns forced synchronization on or off > #effective_cache_size = 128MB [........................] -- Rafael Martinez, <r.m.guerrero@usit.uio.no> Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/>
On Sat, Jun 02, 2007 at 09:13:32AM -0400, Douglas J Hunley wrote: > Our 'esteemed' Engr group recently informed a customer that in their testing, > upgrading to 8.2.x improved the performance of our J2EE > application "approximately 20%", so of course, the customer then tasked me > with upgrading them. We dumped their db, removed pgsql, installed the 8.2.4 > rpms from postgresql.org, did an initdb, and the pg_restored their data. It's > been about a week now, and the customer is complaining that in their testing, > they are seeing a 30% /decrease/ in general performance. After the restore, did you ANALYZE the entire database to update the planner's statistics? Have you enabled autovacuum or are you otherwise vacuuming and analyzing regularly? What kind of queries are slower than desired? If you post an example query and the EXPLAIN ANALYZE output then we might be able to see if the slowness is due to query plans. A few differences between the configuration files stand out. The 7.4 file has the following settings: shared_buffers = 25000 sort_mem = 15000 effective_cache_size = 196608 The 8.2 config has: #shared_buffers = 32MB #work_mem = 1MB #effective_cache_size = 128MB To be equivalent to the 7.4 config the 8.2 config would need: shared_buffers = 195MB work_mem = 15000kB effective_cache_size = 1536MB With 8GB of RAM you might try increasing shared_buffers to 400MB - 800MB (less if the entire database isn't that big) and effective_cache_size to 5GB - 6GB. You might have to increase the kernel's shared memory settings before increasing shared_buffers. Some of the other settings are the same between the configurations but deserve discussion: fsync = off Disabling fsync is dangerous -- are all parties aware of the risk and willing to accept it? Has the risk been weighed against the cost of upgrading to a faster I/O subsystem? How much performance benefit are you realizing by disabling fsync? What kind of activity led to the decision to disable fynsc? Are applications doing anything like executing large numbers of insert/update/delete statements outside of a transaction block when they could be done in a single transaction? commit_delay = 20000 commit_siblings = 3 What kind of activity led to the above settings? Are they a guess or were they determined empirically? How much benefit are they providing and how did you measure that? enable_mergejoin = off geqo = off I've occasionally had to tweak planner settings but I prefer to do so for specific queries instead of changing them server-wide. -- Michael Fuhr
Douglas J Hunley <doug@hunley.homeip.net> writes:
> ... We dumped their db, removed pgsql, installed the 8.2.4
> rpms from postgresql.org, did an initdb, and the pg_restored their data. It's
> been about a week now, and the customer is complaining that in their testing,
> they are seeing a 30% /decrease/ in general performance.
Well, you've definitely blown it on transferring the config-file
settings --- a quick look says that shared_buffers, work_mem, and
max_fsm_pages are all still default in the 8.2 config file.
Don't be frightened off by the "KB/MB" usages in the 8.2 file ---
you can still write "shared_buffers = 25000" if you'd rather specify
it in number of buffers than in megabytes.
There are some things you *did* transfer that I find pretty
questionable, like "enable_mergejoin = false". There are very major
differences between the 7.4 and 8.2 planners, so you need to revisit
the tests that led you to do that.
Another thing that seems strange is that the 8.2 config file does not
seem to have been processed by initdb --- or did you explicitly comment
out the settings it made?
Another thing to check is whether you ANALYZEd the new database after
loading data; a pg_dump/reload sequence doesn't do that.
regards, tom lane
Michael Fuhr wrote: > On Sat, Jun 02, 2007 at 09:13:32AM -0400, Douglas J Hunley wrote: >> Our 'esteemed' Engr group recently informed a customer that in their testing, >> upgrading to 8.2.x improved the performance of our J2EE >> application "approximately 20%", so of course, the customer then tasked me >> with upgrading them. We dumped their db, removed pgsql, installed the 8.2.4 >> rpms from postgresql.org, did an initdb, and the pg_restored their data. It's >> been about a week now, and the customer is complaining that in their testing, >> they are seeing a 30% /decrease/ in general performance. > > After the restore, did you ANALYZE the entire database to update > the planner's statistics? Have you enabled autovacuum or are you > otherwise vacuuming and analyzing regularly? What kind of queries > are slower than desired? If you post an example query and the > EXPLAIN ANALYZE output then we might be able to see if the slowness > is due to query plans. > > A few differences between the configuration files stand out. The > 7.4 file has the following settings: > > shared_buffers = 25000 > sort_mem = 15000 > effective_cache_size = 196608 > > The 8.2 config has: > > #shared_buffers = 32MB > #work_mem = 1MB > #effective_cache_size = 128MB > > To be equivalent to the 7.4 config the 8.2 config would need: > > shared_buffers = 195MB > work_mem = 15000kB > effective_cache_size = 1536MB > > With 8GB of RAM you might try increasing shared_buffers to 400MB - 800MB > (less if the entire database isn't that big) and effective_cache_size > to 5GB - 6GB. You might have to increase the kernel's shared memory > settings before increasing shared_buffers. some testing here has shown that while it is usually a good idea to set effective_cache_size rather optimistically in versions <8.2 it is advisable to make it accurate or even a bit less than that in 8.2 and up. Stefan
On Saturday 02 June 2007 11:21:41 Michael Fuhr wrote: > After the restore, did you ANALYZE the entire database to update > the planner's statistics? Have you enabled autovacuum or are you > otherwise vacuuming and analyzing regularly? What kind of queries > are slower than desired? If you post an example query and the > EXPLAIN ANALYZE output then we might be able to see if the slowness > is due to query plans. I forgot to mention that. Yes, we did: vacuumdb -a -f -v -z We have not yet turned on autovacuum. That was next on our list, and then customer started in w/ the performance. We are doing an 'analyze table' followed by 'vacuum table' on a periodic basis, but I'll have to wait till I'm in the office on Monday to see what that schedule is (customer only allows us to VPN from work) > > A few differences between the configuration files stand out. The > 7.4 file has the following settings: > > shared_buffers = 25000 > sort_mem = 15000 > effective_cache_size = 196608 > > The 8.2 config has: > > #shared_buffers = 32MB > #work_mem = 1MB > #effective_cache_size = 128MB > > To be equivalent to the 7.4 config the 8.2 config would need: > > shared_buffers = 195MB > work_mem = 15000kB > effective_cache_size = 1536MB > > With 8GB of RAM you might try increasing shared_buffers to 400MB - 800MB > (less if the entire database isn't that big) and effective_cache_size > to 5GB - 6GB. You might have to increase the kernel's shared memory > settings before increasing shared_buffers. > We have the following in sysctl.conf: kernel.shmmax=2147483648 kernal.shmall=2097152 kernel.sem = 250 32000 100 128 which should be sufficient, no? > Some of the other settings are the same between the configurations > but deserve discussion: > > fsync = off > > Disabling fsync is dangerous -- are all parties aware of the risk > and willing to accept it? Has the risk been weighed against the > cost of upgrading to a faster I/O subsystem? How much performance > benefit are you realizing by disabling fsync? What kind of activity > led to the decision to disable fynsc? Are applications doing > anything like executing large numbers of insert/update/delete > statements outside of a transaction block when they could be done > in a single transaction? Yes, they're aware. This is a temporary setting while they order upgraded SAN devices. Currently, the I/O on the boxes is horrific. > > commit_delay = 20000 > commit_siblings = 3 > > What kind of activity led to the above settings? Are they a guess > or were they determined empirically? How much benefit are they > providing and how did you measure that? Those are based on a thread their (non-pgsql) DBA found online. I'm perfectly willing to discount him if so advised. > > enable_mergejoin = off > geqo = off > > I've occasionally had to tweak planner settings but I prefer to do > so for specific queries instead of changing them server-wide. I concur. Unfortunately, our Engr group don't actually write the SQL for the app. It's generated, and is done in such a fashion as to work on all our supported dbs (pgsql, oracle, mysql). Thanks a ton for the input thus far -- Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778 http://doug.hunley.homeip.net Anything worth shooting is worth shooting twice. Ammo is cheap. Life is expensive.
On Saturday 02 June 2007 11:25:11 Tom Lane wrote: > Another thing that seems strange is that the 8.2 config file does not > seem to have been processed by initdb --- or did you explicitly comment > out the settings it made? I don't understand this comment. You are saying 'initdb' will make changes to the file? The file I sent is the working copy from the machine in question. -- Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778 http://doug.hunley.homeip.net "Does it worry you that you don't talk any kind of sense?"
When you initdb, a config file is edited from the template by initdb to reflect your machine config.
- Luke
Msg is shrt cuz m on ma treo
-----Original Message-----
From: Douglas J Hunley [mailto:doug@hunley.homeip.net]
Sent: Sunday, June 03, 2007 02:30 PM Eastern Standard Time
To: Tom Lane
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
On Saturday 02 June 2007 11:25:11 Tom Lane wrote:
> Another thing that seems strange is that the 8.2 config file does not
> seem to have been processed by initdb --- or did you explicitly comment
> out the settings it made?
I don't understand this comment. You are saying 'initdb' will make changes to
the file? The file I sent is the working copy from the machine in question.
--
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net
"Does it worry you that you don't talk any kind of sense?"
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
On Sun, 3 Jun 2007, Douglas J Hunley wrote: >> commit_delay = 20000 >> commit_siblings = 3 > Those are based on a thread their (non-pgsql) DBA found online. I'm perfectly > willing to discount him if so advised. Those likely came indirectly from the otherwise useful recommendations at http://www.wlug.org.nz/PostgreSQLNotes , that's the first place I saw that particular combination recommended at. The fact that you mention a thread makes me guess your DBA found https://kb.vasoftware.com/index.php?x=&mod_id=2&id=20 , which is a completely bogus set of suggestions. Anyone who gives out a blanket recommendation for any PostgreSQL performance parameter without asking questions first about things like your memory and your disk setup doesn't really know what they're doing, and I'd suggest discounting the entirety of that advice. Those commit_ values are completely wrong for many workloads; they're introducing a 20ms delay into writes as soon as there are more then 3 clients writing things at once. If someone just took those values from a web page without actually testing them out, you'd be better off turning both values back to the defaults (which disables the feature) and waiting until you have some time to correctly determine useful settings for your system. Note that with fsync=off, I don't think that's actually doing anything right now so it's kind of irrelevant to get excited about; should be addressed before fsync gets turned back on though. Also: some of the recommendations you've been getting for shared_buffers are on the low side as far as I'm concerned. You should consider maxing that value out at 262143 (2GB of RAM) on your server with 8GB of RAM available, then putting effective_cache_size at 5GB or so. That may require just a bit more upward tweaking of your kernel parameters to support. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Douglas J Hunley <doug@hunley.homeip.net> writes:
> On Saturday 02 June 2007 11:25:11 Tom Lane wrote:
>> Another thing that seems strange is that the 8.2 config file does not
>> seem to have been processed by initdb --- or did you explicitly comment
>> out the settings it made?
> I don't understand this comment. You are saying 'initdb' will make changes to
> the file? The file I sent is the working copy from the machine in question.
Yeah --- in a normal installation, initdb will provide un-commented
entries for these postgresql.conf parameters:
max_connections
shared_buffers
max_fsm_pages
datestyle
lc_messages
lc_monetary
lc_numeric
lc_time
(The first three are set dependent on SHMMAX probing, the others
dependent on locale.) Your conf file doesn't seem to have been through
that autoconfiguration step, which suggests someone poking at things
they should have left alone.
regards, tom lane
On Sunday 03 June 2007 16:39:51 Luke Lonergan wrote: > When you initdb, a config file is edited from the template by initdb to > reflect your machine config. I didn't realize that. I'll have to harass the rest of the team to see if someone overwrote that file or not. In the interim, I did an 'initdb' to another location on the same box and then copied those values into the config file. That's cool to do, I assume? -- Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778 http://doug.hunley.homeip.net Cowering in a closet is starting to seem like a reasonable plan.
Douglas J Hunley wrote: > On Sunday 03 June 2007 16:39:51 Luke Lonergan wrote: >> When you initdb, a config file is edited from the template by initdb to >> reflect your machine config. > > I didn't realize that. I'll have to harass the rest of the team to see if > someone overwrote that file or not. In the interim, I did an 'initdb' to > another location on the same box and then copied those values into the config > file. That's cool to do, I assume? Yeah, that's ok. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Absolutely!
A summary of relevant comments so far are:
- enable-mergejoin
- shared-buffers
- fsync
Another to consider if you use indexes is random-page-cost.
What would be helpful is if you could identify a slow query and post the explain analyze here.
The concurrent performance of many users should just be faster with 8.2, so I'd think it's a problem with plans.
- Luke
Msg is shrt cuz m on ma treo
-----Original Message-----
From: Douglas J Hunley [mailto:doug@hunley.homeip.net]
Sent: Monday, June 04, 2007 08:40 AM Eastern Standard Time
To: Luke Lonergan
Cc: Tom Lane; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
On Sunday 03 June 2007 16:39:51 Luke Lonergan wrote:
> When you initdb, a config file is edited from the template by initdb to
> reflect your machine config.
I didn't realize that. I'll have to harass the rest of the team to see if
someone overwrote that file or not. In the interim, I did an 'initdb' to
another location on the same box and then copied those values into the config
file. That's cool to do, I assume?
--
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net
Cowering in a closet is starting to seem like a reasonable plan.
On Sun, 2007-06-03 at 23:17 -0400, Tom Lane wrote: > Douglas J Hunley <doug@hunley.homeip.net> writes: > > On Saturday 02 June 2007 11:25:11 Tom Lane wrote: > >> Another thing that seems strange is that the 8.2 config file does not > >> seem to have been processed by initdb --- or did you explicitly comment > >> out the settings it made? > > > I don't understand this comment. You are saying 'initdb' will make changes to > > the file? The file I sent is the working copy from the machine in question. > > Yeah --- in a normal installation, initdb will provide un-commented > entries for these postgresql.conf parameters: > > max_connections > shared_buffers > max_fsm_pages > datestyle > lc_messages > lc_monetary > lc_numeric > lc_time > > (The first three are set dependent on SHMMAX probing, the others > dependent on locale.) Your conf file doesn't seem to have been through > that autoconfiguration step, which suggests someone poking at things > they should have left alone. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org A WAG, but perhaps the new conf file was overwritten after installation with the one from the 'old' installation '..because that's the configuration that we've already tweaked and was working...'
On Sunday 03 June 2007 18:30:17 Greg Smith wrote: > To be equivalent to the 7.4 config the 8.2 config would need: I've taken all the wonderful advise offered thus far, and put the attached into use. Our initial testing shows a 66% improvement in page load times for our app. I have the customer beating on things and noting anything that is still slow. On a side note, is there any real benefit to using autovacuum over a periodically scheduled vacuum? I ask because we have the latter already coded up and cron'd and it seems to keep things fairly optimized. BTW, I'm on the list, so there's no need to reply direct. I can get the replies from the list Thanks again for everyone's assistance thus far. Y'all rock! -- Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778 http://doug.hunley.homeip.net I feel like I'm diagonally parked in a parallel universe...
Attachment
Douglas J Hunley wrote: > On a side note, is there any real benefit to using autovacuum over a > periodically scheduled vacuum? I ask because we have the latter already coded > up and cron'd and it seems to keep things fairly optimized. No, not really. Maybe autovacuum could get to specific highly-updated tables quickier than the cron job, or slow down when there's no activity; but your current setup is good enough for you there's no reason to change. > BTW, I'm on the list, so there's no need to reply direct. I can get the > replies from the list Huh, sorry, this is just the customary way to use these lists. Personally, I prefer to get several copies of each message and have my software (procmail) deliver only one to me discarding the duplicates. That way, if one is lost or takes long to get home, I don't even notice it (it used to happen a lot on the lists). Look at the "eliminatecc" option in the Majordomo user web pages. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Saturday 02 June 2007 11:21:41 Michael Fuhr wrote:
> If you post an example query and the
> EXPLAIN ANALYZE output then we might be able to see if the slowness
> is due to query plans.
Query 1:
(SELECT
project.path AS rbac_project_path_string,
role_operation.resource_name AS rbac_resource_name,
role_operation.resource_value AS rbac_resource_value
FROM
project project,
role role,
role_default_user role_default_user,
role_operation role_operation
WHERE
role.id=role_default_user.role_id
AND role_default_user.project_id=project.id
AND role.id=role_operation.role_id
AND role.is_deleted=false
AND role_operation.object_type_id='Scm.Repository'
AND role_operation.operation_category='use'
AND role_operation.operation_name='access'
AND project.path='projects.barnes_and_nobles_college_bookse_3'
AND project.is_deleted=false
AND role_default_user.default_user_class_id='1'
)
UNION
(SELECT
project.path AS rbac_project_path_string,
role_operation.resource_name AS rbac_resource_name,
role_operation.resource_value AS rbac_resource_value
FROM
project project,
role role,
role_default_user role_default_user,
role_operation role_operation
WHERE
role.id=role_default_user.role_id
AND role_default_user.project_id=project.id
AND role.id=role_operation.role_id
AND role.is_deleted=false
AND role_operation.object_type_id='Scm.Repository'
AND role_operation.operation_category='use'
AND role_operation.operation_name='access'
AND project.path='projects.barnes_and_nobles_college_bookse_3'
AND project.is_deleted=false
AND role_default_user.default_user_class_id='2'
)
UNION
(SELECT
project.path AS rbac_project_path_string,
role_operation.resource_name AS rbac_resource_name,
role_operation.resource_value AS rbac_resource_value
FROM
project project,
role role,
role_default_user role_default_user,
role_operation role_operation
WHERE
role.id=role_default_user.role_id
AND role_default_user.project_id=project.id
AND role.id=role_operation.role_id
AND role.is_deleted=false
AND role_operation.object_type_id='Scm.Repository'
AND role_operation.operation_category='use'
AND role_operation.operation_name='access'
AND project.path='projects.barnes_and_nobles_college_bookse_3'
AND project.is_deleted=false
AND role_default_user.default_user_class_id='3'
)
UNION
(SELECT
project.path AS rbac_project_path_string,
role_operation.resource_name AS rbac_resource_name,
role_operation.resource_value AS rbac_resource_value
FROM
sfuser sfuser,
project project,
role role,
projectmembership projectmembership,
role_default_user role_default_user,
role_operation role_operation
WHERE
role.id=role_default_user.role_id
AND role_default_user.project_id=project.id
AND role.id=role_operation.role_id
AND role.is_deleted=false
AND role_operation.object_type_id='Scm.Repository'
AND role_operation.operation_category='use'
AND role_operation.operation_name='access'
AND project.path='projects.barnes_and_nobles_college_bookse_3'
AND project.is_deleted=false
AND role_default_user.default_user_class_id='4'
AND projectmembership.member_id=sfuser.id
AND role_default_user.project_id=projectmembership.project_id
AND sfuser.username='rtrejo'
)
UNION
(SELECT
project.path AS rbac_project_path_string,
role_operation.resource_name AS rbac_resource_name,
role_operation.resource_value AS rbac_resource_value
FROM
sfuser sfuser,
project project,
role role,
role_user role_user,
role_operation role_operation
WHERE
role.id=role_user.role_id
AND role_user.project_id=project.id
AND role.id=role_operation.role_id
AND role.is_deleted=false
AND role_operation.object_type_id='Scm.Repository'
AND role_operation.operation_category='use'
AND role_operation.operation_name='access'
AND role_user.user_id=sfuser.id
AND project.path='projects.barnes_and_nobles_college_bookse_3'
AND project.is_deleted=false
AND sfuser.username='rtrejo'
);
take 0m1.693s according to 'time'
Explain attached as explain1
Query 2:
SELECT
artifact.id AS id,
artifact.priority AS priority,
project.path AS projectPathString,
project.title AS projectTitle,
folder.project_id AS projectId,
folder.path AS folderPathString,
folder.title AS folderTitle,
item.folder_id AS folderId,
item.title AS title,
item.name AS name,
artifact.description AS description,
field_value.value AS artifactGroup,
field_value2.value AS status,
field_value2.value_class AS statusClass,
field_value3.value AS category,
field_value4.value AS customer,
sfuser.username AS submittedByUsername,
sfuser.full_name AS submittedByFullname,
item.date_created AS submittedDate,
artifact.close_date AS closeDate,
sfuser2.username AS assignedToUsername,
sfuser2.full_name AS assignedToFullname,
item.date_last_modified AS lastModifiedDate,
artifact.estimated_hours AS estimatedHours,
artifact.actual_hours AS actualHours,
item.version AS version
FROM
relationship relationship,
sfuser sfuser,
sfuser sfuser2,
field_value field_value3,
item item,
project project,
field_value field_value2,
field_value field_value,
artifact artifact,
folder folder,
field_value field_value4
WHERE
artifact.id=item.id
AND item.folder_id=folder.id
AND folder.project_id=project.id
AND artifact.group_fv=field_value.id
AND artifact.status_fv=field_value2.id
AND artifact.category_fv=field_value3.id
AND artifact.customer_fv=field_value4.id
AND item.created_by_id=sfuser.id
AND relationship.is_deleted=false
AND relationship.relationship_type_name='ArtifactAssignment'
AND relationship.origin_id=sfuser2.id
AND artifact.id=relationship.target_id
AND item.is_deleted=false
AND ((project.path='projects.union_gas_gdar_ebt' AND ((folder.path IN
('tracker.cutover_tasks', 'tracker.peer_review_tracker', 'tracker.tars_0', 'tracker.reviews', 'tracker.defects',
'tracker.tars','tracker.database_change_requests'))
OR folder.path LIKE 'tracker.cutover_tasks.%' OR folder.path
LIKE 'tracker.peer_review_tracker.%' OR folder.path LIKE 'tracker.tars_0.%'
OR folder.path LIKE 'tracker.reviews.%' OR folder.path LIKE 'tracker.defects.
%' OR folder.path LIKE 'tracker.tars.%' OR folder.path
LIKE 'tracker.database_change_requests.%')))
AND folder.project_id='proj1775'
AND item.folder_id='tracker11923'
AND folder.path='tracker.defects'
AND (sfuser2.username='nobody' AND field_value2.value_class='Open');
takes 0m9.506s according to time.. it's attached as explain2
TIA, again
--
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net
It's not the pace of life that concerns me, it's the sudden stop at the end.
Attachment
Those plans look like they have a lot of casts to text in them. How have you defined your indexes? Are your id columns really text? And you don't have a 7.4 install around to compare the plans do you? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Gregory Stark wrote: > Those plans look like they have a lot of casts to text in them. How have you > defined your indexes? Are your id columns really text? And did you use the same encoding and locale? Text operations on multibyte encodings are much more expensive. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
On Sat, Jun 02, 2007 at 09:13:32AM -0400, Douglas J Hunley wrote: > Hello great gurus of performance: > Our 'esteemed' Engr group recently informed a customer that in their testing, > upgrading to 8.2.x improved the performance of our J2EE > application "approximately 20%", so of course, the customer then tasked me > with upgrading them. We dumped their db, removed pgsql, installed the 8.2.4 > rpms from postgresql.org, did an initdb, and the pg_restored their data. It's > been about a week now, and the customer is complaining that in their testing, > they are seeing a 30% /decrease/ in general performance. Of course, our Engr > group is being less than responsive, and I have a feeling all they're doing > is googling for answers, so I'm turning to this group for actual > assistance :) > I'd like to start by examining the poistgresql.conf file. Under 7.4.x, we had > spent the better part of their 2 years as a customer tuning and tweaking > setting. I've attached the file that was in place at the time of upgrade. I > did some cursory googling of my own, and quickly realized that enough has > changed in v8 that I'm not comfortable making the exact same modification to > their new config file as some options are new, some have gone away, etc. I've > attached the existing v8 conf file as well. > I'd really like it if someone could assist me in determining which of the v8 > options need adjusted to be 'functionally equivalent' to the v7 file. Right > now, my goal is to get the customer back to the previous level of > performance, and only then pursue further optimization. I can provide any and > all information needed, but didn't know what to include initially, so I've > opted to include the minimal :) > The DB server in question does nothing else, is running CentOS 4.5, kernel > 2.6.9-55.ELsmp. Hyperthreading is disabled in the BIOS and there are 2 Xeon > 3.4Ghz cpus. There is 8Gb of RAM in the machine, and another 8Gb of swap. > > Thank you in advance for any and all assistance you can provide. > -- > Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778 > http://doug.hunley.homeip.net > Douglas, If these are the current config files, it is no wonder that the performance is worse. Here are the things that need to be changed right from the start. The old 7.x is on the left and the 8.2 value is on the right. Make them the same to start and see how it looks then. setting 7.x current 8.2 ------------------------------------------------------ shared_buffers = 25000 / 32MB (=3906) sort_mem/work_mem = 15000/ 1MB (=122) vacuum_mem/maint_work_mem = 100000 / 16MB (=1950) effective_cache = 196608 / 128MB (=15600) should start between 200k-500k These changes alone should get you back to the performance point you are expecting. It would also be worth re-evaluating whether or not you should be disabling enable_mergehashjoin in general, and not just for specific problem queries. I would also tend to start with an effective_cache at the higher end on a dedicated DB server. Good luck with your tuning. If the 8.2 config file you posted is the one that has been in use, these few changes will restore your performance and then some. Ken
On Monday 04 June 2007 17:17:03 Heikki Linnakangas wrote: > And did you use the same encoding and locale? Text operations on > multibyte encodings are much more expensive. The db was created as: createdb -E UNICODE -O <user> <dbname> -- Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778 http://doug.hunley.homeip.net Linux is the answer, now what was your question?
On Monday 04 June 2007 17:11:23 Gregory Stark wrote:
> Those plans look like they have a lot of casts to text in them. How have
> you defined your indexes? Are your id columns really text?
project table:
Indexes:
"project_pk" PRIMARY KEY, btree (id)
"project_path" UNIQUE, btree (path)
role table:
Indexes:
"role_pk" PRIMARY KEY, btree (id)
role_default_user table:
Indexes:
"role_def_user_pk" PRIMARY KEY, btree (id)
"role_def_u_prj_idx" UNIQUE, btree (role_id, default_user_class_id,
project_id)
role_operation table:
Indexes:
"role_operation_pk" PRIMARY KEY, btree (id)
"role_oper_obj_oper" btree (object_type_id, operation_category,
operation_name)
"role_oper_role_id" btree (role_id)
sfuser table:
Indexes:
"sfuser_pk" PRIMARY KEY, btree (id)
"sfuser_username" UNIQUE, btree (username)
projectmembership table:
Indexes:
"pjmb_pk" PRIMARY KEY, btree (id)
"pjmb_projmember" UNIQUE, btree (project_id, member_id)
"pjmb_member" btree (member_id)
relationship table:
Indexes:
"relationship_pk" PRIMARY KEY, btree (id)
"relation_origin" btree (origin_id)
"relation_target" btree (target_id)
"relation_type" btree (relationship_type_name)
field_value table:
Indexes:
"field_value_pk" PRIMARY KEY, btree (id)
"f_val_fid_val_idx" UNIQUE, btree (field_id, value)
"field_class_idx" btree (value_class)
"field_value_idx" btree (value)
item table:
Indexes:
"item_pk" PRIMARY KEY, btree (id)
"item_created_by_id" btree (created_by_id)
"item_folder" btree (folder_id)
"item_name" btree (name)
and yes, the 'id' column is always: character varying type
> And you don't have a 7.4 install around to compare the plans do you?
I have a 7.3.19 db, if that would be useful
--
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net
Whose cruel idea was it for the word "lisp" to have an "s" in it?
Find the attached Postgres.conf file. I am using 8.1 Version in Lab.
I haven't done any changes to this conf file to improve the performance.
What are the attributes needs to be modified in the conf file to improve the performance?
I am looking forward for your assistance.
Regards,
Hanu
On 6/2/07, Douglas J Hunley <doug@hunley.homeip.net> wrote:
Hello great gurus of performance:
Our 'esteemed' Engr group recently informed a customer that in their testing,
upgrading to 8.2.x improved the performance of our J2EE
application "approximately 20%", so of course, the customer then tasked me
with upgrading them. We dumped their db, removed pgsql, installed the 8.2.4
rpms from postgresql.org, did an initdb, and the pg_restored their data. It's
been about a week now, and the customer is complaining that in their testing,
they are seeing a 30% /decrease/ in general performance. Of course, our Engr
group is being less than responsive, and I have a feeling all they're doing
is googling for answers, so I'm turning to this group for actual
assistance :)
I'd like to start by examining the poistgresql.conf file. Under 7.4.x, we had
spent the better part of their 2 years as a customer tuning and tweaking
setting. I've attached the file that was in place at the time of upgrade. I
did some cursory googling of my own, and quickly realized that enough has
changed in v8 that I'm not comfortable making the exact same modification to
their new config file as some options are new, some have gone away, etc. I've
attached the existing v8 conf file as well.
I'd really like it if someone could assist me in determining which of the v8
options need adjusted to be 'functionally equivalent' to the v7 file. Right
now, my goal is to get the customer back to the previous level of
performance, and only then pursue further optimization. I can provide any and
all information needed, but didn't know what to include initially, so I've
opted to include the minimal :)
The DB server in question does nothing else, is running CentOS 4.5, kernel
2.6.9-55.ELsmp. Hyperthreading is disabled in the BIOS and there are 2 Xeon
3.4Ghz cpus. There is 8Gb of RAM in the machine, and another 8Gb of swap.
Thank you in advance for any and all assistance you can provide.
--
Douglas J Hunley (doug at hunley.homeip.net ) - Linux User #174778
http://doug.hunley.homeip.net
Handy Guide to Modern Science:
1. If it's green or it wiggles, it's biology.
2. If it stinks, it's chemistry.
3. If it doesn't work, it's physics.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
With best regards,
Hanumanthappa Kurubar
Mobile: 98 801 800 65
Attachment
On Tuesday 05 June 2007 10:34:04 Douglas J Hunley wrote: > On Monday 04 June 2007 17:11:23 Gregory Stark wrote: > > Those plans look like they have a lot of casts to text in them. How have > > you defined your indexes? Are your id columns really text? > > project table: > Indexes: > "project_pk" PRIMARY KEY, btree (id) > "project_path" UNIQUE, btree (path) > > role table: > Indexes: > "role_pk" PRIMARY KEY, btree (id) > > role_default_user table: > Indexes: > "role_def_user_pk" PRIMARY KEY, btree (id) > "role_def_u_prj_idx" UNIQUE, btree (role_id, default_user_class_id, > project_id) > > role_operation table: > Indexes: > "role_operation_pk" PRIMARY KEY, btree (id) > "role_oper_obj_oper" btree (object_type_id, operation_category, > operation_name) > "role_oper_role_id" btree (role_id) > > sfuser table: > Indexes: > "sfuser_pk" PRIMARY KEY, btree (id) > "sfuser_username" UNIQUE, btree (username) > > projectmembership table: > Indexes: > "pjmb_pk" PRIMARY KEY, btree (id) > "pjmb_projmember" UNIQUE, btree (project_id, member_id) > "pjmb_member" btree (member_id) > > relationship table: > Indexes: > "relationship_pk" PRIMARY KEY, btree (id) > "relation_origin" btree (origin_id) > "relation_target" btree (target_id) > "relation_type" btree (relationship_type_name) > > field_value table: > Indexes: > "field_value_pk" PRIMARY KEY, btree (id) > "f_val_fid_val_idx" UNIQUE, btree (field_id, value) > "field_class_idx" btree (value_class) > "field_value_idx" btree (value) > > item table: > Indexes: > "item_pk" PRIMARY KEY, btree (id) > "item_created_by_id" btree (created_by_id) > "item_folder" btree (folder_id) > "item_name" btree (name) > > and yes, the 'id' column is always: character varying type > > > And you don't have a 7.4 install around to compare the plans do you? > > I have a 7.3.19 db, if that would be useful Any insight given the above? -- Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778 http://doug.hunley.homeip.net "It is our moral duty to corrupt the young"