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

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/>

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

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...'

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings