On Nov 26, 2007 1:52 PM, Bill Moran <wmoran@potentialtech.com> wrote:

My point is:
a) Let me send you a tweaked postgresql.conf file and well find
out whether the performance difference is simply a matter of
untuned configuration and ...

I will post them for all to see, maybe someone will point out something else.

These are the non comment lines in postgresql.conf

hba_file = '/etc/postgresql/8.2/main/pg_hba.conf'
ident_file = '/etc/postgresql/8.2/main/pg_ident.conf'

external_pid_file = '/var/run/postgresql/8.2-main.pid'
listen_addresses = 'localhost'
port = 5432
max_connections = 100
unix_socket_directory = '/var/run/postgresql'
shared_buffers = 24MB

max_fsm_pages = 153600
log_line_prefix = '%t '
stats_row_level = on
autovacuum = on
datestyle = 'iso, dmy'
lc_messages = 'en_CA.UTF-8'
lc_monetary = 'en_CA.UTF-8'
lc_numeric = 'en_CA.UTF-8'

lc_time = 'en_CA.UTF-8'

For MySQL, this is the default delivered with Ubuntu (and I imagine Debian would be close).

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
bind-address = 127.0.0.1
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
query_cache_limit       = 1M
query_cache_size        = 16M
log_bin   = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size         = 100M
skip-bdb

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]

[isamchk]
key_buffer = 16M

!includedir /etc/mysql/conf.d/

Note that there are no config files in the includedir.

What I normally do (not for this benchmark, but for tuning) is:

Comment out the bin-log lines (log_bin, expire_logs_days, max_bin_log_size). This takes of some load, but more importantly saves disk space on some starved VPSs.
Add skip-innodb, which saves some 100MB of RAM for the MySQL.

These are irrelevant to the benchmark at hand anyway, but is useful to know.

For larger web sites, I use a configuration similar to the one listed here:

http://2bits.com/articles/mysql-my-cnf-configuration-for-a-large-drupal-site.html

b) Let me have the query log after the test and I'll see if I
can identify any queries that could be improved, then someone
else can test to see if the improved queries are acceptable
from the MySQL standpoint.