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

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.