Thread: performance problems.
All, Got a little bit of a performance problem I hope that can be resolved. All the files/info I believe you are going to ask for are here: http://www.au.sorbs.net/~matthew/postgres/30.8.06/ The odd thing was it originally was fast (1-2 seconds) which is all I need - the query is a permissions check and I added a permissions caching engine to the client code. However, I was testing part of my new interface and added and "expired" some rows in the permissions, and authorisation tables (taking the row count to ~15) the performance dropped to 86seconds (ish) which is unusable... :-( Unfortunately I do not have a query plan from before the performance issue. work_mem has been adjusted from 512 to 8192, 65536 and 1000000 with no apparent effect. random_page_cost has been 4 and 2 - 2 results in 89seconds for the query. The hardware is a Compaq 6400r with 4G of EDO RAM, 4x500MHz Xeons and a Compaq RAID 3200 in RAID 5 configuration running across 3 spindles (34G total space). The OS is FreeBSD 5.4-RELEASE-p14 The PG Version is 8.1.3 Solutions/tips greatly appreciated. Regards, Mat
On Aug 30, 2006, at 5:29 AM, Matthew Sullivan wrote: > The hardware is a Compaq 6400r with 4G of EDO RAM, 4x500MHz Xeons > and a Compaq RAID 3200 in RAID 5 configuration running across 3 > spindles (34G total space). > > The OS is FreeBSD 5.4-RELEASE-p14 > The PG Version is 8.1.3 What else does this box do? I think you should try these settings, which I use on 4GB dual Opteron boxes running FreeBSD 6.x dedicated to Postgres only. Your effective_cache_size seems overly optimistic for freebsd. cranking up the shared buffers seems to be one of the best bangs for the buck under pg 8.1. I recently doubled them and nearly tripled my performance on a massive write-mostly (insert/update) load. Unless your disk system is *really* slow, random_page_cost should be reduced from the default 4. As you can see, I change *very* little from the default config. shared_buffers = 70000 # min 16 or max_connections*2, 8KB each work_mem = 262144 # min 64, size in KB maintenance_work_mem = 524288 # min 1024, size in KB checkpoint_segments = 256 checkpoint_timeout = 900 effective_cache_size = 27462 # `sysctl -n vfs.hibufspace` / 8192 (BLKSZ) random_page_cost = 2 if you're feeling adventurous try these to reduce the checkpoint impact on the system: bgwriter_lru_percent = 2.0 bgwriter_lru_maxpages = 40 bgwriter_all_percent = 0.666 bgwriter_all_maxpages = 40 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. MailerMailer, LLC Rockville, MD http://www.MailerMailer.com/ +1-301-869-4449 x806
Attachment
On Wed, Aug 30, 2006 at 10:10:28AM -0400, Vivek Khera wrote: > effective_cache_size = 27462 # `sysctl -n > vfs.hibufspace` / 8192 (BLKSZ) > random_page_cost = 2 You misunderstand how effective_cache_size is used. It's the *only* memory factor that plays a role in cost estimator functions. This means it should include the memory set aside for caching in shared_buffers. Also, hibufspace is only talking about filesystem buffers in FreeBSD, which AFAIK has nothing to do with total memory available for caching, since VM pages are also used to cache data. Basically, your best bet for setting effective_cache_size is to use the total memory in the machine, and substract some overhead for the OS and other processes. I'll typically subtract 1G. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Wed, 30 Aug 2006, Jim C. Nasby wrote: > On Wed, Aug 30, 2006 at 10:10:28AM -0400, Vivek Khera wrote: > > effective_cache_size = 27462 # `sysctl -n > > vfs.hibufspace` / 8192 (BLKSZ) > > random_page_cost = 2 > > You misunderstand how effective_cache_size is used. It's the *only* > memory factor that plays a role in cost estimator functions. This means > it should include the memory set aside for caching in shared_buffers. > > Also, hibufspace is only talking about filesystem buffers in FreeBSD, > which AFAIK has nothing to do with total memory available for caching, > since VM pages are also used to cache data. I believe it's not talking about quantities of buffers at all, but about kernel virtual address space. It's something like the amount of kernel virtual address space available for mapping buffer-cache pages in to kernel memory. It certainly won't tell you (or even approximate) how much PostgreSQL data is being cached by the OS. Cached PostgreSQL data will appear in the active, inactive and cached values - and (AFAIK) there isn't any distinction between file-backed pages and swap-backed pages amongst those.
Vivek Khera wrote: > > On Aug 30, 2006, at 5:29 AM, Matthew Sullivan wrote: > >> The hardware is a Compaq 6400r with 4G of EDO RAM, 4x500MHz Xeons >> and a Compaq RAID 3200 in RAID 5 configuration running across 3 >> spindles (34G total space). >> >> The OS is FreeBSD 5.4-RELEASE-p14 >> The PG Version is 8.1.3 > > > What else does this box do? Notihing - it's the developement DB and is dedicated to the development website - which has a total number of users of '1' ;-) > I think you should try these settings, which I use on 4GB dual > Opteron boxes running FreeBSD 6.x dedicated to Postgres only. Your > effective_cache_size seems overly optimistic for freebsd. cranking > up the shared buffers seems to be one of the best bangs for the buck > under pg 8.1. I recently doubled them and nearly tripled my > performance on a massive write-mostly (insert/update) load. Unless > your disk system is *really* slow, random_page_cost should be reduced > from the default 4. I'll give this a try. > > As you can see, I change *very* little from the default config. > > > shared_buffers = 70000 # min 16 or > max_connections*2, 8KB each > work_mem = 262144 # min 64, size in KB > maintenance_work_mem = 524288 # min 1024, size in KB > > checkpoint_segments = 256 > checkpoint_timeout = 900 > > effective_cache_size = 27462 # `sysctl -n vfs.hibufspace` > / 8192 (BLKSZ) > random_page_cost = 2 > > if you're feeling adventurous try these to reduce the checkpoint > impact on the system: > > bgwriter_lru_percent = 2.0 > bgwriter_lru_maxpages = 40 > bgwriter_all_percent = 0.666 > bgwriter_all_maxpages = 40 > That might have some impact on the production server (which is also running PG - but the old DB and RT3) however the new DB is only me in devel, so I think that it will not have much of an effect (I'll still try it though) Regards, Mat
Matthew Sullivan wrote: > > The OS is FreeBSD 5.4-RELEASE-p14 > The PG Version is 8.1.3 > > Solutions/tips greatly appreciated. > This won't help this particular query, but 6.1-RELEASE will possibly be a better performer generally, in particular for your SMP system - e.g. the vfs layer is no longer under the Giant lock in the 6.x series, so parallel io should be much better! Cheers Mark
On 30-Aug-06, at 10:10 AM, Vivek Khera wrote: > > On Aug 30, 2006, at 5:29 AM, Matthew Sullivan wrote: > >> The hardware is a Compaq 6400r with 4G of EDO RAM, 4x500MHz Xeons >> and a Compaq RAID 3200 in RAID 5 configuration running across 3 >> spindles (34G total space). >> >> The OS is FreeBSD 5.4-RELEASE-p14 >> The PG Version is 8.1.3 > > What else does this box do? > > I think you should try these settings, which I use on 4GB dual > Opteron boxes running FreeBSD 6.x dedicated to Postgres only. Your > effective_cache_size seems overly optimistic for freebsd. cranking > up the shared buffers seems to be one of the best bangs for the > buck under pg 8.1. I recently doubled them and nearly tripled my > performance on a massive write-mostly (insert/update) load. Unless > your disk system is *really* slow, random_page_cost should be > reduced from the default 4. > Actually unless you have a ram disk you should probably leave random_page_cost at 4, shared buffers should be 2x what you have here, maintenance work mem is pretty high effective cache should be much larger 3/4 of 4G or about 360000 Setting work _mem this high should be done with caution. From the manual "Note that for a complex query, several sort or hash operations might be running in parallel; each one will be allowed to use as much memory as this value specifies before it starts to put data into temporary files. Also, several running sessions could be doing such operations concurrently. So the total memory used could be many times the value of work_mem" > As you can see, I change *very* little from the default config. > > > shared_buffers = 70000 # min 16 or > max_connections*2, 8KB each > work_mem = 262144 # min 64, size in KB > maintenance_work_mem = 524288 # min 1024, size in KB > > checkpoint_segments = 256 > checkpoint_timeout = 900 > > effective_cache_size = 27462 # `sysctl -n > vfs.hibufspace` / 8192 (BLKSZ) > random_page_cost = 2 > > if you're feeling adventurous try these to reduce the checkpoint > impact on the system: > > bgwriter_lru_percent = 2.0 > bgwriter_lru_maxpages = 40 > bgwriter_all_percent = 0.666 > bgwriter_all_maxpages = 40 > > > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= > Vivek Khera, Ph.D. MailerMailer, LLC Rockville, MD > http://www.MailerMailer.com/ +1-301-869-4449 x806 > >
On Aug 30, 2006, at 12:26 PM, Jim C. Nasby wrote: > You misunderstand how effective_cache_size is used. It's the *only* > memory factor that plays a role in cost estimator functions. This > means > it should include the memory set aside for caching in shared_buffers. > > Also, hibufspace is only talking about filesystem buffers in FreeBSD, > which AFAIK has nothing to do with total memory available for caching, > since VM pages are also used to cache data. > Curious... See Message-ID: <20031011092308.GA39942@perrin.nxad.com> from the October 2003 archives. (I'd provide a full link to it, but the http://archives.postgresql.org/pgsql-performance/ archives are botched -- only some posts are on the browsable archive but it is all in the raw mailbox download, so that's the only way to get the full message.) It reads in part: From: Sean Chittenden <sean@chittenden.org> Date: Sat, 11 Oct 2003 02:23:08 -0700 [...] > echo "effective_cache_size = $((`sysctl -n vfs.hibufspace` / 8192))" > > I've used it for my dedicated servers. Is this calculation correct? Yes, or it's real close at least. vfs.hibufspace is the amount of kernel space that's used for caching IO operations (minus the necessary space taken for the kernel). If you're real paranoid, you could do some kernel profiling and figure out how much of the cache is actually disk IO and multiply the above by some percentage, say 80%? I haven't found it necessary to do so yet. Since hibufspace is all IO and caching any net activity is kinda pointless and I assume that 100% of it is used for a disk cache and don't use a multiplier. The 8192, however, is the size of a PG page, so, if you tweak PG's page size, you have to change this constant (*grumbles*). --END QUOTE-- Given who Sean is, I tend to believe him. Whether this is still valid for FreeBSD 6.x, I'm unable to verify. > Basically, your best bet for setting effective_cache_size is to use > the > total memory in the machine, and substract some overhead for the OS > and > other processes. I'll typically subtract 1G. I'll give this a whirl and see if it helps. Any opinions on using the FreeBSD sysctl kern.ipc.shm_use_phys to bypass the VM system for shared pages?
Attachment
On Aug 30, 2006, at 7:48 PM, Dave Cramer wrote: > Actually unless you have a ram disk you should probably leave > random_page_cost at 4, shared buffers should be 2x what you have > here, maintenance work mem is pretty high > effective cache should be much larger 3/4 of 4G or about 360000 > I've been pondering bumping up SHM settings more, but it is a very big imposition to have to restart the production server to do so. This weekend being a long weekend might be a good opportunity to try it, though... As for maintenence mem, when you have HUGE tables, you want to give a lot of memory to vacuum. With 4GB of RAM giving it 512MB is not an issue. The effective cache size is the big issue with FreeBSD. There are opposing claims of how much memory it will use for cache, and throw in the kern.ipc.shm_use_phys sysctl which causes SHM to bypass the VM system entirely, and who knows what's going on. > Setting work _mem this high should be done with caution. From the > manual "Note that for a complex query, several sort or hash > operations might be running in parallel; each one will be allowed > to use as much memory as this value specifies before it starts to > put data into temporary files. Also, several running sessions could > be doing such operations concurrently. So the total memory used > could be many times the value of work_mem" Again, with boat-loads of RAM why not let the queries use it? We only have a handful of connections at a time so that's not eating up much memory...
Attachment
On 31-Aug-06, at 2:15 PM, Vivek Khera wrote: > > On Aug 30, 2006, at 7:48 PM, Dave Cramer wrote: > >> Actually unless you have a ram disk you should probably leave >> random_page_cost at 4, shared buffers should be 2x what you have >> here, maintenance work mem is pretty high >> effective cache should be much larger 3/4 of 4G or about 360000 >> > > I've been pondering bumping up SHM settings more, but it is a very > big imposition to have to restart the production server to do so. > This weekend being a long weekend might be a good opportunity to > try it, though... > > As for maintenence mem, when you have HUGE tables, you want to give > a lot of memory to vacuum. With 4GB of RAM giving it 512MB is not > an issue. > > The effective cache size is the big issue with FreeBSD. There are > opposing claims of how much memory it will use for cache, and throw > in the kern.ipc.shm_use_phys sysctl which causes SHM to bypass the > VM system entirely, and who knows what's going on. Yes, I have to admit, the setting I proposed works well for linux, but may not for bsd. > >> Setting work _mem this high should be done with caution. From the >> manual "Note that for a complex query, several sort or hash >> operations might be running in parallel; each one will be allowed >> to use as much memory as this value specifies before it starts to >> put data into temporary files. Also, several running sessions >> could be doing such operations concurrently. So the total memory >> used could be many times the value of work_mem" > > Again, with boat-loads of RAM why not let the queries use it? We > only have a handful of connections at a time so that's not eating > up much memory... > As long as you are aware of the ramifications....
Vivek Khera <vivek@khera.org> writes: > Curious... See Message-ID: <20031011092308.GA39942@perrin.nxad.com> > from the October 2003 archives. (I'd provide a full link to it, but > the http://archives.postgresql.org/pgsql-performance/ archives are > botched -- Still? I found it easily enough with a search for 'hibufspace': http://archives.postgresql.org/pgsql-performance/2003-10/msg00383.php regards, tom lane
On Aug 31, 2006, at 3:08 PM, Tom Lane wrote: > Vivek Khera <vivek@khera.org> writes: >> Curious... See Message-ID: <20031011092308.GA39942@perrin.nxad.com> >> from the October 2003 archives. (I'd provide a full link to it, but >> the http://archives.postgresql.org/pgsql-performance/ archives are >> botched -- > > Still? I found it easily enough with a search for 'hibufspace': > http://archives.postgresql.org/pgsql-performance/2003-10/msg00383.php > > regards, tom lane go to "view by thread" or "view by date" for October 2003. Or August 2003. Most messages are missing.