Re: insert/update performance

2016-01-23 23:00 GMT+08:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Jinhua Luo <luajit(dot)io(at)gmail(dot)com> writes:
>> I have a table with 70 columns, and 6 indexes. The data flow is a
>> special OLTP model: frequent inserts (2000 tps), and each inserted row
>> would be updated very soon (i.e. the number of inserts is equal to the
>> number of updates).
>
> Do those predictable updates change any of the indexed columns?

The update statement itself do not modify the indexed columns, but the
before update trigger modifies two indexed columns: one is in
timestamp type, used to record the update time, the trigger would fill
CURRENT_TIMESTAMP into this column; the other is status, which would
be set to 'done'. These two columns are indexed in btree.

>
>> I do a simple test: I truncate the table, disable the autovacuum, and
>> run the application for a few minutes, then I invokes vacuum manually,
>> it gives a strange output:
>> found 598 removable, 25662 nonremovable row versions in 3476 pages
>> DETAIL: 0 dead row versions cannot be removed yet
>> As said before, the number of inserts is equal to the number of
>> updates. So the bloat of the table should be 100%, and the number of
>> removable rows should be equal to the number of nonremovable rows,
>> which is the real number of inserts issued by the application.
>
> What seems likely is that most of the updates are HOT (because they
> don't change any indexed columns) and then the freed space is reclaimable
> by subsequent updates on the same page without needing a VACUUM.
>
> Watching the insert/update/hot-update counts in pg_stat_all_tables would
> provide some evidence.

testdb=# truncate test;
TRUNCATE TABLE
testdb=# vacuum test;
testdb=# select pg_stat_reset_single_table_counters(42515);
pg_stat_reset_single_table_counters
-------------------------------------

(1 row)

testdb=# select
n_tup_ins,n_tup_upd,n_tup_hot_upd,n_tup_del,n_live_tup,n_dead_tup from
pg_stat_all_tables where relid=42515;
n_tup_ins | n_tup_upd | n_tup_hot_upd | n_tup_del | n_live_tup | n_dead_tup
-----------+-----------+---------------+-----------+------------+------------
0 | 0 | 0 | 0 | 0 | 0
(1 row)

##### insert 6 rows

testdb=# select
n_tup_ins,n_tup_upd,n_tup_hot_upd,n_tup_del,n_live_tup,n_dead_tup from
pg_stat_all_tables where relid=42515;
n_tup_ins | n_tup_upd | n_tup_hot_upd | n_tup_del | n_live_tup | n_dead_tup
-----------+-----------+---------------+-----------+------------+------------
6 | 6 | 0 | 0 | 6 | 6
(1 row)

testdb=# vacuum verbose test;
INFO: vacuuming "public.test"
INFO: scanned index "test_pkey" to remove 6 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: scanned index "deliver_done_date_idx" to remove 6 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: scanned index "deliver_task_queue_idx" to remove 6 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: scanned index "message_id_idx" to remove 6 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: scanned index "status_idx" to remove 6 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: scanned index "status_report_done_date_idx" to remove 6 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: scanned index "submit_done_date_idx" to remove 6 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: scanned index "tp_scts_idx" to remove 6 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "test": removed 6 row versions in 1 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "test_pkey" now contains 6 row versions in 2 pages
DETAIL: 6 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "deliver_done_date_idx" now contains 6 row versions in 2 pages
DETAIL: 6 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "deliver_task_queue_idx" now contains 0 row versions in 2 pages
DETAIL: 6 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "message_id_idx" now contains 6 row versions in 2 pages
DETAIL: 6 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "status_idx" now contains 6 row versions in 2 pages
DETAIL: 6 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "status_report_done_date_idx" now contains 6 row versions
in 2 pages
DETAIL: 6 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "submit_done_date_idx" now contains 6 row versions in 2 pages
DETAIL: 6 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "tp_scts_idx" now contains 6 row versions in 2 pages
DETAIL: 6 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "test": found 6 removable, 6 nonremovable row versions in 1 out
of 1 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_42515"
INFO: index "pg_toast_42515_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_42515": found 0 removable, 0 nonremovable row
versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

##### insert another 6 rows

testdb=# select
n_tup_ins,n_tup_upd,n_tup_hot_upd,n_tup_del,n_live_tup,n_dead_tup from
pg_stat_all_tables where relid=42515;
n_tup_ins | n_tup_upd | n_tup_hot_upd | n_tup_del | n_live_tup | n_dead_tup
-----------+-----------+---------------+-----------+------------+------------
12 | 12 | 0 | 0 | 12 | 6
(1 row)

testdb=# vacuum verbose test;
INFO: vacuuming "public.test"
INFO: scanned index "test_pkey" to remove 6 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: scanned index "deliver_done_date_idx" to remove 6 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: scanned index "deliver_task_queue_idx" to remove 6 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: scanned index "message_id_idx" to remove 6 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: scanned index "status_idx" to remove 6 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: scanned index "status_report_done_date_idx" to remove 6 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: scanned index "submit_done_date_idx" to remove 6 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: scanned index "tp_scts_idx" to remove 6 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "test": removed 6 row versions in 1 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "test_pkey" now contains 12 row versions in 2 pages
DETAIL: 6 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "deliver_done_date_idx" now contains 12 row versions in 2 pages
DETAIL: 6 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "deliver_task_queue_idx" now contains 0 row versions in 2 pages
DETAIL: 6 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "message_id_idx" now contains 12 row versions in 2 pages
DETAIL: 6 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "status_idx" now contains 12 row versions in 2 pages
DETAIL: 6 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "status_report_done_date_idx" now contains 12 row
versions in 2 pages
DETAIL: 6 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "submit_done_date_idx" now contains 12 row versions in 2 pages
DETAIL: 6 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "tp_scts_idx" now contains 12 row versions in 2 pages
DETAIL: 6 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "test": found 2 removable, 12 nonremovable row versions in 1
out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_42515"
INFO: index "pg_toast_42515_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_42515": found 0 removable, 0 nonremovable row
versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

You could see that I insert 6 rows two times.
The first vacuum works, which found 6 rows to remove.
The second vacuum is wrong, which only found 2 rows to remove.
However, for indexes, both found 6 rows to remove.

No HOT updates. So is there any other reasons?

Regards,
Jinhua Luo