R17 on spinning disk faster than PostgreSQL on SSD • Hacker News Board

Re: R17 on spinning disk faster than PostgreSQL on SSD

#21

Post by matthewnourse » Tue, Aug 16, 2011, 4:29 AM UTC

skimbrel wrote: post #7

matthewnourse wrote: post #2

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

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…

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.

Re: R17 on spinning disk faster than PostgreSQL on SSD

#22

Post by jpitz » Tue, Aug 16, 2011, 4:32 AM UTC

matthewnourse wrote: post #20

jpitz wrote: post #17

Earlier quoted context omitted.

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?

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.

Re: R17 on spinning disk faster than PostgreSQL on SSD

#23

Post by justinsb » Tue, Aug 16, 2011, 4:28 PM UTC

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.

Re: R17 on spinning disk faster than PostgreSQL on SSD

#24

Post by matthewnourse » Wed, Aug 17, 2011, 12:11 AM UTC

jpitz wrote: post #22

matthewnourse wrote: post #20

Earlier quoted context omitted.

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?

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.

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

Thanks again!

Re: R17 on spinning disk faster than PostgreSQL on SSD

#25

Post by matthewnourse » Wed, Aug 17, 2011, 12:42 AM UTC

justinsb wrote: post #23

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

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.