Skip site navigation (1) Skip section navigation (2)

Re: test / live environment, major performance difference

From: Christo Du Preez <christo(at)mecola(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: test / live environment, major performance difference
Date: 2007-06-12 06:36:28
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
I wonder if my dump/restore routine isn't causing this issue. Seeing
that I do the db development on my laptop (the fast one) and then
restores it on the other two machines. I have confirmed if all the
indexes are present after a restore.

This is the routine:

/usr/local/pgsql/bin/pg_dump -t layer mapdb | gzip > layer.gz

rsync --progress --rsh=ssh layer.gz


/usr/local/pgsql/bin/pg_dump -t visiblelayer mapdb | gzip > visiblelayer.gz

rsync --progress --rsh=ssh visiblelayer.gz


/usr/local/pgsql/bin/pg_dump -t style mapdb | gzip > style.gz

rsync --progress --rsh=ssh style.gz


/usr/local/pgsql/bin/pg_dump -t layertype mapdb | gzip > layertype.gz

rsync --progress --rsh=ssh layertype.gz


DROP TABLE visiblelayer;
DROP TABLE layertype;

gunzip -c layertype.gz | /usr/local/pgsql/bin/psql mapdb
gunzip -c style.gz | /usr/local/pgsql/bin/psql mapdb
gunzip -c visiblelayer.gz | /usr/local/pgsql/bin/psql mapdb
gunzip -c layer.gz | /usr/local/pgsql/bin/psql mapdb

/usr/local/pgsql/bin/vacuumdb -d mapdb -z -v

Craig James wrote:
> On 2007-06-11 Christo Du Preez wrote:
>> I really hope someone can shed some light on my problem. I'm not sure
>> if this is a posgres or potgis issue.
>> Anyway, we have 2 development laptops and one live server, somehow I
>> managed to get the same query to perform very well om my laptop, but
>> on both the server and the other laptop it's really performing bad.
> One simple possibility that bit me in the past: If you do
> pg_dump/pg_restore to create a copy of the database, you have to
> ANALYZE the newly-restored database.  I mistakenly assumed that
> pg_restore would do this, but you have to run ANALYZE explicitely
> after a restore.
> Craig
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>       message can get through to the mailing list cleanly

Christo Du Preez

Senior Software Engineer
Mecola IT
Mobile:	 +27 [0]83 326 8087
Skype:	 christodupreez

In response to

pgsql-performance by date

Next:From: Christo Du PreezDate: 2007-06-12 07:38:06
Subject: Re: test / live environment, major performance difference
Previous:From: Francisco ReyesDate: 2007-06-12 01:14:43
Subject: Best use of second controller with faster disks?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group