Re: autovacuum can't keep up, bloat just continues to rise

Hello,

I changed the test to run for 6 hours at a time regardless of number of
transactions. I also changed the du command to only look at the database
(previously wal logs were included). This is the clearest indication of
the problem I have been able to produce.

Again, this is with 128 clients and 500 warehouses. The first test is a
clean test, everything dropped, vacuumed etc... Each subsequent test is
just starting the test again to have breakpoints.

-------------------------------------+-----------
autovacuum | on
autovacuum_analyze_scale_factor | 0.1
autovacuum_analyze_threshold | 50
autovacuum_freeze_max_age | 200000000
autovacuum_max_workers | 12
autovacuum_multixact_freeze_max_age | 400000000
autovacuum_naptime | 10
autovacuum_vacuum_cost_delay | 0
autovacuum_vacuum_cost_limit | 5000
autovacuum_vacuum_scale_factor | 0.1
autovacuum_vacuum_threshold | 50
autovacuum_work_mem | -1
log_autovacuum_min_duration | -1
max_wal_size | 640
checkpoint_timeout | 86400
checkpoint_completion_target | 0.5

Starting base metric
50G /srv/main/base

Test 1:
90G /srv/main/base
TPS: 838

Test 2:
121G /srv/main/base
TPS: 725

Test 3:
146G /srv/main/base
TPS: 642

Test 4:
171G /srv/main/base
TPS: 549

Test 5:
189G /srv/main/base
TPS: 489

Test 6:
208G /srv/main/base
TPS: 454

As you can see even with aggressive vacuuming, over a period of 36 hours
life gets increasingly miserable.

The largest table is:

postgres=# select
pg_size_pretty(pg_total_relation_size('bmsql_order_line'));
pg_size_pretty
----------------
148 GB
(1 row)

postgres=# \d bmsql_order_line
Table "public.bmsql_order_line"
Column | Type | Modifiers
----------------+-----------------------------+-----------
ol_w_id | integer | not null
ol_d_id | integer | not null
ol_o_id | integer | not null
ol_number | integer | not null
ol_i_id | integer | not null
ol_delivery_d | timestamp without time zone |
ol_amount | numeric(6,2) |
ol_supply_w_id | integer |
ol_quantity | integer |
ol_dist_info | character(24) |
Indexes:
"bmsql_order_line_pkey" PRIMARY KEY, btree (ol_w_id, ol_d_id,
ol_o_id, ol_number)
Foreign-key constraints:
"ol_order_fkey" FOREIGN KEY (ol_w_id, ol_d_id, ol_o_id) REFERENCES
bmsql_oorder(o_w_id, o_d_id, o_id)
"ol_stock_fkey" FOREIGN KEY (ol_supply_w_id, ol_i_id) REFERENCES
bmsql_stock(s_w_id, s_i_id)

With the PK being

postgres=# select pg_size_pretty(pg_relation_size('bmsql_order_line_pkey'));
pg_size_pretty
----------------
48 GB
(1 row)

I tried to see how much data we are dealing with here:

postgres=# select count(*) from bmsql_order_line;
count
-----------
910324839
(1 row)

Time: 503965.767 ms

And just to show that we were pushing to get these numbers:

avg-cpu: %user %nice %system %iowait %steal %idle
2.38 0.00 2.20 1.98 0.00 93.44

Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
sdb 2027.40 239.99 0.05 1199 0
sda 0.80 0.00 0.01 0 0

So we have 910M rows, and it took 8.39941667 minutes to count them at
240MB/s.

I know this is a lot of data and as I said previously, happy to let
anyone look at it. However, we clearly have something deeper to look into.

Thanks in advance,

JD

--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc

PostgreSQL Centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://pgconf.us
***** Unless otherwise stated, opinions are my own. *****