Thread: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

From
Douglas J Hunley
Date:
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

Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

From
Rafael Martinez
Date:
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/>

Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

From
Michael Fuhr
Date:
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

Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

From
Tom Lane
Date:
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

Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

From
Stefan Kaltenbrunner
Date:
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

Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

From
Douglas J Hunley
Date:
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.

Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

From
Douglas J Hunley
Date:
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?"

Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

From
"Luke Lonergan"
Date:

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

Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

From
Greg Smith
Date:
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

Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

From
Tom Lane
Date:
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

Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

From
Douglas J Hunley
Date:
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.

Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

From
Heikki Linnakangas
Date:
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

Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

From
"Luke Lonergan"
Date:

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.

Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

From
Reid Thompson
Date:
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...'

Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

From
Douglas J Hunley
Date:
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

Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

From
Alvaro Herrera
Date:
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.

Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

From
Douglas J Hunley
Date:
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

Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

From
Gregory Stark
Date:
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


Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

From
Heikki Linnakangas
Date:
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

Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

From
Kenneth Marshall
Date:
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

Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

From
Douglas J Hunley
Date:
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?

Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

From
Douglas J Hunley
Date:
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?

Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

From
"Hanu Kurubar"
Date:

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

Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

From
Douglas J Hunley
Date:
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"