Date: Sun, 24 Aug 2008 23:29:53 +0200
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
Message-ID: <20080824212953.GB51548@hyperion.scode.org>
References: <1775c5ea0808221841u746862e3h1b721ea82fdb7794@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="1UWUbFP1cBYEclgG"
Content-Disposition: inline
In-Reply-To: <1775c5ea0808221841u746862e3h1b721ea82fdb7794@mail.gmail.com>
User-Agent: Mutt/1.5.18 (2008-05-17)


--1UWUbFP1cBYEclgG
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

> 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 cre=
ate
> 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 f=
ew
> tuples (< 10). I think you can understand that it's not efficient at all
> because I need to treat a lot of inserts.
>=20
> 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.

--=20
/ 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


--1UWUbFP1cBYEclgG
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.9 (FreeBSD)

iEYEARECAAYFAkix0tAACgkQDNor2+l1i32yoQCbBbcBotMlL5a73lhxzuuaSIqw
iM4AoOMAn0teOQK7HHcmJqbTfqZvxCra
=4keq
-----END PGP SIGNATURE-----

--1UWUbFP1cBYEclgG--