Re: FreeBSD upgrade causes performance degredation - Mailing list pgsql-admin

Interesting resolution to this issue. First, I was wrong that a fresh
install improved performance; upgrade and new install performed similarly.
Upon examination of database activity while our product was running it was
observed that a call to a specific homegrown function was taking between 1
and 30 seconds to complete, and was being executed many times.

The select in the function included a call to another function. We believe
the second function was being called once for every result row in the first
select. Also, explain indicated that the query plan included a seq scan
whereas the modified query did not.

Old function was something like this:
        SELECT COL1 FROM TABLE WHERE COL2 = SP_FUNCTION(ARG, ARG, ARG, ...)

The query was modified so that the call to the second function was executed
as a subselect (and thereby executed only once for all rows in the first
select?):

        SELECT COL1 FROM TABLE WHERE COL2 = (SELECT SP_FUNCTION(ARG, ARG,
ARG...))

The modified query takes about a second to complete everytime it's run. It
still is not clear why the original function executes faster on 4.1, and we
know that there are still performance issues with fsync. I have tried to
educate myself on the pro's and con's of running postmaster without fsync,
but it seems safest to run with it on.

Thanks for all of your input!

Peter

> -----Original Message-----
> From: Sean Chittenden [mailto:sean@chittenden.org]
> Sent: Monday, October 29, 2001 1:46 PM
> To: Peter Schmidt
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] FreeBSD upgrade causes performance degredation
>
>
> > We run BSD upgrade on 4.1 ...boot from the CD and select upgrade
> > during the install. I haven't done anything to tune the postgres
> > config...what is recommended?
>
> Tons, but turning off fsync is the biggest/easiest win you can get.
> Check out the online docs regarding performance tuning, they're quite
> good.
>
> You'll also probably want to turn on soft-updates.  Type 'mount' and
> see if anything similar to:
>
> /dev/da0s1f on /opt (ufs, NFS exported, local, soft-updates)
>
> If not, then you'll want to turn on soft-updates via the command
> 'tunefs -n enable /fs/mount/point'.  This is something you can read
> about in the tuning(7) man page (EXCELLENT read that I recommend
> everyone read through if you use FreeBSD).
>
> > Eveything has been running fine out of the box up to this
> > point. Further analysis indicates this could be a problem with the
> > BSD upgrade, as performance seems to have improved after a clean
> > install of FreeBSD 4.4.
>
> That's odd...  If you get a chance and are savvy with using cvsup,
> check out the -STABLE branch because it has some new DIRPERF code that
> gives at least a 3x speed up to doing a cvs update for a local copy of
> FreeBSD and is probably going to have a significant increase in
> performance for database operations as well (very disk IO intensive).
> The DIRPERF code was MFC'ed 5 days after 4.4 was released.  The other
> tid-bit of code that may be useful is the DIRUHASH code (optional that
> you'll have to compile into your kernel) which speeds up routines for
> large number of files per directory.  -sc