Обсуждение: 8.4 to 9.2 migration performance
I am migrating a Postgres 8.4 installation on a dedicated server to Postgres 9.2 running on a Virtual Machine. A sample query that run in 10 minutes on the 8.4 installation take 40 minutes on the 9.2 installation.
Current Server, Postgres 8.4
· 6-core, 3GHz AMD system
· 12GB of RAM
· 4 SATA drive RAID-1 storage
· Mandriva OS
· SQL encoding and ‘C’ collation
Virtual Machine, Postgres 9.2 ( two different systems)
· 4-core, 3Ghz Intel system
· 12GB or RAM
· SAS storage on one, and 4-SATA drive RAID-10 system on second
· CentOS 6.3 OS
· UTF-8 encoding, and I have tried both ‘C’ and en_US collation
The first VM is at a local Data Center and the second in on a dedicated server in my office. Both give similar results.
The data, indexes and constraints have all been successfully migrated to the new system.
I have tuned the VM systems using pgtune with no significant before and after difference.
The ‘explain’ output for the query is very different between the two systems.
It seems like I am missing some simple step for there to be such a huge performance difference.
Any suggestions on what else to text/check would be very much appreciated.
Tom
The ‘explain’ output for the query is very different between the two systems.
Also, some tips on getting answers with (potentially) less ping pong discussion can be found here: https://wiki.postgresql.org/wiki/Slow_Query_Questions