matthewnourse

All the best with the stabilizing, please add me to your beta users list whenever you're ready :).

Sounds cool, can you share a link/download ?

So James & Paul, are your needs covered by Custora or one of the other services? Or is there something more that one or both of you need?

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.

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!

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.

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?

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.

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.

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

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.

Yes, right now they are one and the same.

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

Syntactically it's a cross between UNIX shell script and SQL and it's got some nice performance/scalability characteristics. Am very keen to hear the collective HN wisdom about it.

Sounds great! Even better if you can squeeze in another run. For me, consistency is the key...whatever I can keep doing consistently is the most helpful. For legs the best things I've found are sprinting in sand and bike riding up long hills in tall gears. The more I beg for mercy in the morning, the better the code I write in the afternoon :).

I'd love to see where the crossover point is on different architectures...especially when the size of the collection is just over the CPU's L1 cache size. I wonder if the vector's 1-or-many cache miss(es) would exceed the cost of the list's guaranteed-one-or-two cache miss(es).