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
