pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Martin Foster <martin@ethereal-realms.org>
To: PostgreSQL Novice <pgsql-novice@postgresql.org>
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Extreme high load averages
Date: Sat, 05 Jul 2003 22:54:57 -0600
Message-ID: <3F07ABA1.2070100@ethereal-realms.org> (raw)

The only time that I have ever seen load averages of 30 or more under 
OpenBSD is when one of my scripts goes wild.    However, I can say that 
I am also seeing these load averages under PostgreSQL 7.3.2 after a 
migration to it from MySQL.

MySQL Statistics:
Uptime: 1055352  Threads: 178  Questions: 75161710  Slow queries: 46 
Opens: 1084  Flush tables: 1  Open tables: 206 Queries per second avg: 
71.220

The above are statistics from older generation scripts that would make 
use of MySQL as to give an idea of what's going on.   That generation of 
scripts would handle the referential integrity, since foreign key 
constraints are not enforced under that system.   However, the system 
handled 250 concurrent users without a singular problem, while under 
Postgres with new scripts using functions, referential integrity, 
transactions and lighter code, the system starts to buckle at even less 
then 70 users.

What I would like to know is.   Why?   The kernel has been compiled to 
handle the number of concurrent connections, the server may not be the 
best, but it should be able to handle the requests: PIII 1Ghz, 1GB 
SDRAM, 2 IDE 20GB drives.

I have changed settings to take advantage of the memory.  So the 
following settings are of interest:
	shared_buffers = 16384
	wal_buffers = 256
	sort_mem = 16384
	vacuum_mem = 32768

Statistics gathering has now been disabled, and logging is done through 
syslog.    I do not expect those settings to cripple system performance 
however.

The scripts are heavy SELECTS with a fair dose of UPDATES and INSERTS. 
  To get a concept of what these scripts done, you can look at Ethereal 
Realms (http://www.ethereal-realms.org) which are running the PostgreSQL 
script variants or consider that this is a chat site.

Anyone have ideas?   Is the use of connection pooling consider bad? 
Should flush be run more then once a day? I have no intention of going 
back to MySQL, and would like to make this new solution work.

	Martin Foster
	Creator/Designer Ethereal Realms
	martin@ethereal-realms.org





view thread (22+ messages)  latest in thread

Message-ID: <3F07ABA1.2070100@ethereal-realms.org>
Permalink:  ../3F07ABA1.2070100@ethereal-realms.org/
Also on:    postgresql.org/message-id/3F07ABA1.2070100@ethereal-realms.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: martin@ethereal-realms.org, pgsql-novice@postgresql.org
  Subject: Re: Extreme high load averages
  In-Reply-To: <3F07ABA1.2070100@ethereal-realms.org>
  References: <3F07ABA1.2070100@ethereal-realms.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox