Thread: After dump/restoring from 32bit 8.4-windows to 64bit 9.2.4-linux experiencing 10x slowdown on queries

On 04/09/2013 02:29 PM, Giovanni Martina wrote:
> Hi,
>
> I'm trying to upgrade our database server from postgresql 32-bit 8.2.4
> running on Windows Server 2008 to postgresql 64-bit 9.2.4 on ubuntu
> server 12.04.02 LTS.
>
> I have dumped one of our databases from our windows server and restored
> it on the postgres server running on ubuntu in order to test for
> incompatibilities. But the thing I am noticing playing with pgAdmin is
> that queries are being performed much more slowly on the linux server
> compared to the old windows 2k8 server, even with linux running on a ssd
> with more ram, faster cpu etc.
>
> I've tried running ANALYZE, VACUUM and combinations of these via pgAdmin
> on the linux database but performing queries consistently take 10x the
> amount of time that they take on the windows server so I'm obviously
> missing something here.
>
> The dump is a simple pg_dump -F c -f data.backup, then using pg_restore
> to restore in an empty database created with template0. What could be
> causing the new database to perform so abysmal?
>
>
connect with psql to your new database and run "reindex database <your
db name>;"

On 04/09/2013 02:29 PM, Giovanni Martina wrote:
> Hi,
>
> I'm trying to upgrade our database server from postgresql 32-bit 8.2.4
> running on Windows Server 2008 to postgresql 64-bit 9.2.4 on ubuntu
> server 12.04.02 LTS.
>
> I have dumped one of our databases from our windows server and restored
> it on the postgres server running on ubuntu in order to test for
> incompatibilities. But the thing I am noticing playing with pgAdmin is
> that queries are being performed much more slowly on the linux server
> compared to the old windows 2k8 server, even with linux running on a ssd
> with more ram, faster cpu etc.
>
> I've tried running ANALYZE, VACUUM and combinations of these via pgAdmin
> on the linux database but performing queries consistently take 10x the
> amount of time that they take on the windows server so I'm obviously
> missing something here.
>
> The dump is a simple pg_dump -F c -f data.backup, then using pg_restore
> to restore in an empty database created with template0. What could be
> causing the new database to perform so abysmal?
>
I suspect a spurious network configuration

Rob Sargent wrote:
> On 04/09/2013 02:29 PM, Giovanni Martina wrote:
>> I'm trying to upgrade our database server from postgresql 32-bit 8.2.4
>> running on Windows Server 2008 to postgresql 64-bit 9.2.4 on ubuntu
>> server 12.04.02 LTS.
>>
>> I have dumped one of our databases from our windows server and restored
>> it on the postgres server running on ubuntu in order to test for
>> incompatibilities. But the thing I am noticing playing with pgAdmin is
>> that queries are being performed much more slowly on the linux server
>> compared to the old windows 2k8 server, even with linux running on a ssd
>> with more ram, faster cpu etc.
>>
>> I've tried running ANALYZE, VACUUM and combinations of these via pgAdmin
>> on the linux database but performing queries consistently take 10x the
>> amount of time that they take on the windows server so I'm obviously
>> missing something here.
>>
>> The dump is a simple pg_dump -F c -f data.backup, then using pg_restore
>> to restore in an empty database created with template0. What could be
>> causing the new database to perform so abysmal?

> connect with psql to your new database and run "reindex database <your
> db name>;"

That would be pretty useless since the indexes have been
created recently.

I would try to identify the bottleneck: is it disk-I/O, CPU
or something else? Also, turn on log_duration to see if the
query takes long on the server or if network or client
processing are part of the problem.
Is postgresql.conf identical on both machines?

For more specific help, you'll have to share more details
about your setup.

Yours,
Laurenz Albe

Hi, did you find a resolution to this issue? I'm running into the same
problem now!



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/After-dump-restoring-from-32bit-8-4-windows-to-64bit-9-2-4-linux-experiencing-10x-slowdown-on-queries-tp5751526p5784087.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.