> 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@xxxxxxxxxxxx>' Key retrieval: Send an E-Mail to getpgpkey@xxxxxxxxx E-Mail: peter.schuller@xxxxxxxxxxxx Web: http://www.scode.org
Attachment:
pgpQCxWNhH0GL.pgp
Description: PGP signature
