Re: Performance problems with Postgresql

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:

> 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*
>
> *H**ere 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
>
> *Here is some postgresql.conf stuff:*
>
> # Connection Parameters
> #
> #tcpip_socket = false
> #ssl = false
>
> *max_connections = 128* # 1-1024
> port = 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 = 4096
> 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
> bjordan(at)esbex(dot)com <mailto:bjordan(at)esbex(dot)com>