From: H. Hall <hhall1001@reedyriver.com>
To: Loic Petit <tls.wydd@free.fr>
To: pgsql-performance@postgresql.org
Subject: Re: Large number of tables slow insert
Date: Sun, 24 Aug 2008 17:01:58 -0400
Message-ID: <48B1CC46.8080502@reedyriver.com> (raw)
In-Reply-To: <1775c5ea0808221841u746862e3h1b721ea82fdb7794@mail.gmail.com>
References: <1775c5ea0808221841u746862e3h1b721ea82fdb7794@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: hhall1001@reedyriver.com, tls.wydd@free.fr
Subject: Re: Large number of tables slow insert
In-Reply-To: <48B1CC46.8080502@reedyriver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox