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: 466E3EEC.9040204@mecola.com (view raw or flat)
Thread:
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
root(at)???(dot)???(dot)???(dot)???:/home/postgres/layer.gz

--

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

rsync --progress --rsh=ssh visiblelayer.gz
root(at)???(dot)???(dot)???(dot)???:/home/postgres/visiblelayer.gz

--

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

rsync --progress --rsh=ssh style.gz
root(at)???(dot)???(dot)???(dot)???:/home/postgres/style.gz

--

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

rsync --progress --rsh=ssh layertype.gz
root(at)???(dot)???(dot)???(dot)???:/home/postgres/layertype.gz

--

DROP TABLE visiblelayer;
DROP TABLE style;
DROP TABLE layer;
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
Website: http://www.locateandtrade.co.za


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-2014 The PostgreSQL Global Development Group