Re: Performance problems with Postgresql - Mailing list pgsql-admin
| From | Ben Jordan |
|---|---|
| Subject | Re: Performance problems with Postgresql |
| Date | |
| Msg-id | 000001c2e37c$898aa590$1000000a@BenJordan Whole thread Raw |
| Responses |
Re: Performance problems with Postgresql
|
| List | pgsql-admin |
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 = 4096
kernel.shmall = 1610612736
kernel.shmmax = 1610612736
kernel.shmall = 1610612736
kernel.shmmax = 1610612736
Here is some postgresql.conf stuff:
# Connection Parameters
#
#tcpip_socket = false
#ssl = false
#
#tcpip_socket = false
#ssl = false
max_connections = 128 # 1-1024
port = 5432
#hostname_lookup = false
#show_source_port = false
port = 5432
#hostname_lookup = false
#show_source_port = false
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777
#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 = 4096
sort_mem = 2048
#max_connections=128
#fsync=false
sort_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
pgsql-admin by date: