Re: [PERFORM] PostgreSQL on Solaris 8 and ufs
I just wanted to follow up and let everyone know that the biggest improvement in performance came from moving the pg_xlog directory to another filesystem (different set of disks) separate from the data directory. Thanks for the suggestions. -- Brandon ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] PostgreSQL on Solaris 8 and ufs
On Tue, Mar 22, 2005 at 03:23:18PM -0600, Brandon Metcalf wrote: s What are you using to measure s performance? Nothing too scientific other than the fact that since we have moved the DB, we consistenly see a large number of postmater processes (close to 100) where before we did not. What did you move from? The Solaris ps (not in ucb, which is the BSD-style ps) shows the parent process name, so everything shows up as postmaster rather than postgres. There's always one back end per connection. If you are in fact using more connections, by the way, I can tell you that Solaris 8, in my experience, is _very bad_ at managing context switches. So you may not be merely I/O bound (although your other reports seem to indicate that you are). A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] PostgreSQL on Solaris 8 and ufs
a == [EMAIL PROTECTED] writes: a On Tue, Mar 22, 2005 at 03:23:18PM -0600, Brandon Metcalf wrote: a s What are you using to measure a s performance? a a Nothing too scientific other than the fact that since we have moved a the DB, we consistenly see a large number of postmater processes a (close to 100) where before we did not. a What did you move from? The Solaris ps (not in ucb, which is the a BSD-style ps) shows the parent process name, so everything shows up a as postmaster rather than postgres. There's always one back end a per connection. a If you are in fact using more connections, by the way, I can tell you a that Solaris 8, in my experience, is _very bad_ at managing context a switches. So you may not be merely I/O bound (although your other a reports seem to indicate that you are). We moved from an HP-UX 10.20 box where the pgsql installation and data were on a vxfs fileystem. And we're definitely seeing more connections at a time which indicates that each process is taking longer to complete. -- Brandon ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] PostgreSQL on Solaris 8 and ufs
On the context switching issue, we've found that this setting in /etc/system helps: set rechoose_interval=30 this sets the minimum time that a process is eligible to be switched to another cpu. (the default is 3). You can monitor context switching with the cs column in vmstat. We've found that high context switching seems to be more a symptom, rather than a cause of problems -- for example we had an issue with column statistics and some really bad queries, and the cpu's start context switching like crazy. (20,000 - 50,000 or more in a 5 second period, normally 5000 per 5 second period under heavy load.) Brandon Metcalf wrote: a == [EMAIL PROTECTED] writes: a On Tue, Mar 22, 2005 at 03:23:18PM -0600, Brandon Metcalf wrote: a s What are you using to measure a s performance? a a Nothing too scientific other than the fact that since we have moved a the DB, we consistenly see a large number of postmater processes a (close to 100) where before we did not. a What did you move from? The Solaris ps (not in ucb, which is the a BSD-style ps) shows the parent process name, so everything shows up a as postmaster rather than postgres. There's always one back end a per connection. a If you are in fact using more connections, by the way, I can tell you a that Solaris 8, in my experience, is _very bad_ at managing context a switches. So you may not be merely I/O bound (although your other a reports seem to indicate that you are). We moved from an HP-UX 10.20 box where the pgsql installation and data were on a vxfs fileystem. And we're definitely seeing more connections at a time which indicates that each process is taking longer to complete. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] PostgreSQL on Solaris 8 and ufs
On Wed, Mar 23, 2005 at 11:16:29AM -0600, Brandon Metcalf wrote: We moved from an HP-UX 10.20 box where the pgsql installation and data were on a vxfs fileystem. My best guess, then, is that ufs tuning really is your issue. We always used vxfs for our Sun database servers (which was a nightmare all on its own, BTW, so I don't actually recommend this), so I don't have any real ufs tuning advice. The Packer Solaris database book (Packer, Allan N., _Configuring Tuning Databases on the Solaris Platform_. Palo Alto: Sun Microsystems P, 2002. ISBN 0-13-083417-3) does suggest mounting the filesystems with forcedirectio; I dimly recall using this for the wal partition on one test box, and STR that it helped. Also, you want to make sure you use the right fsync method; if it's still set to fsync in the config file, you'll want to change that. I remember finding that fsync was something like 3 times slower than everything else. I don't have any more Solaris boxes to check, but I believe we were using open_datasync as our method. You'll want to run some tests. You also should enable priority paging, but expect that this will give you really strange po numbers from vmstat and friends. Priority paging, I found, makes things look like you're swapping when you aren't. Procmem is useful, but if you really want the goods on what's going on, you need the SE toolkit. Just be careful using it as root -- in some cases it'll modify kernel parameters behind the scenes. In my case, I didn't have superuser access, so there wasn't a danger; but I've heard sysadmins complain about this. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] PostgreSQL on Solaris 8 and ufs
Andrew, The Packer Solaris database book (Packer, Allan N., _Configuring Tuning Databases on the Solaris Platform_. Palo Alto: Sun Microsystems P, 2002. ISBN 0-13-083417-3) does suggest mounting the filesystems with forcedirectio; I dimly recall using this for the wal partition on one test box, and STR that it helped. This is a good idea for a WAL partition, but is NOT a good idea for the database. You pay want to look into setting segmap_percent to 70% or something. On Solaris 10 at least, the OS by default only uses 10% of memory for disk buffers. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] PostgreSQL on Solaris 8 and ufs
We've recently moved our pgsql installation and DBs to a Solaris 8 machine with striped and mirrored ufs filesystem that houses the DB data. We are now seeing terrible performance and the bottleneck is no doubt disk I/O. We've tried modifying a tunables related to ufs, but it doesn't seem to be helping. Is there anything we should be looking at that is specifically related to ufs filesystems on Solaris 8 or possibly something in general that would improve performance? Thanks. -- Brandon ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] PostgreSQL on Solaris 8 and ufs
Brandon Metcalf wrote: We've recently moved our pgsql installation and DBs to a Solaris 8 machine with striped and mirrored ufs filesystem that houses the DB data. We are now seeing terrible performance and the bottleneck is no doubt disk I/O. We've tried modifying a tunables related to ufs, but it doesn't seem to be helping. Is there anything we should be looking at that is specifically related to ufs filesystems on Solaris 8 or possibly something in general that would improve performance? Well, Solaris 8 is a bit old now, so I don't remember all the details. But, if memory servers, Solaris 8 still has some high water and lo water tunables related to the amount of IO can be outstanding to a single file. Try setting set ufs:ufs_WRITES=0 in /etc/system and rebooting, which basically says any amount of disk IO can be outstanding. There's a tunables doc on docs.sun.com that explains this option. Also, logging UFS might help with some of the metadata requirements of UFS as well. So, use mount -o logging or add the relevant entry in /etc/vfstab. Of course, the best thing is Solaris 9 or 10, which would be much better for this sort of thing. Hope this helps. -- Alan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] PostgreSQL on Solaris 8 and ufs
s == [EMAIL PROTECTED] writes: s Try setting s set ufs:ufs_WRITES=0 s in /etc/system and rebooting, which basically says any amount of disk s IO can be outstanding. There's a tunables doc on docs.sun.com that s explains this option. s Also, logging UFS might help with some of the metadata requirements of s UFS as well. So, use mount -o logging or add the relevant entry in s /etc/vfstab. OK. I'll try these out. We do have ufs_WRITES enabled with the following parameters: set ncsize = 257024 set autoup = 90 set bufhwm =15000 set tune_t_fsflushr = 15 set ufs:ufs_HW = 16777216 set ufs:ufs_LW = 8388608 -- Brandon ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] PostgreSQL on Solaris 8 and ufs
s == [EMAIL PROTECTED] writes: s What are you using to create your raid? Hm. I didn't set this up. I'll have to check. s You say it is no doubt disk s I/O - does iostat confirm this? A lot of performance issues are related s to the size of the stripe you chose for the striped portion of the s array, the actual array configuration, etc. I am assuming you have s looked at system variables such as autoup and the likes? What tweaks s have you done? I've mainly been using Glance which shows a lot of queued requests for the disks in question. Here's currently what we have in /etc/system related to ufs: set ncsize = 257024 set autoup = 90 set bufhwm =15000 set tune_t_fsflushr = 15 set ufs:ufs_HW = 16777216 set ufs:ufs_LW = 8388608 s Also, are your pg_xlog and data directories separated onto separate s volumes? Doing so will help immensely. No, they are on the same volume. s What are you using to measure s performance? Nothing too scientific other than the fact that since we have moved the DB, we consistenly see a large number of postmater processes (close to 100) where before we did not. -- Brandon ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] PostgreSQL on Solaris 8 and ufs
Brandon Metcalf wrote: We've recently moved our pgsql installation and DBs to a Solaris 8 machine with striped and mirrored ufs filesystem that houses the DB data. We are now seeing terrible performance and the bottleneck is no doubt disk I/O. We've tried modifying a tunables related to ufs, but it doesn't seem to be helping. Is there anything we should be looking at that is specifically related to ufs filesystems on Solaris 8 or possibly something in general that would improve performance? Thanks. I found that mounting the filesystem that contains the PGDATA directory (probably only the pg_xlog directory in fact) without logging improved things a great deal (assuming you are using logging that is...). In addition changing the postgresql.conf parameter wal_sync_method from the default of open_datasync to fdatasync improved things a bit more. However I seem to recall a posting suggesting the opposite! ...so feel free to experiment and let us know! Mark P.s : original tests on Solaris 8, http://archives.postgresql.org/pgsql-performance/2003-12/msg00165.php ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
