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. *****
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
