[KLUG Members] Postgres questions.

[members@kalamazoolinux.org]

>Don't know if there are a lot of postgres gurus out there, but hoo boy, I
>need some help.

Me use PostgreSQL too.

>I'm in charge of a rather busy postgres server.  It's been running 7 point
>something for  a while now, and it's running smoothly.  Well, except for
>postgres seems to be slowing down bit by bit every day.  I've made some
>changes in the past to try to speed things up, but to no avail.  Here's
>how I run it, pretty much:
>postmaster -i -N 128 -B 256 -D/[data]
>This is upgraded from the standard N of 32, and B of 64 (I think?  Number
>of connections and Buffers, for the un-initiated) because I was getting
>'too many connections' errors.

Yep.  Do you actually have close to 128 connections?  If so I think -B256 is a
bit conservative.  Thats only 2Mb!

>So anyway, every night, I Vaccuum (or try, I get the errors like
>"transaction in progress, can't shrink relation, but I haven't found a
>solution to that) and do other items related to the database itself that
>allegedly will help speed things up.

Eck!  Not bieng able to Vaccuum is going to kill you.  Is there no time when you
think the db should be idle?

>However, it just gets slower.  

Yes, the nature of an RDBMS.  My $50k commercial one does the same thing.  They
require constant cooing and nuzzling to remain happy.

>My users (who are snippy to begin with) and
>now becoming brutal, and I really have no recourse but to just say, "Shut
>your pie hole, it's your own damn fault".  The person who set up this
>system is gone, and not entirely available, and I really don't want to
>kludge stuff here.
>If you have any general pointers, I'd be glad to hear them, but I don't
>have much to say or do here...

cut-n-pasted from another message on the same topic:

1. Is your data directory in it's own filesystem?  If not you need it to be.  If
so set the noatime mount option,  that will reduce background I/O significantly.
 How full is that filesystem?  (Optimal sweet-spot for file expansion under
ext2/ext3 is a filesystem ~30% full.  Real filesystems like xfs do not care).

2. Are your users accessing the database via TCP/IP (ODBC, etc...)?  If so
increase the systems send/recieve buffers.

3. See any "out of file handles" messages in dmesg?  If your fs.file-max isn't
already jacked up, set it to something like 32767. (look at fs.file-nr)

4.  What type of filesystem is your data directory on?  ext2, etx3, xfs, etc...
  If it is a journalling system a.) What is your journal mode? b.) Is your
journal internal or external? c.) What is the size of the journal? (If using
ext3 use tune2fs to create a nice large external journal and chattr -R +j your
data directory)

5.  How many drives do you have?  What kind (specs)?  Any RAID or LVM involved?
 If so what is your chunk to block size ratio?  If you monitor iostat for awhile
does it look unbalanced?

6. I you have alot of TCP connections and available RAM increase the portion
available to the TCP/IP stack so that is doesn't page clean as often.  If you
only use this host via LAN connections (no internet, no WAN) disable sack &
timestamps.

7.  If you have available RAM (server is nothing but a db server) and smart
disks (SCSI on a reputable controller) adjust bdflush so that more pages can be
buffered between iterations.  Your smart re-ordering drives & controller will
run more efficently.  If using IDE drives, replace tham with SCSI :)

8. Make sure your syslog isn't fsync() on log files you don't really care about
on a db server (maillog, etc...)