Hello
We have a fairly large postgresql-9.4 database (currently 70TB and growing to approx 80TB ) running on Centos 7.
The HW is 48 core Xeon with 180GB of RAM with data on a enterprise grade SAN storage.
We started feeding it several weeks ago and everything went smoothly until we hit this issue:
2017-08-09 05:21:50.946 WIB >DETAIL: This command would create a multixact with 2 members, but the remaining space is only enough for 0 members.
2017-08-09 05:21:50.946 WIB >HINT: Execute a database-wide VACUUM in database with OID 20101 with reduced vacuum_multixact_freeze_min_age and vacuum_multixact_freeze_table_age settings.
I did what the hint proposes, but on a such large database a vacuum takes several days.
We are currently in data loading phase and we are sending only INSERT statements, there should be very little UPDATEs or DELETEs.
Yesterday, the message disappeared shortly, but today it's back (vacuum is still running)
Is there a way how to prevent/fix this so we can finish the loading (97% done), because the performance went down from 100 ops/sec to 15ops/min.
Most tables have around 150 M rows with toast data.
There are several huge tables with toast data, currently autovacuumed, I guess this is the reason for the performance drop:
| usename | application_name | state | backend_xmin | query
-+----------+------------------+--------+--------------+----------------------------------------------------------------------
| postgres | | active | 1683428686 | autovacuum: VACUUM pg_toast.pg_toast_14548803 (to prevent wraparound)
| postgres | | active | 1683428693 | autovacuum: VACUUM pg_toast.pg_toast_14548821 (to prevent wraparound)
| postgres | | active | 1683428705 | autovacuum: VACUUM pg_toast.pg_toast_14548828 (to prevent wraparound)
| postgres | | active | 1683428719 | autovacuum: VACUUM pg_toast.pg_toast_14548835 (to prevent wraparound)
| postgres | | active | 1683428732 | autovacuum: VACUUM pg_toast.pg_toast_14549150 (to prevent wraparound)
After changing the vacuum_cost_limit to 10000 for one night, I saw 200MB/s of writes the whole night, but I had to change it back to 2000, because the insert perf went to 0.
The autovacuumed tables are mostly toast tables of those:
SELECT relname, age(relminmxid) as mxid_age, pg_size_pretty(pg_table_size(oid)) as table_size
FROM pg_class
WHERE relkind = 'r' and pg_table_size(oid) > 100000000
ORDER BY age(relminmxid) DESC LIMIT 20;
relname | mxid_age | table_size
------------------------------+------------+------------
t1 | 1554084722 | 172 GB
t2 | 1554084722 | 10 TB
t3 | 1554084722 | 21 GB
t4 | 1554084722 | 8928 MB
t5 | 1554084722 | 43 GB
t6 | 1554084722 | 2788 GB
t7 | 1554084722 | 290 GB
t8 | 1554084722 | 11 GB
t9 | 1554084722 | 2795 GB
t10 | 1554084722 | 20 GB
t11 | 1554084722 | 16 TB
There is a large number of member files in pg_multixact/members
-bash-4.2$ ll ../data/pg_multixact/members/|wc -l
82041
With the oldes file from the beginning of July.
pg_controldata output:
-bash-4.2$ pg_controldata /var/lib/pgsql/9.4/data/
pg_control version number: 942
Catalog version number: 201409291
Database system identifier: 6421090142329829830
Database cluster state: in production
pg_control last modified: Wed 09 Aug 2017 03:48:47 PM WIB
Latest checkpoint location: 589E/9DB366A0
Prior checkpoint location: 589E/95E8DEE0
Latest checkpoint's REDO location: 589E/963FC1B8
Latest checkpoint's REDO WAL file: 000000010000589E00000096
Latest checkpoint's TimeLineID: 1
Latest checkpoint's PrevTimeLineID: 1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0/1683422006
Latest checkpoint's NextOID: 2293738628
Latest checkpoint's NextMultiXactId: 129346573
Latest checkpoint's NextMultiOffset: 4294967295
Latest checkpoint's oldestXID: 784247712
Latest checkpoint's oldestXID's DB: 20101
Latest checkpoint's oldestActiveXID: 0
Latest checkpoint's oldestMultiXid: 11604
Latest checkpoint's oldestMulti's DB: 20101
Time of latest checkpoint: Wed 09 Aug 2017 03:34:33 PM WIB
Fake LSN counter for unlogged rels: 0/1
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
Current wal_level setting: minimal
Current wal_log_hints setting: off
Current max_connections setting: 1200
Current max_worker_processes setting: 8
Current max_prepared_xacts setting: 0
Current max_locks_per_xact setting: 64
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Data page checksum version: 0
Thank you
Peter Huncar
