4 ms·

R17 on spinning disk faster than PostgreSQL on SSD

13 points by matthewnourse 15y ago

matthewnourse 15y ago
R17 is a data mining language that's a cross between SQL and Bash. For example this SELECT username, COUNT(1) AS num FROM users GROUP BY username ORDER BY num; is roughly equivalent to io.file.read('users') | rel.select(username) | rel.group(count) | rel.order_by(_count); The most interesting difference is that each r17 clause executes concurrently :). Download link is here: http://www.rseventeen.com/#download http://www.rseventeen.com/#download
- pyre 15y ago
  Is R17 a language and an implementation then?
  - matthewnourse 15y ago
    Yes, right now they are one and the same.
- skimbrel 15y ago
  At first glance it looks like doing a query involves streaming the entire dataset into memory while selecting and projecting on the fly. If that's true, what happens when you have truly massive rows (i.e., things containing MEDIUMTEXTs or worse)? Okay, reading further down you only get very basic data types. Still, nothing in the spec appears to prohibit very long rows, and I'd imagine performance starts to fall off once you're throwing around tens of kilobytes per row. Any plans to support pushing the projection operation into the read phase so you can work with massive individual records? And where's the source? I want to see exactly how much this differs from a modern SQL engine.
  - matthewnourse 15y ago
    It streams the dataset into memory 256K (more for longer rows) at a time. It doesn't load the whole dataset into RAM unless it must eg for a join, sort or grouping. I don't currently have plans to push projection into the read phase, but the phases are all pretty close together :) so maybe it wouldn't be required. How massive is "massive" for you? 10s of K? Megs? R17 is not currently open source, but I haven't ruled it out.
kemiller 15y ago
I think it's disingenuous to measure load+index+query time and then declare that you're faster. Not saying there aren't use cases where that's valuable (analytics/warehousing db for one) but it's not what most people are looking for. Is this usable as a transactional store? What do those numbers look like? The pipeline model is very interesting, though, and I'll take a look for our warehouse, which at some point became too expensive to keep up with.
- matthewnourse 15y ago
  I agree that it would be disingenuous to base "faster" on load+index+query. I'm basing it on the query times alone. I would like very much to base it on load+index+query 'cause then I could have said "faster than MySQL and PostgreSQL" :) [and probably many systems, since r17 is built specifically for zero indexing overhead]. R17 is _only_ for analytics & warehousing, it doesn't do transactions at all. So in that sense this comparison is unfair, which is why next up I want to do the same comparison with Hadoop. Thanks for taking a closer look for your warehouse! Please contact me directly or comment somewhere if you'd like any help from me.
fleitz 15y ago
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).
- matthewnourse 15y ago
  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.
rbranson 15y ago
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?
ck2 15y ago
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?
- matthewnourse 15y ago
  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.
molecule 15y ago
what configurations were used for the mysql and postgres daemons?
justinsb 15y ago
This benchmark is probably accurate, but not necessarily fair. Here's why: If all the systems have to do a table scan (complete scan of the data), then you're (almost always) I/O bound. Because we're relying on sequential I/O, a good hard disk is as good as a consumer-grade SSD. Postgresql doesn't do compression (if rows fit on a single page), and MySQL doesn't by default either (though you can enable InnoDB page compression), so if you're using compression with r17 you can get more rows per MB transferred, so you win. However, this only works if you're doing table scans on highly compressible data. The minute you compete against an index, I suspect you're going to lose heavily (e.g. selecting the number of hits by second over a one minute interval should be very fast on the relational databases, as they should be able to answer from the index) MySQL and PostgreSQL are really OLTP databases, but you're running an OLAP workload here (multi-minute queries and table scans are essentially the definition of OLAP). As you say, a comparison against Hadoop is probably more relevant (as long as you compress the data). For this workload, an OLAP database is the tool to beat, and Hadoop is probably the leading contender in the open-source OLAP space. Though I'd normally bet on PostgreSQL, I think the easiest fix is to turn on Innodb compression in MySQL: you should see query times that are much more comparable. You'd probably still win (because MySQL is doing a lot more), but the margin should be much smaller. I think it's also possible to denormalize your tables and improve your indexing, so that we can avoid table scans and then PostgreSQL/MySQL should be much faster, but I don't think this is really the point of your benchmark. Edit: Just spotted a (much bigger) problem - if your data is 54GB raw, when compressed that should be within the memory size of your machine (12GB), but if MySQL and PostgreSQL aren't compressing their data won't fit into memory (particularly with all those unused indexes). So in fact you're comparing RAM speeds to SSD speeds, which really isn't apples to apples.
- matthewnourse 15y ago
  Thanks for taking the time to look over the tests in such detail! MySQL's EXPLAIN output says that it's using the indexes. PostgreSQL's EXPLAIN output says that it's _not_ (and from what I can tell so far, this is a deliberate design decision by PostgreSQL developers). r17 "loses" to MySQL on one query and "wins" on the other. So I agree that r17 has a much harder job competing against indexed data...but with r17 you didn't have to wait to create the index....not such a big deal for OLTP, a much bigger deal with OLAP. I would like to redo the bakeoff(s) with compressed InnoDB tables but as they can take several days to run and life is short, first I should focus on Hadoop as we both agree that is more relevant. Re the data size issue: the 54GB raw data set compresses to 28GB. The data generator I use creates data that's more random than the "real world" and so doesn't compress very well...makes life harder for r17, which is what I want. The smaller data set for the SSD test compresses to about 13GB, which isn't ideal I agree...I should have bought a larger SSD, I didn't think that MySQL would make so many large temporary files :). To mitigate this issue I ensured that the data set was _not_ cached before I ran the r17 script. I am very keen to find out the truth about r17's usefulness, thanks for your part in that. For the next bakeoff I'll provide more details about methods and machine behavior.

