Agree. Can we see the results of running the query on this page? http://wiki.postgresql.org/wiki/Server_Configuration
The stock config is violently wrong for a machine with 12GB of RAM.
The stock config is violently wrong for a machine with 12GB of RAM.
name | current_setting
version | PostgreSQL 9.0.4 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit
external_pid_file | /var/run/postgresql/9.0-main.pid
lc_collate | en_AU.utf8
lc_ctype | en_AU.utf8
log_line_prefix | %t
max_connections | 100
max_stack_depth | 2MB
port | 5432
server_encoding | UTF8
shared_buffers | 32MB
ssl | on
TimeZone | localtime
unix_socket_directory | /var/run/postgresql
(13 rows)
lc_ctype | en_AU.utf8
server_encoding | UTF8
Does r17 use utf-8?
shared_buffers | 32MB
Thats mean! :D
Should be 3GB on that machine. effective_cache_size ought to be 10-12GB ish.
Will alter and re-run in a few minutes.
r17 supports only UTF8 strings but compares with strcmp/strcasecmp/memcmp depending on the situation. Would you like a different collation for PostgreSQL too?
I'm more interested in what the memory settings do to the benchmark. After that, I'd want to see EXPLAIN ANALYZE.
I made these changes to postgresql.conf and restarted postgresql:
random_page_cost = 1.0
effective_cache_size = 11GB
shared_buffers = 3GB
The times for load, index and the queries were about the same as for the published run.
My apologies, I should have said earlier & in the blog that PostgreSQL is _CPU bound_ even before I made these changes. Next time I will also track CPU and disk usage and publish those for more clarity.
I've re-run EXPLAIN and done a bit more digging than before, looks like PostgreSQL does not use the "username" index to do the GROUP BY, I suspect it's related to this: http://archives.postgresql.org/pgsql-bugs/2008-02/msg00220.p...
Thanks again!