From: | Luke Cowell <lcowell(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Possible performance regression with pg_dump of a large number of relations |
Date: | 2018-01-12 01:26:52 |
Message-ID: | 34137B8A-FE53-4543-AC0E-6D78B3AE0828@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
I've been troubleshooting an issue with slow pg_dump times on postgres 9.6.6. I believe something changed between 9.5.10 and 9.6.6 that has made dumps significantly slower for databases with a large number of relations. I posted this in irc and someone suggested that I should post this here. I'm sorry if this isn't the right place.
To simulate the issue I generated 150,000 relations spread across 1000 schemas (this roughly reflects my production setup).
```ruby
File.write "many_relations.sql", (150000 / 150).times.flat_map {|n|
[
"create schema s_#{n};",
150.times.map do |t|
"create table s_#{n}.test_#{t} (id int);"
end
]
}.join("\n")
```
I have 2 identical pieces of hardware. I've installed 9.5 on one and 9.6 on the other. I've run the same generated piece of sql in a fresh database on both systems.
On my 9.5.10 system:
> time pg_dump -n s_10 testing > /dev/null
real 0m5.492s
user 0m1.424s
sys 0m0.184s
On my 9.6.6 system:
> time pg_dump -n s_10 testing > /dev/null
real 0m27.342s
user 0m1.748s
sys 0m0.248s
If I call that same pg_dump command with the verbose option, the delay is at `pg_dump: reading user-defined tables` step.
I don't have identical hardware, so I can't say for sure, but I believe this issue is still present in 10.1.
Is this a legitimate issue? Is there more information I can provide to help better assess the situation?
Thanks in advance everyone!
Luke
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Munro | 2018-01-12 01:35:37 | Re: [HACKERS] Re: proposal - psql: possibility to specify sort for describe commands, when size is printed |
Previous Message | Michael Paquier | 2018-01-12 01:24:40 | Re: pg_get_functiondef forgets about most GUC_LIST_INPUT GUCs |