Thread: FreeBSD upgrade causes performance degredation
FreeBSD 4.1 vs FreeBSD 4.4 on X86 Postrgesql 7.03 We are experiencing a siginficant drop in performance in some PostgreSQL database operations after upgrading FreeBSD 4.1 to FreeBSD 4.4. Database connections appear to be twice as slow while calling some of our own functions can take 10x longer. Does anyone have any ideas why this is occurring? kernel options added for postgres present in both versions of FreeBSD (gives us ~100 connections) SEMMNI=22 # number of semaphore identifiers SEMMNS=122 # number of semaphores in system SEMUME=22 # max number of undo entries per process SEMMNU=62 # number of undo structures in system Thanks for any and all comments. Peter Schmidt peterjs@home.com
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? 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. Thanks again for you comments. Peter > -----Original Message----- > From: Sean Chittenden [mailto:sean@chittenden.org] > Sent: Monday, October 29, 2001 12:31 PM > To: Peter Schmidt > Cc: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] FreeBSD upgrade causes performance degredation > > > > FreeBSD 4.1 vs FreeBSD 4.4 on X86 > > Postrgesql 7.03 > > > > We are experiencing a siginficant drop in performance in some PostgreSQL > > database operations after upgrading FreeBSD 4.1 to FreeBSD 4.4. Database > > connections appear to be twice as slow while calling some of our own > > functions can take 10x longer. Does anyone have any ideas why this is > > occurring? > > When you say upgrading, how'd you upgrade? Source upgrade, or is it a > build from scratch (CD install)? If it's a build from scratch, I'd > run through tuning(7) and enable soft-updates if they're not already > on. When you say it's slower, it sounds like disk-access is slower. > Have you tuned the postgresql config? -sc
> FreeBSD 4.1 vs FreeBSD 4.4 on X86 > Postrgesql 7.03 > > We are experiencing a siginficant drop in performance in some PostgreSQL > database operations after upgrading FreeBSD 4.1 to FreeBSD 4.4. Database > connections appear to be twice as slow while calling some of our own > functions can take 10x longer. Does anyone have any ideas why this is > occurring? When you say upgrading, how'd you upgrade? Source upgrade, or is it a build from scratch (CD install)? If it's a build from scratch, I'd run through tuning(7) and enable soft-updates if they're not already on. When you say it's slower, it sounds like disk-access is slower. Have you tuned the postgresql config? -sc
> 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
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
"Peter Schmidt" <peterjs@home.com> writes:
> 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...))
This seems like a rather klugy substitute for marking the function
"iscachable". You might want to read up on that in the CREATE FUNCTION
documentation.
I don't see why an OS change would affect this though; I think you still
have more digging to do to understand the issue. Did you reinstall
Postgres after the OS update, and if so is it possible you neglected to
do VACUUM ANALYZE after reloading the database?
regards, tom lane
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > I don't see why an OS change would affect this though; I think you still > have more digging to do to understand the issue. Did you reinstall > Postgres after the OS update, and if so is it possible you neglected to > do VACUUM ANALYZE after reloading the database? Postgres was installed after the OS update. All databases are vacuum analyzed during installation of our product. Definitely more digging to do. Peter
On Monday 29 October 2001 09:46, Peter Schmidt wrote:
> FreeBSD 4.1 vs FreeBSD 4.4 on X86
> Postrgesql 7.03
>
> We are experiencing a siginficant drop in performance in some PostgreSQL
> database operations after upgrading FreeBSD 4.1 to FreeBSD 4.4. Database
> connections appear to be twice as slow while calling some of our own
> functions can take 10x longer. Does anyone have any ideas why this is
> occurring?
>
--SNIP--
What type of drives are running? ATA or SCSI? There have been changes to
both systems since 4.1.
Also, try enabling noatime in fstab for the mount and do a tunefs -n enable
/dev/disk_device to enable softupdates.
GB
--
GB Clark II | Roaming FreeBSD Admin
gclarkii@VSServices.COM | General Geek
CTHULU for President - Why choose the lesser of two evils?
Dear all, I am wondering if someone could tell me any experience of building architecture of load balancing for PostgreSQL. I am interested in clustering and a high-availability server. Is that possible? Which tool should I use? Many thanks in advance Miguel
I just spent a week or so working on FailSafe without much luck. have a look on http://www.sgi.com/software/failsafe/ http://oss.sgi.com/projects/failsafe/mail.html you can get it at: ftp://ftp.suse.com/pub/projects/failsafe/ I think using the RedHat 7.2 Distribution gave me the problems in building the underlying libraries. It is definatlely more slanted toward the Suse distribution. In the meantime I evaluated DataGuard Convolo from www.MissionCriticalLinux.com based on KimberLite. It took me two days to setup (I my opionion this is good for Clustering software) In all the tests I have been doing today it seems to be holding up. (Bombardment, Plug Pulling, Kicking the PC and verbal taunting of the Database Process) I will probably get back to FailSafe and getting it to work on the RedHat 7.2 Distribution when I have some more time. But for now I know there is a reasonably priced commercial solution based on Open Source that seems simple to configure and also works. Compaq supposedly is backing an OpenSource HA Clustering Project too. Best Regards, Jeff Young "Miguel Gonzalez" <iafmgc@unileon.es> wrote in message news:3514637767.20011128125549@unileon.es... > Dear all, > > I am wondering if someone could tell me any experience of building > architecture of load balancing for PostgreSQL. I am interested in > clustering and a high-availability server. Is that possible? Which > tool should I use? > > Many thanks in advance > > Miguel > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
On Wed, Nov 28, 2001 at 02:51:04PM -0500, news.i-2000.com wrote: > I just spent a week or so working on FailSafe without much luck. > > have a look on > http://www.sgi.com/software/failsafe/ > http://oss.sgi.com/projects/failsafe/mail.html > > you can get it at: > ftp://ftp.suse.com/pub/projects/failsafe/ > > I think using the RedHat 7.2 Distribution gave me the problems in building > the underlying libraries. > It is definatlely more slanted toward the Suse distribution. It should work on RedHat as well. > Compaq supposedly is backing an OpenSource HA Clustering Project too. Compaq is backing several clustering projects. They have the Cluster Infrastructure for Linux (CI) (http://ci-linux.sourceforge.net/) project and the Single System Image Cluster for Linux (SSI) (http://ssic-linux.sourceforge.net/). In addition they're part of the effort to create a common framework for cluster software for linux. There are aslo a bunch of other available cluster software, see: http://vestinternett.no/~ragnark/ha-software.txt My personal preferanse is heartbeat (www.linux-ha.org), because it's small and easy. -- Ragnar Kjørstad Big Storage
At 12:51 PM 11/28/2001 , news.i-2000.com wrote:
>I just spent a week or so working on FailSafe without much luck.
And PolyServe has gotten good reviews.
-crl
--
Chad R. Larson (CRL22) chad@eldocomp.com
Eldorado Computing, Inc. 602-604-3100
5353 North 16th Street, Suite 400
Phoenix, Arizona 85016-3228