Hello everyone !
We are trying to upgrade our instances to PG 10 but we have had some
major speed issues.
During the upgrade process pg_upgrade launches a series of pg_dump
that are exceedingly slow. Combined they take more than 3 hour on one
2TB cluster (that I will call "cluster A" in the following
description).
We believe this is related to the size of our pg_catalog, we have
thousands (if not hundred of thousands of views) on most of our
databases. Here is an example on cluster A :
```
Amount of views per database in cluster A
--------------------
database_9999 40812
database_1001 229
database_5030 69142
database_5010 77938
database_8000 291
database_9997 42961
database_5001 137986
database_5000 129315
database_5003 307073
database_5002 183886
database_9998 56911
database_5005 84742
database_5004 401165
database_9996 31862
database_9995 41999
database_5009 31149
database_5008 131806
database_5007 200192
database_5006 311978
database_9994 9883
database_9993 44324
database_9992 34590
database_9991 12735
database_9990 35551
database_9986 4706
database_9985 1420
database_5014 62552
database_9987 20299
database_5012 193093
database_5013 24980
database_5011 360617
database_9988 33474
database_9989 2815
```
We are completely aware that this is a *bad* idea, and our newer
developments are all moving far far away for this type of schema,
however we are currently stuck with this for at least a few more
months if not years.
My question to this mailing list is : Are we missing something that
could speed up the pg_dump ?
This is the pg_upgrade command we have used :
```
/usr/lib/postgresql/10/bin/pg_upgrade -k -j 8
--old-datadir=/var/lib/postgresql/9.5/main
--new-datadir=/var/lib/postgresql/10/main
--old-bindir=/usr/lib/postgresql/9.5/bin
--new-bindir=/usr/lib/postgresql/10/bin --old-options '-c
config_file=/etc/postgresql/9.5/main/postgresql.conf' --new-options
'-c config_file=/etc/postgresql/10/main/postgresql.conf'
```
The pg_dump command generated (one per database) by the pg_upgrade binary :
```
command: "/usr/lib/postgresql/10/bin/pg_dump" --host
/var/lib/postgresql --port 50432 --username postgres --schema-only
--quote-all-identifiers --binary-upgrade --format=custom
--file="pg_upgrade_dump_6667558.custom" 'dbname=database_5002' >>
"pg_upgrade_dump_6667558.log" 2>&1
```
Description of our PostgreSQL infrastructure :
Our cluster have all the same PostgreSQL configuration, they are all
on PostgreSQL 9.4 or 9.5 and they all use the same kind of hardware.
They also host the same kind of data (same schema only the load amount
is different).
Hardware configuration :
All of our PostgreSQL servers are on google-cloud-engine with a
`n1-highmem-8` flavour. That means 50GB of ram and 8 virtual CPUs.
We are using google's `SSD persistent disk` which gives us up to 240
MB/S Read and Write :
```
Operation Type Read Write
Sustained random IOPS limit 15,000.00 15,000.00
Sustained throughput limit (MB/s) 240.00 240.00
```
Here are our PostgreSQL settings :
```
postgres=# select name,setting from pg_settings where source LIKE
'configuration file';
name |
setting
-----------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------
autovacuum_max_workers | 3
DateStyle | ISO, MDY
default_text_search_config | pg_catalog.english
effective_cache_size | 3540224
external_pid_file | /var/run/postgresql/9.5-main.pid
hot_standby | on
lc_messages | en_US.UTF-8
lc_monetary | en_US.UTF-8
lc_numeric | en_US.UTF-8
lc_time | en_US.UTF-8
listen_addresses | 10.16.13.2
log_autovacuum_min_duration | 1000
log_checkpoints | on
log_connections | off
log_destination | syslog,csvlog
log_directory | /var/log/postgresql
log_filename | postgresql.%a
log_line_prefix | %t [%p]: [%l-1] appname=%a user=%u, db=%d
log_lock_waits | on
log_min_duration_statement | 1000
log_rotation_age | 1440
log_rotation_size | 0
log_statement | ddl
log_temp_files | 2048
log_timezone | UTC
log_truncate_on_rotation | on
logging_collector | on
maintenance_work_mem | 3145728
max_connections | 1500
max_locks_per_transaction | 258
max_replication_slots | 20
max_wal_senders | 20
max_wal_size | 30
max_worker_processes | 50
random_page_cost | 2.7
shared_buffers | 1416064
shared_preload_libraries | pglogical
ssl | on
ssl_cert_file | /etc/ssl/certs/ssl-cert-snakeoil.pem
ssl_key_file | /etc/ssl/private/ssl-cert-snakeoil.key
synchronous_commit | on
syslog_facility | local2
syslog_ident | postgres
temp_buffers | 1024
temp_file_limit | 8304640
TimeZone | UTC
track_commit_timestamp | on
wal_keep_segments | 1000
wal_level | logical
wal_log_hints | on
work_mem | 10240
```
Any help or comment is appreciated !
--
Leo
