Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id A0E2C9F9398 for ; Mon, 4 Dec 2006 23:40:19 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 81098-09 for ; Mon, 4 Dec 2006 23:40:11 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.illumen.com (unknown [64.207.29.137]) by postgresql.org (Postfix) with ESMTP id 8F30B9FA192 for ; Mon, 4 Dec 2006 23:40:08 -0400 (AST) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable Subject: Re: Performance question X-MimeOLE: Produced By Microsoft Exchange V6.5 Date: Mon, 4 Dec 2006 20:40:06 -0700 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] Performance question Thread-Index: AccYHRIPwOVBMjxYQPut5/FLh4WA0QAAVcQQ From: "Benjamin Krajmalnik" To: "Jim C. Nasby" Cc: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=BAYES_50, SPF_PASS X-Spam-Level: X-Archive-Number: 200612/31 X-Sequence-Number: 23823 Unfortunately, the application doing the logging is not written by me, so I have to play with the deck of cards which I am dealt. The developer stated that he uses connection pooling, but apparently the pg odbc driver does not support it, since I can see a new connection being made for each statement execution. I have asked him to see if he can create one connection and maintain it - we'll see. I have another scenario to try out, whereby instead of making a stored procedure call via ODBC I will insert the parameters of the SP call into a table via simple insert statements. If I see significant improvements (I am not holding my breath), I will use that route and run a background peocess on the server to issue the SP calls off of that recordset. > -----Original Message----- > From: Jim C. Nasby [mailto:decibel@decibel.org]=20 > Sent: Monday, December 04, 2006 8:25 PM > To: Benjamin Krajmalnik > Cc: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Performance question >=20 > Please cc the list so others can learn and help. >=20 > Yes, if ODBC is tearing the connection down after every call,=20 > performance *WILL* suck. Setup some kind of persistent connection. > Better yet, don't make lots of tiny calls to the database if=20 > you can avoid it. >=20 > On Mon, Dec 04, 2006 at 06:28:03PM -0700, Benjamin Krajmalnik wrote: > > Hi Jim, > >=20 > > Apples to apples (as best as I can tell). > > The test procedure was as follows: > >=20 > > I captured the SQL statements which call the stored procedures from=20 > > our monitoring box to a file. > > I copied the file to the FreeBSD data server. > > Executed psql, and pumped the file to it through \i filename.=20 > > Execution speed for the code in question was sub 10 seconds. > > Running the query directly via ODBC is taking about 60 ms. > >=20 > > The numbers I just stated above are after I removed the=20 > code which was=20 > > accessing the one partitioned table where the constraint=20 > exclusion was=20 > > not taking place (more on that later). This code happened to be=20 > > updating a daily aggregate - so each SP call was selecting the test=20 > > record for the day and aggregating data. I ended up adding=20 > a few more=20 > > fields to a records which keeps some data in arrays for=20 > quick access=20 > > to generate our graphs, and now I run a scheduled task at midnight=20 > > which takes the previous days aggregates and creates our snapshot=20 > > records (which we use for historical trending of up to 24 months). =20 > > This process now takes about 5 seconds, whereas running it=20 > through the=20 > > stored procedure it was adding almost 200 miliseconds to=20 > the execution time). > > I assume this was caused by the fact that there was a=20 > select - and the=20 > > execution planner was not isolating to the single=20 > partitioned table,=20 > > followed by either an insert or another update. > > The new code is much more efficient and loads the db much=20 > less, so it=20 > > will be the preferred method. > > Right now the datra path between the monitoring appliacne=20 > and the data=20 > > server is 100Mbit. We are moving it to Gig tonight - so at least I=20 > > hope I will realize some speed advantages. I have been=20 > monitoring the=20 > > switch, and the actual throughput is only 150kbit/sec, so I do not=20 > > have a data throughput bpttlenecg, but rather I suspect=20 > latencies in=20 > > the ODBC connection. > >=20 > > The item I found interesting is that the statement duration=20 > was much=20 > > longer. > > What I am thinking is that when running through psql, it is using a=20 > > local pipe. > > When running through ODBC, each stored procedure call is creating a=20 > > connection and then disconnecting, so it has added a huge=20 > overhead. I=20 > > think the duration is including the time it is taking to=20 > > connect/authenticate disconnect/teardown. > >=20 > > Thanks for the tip on the constraint exclusion. > > I need to go back and see how we are querying it for the=20 > reports, and=20 > > will adjust it accordingly. > > Does it make a difference if it is timestamp or timestamp without=20 > > timezone? > >=20 > >=20 > > > -----Original Message----- > > > From: Jim Nasby [mailto:decibel@decibel.org] > > > Sent: Monday, December 04, 2006 5:53 PM > > > To: Benjamin Krajmalnik > > > Cc: pgsql-admin@postgresql.org > > > Subject: Re: [ADMIN] Performance question > > >=20 > > > Are you sure you're doing an apples-apples comparison? Is=20 > the load=20 > > > on both machines the same, or does production have extra stuff=20 > > > running? > > > Have you tried your original test again in the same setup without=20 > > > ODBC to eliminate that possibility? (or redone your original test=20 > > > with ODBC). > > >=20 > > > As for the constraint exclusion, try changing the query so that=20 > > > '2006-12-01' is cast to a timestamp (or change the constraints so=20 > > > that you're constraining dates... the check constraints should=20 > > > really match the type of the underlying table). > > >=20 > > > On Dec 1, 2006, at 9:18 PM, Benjamin Krajmalnik wrote: > > >=20 > > > > I am battling a performance issue and was wondering if > > > someone could > > > > help. PostgreSQL 8.1.5, FreeBSD. > > > > > > > > I have a very intense stored procedure which performs real time=20 > > > > aggregation of data. > > > > > > > > I captured the stored procedure calls from a production=20 > system and=20 > > > > pumped them through psql, logging duration. > > > > The stored procedure ran at about 30ms per stored=20 > procedure call -=20 > > > > which > > > > was significantly faster than the previous production system. =20 > > > > Based on > > > > these results, I was extremely optimistic and migrated=20 > to the new=20 > > > > server. > > > > > > > > Now that we turned up the new server, the stored procedure > > > is taking > > > > over 250ms. > > > > These are the execution times as recorded by enable=20 > "all" logging. > > > > > > > > The only difference is that when I pumped the data in the test=20 > > > > environment, I pumped it by reading the script via psql, > > > whereas the > > > > current system is calling the stored procedure via ODBC=20 > through a=20 > > > > 100Mbit ethernet connection. > > > > > > > > I cannot explain the difference between the 2=20 > scenarios. Does the=20 > > > > duration include the overhead of the ODBC layer? > > > > > > > > Now, the second part that was troubling relates to the=20 > execution=20 > > > > planner. > > > > > > > > > > > > Two of the tables in which data is being entered are=20 > partitioned=20 > > > > tables. > > > > Data is being routed to the partition by using rules. Each > > > partition > > > > has a check constraint on a date range. Constraint=20 > exclusion is on. > > > > > > > > The first partitioned table is very simple, and the stored > > > procedure > > > > simply inserts a record for each test which is executed. These=20 > > > > are later used by a procedure running in the background which > > > calculates > > > > statistical data. > > > > > > > > My problem is with the second partitioned table. As part of my=20 > > > > troubleshooting, I omitted the code from the stored procedure=20 > > > > which accesses this data. In this table, I attempt to retrieve=20 > > > > the daily record for a specific test. If it is not=20 > found, after=20 > > > > aggregating data I insert it. If the record already=20 > exists, data=20 > > > > is aggregated and the record is updated. > > > > > > > > The key is composed of a date field (monthdate) and an=20 > int4 field=20 > > > > (kstestsysid). > > > > The check constraints are of the form of: > > > > > > > > ALTER TABLE tblkssnapshot12 > > > > ADD CONSTRAINT tblkssnapshot12_chk CHECK (monthdate >=3D > > > '2006-12-01 > > > > 00:00:00'::timestamp without time zone AND monthdate <=20 > '2007-01-01=20 > > > > 00:00:00'::timestamp without time zone); > > > > > > > > I ran a sample query which would be issued by the=20 > stored procedure=20 > > > > with the explain option. > > > > The query was: > > > > > > > > select monthdate, kstestssysid from tblkssnapshotdaily > > > where monthdate > > > > =3D '2006-12-01' and kstestssysid =3D 3143 > > > > > > > > The explain is as follows: > > > > > > > > select monthdate, kstestssysid from tblkssnapshotdaily > > > where monthdate > > > > =3D '2006-12-01' and kstestssysid =3D 3143 Result =20 > (cost=3D0.00..122.49 > > > > rows=3D25 width=3D8) > > > > -> Append (cost=3D0.00..122.49 rows=3D25 width=3D8) > > > > -> Index Scan using=20 > tblkssnapshotdaily_idx_monthtest on=20 > > > > tblkssnapshotdaily (cost=3D0.00..3.52 rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > -> Index Scan using tblkssnapshot01_idx_monthtest on > > > > tblkssnapshot01 tblkssnapshotdaily (cost=3D0.00..4.83=20 > rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > -> Index Scan using tblkssnapshot02_idx_monthtest on > > > > tblkssnapshot02 tblkssnapshotdaily (cost=3D0.00..4.83=20 > rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > -> Index Scan using tblkssnapshot03_idx_monthtest on > > > > tblkssnapshot03 tblkssnapshotdaily (cost=3D0.00..4.83=20 > rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > -> Index Scan using tblkssnapshot04_idx_monthtest on > > > > tblkssnapshot04 tblkssnapshotdaily (cost=3D0.00..4.83=20 > rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > -> Index Scan using tblkssnapshot05_idx_monthtest on > > > > tblkssnapshot05 tblkssnapshotdaily (cost=3D0.00..4.83=20 > rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > -> Index Scan using tblkssnapshot06_idx_monthtest on > > > > tblkssnapshot06 tblkssnapshotdaily (cost=3D0.00..4.83=20 > rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > -> Index Scan using tblkssnapshot07_idx_monthtest on > > > > tblkssnapshot07 tblkssnapshotdaily (cost=3D0.00..5.94=20 > rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > -> Index Scan using tblkssnapshot08_idx_monthtest on > > > > tblkssnapshot08 tblkssnapshotdaily (cost=3D0.00..4.73=20 > rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > -> Index Scan using tblkssnapshot09_idx_monthtest on > > > > tblkssnapshot09 tblkssnapshotdaily (cost=3D0.00..5.56=20 > rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > -> Index Scan using tblkssnapshot10_idx_monthtest on=20 > > > > tblkssnapshot10 tblkssnapshotdaily (cost=3D0.00..5.75=20 > rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > -> Index Scan using tblkssnapshot11_idx_monthtest on > > > > tblkssnapshot11 tblkssnapshotdaily (cost=3D0.00..5.81=20 > rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > -> Index Scan using tblkssnapshot12_idx_monthtest on > > > > tblkssnapshot12 tblkssnapshotdaily (cost=3D0.00..4.33=20 > rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > -> Index Scan using tblkssnapshot13_idx_monthtest on > > > > tblkssnapshot13 tblkssnapshotdaily (cost=3D0.00..4.83=20 > rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > -> Index Scan using tblkssnapshot14_idx_monthtest on > > > > tblkssnapshot14 tblkssnapshotdaily (cost=3D0.00..4.83=20 > rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > -> Index Scan using tblkssnapshot15_idx_monthtest on > > > > tblkssnapshot15 tblkssnapshotdaily (cost=3D0.00..4.83=20 > rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > -> Index Scan using tblkssnapshot16_idx_monthtest on > > > > tblkssnapshot16 tblkssnapshotdaily (cost=3D0.00..4.83=20 > rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > -> Index Scan using tblkssnapshot17_idx_monthtest on > > > > tblkssnapshot17 tblkssnapshotdaily (cost=3D0.00..4.83=20 > rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > -> Index Scan using tblkssnapshot18_idx_monthtest on > > > > tblkssnapshot18 tblkssnapshotdaily (cost=3D0.00..4.83=20 > rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > -> Index Scan using tblkssnapshot19_idx_monthtest on > > > > tblkssnapshot19 tblkssnapshotdaily (cost=3D0.00..4.83=20 > rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > -> Index Scan using tblkssnapshot20_idx_monthtest on=20 > > > > tblkssnapshot20 tblkssnapshotdaily (cost=3D0.00..4.83=20 > rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > -> Index Scan using tblkssnapshot21_idx_monthtest on > > > > tblkssnapshot21 tblkssnapshotdaily (cost=3D0.00..4.83=20 > rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > -> Index Scan using tblkssnapshot22_idx_monthtest on > > > > tblkssnapshot22 tblkssnapshotdaily (cost=3D0.00..4.83=20 > rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > -> Index Scan using tblkssnapshot23_idx_monthtest on > > > > tblkssnapshot23 tblkssnapshotdaily (cost=3D0.00..4.83=20 > rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > -> Index Scan using tblkssnapshot24_idx_monthtest on > > > > tblkssnapshot24 tblkssnapshotdaily (cost=3D0.00..4.83=20 > rows=3D1 width=3D8) > > > > Index Cond: ((monthdate =3D '2006-12-01'::date) = AND=20 > > > > (kstestssysid =3D 3143)) > > > > > > > > If constraint exclusion is on, why are index scans taking > > > place on all > > > > of the tables and not only on the only partitioned table > > > for which the > > > > constraint exists? > > > > > > > > I went to a partitioned table thinking this would improve > > > performance > > > > from the previous schema in which all of the data was in the=20 > > > > parent table, but the end result was significantly slower=20 > > > > performance (by orders of magnitude). > > > > > > > > > > > > > > > > ---------------------------(end of > > > > broadcast)--------------------------- > > > > TIP 5: don't forget to increase your free space map settings > > > > > > >=20 > > > -- > > > Jim Nasby jim@nasby.net > > > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > > >=20 > > >=20 > > >=20 > >=20 >=20 > --=20 > Jim C. Nasby, Database Architect decibel@decibel.org=20 > Give your computer some brain candy! www.distributed.net Team #1828 >=20 > Windows: "Where do you want to go today?" > Linux: "Where do you want to go tomorrow?" > FreeBSD: "Are you guys coming, or what?" >=20