From: | Sebastien Lemieux <slemieux(at)elitra(dot)com> |
---|---|
To: | Postgresql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | How to efficiently duplicate a whole schema? |
Date: | 2003-08-06 18:56:14 |
Message-ID: | Pine.LNX.4.33.0308061437550.10321-100000@moebius.elitra.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi,
I'm running on Redhat 7.2 with postgresql 7.3.2 and I have two schema in
the same database 'db' and 'db_dev'. Both contain a set of >20 tables for
a total of less than 50 Mb of data each (on the order of 50k rows in
total). Once in a while (often these days!), I need to synchronize the
dev version from the production 'db'. Currently, I do this by setting
constraints to deferred, deleting everything in db_dev, then issue a serie
of insert ... select ... to copy data from each table in db to the
equivalent table in db_dev.
This approach used to run in less than 30 seconds in MySQL, but in
PostgreSQL it currently takes around 30 minutes. The postmaster process
is running at 100% cpu all the time. I enclosed all the delete statement
in one transaction and all the insert statements in a second transaction.
All the time is taken at the commit of both transaction.
Is there a more straightforward way to synchronize a development
database to a production one? Is there anyway to increase the performance
of this delete/insert combination? I've got indexes and constraints on
most tables, could that be the problem? At some point in the future, I
will also need to make a copy of a whole schema ('db' into 'db_backup'),
what would be an efficient way to do that?
These are the parameters I've adjusted in the postgresql.conf:
max_connections = 16
shared_buffers = 3000
max_fsm_relations = 2000
max_fsm_pages = 20000
sort_mem = 20000
vacuum_mem = 20000
effective_cache_size = 15000
And this is the memory state of the machine:
slemieux(at)neptune> free
total used free shared buffers cached
Mem: 2059472 2042224 17248 24768 115712 1286572
-/+ buffers/cache: 639940 1419532
Swap: 2096440 490968 1605472
thanks,
--
Sebastien Lemieux
Bioinformatics, post-doc
Elitra-canada
From | Date | Subject | |
---|---|---|---|
Next Message | scott.marlowe | 2003-08-06 19:09:47 | Re: How to efficiently duplicate a whole schema? |
Previous Message | Tom Lane | 2003-08-06 18:16:47 | Re: PostgreSQL performance problem -> tuning |