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

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.