13 points by matthewnourse 15y ago

R17 is a data mining language that's a cross between SQL and Bash. For example this SELECT username, COUNT(1) AS num FROM users GROUP BY username ORDER BY num; is roughly equivalent to io.file.read('users') | rel.select(username) | rel.group(count) | rel.order_by(_count); The most interesting difference is that each r17 clause executes concurrently :). Download link is here: http://www.rseventeen.com/#download http://www.rseventeen.com/#download

pyre 15y ago
Is R17 a language and an implementation then?
- matthewnourse 15y ago
  Yes, right now they are one and the same.
skimbrel 15y ago
At first glance it looks like doing a query involves streaming the entire dataset into memory while selecting and projecting on the fly. If that's true, what happens when you have truly massive rows (i.e., things containing MEDIUMTEXTs or worse)? Okay, reading further down you only get very basic data types. Still, nothing in the spec appears to prohibit very long rows, and I'd imagine performance starts to fall off once you're throwing around tens of kilobytes per row. Any plans to support pushing the projection operation into the read phase so you can work with massive individual records? And where's the source? I want to see exactly how much this differs from a modern SQL engine.
- matthewnourse 15y ago
  It streams the dataset into memory 256K (more for longer rows) at a time. It doesn't load the whole dataset into RAM unless it must eg for a join, sort or grouping. I don't currently have plans to push projection into the read phase, but the phases are all pretty close together :) so maybe it wouldn't be required. How massive is "massive" for you? 10s of K? Megs? R17 is not currently open source, but I haven't ruled it out.

kemiller 15y ago

I think it's disingenuous to measure load+index+query time and then declare that you're faster. Not saying there aren't use cases where that's valuable (analytics/warehousing db for one) but it's not what most people are looking for. Is this usable as a transactional store? What do those numbers look like? The pipeline model is very interesting, though, and I'll take a look for our warehouse, which at some point became too expensive to keep up with.

matthewnourse 15y ago
I agree that it would be disingenuous to base "faster" on load+index+query. I'm basing it on the query times alone. I would like very much to base it on load+index+query 'cause then I could have said "faster than MySQL and PostgreSQL" :) [and probably many systems, since r17 is built specifically for zero indexing overhead]. R17 is _only_ for analytics & warehousing, it doesn't do transactions at all. So in that sense this comparison is unfair, which is why next up I want to do the same comparison with Hadoop. Thanks for taking a closer look for your warehouse! Please contact me directly or comment somewhere if you'd like any help from me.

fleitz 15y ago

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).

matthewnourse 15y ago
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.

rbranson 15y ago

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?

ck2 15y ago

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?

matthewnourse 15y ago
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.

molecule 15y ago

what configurations were used for the mysql and postgres daemons?

justinsb 15y ago

This benchmark is probably accurate, but not necessarily fair. Here's why: If all the systems have to do a table scan (complete scan of the data), then you're (almost always) I/O bound. Because we're relying on sequential I/O, a good hard disk is as good as a consumer-grade SSD. Postgresql doesn't do compression (if rows fit on a single page), and MySQL doesn't by default either (though you can enable InnoDB page compression), so if you're using compression with r17 you can get more rows per MB transferred, so you win. However, this only works if you're doing table scans on highly compressible data. The minute you compete against an index, I suspect you're going to lose heavily (e.g. selecting the number of hits by second over a one minute interval should be very fast on the relational databases, as they should be able to answer from the index) MySQL and PostgreSQL are really OLTP databases, but you're running an OLAP workload here (multi-minute queries and table scans are essentially the definition of OLAP). As you say, a comparison against Hadoop is probably more relevant (as long as you compress the data). For this workload, an OLAP database is the tool to beat, and Hadoop is probably the leading contender in the open-source OLAP space. Though I'd normally bet on PostgreSQL, I think the easiest fix is to turn on Innodb compression in MySQL: you should see query times that are much more comparable. You'd probably still win (because MySQL is doing a lot more), but the margin should be much smaller. I think it's also possible to denormalize your tables and improve your indexing, so that we can avoid table scans and then PostgreSQL/MySQL should be much faster, but I don't think this is really the point of your benchmark. Edit: Just spotted a (much bigger) problem - if your data is 54GB raw, when compressed that should be within the memory size of your machine (12GB), but if MySQL and PostgreSQL aren't compressing their data won't fit into memory (particularly with all those unused indexes). So in fact you're comparing RAM speeds to SSD speeds, which really isn't apples to apples.

matthewnourse 15y ago
Thanks for taking the time to look over the tests in such detail! MySQL's EXPLAIN output says that it's using the indexes. PostgreSQL's EXPLAIN output says that it's _not_ (and from what I can tell so far, this is a deliberate design decision by PostgreSQL developers). r17 "loses" to MySQL on one query and "wins" on the other. So I agree that r17 has a much harder job competing against indexed data...but with r17 you didn't have to wait to create the index....not such a big deal for OLTP, a much bigger deal with OLAP. I would like to redo the bakeoff(s) with compressed InnoDB tables but as they can take several days to run and life is short, first I should focus on Hadoop as we both agree that is more relevant. Re the data size issue: the 54GB raw data set compresses to 28GB. The data generator I use creates data that's more random than the "real world" and so doesn't compress very well...makes life harder for r17, which is what I want. The smaller data set for the SSD test compresses to about 13GB, which isn't ideal I agree...I should have bought a larger SSD, I didn't think that MySQL would make so many large temporary files :). To mitigate this issue I ensured that the data set was _not_ cached before I ran the r17 script. I am very keen to find out the truth about r17's usefulness, thanks for your part in that. For the next bakeoff I'll provide more details about methods and machine behavior.