Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 820456501A2 for ; Sun, 24 Aug 2008 19:30:54 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 65953-08 for ; Sun, 24 Aug 2008 19:30:45 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.227]) by postgresql.org (Postfix) with ESMTP id 5B6FC6500CD for ; Sun, 24 Aug 2008 19:30:49 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id i31so575090wxd.28 for ; Sun, 24 Aug 2008 15:30:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:sender :to:subject:cc:mime-version:content-type:x-google-sender-auth; bh=ex+iKS3T9163/oCtbi4HwcPVEaJm1FXW9XDH69/6IlY=; b=RSTwL4wPcciK8NRMyVnzt8+wHISRRw07WQHuaDw8bpu+y5wlXkPesfWNFq+5gilpgT uk10/1+35oF0TSrZ1EgtIHESJWF+JHMo9J6x7weuV+Pj+8zvYI4DOZCDzqjdq5p39t9l 0UriBzJZWt8daeYRHC1qHO1JJPTo+i2znpGWA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:sender:to:subject:cc:mime-version:content-type :x-google-sender-auth; b=mFrU5LhTN+wfH9j3It1URhvAo8UG3w0M3azYn0DuZgQLNORwRKvo25/86MzZRI4Tw+ 8aqshd1q3tF48Q1SSUyodFNfAbtnHWNzEndGUJtx+vFsKIi0/c1jB5h5eD16ZjfCTcfN jCBh9rjakFuA8MfWNnAQxJuayFDurz102noB0= Received: by 10.70.51.7 with SMTP id y7mr5137511wxy.84.1219617044058; Sun, 24 Aug 2008 15:30:44 -0700 (PDT) Received: by 10.70.22.18 with HTTP; Sun, 24 Aug 2008 15:30:44 -0700 (PDT) Message-ID: <1775c5ea0808241530o23baf46fo21bc1c9c16f39563@mail.gmail.com> Date: Sun, 24 Aug 2008 23:30:44 +0100 From: "Loic Petit" To: peter.schuller@infidyne.com Subject: Re: Large number of tables slow insert Cc: pgsql-performance@postgresql.org, hhall1001@reedyriver.com, scott@richrelevance.com, dforums@vieonet.com, max@nts.biz.ua MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_102651_29572968.1219617044149" X-Google-Sender-Auth: c7fbad104f04572f X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200808/312 X-Sequence-Number: 31193 ------=_Part_102651_29572968.1219617044149 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Quite a lot of answers ! > Does all INSERTs are made by one application? Maybe PREPARED STATEMENTS will help you? What about optimization on application level? Yes it's on only one application (through JDBC), optimizations (grouped transaction, prepared statement) will be done but that our very first test in hard condition which scared us all :p. > Can you tell us what kind of application this is? It sounds like a contro= l 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 sensor= s realistic? That would be a lot of sensors for one control system. Our research project is trying to manage large scale sensor network deployments. 3.000 is quite a huge deployment but it can be realistic for huge aggricultural deployment for example. > 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 measurin= g with the instruments e.g. is this a process plant or manufacturing facility= ? What will people do with this data? I try to suppress the indexes the more I can. Actually I only really need the index on timestamp to see for example the last readings, and to search for a historical data by period, the others (values) are more for "when thi= s sensor was over 45=BAC" for instance but it can be without indexes (will be slow but less heavy at insert time). I get the data from differents telosb motes that gathers temperature / humidity and light. > Have you checked what you are bottlenecking on - CPU or disk? Try iostat/top/etc during the inserts. Also check actual disk utilizatio (iosta= t -x on linux/freebsd; varies on others) to see what percentage of time the disk/storage device is busy. I saw the results of iostat and top, the postgres process was at 70% cpu . Yes I know that my test machine is not brand new but I have to find a good solution with this. Ok I just ran some tests. It seems that I spammed too much right after the creation of the tables, thus the vacuum analyse could not be ran. I have better results now : Average of writing 10 rows in each table ON 1000 TABLES Without indexes at all : ~1.5s With only the index on timestamp : ~2.5s With all indexes : ~30s ON 3000 TABLES Without indexes at all : ~8s With only the index on timestamp : ~45s With all indexes : ~3min I don't know why but the difference is quite huge with indexes ! When I di= d my vacuum the system told me about the "max_fsm_relations" (1000). Do you think it will change something (as Scott said). I didn't have time to run tests with vacuum analyze on system table see you tomorow for other news... ------=_Part_102651_29572968.1219617044149 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Quite a lot of answers !

> Does all INSERTs are = made by one application? Maybe PREPARED STATEMENTS will help you? What abou= t optimization on application level?
Yes it's on only one applicatio= n (through JDBC), optimizations (grouped transaction, prepared statement) w= ill be done but that our very first test in hard condition which scared us = all :p.

> 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?  A= lso is 3,000 sensors realistic? That would be a lot of sensors for one cont= rol system.
Our research project is trying to manage large scale sensor network deploym= ents. 3.000 is quite a huge deployment but it can be realistic for huge agg= ricultural deployment for example.

> 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?
I try to suppress the indexes the more I can. Actually I only really need t= he index on timestamp to see for example the last readings, and to search f= or a historical data by period, the others (values) are more for "when= this sensor was over 45=BAC" for instance but it can be without index= es (will be slow but less heavy at insert time). I get the data from differ= ents telosb motes that gathers temperature / humidity and light.

> Have you checked what you are bottlenecking on - CPU or disk? Try = iostat/top/etc during the inserts. Also check actual disk utilizatio (iosta= t -x on linux/freebsd; varies on others) to see what percentage of time the= disk/storage device is busy.
I saw the results of iostat and top, the postgres process was at 70% cpu . = Yes I know that my test machine is not brand new but I have to find a good = solution with this.

Ok I just ran some tests. It seems that I spamme= d too much right after the creation of the tables, thus the vacuum analyse = could not be ran. I have better results now :

Average of writing 10 rows in each table
ON 1000 TABLES
 &n= bsp;  Without indexes at all : ~1.5s
    With only = the index on timestamp : ~2.5s
    With all indexes : ~30s

ON 3000 TABLES
    Without indexes at all : ~8s
    With only the index on timestamp : ~45s
  &= nbsp; With all indexes : ~3min

I don't know why but the differen= ce is quite huge with indexes  ! When I did my vacuum the system told = me about the "max_fsm_relations" (1000). Do you think it will cha= nge something (as Scott said). I didn't have time to run tests with vac= uum analyze on system table see you tomorow for other news...
------=_Part_102651_29572968.1219617044149--