4 ms·

This fails at the most basic benchmark rules. Do you really think PostgreSQL is 20-40x slower than alternative implementations? Do you think that is reasonable?

This fails at the most basic benchmark rules. Do you really think PostgreSQL is 20-40x slower than alternative implementations? Do you think that is reasonable? I'm going to go ahead and assume (as with most benchmarks) that the PostgreSQL instance was not configured properly and was running a stock configuration.

jpitz 15y ago
Agree. Can we see the results of running the query on this page? http://wiki.postgresql.org/wiki/Server_Configuration http://wiki.postgresql.org/wiki/Server_Configuration The stock config is violently wrong for a machine with 12GB of RAM.
- matthewnourse 15y ago
  Sure, here it is: 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)
  - 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!
matthewnourse 15y ago
[re fail] my main goal is to find out if r17 is "generally useful" for other people doing data mining, _not_ to pull the wool over any eyes. What would you like to see done differently? I am very happy to redo the comparison...or better yet, help someone else to redo it and satisfy themselves (or not) of r17's usefulness. [re reasonableness PostgreSQL being 20-40x slower] Yes, but only at some things. In the first bakeoff (http://www.rseventeen.com/blog/bakeoff_part_1_mysql_postgresql_r17.html http://www.rseventeen.com/blog/bakeoff_part_1_mysql_postgres...) PostgreSQL was clearly "better" than MySQL. And r17 can't do transactions and is useless for anything "on line", which is where PostgreSQL really shines. (As commented below) next up I'll be baking off against Hadoop, which is a more apples<->apples comparison. [re misconfiguration] is there a specific configuration that you would prefer that I use? I'm also happy to show the EXPLAIN output if that would help.
- molecule 15y ago
  In default configuration for mysql and postgresql daemons, these benchmarks don't do anything to make full use of the hardware cited. Here are guides for the respective db configs. http://www.revsys.com/writings/postgresql-performance.html http://www.revsys.com/writings/postgresql-performance.html http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/ http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-...
- jpitz 15y ago
  The effective_cache_size and shared_buffers settings from the revsys link are good, but thats generally older guidance ( max_fsm_pages is gone now ) If r17 doesn't use fsync.... Well. Perhaps you could show a postgres benchmark with that off. That's "Eat my data" mode. I don't recommend that mode, but it is faster, and might be more apples-to-apples. MAINTENANCE_WORK_MEM and WORK_MEM could also help. Maybe. Thats a big dataset. On SSD, random_page_cost may need tuned closer to sequential_page_cost.

jpitz 15y ago

Agree. Can we see the results of running the query on this page? http://wiki.postgresql.org/wiki/Server_Configuration http://wiki.postgresql.org/wiki/Server_Configuration The stock config is violently wrong for a machine with 12GB of RAM.

matthewnourse 15y ago
Sure, here it is: 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)
- 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!

matthewnourse 15y ago

[re fail] my main goal is to find out if r17 is "generally useful" for other people doing data mining, _not_ to pull the wool over any eyes. What would you like to see done differently? I am very happy to redo the comparison...or better yet, help someone else to redo it and satisfy themselves (or not) of r17's usefulness. [re reasonableness PostgreSQL being 20-40x slower] Yes, but only at some things. In the first bakeoff (http://www.rseventeen.com/blog/bakeoff_part_1_mysql_postgresql_r17.html http://www.rseventeen.com/blog/bakeoff_part_1_mysql_postgres...) PostgreSQL was clearly "better" than MySQL. And r17 can't do transactions and is useless for anything "on line", which is where PostgreSQL really shines. (As commented below) next up I'll be baking off against Hadoop, which is a more apples<->apples comparison. [re misconfiguration] is there a specific configuration that you would prefer that I use? I'm also happy to show the EXPLAIN output if that would help.

molecule 15y ago
In default configuration for mysql and postgresql daemons, these benchmarks don't do anything to make full use of the hardware cited. Here are guides for the respective db configs. http://www.revsys.com/writings/postgresql-performance.html http://www.revsys.com/writings/postgresql-performance.html http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/ http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-...
jpitz 15y ago
The effective_cache_size and shared_buffers settings from the revsys link are good, but thats generally older guidance ( max_fsm_pages is gone now ) If r17 doesn't use fsync.... Well. Perhaps you could show a postgres benchmark with that off. That's "Eat my data" mode. I don't recommend that mode, but it is faster, and might be more apples-to-apples. MAINTENANCE_WORK_MEM and WORK_MEM could also help. Maybe. Thats a big dataset. On SSD, random_page_cost may need tuned closer to sequential_page_cost.