Message-ID: <48B1CC46.8080502@reedyriver.com>
Date: Sun, 24 Aug 2008 17:01:58 -0400
From: "H. Hall" <hhall1001@reedyriver.com>
User-Agent: Thunderbird 2.0.0.16 (Windows/20080708)
MIME-Version: 1.0
To: Loic Petit <tls.wydd@free.fr>, pgsql-performance@postgresql.org
Subject: Re: Large number of tables slow insert
References: <1775c5ea0808221841u746862e3h1b721ea82fdb7794@mail.gmail.com>
In-Reply-To: <1775c5ea0808221841u746862e3h1b721ea82fdb7794@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Loic Petit wrote:
> Hi,
>
> I use Postgresql 8.3.1-1 to store a lot of data coming from a large 
> amount of sensors. In order to have good performances on querying by 
> timestamp on each sensor, I partitionned my measures table for each 
> sensor. Thus I create a lot of tables.
> I simulated a large sensor network with 3000 nodes so I have ~3000 
> tables. And it appears that each insert (in separate transactions) in 
> the database takes about 300ms (3-4 insert per second) in tables where 
> there is just few tuples (< 10). I think you can understand that it's 
> not efficient at all because I need to treat a lot of inserts.
Can you tell us what kind of application this is? It sounds like a 
control systems application where you will write the current values of 
the sensors with each scan of a PLC.  If so, is that a good idea?  Also 
is 3,000 sensors realistic? That would be a lot of sensors for one 
control system.
>
> Do you have any idea why it is that slow ? and how can have good insert ?
How often do you write data for a sensor?
Once write per sensor per second = 3,000 writes per second
That would be an insert plus updates to each of your 6 indexes every 
0.33 ms .

Is that a good idea?  Is there a better strategy? What are you measuring 
with the instruments e.g. is this a process plant or manufacturing 
facility? What will people do with this data?
>
> My test machine: Intel p4 2.4ghz, 512mb ram, Ubuntu Server (ext3)
> iostat tells me that I do : 0.5MB/s reading and ~6-7MB/s writing while 
> constant insert
>
> Here is the DDL of the measures tables:
> -------------------------------------------------------
> CREATE TABLE measures_0
> (
>  "timestamp" timestamp without time zone,
>  storedtime timestamp with time zone,
>  count smallint,
>  "value" smallint[]
> )
> WITH (OIDS=FALSE);
> CREATE INDEX measures_0_1_idx
>  ON measures_0
>  USING btree
>  ((value[1]));
>
> -- Index: measures_0_2_idx
> CREATE INDEX measures_0_2_idx
>  ON measures_0
>  USING btree
>  ((value[2]));
>
> -- Index: measures_0_3_idx
> CREATE INDEX measures_0_3_idx
>  ON measures_0
>  USING btree
>  ((value[3]));
>
> -- Index: measures_0_count_idx
> CREATE INDEX measures_0_count_idx
>  ON measures_0
>  USING btree
>  (count);
>
> -- Index: measures_0_timestamp_idx
> CREATE INDEX measures_0_timestamp_idx
>  ON measures_0
>  USING btree
>  ("timestamp");
>
> -- Index: measures_0_value_idx
> CREATE INDEX measures_0_value_idx
>  ON measures_0
>  USING btree
>  (value);
> -------------------------------------------------------
>
> Regards
>
> Loīc Petit
>
> --------------------------------
>
>


-- 
H. Hall
ReedyRiver Group LLC
http://www.reedyriver.com