Hello,
After a series of sessions to search the web for information, I am
asking the help of people having a bit more knowledge of the internals
of pg_dump to try to solve a performance problem I have. I am running
PostgreSQL version 8.3.8 both server and pg_dump,
The context is a farm hosting of a web application
(http://www.indefero.net) where each installation get its own schema.
Each table in the schema is a "real" table, not a view of the same
table in the public schema with "WHERE schema='currentschema'" clause.
This setup allows me to easily run the web application nearly
unmodified between the downloadable version and the "farm" version
(schemas rock!). Now you have the background.
Each night I am running:
pg_dump --blobs --schema=%s --no-acl -U postgres indefero | gzip >
/path/to/backups/%s/%s-%s.sql.gz
this for each installation, so 1100 times. Substitution strings are to
timestamp and get the right schema.
My problem is that the dump increased steadily with the number of
schemas (now about 20s from about 12s with 850 schemas) and pg_dump is
now ballooning at 120MB of memory usage when running the dump.
The thing is that my overage schema size is a bit more than 1MB, with
the schema just after installation being 850kB.
Max size: 2.8MB
Min size: 0.85MB
Avg: 1.0MB
Total size: ~1GB
To get the size I run the following command with sum off the "size":
SELECT relname, pg_total_relation_size(CAST(relname AS
TEXT)) AS size FROM pg_class AS pgc, pg_namespace AS pgn
WHERE pg_table_is_visible(pgc.oid) IS TRUE AND relkind = 'r'
AND pgc.relnamespace = pgn.oid
AND pgn.nspname NOT IN ('information_schema', 'pg_catalog')';
I think that pg_dump, when looking at the objects to dump, also it is
limited to a given schema, is scanning the complete database in one
those calls:
http://doxygen.postgresql.org/pg__dump_8c-source.html#l00717
Is there an option: "I know what I am doing, do not look outside of
the schema" available which can help in my case? Because running
SELECT is snappy, vmstats shows no swapping and the complete system is
basically running very well with a load average below 1.
So, your help is very welcomed,
loïc
--
Loïc d'Anterroches - Céondo Ltd - http://www.ceondo.com
