Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

R17 on spinning disk faster than PostgreSQL on SSD

rseventeen.com

Post reply on HN
Print view View original

11–20 of 25 posts

Re: R17 on spinning disk faster than PostgreSQL on SSD

#11

Post by jpitz »

rbranson wrote: post #8

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.

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.

Re: R17 on spinning disk faster than PostgreSQL on SSD

#13

Post by matthewnourse »

rbranson wrote: post #8

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.

[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_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 applesapples 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.

Re: R17 on spinning disk faster than PostgreSQL on SSD

#14

Post by molecule »

matthewnourse wrote: post #13
rbranson wrote: post #8

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.

[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 ba…

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.mysqlperformanceblog.com/2006/09/29/what-to-tune-...

Re: R17 on spinning disk faster than PostgreSQL on SSD

#15

Post by matthewnourse »

jpitz wrote: post #11
rbranson wrote: post #8

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.

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.

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)

Re: R17 on spinning disk faster than PostgreSQL on SSD

#16

Post by jpitz »

matthewnourse wrote: post #13
rbranson wrote: post #8

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.

[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 ba…

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.

Re: R17 on spinning disk faster than PostgreSQL on SSD

#17

Post by jpitz »

matthewnourse wrote: post #15
jpitz wrote: post #11

Earlier quoted context omitted.

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.

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_con…

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.

Re: R17 on spinning disk faster than PostgreSQL on SSD

#18

Post by matthewnourse »

ck2 wrote: post #10

Boosting speed, fantastic - replacing the completely easy and logical SQL language, not so much: io.file.read('users') | rel.select(username) | rel.group(count) | rel.order_by(_count); is SELECT username, COUNT(1) AS num FROM users GROUP BY username ORDER BY num; seriously? why?

I hear you, and I would have preferred to use the SQL (or any other well-understood) syntax. Some of the reasons are:

1) I want the query writer to be in complete control over what happens first, rather than a query optimizer. The r17 syntax makes this explicit.

2) Similarly, I want it to be clear which things will be executed in parallel and which won't.

3) I agree that SQL is "completely easy and logical" if your query is small but for large SQL queries I find that I need to understand the query as one big mass. With r17 you can take it one clause at a time. It's a bit like a stack-based language in this respect. You can keep adding clauses at the end of a pipeline, eg WHERE (or even multiple WHEREs) can come after a GROUP BY, ORDER BY etc...contrast this with SQL's need for a special HAVING clause.

Re: R17 on spinning disk faster than PostgreSQL on SSD

#19

Post by matthewnourse »

fleitz wrote: post #6

That's great but does it beat SSAS? Does it beat LINQ? It seems that the primary competitors to r17 would be K/Q,LINQ,Powershell,and possibly SSAS (unsure how much statistical power is in r17).

r17 doesn't run on Windows, and I don't have any plans to make it work on Windows at this time.

Apart from that niggle, I agree...more bakeoffs needed. There's not much statistical power in r17, it's more a brute force thing. If you want more finesse the idea is to hand off to a fancier (and most likely slower) language like R.

Re: R17 on spinning disk faster than PostgreSQL on SSD

#20

Post by matthewnourse »

jpitz wrote: post #17
matthewnourse wrote: post #15

Earlier quoted context omitted.

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_con…

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.

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?

Post reply on HN