Re: Performance Bottleneck - Mailing list pgsql-novice

Martin Foster wrote:
 > I run a Perl/CGI driven website that makes extensive use of PostgreSQL
 > (7.4.3) for everything from user information to formatting and display
 > of specific sections of the site.   The server itself, is a dual
 > processor AMD Opteron 1.4Ghz w/ 2GB Ram and 2 x 120GB hard drives
 > mirrored for redundancy running under FreeBSD 5.2.1 (AMD64).
 >
 > Recently loads on the site have increased during peak hours to the point
 > of showing considerable loss in performance.    This can be observed
 > when connections move from the 120 concurrent connections to PostgreSQL
 > to roughly 175 or more.     Essentially, the machine seems to struggle
 > to keep up with continual requests and slows down respectively as
 > resources are tied down.

There are several aspects which are performance-related.

First, try to find out exactly where the bottleneck is.
Are you sure that it's the database server backends?
(I.e. not the web server, not the perl CGIs or anything
else?  I assume you use mod_perl, do you?)

Are you running out of processor time, or out of RAM so
the machine starts swapping/paging, or is there an I/O
bandwidth bottleneck, or maybe it's even the network?
Commands like "top", "vmstat", "iostat", "netstat" etc.
will help finding out.  If "top" displays nearly 100%
"user CPU state", then the processor time is your problem,
otherwise it's more likely that I/O is the bottleneck.

As long as you have enough RAM available, yu can try
increasing FSM values etc. in your postgresql.conf (you
might need to increase the kernel limits on SysV shared
memory, too).

Disabling fsync, as you suggested, will improve performance
somewhat (especially if you have a lot of insert and update
commands), at the cost of increased risk of inconsistencies
and data loss in the case of a crash.  If you're willing to
go that far, you might as well mount your data filesystem
async (or with soft-updates), and with the "noatime" flag.

Second, there are several ways to tune FreeBSD's settings
for your particular application.  I suggest that you study
the tuning(7) manual page.  It covers a lot of topics,
raning from optimized newfs filesystem parameters to things
like network buffers, VFS settings and VM settings.  It
also contains pointers to several other related manual
pages.

Third, there might be room for optimizing your database
queries.  Use the explain analyze command to find out which
query plans are choosen for your select statements.  Maybe
there are ways you can improve on them, e.g. by creating
an appropriate index, by re-formulating the queries in a
different way, or by adapting your database design in a way
that suits your queries better and allows for improved
performance.  (If in doubt, you can post your queries in
this list, and I'm sure someone will have good advice for
you.)

Best regards
   Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

"To this day, many C programmers believe that 'strong typing'
just means pounding extra hard on the keyboard."
        -- Peter van der Linden