Re: Performance question - Mailing list pgsql-admin

Please cc the list so others can learn and help.

Yes, if ODBC is tearing the connection down after every call,
performance *WILL* suck. Setup some kind of persistent connection.
Better yet, don't make lots of tiny calls to the database if you can
avoid it.

On Mon, Dec 04, 2006 at 06:28:03PM -0700, Benjamin Krajmalnik wrote:
> Hi Jim,
>
> Apples to apples (as best as I can tell).
> The test procedure was as follows:
>
> I captured the SQL statements which call the stored procedures from 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.
> Execution speed for the code in question was sub 10 seconds.
> Running the query directly via ODBC is taking about 60 ms.
>
> The numbers I just stated above are after I removed the code which was
> accessing the one partitioned table where the constraint exclusion was
> not taking place (more on that later).  This code happened to be
> updating a daily aggregate - so each SP call was selecting the test
> record for the day and aggregating data.  I ended up adding a few more
> fields to a records which keeps some data in arrays for quick access to
> generate our graphs, and now I run a scheduled task at midnight which
> takes the previous days aggregates and creates our snapshot records
> (which we use for historical trending of up to 24 months).  This process
> now takes about 5 seconds, whereas running it through the stored
> procedure it was adding almost 200 miliseconds to the execution time).
> I assume this was caused by the fact that there was a select - and the
> execution planner was not isolating to the single partitioned table,
> followed by either an insert or another update.
> The new code is much more efficient and loads the db much less, so it
> will be the preferred method.
> Right now the datra path between the monitoring appliacne and the data
> server is 100Mbit.  We are moving it to Gig tonight - so at least I hope
> I will realize some speed advantages.  I have been monitoring the
> switch, and the actual throughput is only 150kbit/sec, so I do not have
> a data throughput bpttlenecg, but rather I suspect latencies in the ODBC
> connection.
>
> The item I found interesting is that the statement duration was much
> longer.
> What I am thinking is that when running through psql, it is using a
> local pipe.
> When running through ODBC, each stored procedure call is creating a
> connection and then disconnecting, so it has added a huge overhead.  I
> think the duration is including the time it is taking to
> connect/authenticate disconnect/teardown.
>
> Thanks for the tip on the constraint exclusion.
> I need to go back and see how we are querying it for the reports, and
> will adjust it accordingly.
> Does it make a difference if it is timestamp or timestamp without
> timezone?
>
>
> > -----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
> >
> > Are you sure you're doing an apples-apples comparison? Is the
> > load on both machines the same, or does production have extra
> > stuff running?
> > Have you tried your original test again in the same setup
> > without ODBC to eliminate that possibility? (or redone your
> > original test with ODBC).
> >
> > As for the constraint exclusion, try changing the query so
> > that '2006-12-01' is cast to a timestamp (or change the
> > constraints so that you're constraining dates... the check
> > constraints should really match the type of the underlying table).
> >
> > On Dec 1, 2006, at 9:18 PM, Benjamin Krajmalnik wrote:
> >
> > > 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
> > > aggregation of data.
> > >
> > > I captured the stored procedure calls from a production system and
> > > pumped them through psql, logging duration.
> > > The stored procedure ran at about 30ms per stored procedure call -
> > > which
> > > was significantly faster than the previous production system.
> > > Based on
> > > these results, I was extremely optimistic and migrated to the new
> > > 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 "all" logging.
> > >
> > > The only difference is that when I pumped the data in the test
> > > environment, I pumped it by reading the script via psql,
> > whereas the
> > > current system is calling the stored procedure via ODBC through a
> > > 100Mbit ethernet connection.
> > >
> > > I cannot explain the difference between the 2 scenarios.  Does the
> > > duration include the overhead of the ODBC layer?
> > >
> > > Now, the second part that was troubling relates to the execution
> > > planner.
> > >
> > >
> > > Two of the tables in which data is being entered are partitioned
> > > tables.
> > > Data is being routed to the partition by using rules.  Each
> > partition
> > > has a check constraint on a date range.  Constraint 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 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
> > > troubleshooting, I omitted the code from the stored procedure which
> > > accesses this data.  In this table, I attempt to retrieve the daily
> > > record for a specific test.  If it is not found, after aggregating
> > > data I insert it.  If the record already exists, data is aggregated
> > > and the record is updated.
> > >
> > > The key is composed of a date field (monthdate) and an int4 field
> > > (kstestsysid).
> > > The check constraints are of the form of:
> > >
> > > ALTER TABLE tblkssnapshot12
> > >   ADD CONSTRAINT tblkssnapshot12_chk CHECK (monthdate >=
> > '2006-12-01
> > > 00:00:00'::timestamp without time zone AND monthdate < '2007-01-01
> > > 00:00:00'::timestamp without time zone);
> > >
> > > I ran a sample query which would be issued by the stored procedure
> > > with the explain option.
> > > The query was:
> > >
> > > select monthdate, kstestssysid from tblkssnapshotdaily
> > where monthdate
> > > = '2006-12-01' and kstestssysid = 3143
> > >
> > > The explain is as follows:
> > >
> > > select monthdate, kstestssysid from tblkssnapshotdaily
> > where monthdate
> > > = '2006-12-01' and kstestssysid = 3143 Result  (cost=0.00..122.49
> > > rows=25 width=8)
> > >   ->  Append  (cost=0.00..122.49 rows=25 width=8)
> > >         ->  Index Scan using tblkssnapshotdaily_idx_monthtest on
> > > tblkssnapshotdaily  (cost=0.00..3.52 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot01_idx_monthtest on
> > > tblkssnapshot01 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot02_idx_monthtest on
> > > tblkssnapshot02 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot03_idx_monthtest on
> > > tblkssnapshot03 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot04_idx_monthtest on
> > > tblkssnapshot04 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot05_idx_monthtest on
> > > tblkssnapshot05 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot06_idx_monthtest on
> > > tblkssnapshot06 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot07_idx_monthtest on
> > > tblkssnapshot07 tblkssnapshotdaily  (cost=0.00..5.94 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot08_idx_monthtest on
> > > tblkssnapshot08 tblkssnapshotdaily  (cost=0.00..4.73 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot09_idx_monthtest on
> > > tblkssnapshot09 tblkssnapshotdaily  (cost=0.00..5.56 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot10_idx_monthtest on
> > > tblkssnapshot10 tblkssnapshotdaily  (cost=0.00..5.75 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot11_idx_monthtest on
> > > tblkssnapshot11 tblkssnapshotdaily  (cost=0.00..5.81 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot12_idx_monthtest on
> > > tblkssnapshot12 tblkssnapshotdaily  (cost=0.00..4.33 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot13_idx_monthtest on
> > > tblkssnapshot13 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot14_idx_monthtest on
> > > tblkssnapshot14 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot15_idx_monthtest on
> > > tblkssnapshot15 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot16_idx_monthtest on
> > > tblkssnapshot16 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot17_idx_monthtest on
> > > tblkssnapshot17 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot18_idx_monthtest on
> > > tblkssnapshot18 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot19_idx_monthtest on
> > > tblkssnapshot19 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot20_idx_monthtest on
> > > tblkssnapshot20 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot21_idx_monthtest on
> > > tblkssnapshot21 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot22_idx_monthtest on
> > > tblkssnapshot22 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot23_idx_monthtest on
> > > tblkssnapshot23 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 3143))
> > >         ->  Index Scan using tblkssnapshot24_idx_monthtest on
> > > tblkssnapshot24 tblkssnapshotdaily  (cost=0.00..4.83 rows=1 width=8)
> > >               Index Cond: ((monthdate = '2006-12-01'::date) AND
> > > (kstestssysid = 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 parent
> > > table, but the end result was significantly slower performance (by
> > > orders of magnitude).
> > >
> > >
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 5: don't forget to increase your free space map settings
> > >
> >
> > --
> > Jim Nasby                                            jim@nasby.net
> > EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
> >
> >
> >
>

--
Jim C. Nasby, Database Architect                decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"