Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 4C990136AEA1 for ; Wed, 30 Nov 2011 22:01:11 -0400 (AST) Received: from mail-vx0-f174.google.com ([209.85.220.174]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RVvxV-0003mY-Oc for pgsql-performance@postgresql.org; Thu, 01 Dec 2011 02:01:10 +0000 Received: by vcbfk14 with SMTP id fk14so1046771vcb.19 for ; Wed, 30 Nov 2011 18:00:56 -0800 (PST) Received: by 10.220.107.209 with SMTP id c17mr852860vcp.134.1322704856725; Wed, 30 Nov 2011 18:00:56 -0800 (PST) Received: from [192.168.1.110] (c-68-60-234-23.hsd1.il.comcast.net. [68.60.234.23]) by mx.google.com with ESMTPS id p2sm4668816vdi.22.2011.11.30.18.00.55 (version=SSLv3 cipher=OTHER); Wed, 30 Nov 2011 18:00:55 -0800 (PST) Message-ID: <4ED6DFD8.3080104@getcarbonblack.com> Date: Wed, 30 Nov 2011 20:00:56 -0600 From: Benjamin Johnson User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:8.0) Gecko/20111105 Thunderbird/8.0 MIME-Version: 1.0 To: Leonardo Francalanci CC: "pgsql-performance@postgresql.org" Subject: Re: Guidance Requested - Bulk Inserting + Queries References: <1322669853.10239.YahooMailNeo@web29003.mail.ird.yahoo.com> In-Reply-To: <1322669853.10239.YahooMailNeo@web29003.mail.ird.yahoo.com> X-Enigmail-Version: 1.3.3 Content-Type: multipart/alternative; boundary="------------040407000007040201050408" X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201111/289 X-Sequence-Number: 45800 This is a multi-part message in MIME format. --------------040407000007040201050408 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit We're trying to split the current day into hourly tables so that the size of the indexes that are popular is much lower and therefore we can support more rows across the day. We also are using numerics where we could be using bigints, so we're going to also work on that to see how much smaller we can get it. Once a daily table is not "today", we will remove duplicates, so we can combine that step with rolling up the hourly tables into one daily table. In a *small* test (1-2 orders of magnitude smaller than some potential customer environments), the cumulative size of the daily indexes is 3.6 GB and that's for only about half of the test. We're talking 4 different daily partitioned tables with each table having 1 - 6 indexes (yes, a lot!). I'll post another update when I have it. Thanks Leonardo. On 11/30/2011 10:17 AM, Leonardo Francalanci wrote: >> We now found (thanks Andres and Snow-Man in #postgresql) that in our >> tests, after the indexes get too large performance drops signficantly >> and our system limps forward due to disk reads (presumably for the >> indexes). If we remove the indexes, performance for our entire sample >> test is great and everything is written to postgresql very quickly. > It's usually the fact that the data you index is "random" as opposed to, > say, an always incremented value (could be a timestamp, or a sequence) > that leads to insert problems with btrees. >> My question is, what possible routes can I take where we can have both >> fast inserts (with indexes removed until the end of the day), but still >> allow a user to query against today's data? Is this even possible? One >> idea would be possibly have hourly tables for today and as soon as we >> can try to re-add indexes. > Yep, that's the only way I've found: use smaller partitions. That leads > to slower reads (due to the fact that you have to visit more indexes to > read the same amount of data). But you'll get faster writes. > >> Another possible solution might be to stream >> the data to another "reader" postgres instance that has indexes, >> although I'm not very versed in replication. > I don't think you can do that. > Another option that you have is to use ssd instead of HD for the indexes > only (that is, having the indexes in a separate tablespace of ssds). The > problem is that your disks usually can't keep up with the number of > random writes it takes to update N "random values" btrees; ssd might help. > Can you post some numbers, such as # of indexes, # of rows you're trying > to insert per hour etc etc? > > -- Benjamin Johnson http://getcarbonblack.com/ | @getcarbonblack cell: 312.933.3612 --------------040407000007040201050408 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit We're trying to split the current day into hourly tables so that the size of the indexes that are popular is much lower and therefore we can support more rows across the day.  We also are using numerics where we could be using bigints, so we're going to also work on that to see how much smaller we can get it.  Once a daily table is not "today", we will remove duplicates, so we can combine that step with rolling up the hourly tables into one daily table.

In a *small* test (1-2 orders of magnitude smaller than some potential customer environments), the cumulative size of the daily indexes is 3.6 GB and that's for only about half of the test.

We're talking 4 different daily partitioned tables with each table having 1 - 6 indexes (yes, a lot!).

I'll post another update when I have it.

Thanks Leonardo.

On 11/30/2011 10:17 AM, Leonardo Francalanci wrote:
>> We now found (thanks Andres and Snow-Man in #postgresql) that in our
>> tests, after the indexes get too large performance drops signficantly
>> and our system limps forward due to disk reads (presumably for the
>> indexes). If we remove the indexes, performance for our entire sample
>> test is great and everything is written to postgresql very quickly.
> It's usually the fact that the data you index is "random" as opposed to,
> say, an always incremented value (could be a timestamp, or a sequence)
> that leads to insert problems with btrees.
>> My question is, what possible routes can I take where we can have both
>> fast inserts (with indexes removed until the end of the day), but still
>> allow a user to query against today's data? Is this even possible? One
>> idea would be possibly have hourly tables for today and as soon as we
>> can try to re-add indexes.
> Yep, that's the only way I've found: use smaller partitions. That leads
> to slower reads (due to the fact that you have to visit more indexes to
> read the same amount of data). But you'll get faster writes.
>
>> Another possible solution might be to stream
>> the data to another "reader" postgres instance that has indexes,
>> although I'm not very versed in replication.
> I don't think you can do that.
> Another option that you have is to use ssd instead of HD for the indexes
> only (that is, having the indexes in a separate tablespace of ssds). The
> problem is that your disks usually can't keep up with the number of
> random writes it takes to update N "random values" btrees; ssd might help.
> Can you post some numbers, such as # of indexes, # of rows you're trying
> to insert per hour etc etc?
>
>

--
Benjamin Johnson
http://getcarbonblack.com/ | @getcarbonblack
cell: 312.933.3612

--------------040407000007040201050408--