From: Peter Schuller <peter.schuller@infidyne.com>
To: Loic Petit <tls.wydd@free.fr>
Cc: pgsql-performance@postgresql.org
Subject: Re: Large number of tables slow insert
Date: Sun, 24 Aug 2008 23:29:53 +0200
Message-ID: <20080824212953.GB51548@hyperion.scode.org> (raw)
In-Reply-To: <1775c5ea0808221841u746862e3h1b721ea82fdb7794@mail.gmail.com>
References: <1775c5ea0808221841u746862e3h1b721ea82fdb7794@mail.gmail.com>
> 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.
>
> Do you have any idea why it is that slow ? and how can have good insert ?
>
> 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
Have you checked what you are bottlenecking on - CPU or disk? Try
iostat/top/etc during the inserts. Also check actual disk utilizatio
(iostat -x on linux/freebsd; varies on others) to see what percentage
of time the disk/storage device is busy.
You say you have 3-4 inserts/second causing 6-7 MB/s writing. That
suggests to me the inserts are fairly large. Are they in the MB range,
which would account for the I/O?
My suspicion is that you are bottlenecking on CPU, since in my
experience there is definitely something surprisingly slow about
encoding/decoding data at the protocol level or somewhere else that is
involved in backend/client communication. I.e, I suspect your client
and/or server is spending a lot of CPU time with things not directly
related to the actual table inserts. If so, various suggested schemes
w.r.t. indexing, table bloat etc won't help at all.
In short, 6-7 MB/second would be fairly consistent with INSERT/COPY
operations being CPU bound on a modern CPU, in my experience. It may
be that this is entirely untrue in your case, but it sounds like a
reasonable thing to at least consider.
--
/ Peter Schuller
PGP userID: 0xE9758B7D or 'Peter Schuller <peter.schuller@infidyne.com>'
Key retrieval: Send an E-Mail to getpgpkey@scode.org
E-Mail: peter.schuller@infidyne.com Web: http://www.scode.org
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: peter.schuller@infidyne.com, tls.wydd@free.fr
Subject: Re: Large number of tables slow insert
In-Reply-To: <20080824212953.GB51548@hyperion.scode.org>
* 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