Re: Performance problems with Postgresql

Ben,

For a system with 2GB RAM your setting for
shared_buffers actually seem kind of low. Try
increasing to at least 40960.
shared_buffers = 4096 = 32MB since each buffer has 8k
sort_mem = 2048 = 2MB max in memory per sort
operation; larger sorts go to disk.

There must be something else going on.
Do you have any error messages or system logs when the
processes die?
What exactly are the large customers doing that's
taking 5 minutes? Can you share the SQL that is being
executed?

You may want to look at your memory usage with vmstat.

Regards,
Nikolaus Dilger

"Ben Jordan" wrote:

Message

I'm very new to all
this so I apologize if I do things incorrectly.  I got
thrown into a mix
here where I'm in charge of software, hardware
monitoring, lots of stuff, among
them is our postgres database, which I know very little
about.  I have been
studying the posts and a couple of postgresql books for
weeks and weeks
and I am currently so stuck I don't know what else to
do.  By the way,
thanks to everyone for their contributions.  I have
learned a lot and had a
lot of help from reading your posts.
 
We have a static
sitebuilder where our clients build and manage an
ecommerce site.  They
have to publish the site to get their changes to showup
on their web site. 
(We will soon be going to a dynamic site but still need
to support this one
for some time).  For most people this process works
fine, albeit a bit
slow, (between 10 seconds and 5 minutes) but for our
larger users,
they publish process dies after a while and they are
unable to update their
sites.  I changed some memory and postgresql.conf
settings a while ago and
it seemed to totally fix the problem.  Then we lost
some RAM in our
server.  We were at 2.5 Gigs, now at 2.25 Gigs, if
that's even a valid
amount.  Anyways, ever since then I'm having the same
publishing
problems.  The main problem for my large users is that
after 5 minutes and
5 seconds the process just quits and dissapears, and I
can see it die out of the
top.  Here are some of my current settings:
 
IBM
Netfinity 7100
Dual P3 700
Mhz
2.25 Gigs of
RAM
3 18 Gig
SCSI Hard Drives RAID 5
Standard
stuff here I guess
 
Here is the
latest for my sysctl, of which i don't even understand
the
shmmni.  I have changed these settings quite a few
times.
 
kernel.shmmni
= 4096kernel.shmall = 1610612736kernel.shmmax =
1610612736
 
Here
is some postgresql.conf stuff:
 
#       Connection
Parameters##tcpip_socket = false#ssl =
false
 
max_connections = 128 # 1-1024port
= 5432#hostname_lookup = false#show_source_port =
false
 
#unix_socket_directory = ''#unix_socket_group =
''#unix_socket_permissions = 0777
 
#virtual_host
= ''
 
#krb_server_keyfile = ''
 
##       Performance##sort_mem
= 512#shared_buffers = 2*max_connections # min 16#fsync
=
true
 
shared_buffers = 4096sort_mem =
2048#max_connections=128#fsync=false
I need at least 128
connections because of a Sorry, too many clients error
that was occurring. 
I have tried so many different configurations of shared
buffers and sort memory
that I don't know what to do now.  Some seem to work,
but none very
consistently.  I've tried very high, with so so
results.  Now I'm back
to square one. 
 
I apologize for the
long post and for a major ignorance on my part.  I have
tried to learn and
read as much as I can but am at my wits end here.  If
any onf you know any
small tips I would greatly appreciate it.  Thanks.
 
Ben Jordan -
Complete Newbie
bjordan(at)esbex(dot)com