undefined | Better HN

0 pointsdanielmewes11y ago0 comments

We did a couple of scalability improvements in 2.0, but didn't optimize groups and counts specifically.

Would you mind writing me an email with your query or opening an issue at https://github.com/rethinkdb/rethinkdb/issues (unless you have already?)? I'd like to look into it to see how we can best improve this.

We're planning to implemented a faster count algorithm that might help with this (https://github.com/rethinkdb/rethinkdb/issues/3949), but it's not completely trivial and will take us slightly longer to implement.

0 comments

6 comments · 1 top-level

lobster_johnson11y ago· 5 in thread

What I was doing is so trivial, you don't really need this information. This was my reference SQL query:

  select path, count(*) from posts group by path;

(I don't have the exact Rethink query written down, but it was analogous to the SQL version.)

You can demonstrate RethinkDB's performance issue with any largeish dataset by trying to group on a single field.

The path column in this case has a cardinality of 94, and the whole dataset is about 1 million documents. Some rows are big, some not; each has metadata plus a JSON document. The Postgres table is around 3.1GB (1GB for the main table + a 2.1GB TOAST table). Postgres does a seqscan + hash aggregate in about 1500ms.

It's been months since I did this, and I've since deleted RethinkDB and my test dataset.

danielmewesOP11y ago

As a second data point: I tried

    table.groupBy(function (x) { ... }).count()

where the function maps the documents into one out of 32 groups (so that's less than your 94, but shouldn't make a giant difference... I just had this database around). Did that on both 1 million and a 25 million document table, and memory usage looked fine and very stable. This was on RethinkDB 2.0, and I might retry that on 1.16 later to see if I can reproduce it there.

Do you remember if you had set an explicit cache size back when you were testing RethinkDB?

lobster_johnson11y ago

Cool. Well, the process eventually crashed if I used the defaults. I had to give it a 6GB cache (I think, maybe it was more) for it to return anything. The process would actually allocate that much, too, so it's clear that it was effectively loading everything into memory.

SamReidHughes11y ago

Are you sure the analogous RethinkDB query was using the index? Iirc it's not enough just to use the column name (or wasn't, I don't keep up).

lobster_johnson11y ago

It wasn't using an index, but then Postgres wasn't, either. I don't think aggregating via B-tree index is a good idea; aggregation is inherently suited to sequential access. An index is useful only when the selectivity is very low.

1 more reply

danielmewesOP11y ago

Thanks for the info. I'll look into this. The fact that we are running out of memory suggests that we're doing something wrong for this query.

j / k navigate · click thread line to collapse

0 comments

6 comments · 1 top-level

lobster_johnson11y ago· 5 in thread

What I was doing is so trivial, you don't really need this information. This was my reference SQL query:

  select path, count(*) from posts group by path;

(I don't have the exact Rethink query written down, but it was analogous to the SQL version.)

You can demonstrate RethinkDB's performance issue with any largeish dataset by trying to group on a single field.

It's been months since I did this, and I've since deleted RethinkDB and my test dataset.

danielmewesOP11y ago

As a second data point: I tried

    table.groupBy(function (x) { ... }).count()

Do you remember if you had set an explicit cache size back when you were testing RethinkDB?

lobster_johnson11y ago

SamReidHughes11y ago

Are you sure the analogous RethinkDB query was using the index? Iirc it's not enough just to use the column name (or wasn't, I don't keep up).

lobster_johnson11y ago

1 more reply

danielmewesOP11y ago

Thanks for the info. I'll look into this. The fact that we are running out of memory suggests that we're doing something wrong for this query.

j / k navigate · click thread line to collapse