Re: Performance problems with Postgresql
| От | Andrew Biagioni |
|---|---|
| Тема | Re: Performance problems with Postgresql |
| Дата | |
| Msg-id | 3E7A0B2A.4010907@e-greek.net обсуждение исходный текст |
| Ответ на | Re: Performance problems with Postgresql ("Ben Jordan" <bjordan@esbex.com>) |
| Список | pgsql-admin |
I didn't see a response to your question yet, so I'll throw in my 2 cents.
I had similar questions as yours; I found my answers in section 3.5 of the Administrator's Guide (I looked at v.7.2.x, but it's also in v7.3.1). They are all available at
http://www.us.postgresql.org/users-lounge/docs/
Hopefully, though not all-encompassing, it should be enough to help you figure out what needs doing.
The other issue, it sees, is that you don't have good logs, OR you don't know what to look for in them. If you look at the postgresql.conf (section 3.4 in the Admin Guide) you see that you can log pretty much everything, and as of v7.3 you can specify timeouts on queries. If you turn on most all logging options, any time a transaction is interrupted you get an ERROR printout specifying what got interrupted and why (if it's due to the timeout, is just says "query was cancelled" or something like that).
If you can post printouts of log files, probably people more competent than myself can help you figure out the details of your problems.
Andrew
Ben Jordan wrote:
#
# Performance
#
#sort_mem = 512
#shared_buffers = 2*max_connections # min 16
#fsync = true
I had similar questions as yours; I found my answers in section 3.5 of the Administrator's Guide (I looked at v.7.2.x, but it's also in v7.3.1). They are all available at
http://www.us.postgresql.org/users-lounge/docs/
Hopefully, though not all-encompassing, it should be enough to help you figure out what needs doing.
The other issue, it sees, is that you don't have good logs, OR you don't know what to look for in them. If you look at the postgresql.conf (section 3.4 in the Admin Guide) you see that you can log pretty much everything, and as of v7.3 you can specify timeouts on queries. If you turn on most all logging options, any time a transaction is interrupted you get an ERROR printout specifying what got interrupted and why (if it's due to the timeout, is just says "query was cancelled" or something like that).
If you can post printouts of log files, probably people more competent than myself can help you figure out the details of your problems.
Andrew
Ben Jordan wrote:
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 по дате отправления: