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...
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.
-- Khalid M. Baheyeldin 2bits.com http://2bits.com Drupal optimization, development, customization and consulting.