Re: increased load on server

Hi,

I had encountered this problem when there were a large number of writes on the disk (the operation involved bulk
imports- read and writes) and disk configuration at that point of time showed latency in write operations - and inturn
slowingdown the reads. The problem was resolved with following steps: 

1) Changing disk configuration - it was a RAID 5. Had it replaced by a RAID 10 array. You may not require a large array
dueto size of database, but it will certainly help to have multiple disks. 
2) Regular VACUUM (auto vacuum/ analyze,  manual vacuum/ analyze on certain tables during day and complete db vacuum
andanalyze during weekends). 
3) Regular reindexing - I schedule reindexes on tables after every 3-4 weeks.

HTH,

Shrirang Chitnis
Sr. Manager, Applications Development
HOV Services


Office: (866) 808-0935 Ext: 39210
shrirang.chitnis@hovservices.com
www.hovservices.com

The information contained in this message, including any attachments, is attorney privileged and/or confidential
informationintended only for the use of the individual or entity named as addressee.  The review, dissemination,
distributionor copying of this communication by or to anyone other than the intended addressee is strictly prohibited.
Ifyou have received this communication in error, please immediately notify the sender by replying to the message and
destroyall copies of the original message. 

-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: Saturday, February 12, 2011 8:05 PM
To: jfmeteo@gmail.com; pgsql-admin@postgresql.org
Subject: Re: increased load on server

jf  wrote:

>>> I tried to restart postgresql server and to reboot the server,
>>> but 5 minutes later, the trouble was here again.
>>
>> Any idea what changed during those 5 minutes?
> 5 minutes: just the time for my users to reconnect to my frontend.

> max_fsm_pages = 153600

Probably not the main problem, but you should be sure to schedule a
VACUUM ANALYZE of the whole database (run as the database superuser)
and check the last few lines to make sure the free space manager has
enough space to prevent bloat.

> I saw those things during the problem:
> - a lot of blocks read per seconds (max to 46k) on the disk which
> only contains /var/lib (PostgreSQL datas but not pg_xlog)
> - lot of blocks write on the same disk (max 20k) and on the system
> + pg_xlog disk (max 25k)

> max_connections = 100
> shared_buffers = 64MB

> Connections during problem: peak to 63.
> During normal use my average number is 6.5
>
> My system:
> - RAM: 4Go
> - 2 disks:
> - sdb: contains only /var/lib,
> /var/lib/postgresql/8.3/main/pg_xlog is a symlink to an other
> partition on an other disk
> - sda contains partitions for /, /var, /tmp, /boot, /home
> - CPU: Intel Xeon 1.6G with 4 cores
> - No error on Linux system logs: dmesg, /var/log/syslog

You need to use some sort of connection pool to funnel those requests
through about nine or ten connections.  When you have more
connections than that active on the hardware you describe, you're
going to cut both throughput and response time, just when you don't
want those to suffer.  You want a pool which has a hard upper limit
on number of connections and will queue requests when at the limit.

Once you've done that you'll be in a better position to determine
whether your hardware is hefty enough to handle the load.  In
particular, I'm concerned that you don't have RAID, both from a
performance perspective, and from the ability to survive a drive
failure.  Those do happen.

-Kevin