3 ms·

Sure, here it is: name | current_setting -----

jpitz 15y ago
lc_collate | en_AU.utf8 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.
- matthewnourse 15y ago
  Cool, thanks! 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?
  - jpitz 15y ago
    I've no idea how those behave with utf8 data. I'm more interested in what the memory settings do to the benchmark. After that, I'd want to see EXPLAIN ANALYZE.
    - matthewnourse 15y ago
      They work as expected for English. They "work" for languages with characters outside the English set, but they order based on ASCII byte values rather than the order expected by native speakers. Proper collation is a TODO for r17. 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.php http://archives.postgresql.org/pgsql-bugs/2008-02/msg00220.p... Thanks again!

jpitz 15y ago

lc_collate | en_AU.utf8 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.

matthewnourse 15y ago
Cool, thanks! 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?
- jpitz 15y ago
  I've no idea how those behave with utf8 data. I'm more interested in what the memory settings do to the benchmark. After that, I'd want to see EXPLAIN ANALYZE.
  - matthewnourse 15y ago
    They work as expected for English. They "work" for languages with characters outside the English set, but they order based on ASCII byte values rather than the order expected by native speakers. Proper collation is a TODO for r17. 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.php http://archives.postgresql.org/pgsql-bugs/2008-02/msg00220.p... Thanks again!