Thread: Slower Performance on Postgres 9.1.6 vs 8.2.11
All... first let me say thank you for this forum.... I am new to it and
relatively new to postgres, more of a sysadmin than a DBA, but let me
explain my issue. I'll try also to post relevant information as well.
Our IT group took over an app that we have running using postgres and it has
been on version 8.2.11 since we acquired it. It is time to get current, so
I have created instances of our production database that mirror exact
hardware for our existing implementation on version 8.2.11 (running Fedora
Core 8 - wow I know) and also version 9.1.6 on Fedora 17. I am able to
mimic the production 8.2 environment exactly without any of the load of
production and the same for the new 9.1 environment so there is no
perverting of numbers based on load that I can't control
Machines are Cloud based images running 4 (dual Core) Processors, with 15GB
of memory... AMAZON m1.Xlarge boxes - 64 bit OS.
I'm running my query using PSQL from the server
Here is what I discovered. I have this Query:
SELECT s.customernumber AS "Cust Num",
s.name AS "Site",
UPPER( p.name ) AS "Product",
UPPER( ii.lotnumber ) AS "Lot Number",
SUM( ii.quantityremaining ) AS "On Hand"
FROM inventoryitems ii
INNER JOIN inventories i ON i.inventoryid = ii.inventoryid
INNER JOIN sites s ON s.siteid = i.siteid
INNER JOIN accounts a ON a.accountid = s.accountid
INNER JOIN products p ON p.productid = ii.productid
WHERE a.customernumber = 'DS-1007'
GROUP BY s.customernumber, s.name, UPPER( p.name ), UPPER( ii.lotnumber )
HAVING SUM( ii.quantityremaining ) > 0
ORDER BY s.name, UPPER( p.name );
EXPLAIN ANALYZE OUTPUT on 8.2.11 is as follows:
http://explain.depesz.com/s/JdW
-or-
(20 rows)
EXPLAIN ANALYZE OUTPUT on 9.1.6 is as follows:
http://explain.depesz.com/s/QZVF
I KNOW, I KNOW the difference is VERY small in terms of actual time, but
percentage wise this is statistically relevant and we are under a crunch to
make our application perform better.
In looking at the explain analyze output, it appears that in every case, 9.1
out performed the 8.2.11 in actually getting the data, but the NESTED LOOP
time is slow enough to make the Total Runtime but as much as a 10th of a
second slower on average...
I have tried tweaking every parameter I can think of and here are some of
the relevant Parameters from my POSTGRESQL.CONF file (and both machines are
running with KERNEL value " sysctl -w kernel.shmmax=665544320" )
9.1.6 values
max_connections = 250
shared_buffers = 800MB
temp_buffers = 8MB
work_mem = 10MB
maintenance_work_mem = 100MB
wal_buffers = 16MB
effective_cache_size = 8GB
8.2.11 values
max_connections = 250
shared_buffers = 600MB
temp_buffers = 1024
work_mem = 6MB
maintenance_work_mem = 100MB
wal_buffers = 64kB
effective_cache_size = 8GB
In my first attempt at migrating to 9.1 I had a different lc_collate value
at the default and the 9.1 query was running at around 2500 to 2600 ms and
that was huge... When I re-init'd my DB with the proper lc_locale set, I
expected my issue to be gone, and while it was to the extent of performance
before, it is still slower consistently.
AGAIN, the time difference is in the nested loop nodes themselves, not in
the Index Scan's. I don't understand this...
Any help will be greatly appreciated.
Rob Cron
rocron@pssd.com
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Slower-Performance-on-Postgres-9-1-6-vs-8-2-11-tp5729749.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
robcron <rocron@pssd.com> writes:
> Our IT group took over an app that we have running using postgres and it has
> been on version 8.2.11 since we acquired it. It is time to get current, so
> I have created instances of our production database that mirror exact
> hardware for our existing implementation on version 8.2.11 (running Fedora
> Core 8 - wow I know) and also version 9.1.6 on Fedora 17. I am able to
> mimic the production 8.2 environment exactly without any of the load of
> production and the same for the new 9.1 environment so there is no
> perverting of numbers based on load that I can't control
> Machines are Cloud based images running 4 (dual Core) Processors, with 15GB
> of memory... AMAZON m1.Xlarge boxes - 64 bit OS.
Hm ... Amazon cloud is not exactly known for providing rock-stable
performance environment, but anyway the first thing I would have guessed
at, seeing that the plans are basically the same, was a non-C locale
setting. Another thing to check is whether the new machine has higher
timing overhead --- is the speed difference the same when you just run
the query, rather than EXPLAIN ANALYZE'ing it? (If not,
contrib/pg_test_timing from 9.2 or later might yield useful data.)
regards, tom lane
Sorry, Again, I'm really new and so don't know how I would go about getting results from "contrib/pg_test_timing" Is this something that can be done from psql prompt, or will I need my developers to get involved and write me something...? Sorry for being such a newbie....:) -- View this message in context: http://postgresql.1045698.n5.nabble.com/Slower-Performance-on-Postgres-9-1-6-vs-8-2-11-tp5729749p5729764.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
Okay, so I took EXPLAIN ANALYZE off and made sure that timing is on "psql" command \timing shows Timing = on Run the query several times.. 9.1.6 runs this query an average of 354 ms 8.2.11 runs this query an average of 437 ms So 9.1 IS FASTER Why is EXPLAIN ANALYZE showing the reverse...of that...? Evidently, since I fixed the database Collation ( set to a value of "C") it has been faster but I got locked into looking at the EXPLAIN ANALYZE results... MMMM very curious. Rob -- View this message in context: http://postgresql.1045698.n5.nabble.com/Slower-Performance-on-Postgres-9-1-6-vs-8-2-11-tp5729749p5729768.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
Hello 2012/10/26 robcron <rocron@pssd.com>: > Okay, so I took EXPLAIN ANALYZE off and made sure that timing is on "psql" > command \timing shows > > Timing = on > > Run the query several times.. > > 9.1.6 runs this query an average of 354 ms > 8.2.11 runs this query an average of 437 ms > > So 9.1 IS FASTER > > Why is EXPLAIN ANALYZE showing the reverse...of that...? > > Evidently, since I fixed the database Collation ( set to a value of "C") it > has been faster but I got locked into looking at the EXPLAIN ANALYZE > results... > > MMMM very curious. 9.1 EXPLAIN ANALYZE collect significantly more information about execution - so there can be higher overhead Regards Pavel > > Rob > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/Slower-Performance-on-Postgres-9-1-6-vs-8-2-11-tp5729749p5729768.html > Sent from the PostgreSQL - performance mailing list archive at Nabble.com. > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance
Thank you all for your replies. I did figure out what is going on. 9.1 is indeed faster than 8.2.11 so we are good to go forward. Thank you again -- View this message in context: http://postgresql.1045698.n5.nabble.com/Slower-Performance-on-Postgres-9-1-6-vs-8-2-11-tp5729749p5729991.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.