pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedperformance problems
13+ messages / 8 participants
[nested] [flat]
* performance problems
@ 2005-03-05 12:26 Michael Zoephel <michel@edmi.de>
0 siblings, 0 replies; 13+ messages in thread
From: Michael Zoephel @ 2005-03-05 12:26 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have performance problems with the postmaster-process. The CPU usage is permanently
around 90 %.
My DB:
3,5 GB Size
largest table ca. 700 000 rows
Most access:
Finde a row - Update if exist Insert else
Average access:
10 times a second
every Query needs ca. 20 ms
All server options are still default !!
Any idea on wich "screw" I can turn ?.
Thanks for suggestions!
Michael Zöphel
e-Mail: zoephel@rechnerbetriebsgruppe.de
^ permalink raw reply [nested|flat] 13+ messages in thread
* performance problems.
@ 2006-08-30 09:29 Matthew Sullivan <matthew@sorbs.net>
0 siblings, 2 replies; 13+ messages in thread
From: Matthew Sullivan @ 2006-08-30 09:29 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: performance problems.
@ 2006-08-30 14:10 Vivek Khera <vivek@khera.org>
parent: Matthew Sullivan <matthew@sorbs.net>
1 sibling, 3 replies; 13+ messages in thread
From: Vivek Khera @ 2006-08-30 14:10 UTC (permalink / raw)
To: Matthew Sullivan <matthew@sorbs.net>; +Cc: pgsql-performance
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
Attachments:
smime.p7s (application/pkcs7-signature, 2.5K) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: performance problems.
@ 2006-08-30 16:26 Jim C. Nasby <jnasby@pervasive.com>
parent: Vivek Khera <vivek@khera.org>
2 siblings, 2 replies; 13+ messages in thread
From: Jim C. Nasby @ 2006-08-30 16:26 UTC (permalink / raw)
To: Vivek Khera <vivek@khera.org>; +Cc: Matthew Sullivan <matthew@sorbs.net>; pgsql-performance
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: performance problems.
@ 2006-08-30 18:22 Alex Hayward <xelah-pgsql@xelah.com>
parent: Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 0 replies; 13+ messages in thread
From: Alex Hayward @ 2006-08-30 18:22 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: performance problems.
@ 2006-08-30 22:17 Matthew Sullivan <matthew@sorbs.net>
parent: Vivek Khera <vivek@khera.org>
2 siblings, 0 replies; 13+ messages in thread
From: Matthew Sullivan @ 2006-08-30 22:17 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: performance problems.
@ 2006-08-30 23:36 Mark Kirkwood <markir@paradise.net.nz>
parent: Matthew Sullivan <matthew@sorbs.net>
1 sibling, 0 replies; 13+ messages in thread
From: Mark Kirkwood @ 2006-08-30 23:36 UTC (permalink / raw)
To: Matthew Sullivan <matthew@sorbs.net>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: performance problems.
@ 2006-08-30 23:48 Dave Cramer <pg@fastcrypt.com>
parent: Vivek Khera <vivek@khera.org>
2 siblings, 1 reply; 13+ messages in thread
From: Dave Cramer @ 2006-08-30 23:48 UTC (permalink / raw)
To: Vivek Khera <vivek@khera.org>; +Cc: Matthew Sullivan <matthew@sorbs.net>; pgsql-performance
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
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: performance problems.
@ 2006-08-31 18:10 Vivek Khera <vivek@khera.org>
parent: Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 1 reply; 13+ messages in thread
From: Vivek Khera @ 2006-08-31 18:10 UTC (permalink / raw)
To: pgsql-performance
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?
Attachments:
smime.p7s (application/pkcs7-signature, 2.5K) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: performance problems.
@ 2006-08-31 18:15 Vivek Khera <vivek@khera.org>
parent: Dave Cramer <pg@fastcrypt.com>
0 siblings, 1 reply; 13+ messages in thread
From: Vivek Khera @ 2006-08-31 18:15 UTC (permalink / raw)
To: pgsql-performance
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...
Attachments:
smime.p7s (application/pkcs7-signature, 2.5K) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: performance problems.
@ 2006-08-31 18:28 Dave Cramer <pg@fastcrypt.com>
parent: Vivek Khera <vivek@khera.org>
0 siblings, 0 replies; 13+ messages in thread
From: Dave Cramer @ 2006-08-31 18:28 UTC (permalink / raw)
To: Vivek Khera <vivek@khera.org>; +Cc: pgsql-performance
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....
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: performance problems.
@ 2006-08-31 19:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Vivek Khera <vivek@khera.org>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2006-08-31 19:08 UTC (permalink / raw)
To: Vivek Khera <vivek@khera.org>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: performance problems.
@ 2006-08-31 19:40 Vivek Khera <vivek@khera.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 13+ messages in thread
From: Vivek Khera @ 2006-08-31 19:40 UTC (permalink / raw)
To: pgsql-performance
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.
Attachments:
smime.p7s (application/pkcs7-signature, 2.5K) download
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2006-08-31 19:40 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-05 12:26 performance problems Michael Zoephel <michel@edmi.de>
2006-08-30 09:29 performance problems. Matthew Sullivan <matthew@sorbs.net>
2006-08-30 14:10 ` Re: performance problems. Vivek Khera <vivek@khera.org>
2006-08-30 16:26 ` Re: performance problems. Jim C. Nasby <jnasby@pervasive.com>
2006-08-30 18:22 ` Re: performance problems. Alex Hayward <xelah-pgsql@xelah.com>
2006-08-31 18:10 ` Re: performance problems. Vivek Khera <vivek@khera.org>
2006-08-31 19:08 ` Re: performance problems. Tom Lane <tgl@sss.pgh.pa.us>
2006-08-31 19:40 ` Re: performance problems. Vivek Khera <vivek@khera.org>
2006-08-30 22:17 ` Re: performance problems. Matthew Sullivan <matthew@sorbs.net>
2006-08-30 23:48 ` Re: performance problems. Dave Cramer <pg@fastcrypt.com>
2006-08-31 18:15 ` Re: performance problems. Vivek Khera <vivek@khera.org>
2006-08-31 18:28 ` Re: performance problems. Dave Cramer <pg@fastcrypt.com>
2006-08-30 23:36 ` Re: performance problems. Mark Kirkwood <markir@paradise.net.nz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox