Re: Millions of tables

On Tue, Sep 27, 2016 at 9:42 AM, Mike Sofen <msofen(at)runbox(dot)com> wrote:

> *From:* Mike Sofen *Sent:* Tuesday, September 27, 2016 8:10 AM
>
> *From:* Greg Spiegelberg *Sent:* Monday, September 26, 2016 7:25 AM
> I've gotten more responses than anticipated and have answered some
> questions and gotten some insight but my challenge again is what should I
> capture along the way to prove or disprove this storage pattern?
> Alternatives to the storage pattern aside, I need ideas to test rig,
> capture metrics and suggestions to tune it.
>
>
>
> In the next 24 hours, I will be sending ~1 trillion records to the test
> database. Because of time to set up, I'd rather have things set up
> properly the first go.
>
>
>
> Thanks!
>
> -Greg
>
> ---------------------
>
> Greg, I ran another quick test on a wider table than you’ve described, but
> this time with 80 million rows, with core counts, ram and ssd storage
> similar to what you’d have on that AWS EC2 instance. This table had 7
> columns (3 integers, 3 text, 1 timestamptz) with an average width of 157
> chars, one btree index on the pk int column. Using explain analyze, I
> picked one id value out of the 80m and ran a select * where id = x. It did
> an index scan, had a planning time of 0.077ms, and an execution time of
> 0.254 seconds. I ran the query for a variety of widely spaced values (so
> the data was uncached) and the timing never changed. This has been
> mirroring my general experience with PG – very fast reads on indexed
> queries.
>
>
>
> Summary: I think your buckets can be WAY bigger than you are envisioning
> for the simple table design you’ve described. I’m betting you can easily
> do 500 million rows per bucket before approaching anything close to the
> 30ms max query time.
>
>
>
> Mike Sofen (Synthetic Genomics)
>
>
>
> Totally typo’d the execution time: it was 0.254 MILLISECONDS, not
> SECONDS. Thus my comment about going up 10x in bucket size instead of
> appearing to be right at the limit. Sorry!
>
>
>
I figured. :)

Haven't ruled it out but expectations of this implementation is to perform
at worst 3X slower than memcache or Redis.

Bigger buckets mean a wider possibility of response times. Some buckets
may contain 140k records and some 100X more.

-Greg