PostgreSQL et updates heap-only-tuples - partie 3
Voici une série d’articles qui va porter sur une nouveauté de la version 11.
Durant le développement de cette version, une fonctionnalité a attiré mon attention. On peut la retrouver dans les releases notes : https://www.postgresql.org/docs/11/static/release-11.html
Allow heap-only-tuple (HOT) updates for expression indexes when the values of the expressions are unchanged (Konstantin Knizhnik)
J’avoue que ce n’est pas très explicite et cette fonctionnalité nécessite quelques connaissances sur le fonctionnement du moteur que je vais essayer d’expliquer à travers plusieurs articles :
- Fonctionnement du MVCC et update heap-only-tuples
- Quand le moteur ne fait pas d’update heap-only-tuple et présentation de la nouveauté de la version 11
- Impact sur les performances
Cette fonctionnalité a été désactivée en 11.1 car elle pouvait conduire à des crash d’instance1. J’ai tout de même choisi de publier ces articles car ils permettent de comprendre le mécanisme des updates HOT et le gain que pourrait apporter cette fonctionnalité.
Je remercie au passage Guillaume Lelarge pour la relecture de cet article ;).
Impact sur les performances
Voici un test assez simple pour mettre en évidence l’intérêt de cette fonctionnalité. On pourrait s’attendre à des gains en performances car le moteur évite de mettre à jour les index, ainsi qu’en matière de taille d’index, comme vu précédemment, on évite la fragmentation.
1CREATE TABLE t5 (c1 jsonb, c2 int,c3 int);
2CREATE INDEX ON t5 ((c1->>'prenom')) ;
3CREATE INDEX ON t5 (c2);
4INSERT INTO t5 VALUES ('{ "prenom":"adrien" , "valeur" : "1"}'::jsonb,1,1);
5INSERT INTO t5 VALUES ('{ "prenom":"guillaume" , "valeur" : "2"}'::jsonb,2,2);
6\dt+ t5
7 List of relations
8 Schema | Name | Type | Owner | Size | Description
9--------+------+-------+----------+-------+-------------
10 public | t5 | table | postgres | 16 kB |
11(1 row)
12
13\di+ t5*
14 List of relations
15 Schema | Name | Type | Owner | Table | Size | Description
16--------+-------------+-------+----------+-------+-------+-------------
17 public | t5_c2_idx | index | postgres | t5 | 16 kB |
18 public | t5_expr_idx | index | postgres | t5 | 16 kB |
19(2 rows)
Puis ce test pgbench :
1\set id random(1, 100000)
2\set id2 random(1, 100000)
3
4UPDATE t5 SET c1 = '{"valeur": ":id", "prenom": "guillaume"}' WHERE c2=2;
5UPDATE t5 SET c1 = '{"valeur": ":id2", "prenom": "adrien"}' WHERE c2=1;
Qu’on exécute pendant 60 secondes, avec recheck_on_update=on (par défaut) :
1pgbench -f test.sql -n -c6 -T 120
2transaction type: test.sql
3scaling factor: 1
4query mode: simple
5number of clients: 6
6number of threads: 1
7duration: 120 s
8number of transactions actually processed: 2743163
9latency average = 0.262 ms
10tps = 22859.646914 (including connections establishing)
11tps = 22859.938191 (excluding connections establishing)
12
13 \dt+ t5*
14 List of relations
15 Schema | Name | Type | Owner | Size | Description
16--------+------+-------+----------+--------+-------------
17 public | t5 | table | postgres | 376 kB |
18(1 row)
19\di+ t5*
20 List of relations
21 Schema | Name | Type | Owner | Table | Size | Description
22--------+-------------+-------+----------+-------+-------+-------------
23 public | t5_c2_idx | index | postgres | t5 | 16 kB |
24 public | t5_expr_idx | index | postgres | t5 | 32 kB |
25(2 rows)
26
27SELECT * FROM pg_stat_user_tables WHERE relname = 't5';
28-[ RECORD 1 ]-------+------------------------------
29relid | 8890622
30schemaname | public
31relname | t5
32seq_scan | 4
33seq_tup_read | 0
34idx_scan | 7999055
35idx_tup_fetch | 7999055
36n_tup_ins | 4
37n_tup_upd | 7999055
38n_tup_del | 0
39n_tup_hot_upd | 7998236
40n_live_tup | 2
41n_dead_tup | 0
42n_mod_since_analyze | 0
43last_vacuum |
44last_autovacuum | 2018-09-19 06:29:37.690575+00
45last_analyze |
46last_autoanalyze | 2018-09-19 06:29:37.719911+00
47vacuum_count | 0
48autovacuum_count | 5
49analyze_count | 0
50autoanalyze_count | 5
Et maintenant avec recheck_on_update=off. Donc même jeu de donnée que
précédemment mais cette fois l’index est créé avec cet ordre :
CREATE INDEX ON t5 ((c1->>'prenom')) WITH (recheck_on_update=off);
1pgbench -f test.sql -n -c6 -T 120
2transaction type: test.sql
3scaling factor: 1
4query mode: simple
5number of clients: 6
6number of threads: 1
7duration: 120 s
8number of transactions actually processed: 1065688
9latency average = 0.676 ms
10tps = 8880.679565 (including connections establishing)
11tps = 8880.796478 (excluding connections establishing)
12
13\dt+ t5
14 List of relations
15 Schema | Name | Type | Owner | Size | Description
16--------+------+-------+----------+---------+-------------
17 public | t5 | table | postgres | 9496 kB |
18(1 row)
19
20\di+ t5*
21 List of relations
22 Schema | Name | Type | Owner | Table | Size | Description
23--------+-------------+-------+----------+-------+--------+-------------
24 public | t5_c2_idx | index | postgres | t5 | 768 kB |
25 public | t5_expr_idx | index | postgres | t5 | 58 MB |
26(2 rows)
27
28select * from pg_stat_user_tables where relname = 't5';
29-[ RECORD 1 ]-------+------------------------------
30relid | 8890635
31schemaname | public
32relname | t5
33seq_scan | 2
34seq_tup_read | 0
35idx_scan | 2131376
36idx_tup_fetch | 2131376
37n_tup_ins | 2
38n_tup_upd | 2131376
39n_tup_del | 0
40n_tup_hot_upd | 19
41n_live_tup | 2
42n_dead_tup | 0
43n_mod_since_analyze | 0
44last_vacuum |
45last_autovacuum | 2018-09-19 06:34:42.045905+00
46last_analyze |
47last_autoanalyze | 2018-09-19 06:34:42.251183+00
48vacuum_count | 0
49autovacuum_count | 3
50analyze_count | 0
51autoanalyze_count | 3
| recheck_on_update | on | off | Gain |
|---|---|---|---|
| TPS | 22859 | 8880 | 157% |
| t5 size | 376 kB | 9496 kB | -96% |
| t5_c2_idx size | 16 kB | 768 kB | -98% |
| t5_expr_idx size | 32 kB | 58 MB | -99.9% |
L’écart de performance est assez impressionnant de même que la taille des tables et index.
J’ai refait le premier test en désactivant l’autovacuum et voici le résultat :
1pgbench -f test.sql -n -c6 -T 120
2transaction type: test.sql
3scaling factor: 1
4query mode: simple
5number of clients: 6
6number of threads: 1
7duration: 120 s
8number of transactions actually processed: 2752479
9latency average = 0.262 ms
10tps = 22937.271749 (including connections establishing)
11tps = 22937.545872 (excluding connections establishing)
12
13
14select * from pg_stat_user_tables where relname = 't5';
15-[ RECORD 1 ]-------+--------
16relid | 8890643
17schemaname | public
18relname | t5
19seq_scan | 2
20seq_tup_read | 0
21idx_scan | 5504958
22idx_tup_fetch | 5504958
23n_tup_ins | 2
24n_tup_upd | 5504958
25n_tup_del | 0
26n_tup_hot_upd | 5504258
27n_live_tup | 2
28n_dead_tup | 2416
29n_mod_since_analyze | 5504960
30last_vacuum |
31last_autovacuum |
32last_analyze |
33last_autoanalyze |
34vacuum_count | 0
35autovacuum_count | 0
36analyze_count | 0
37autoanalyze_count | 0
38
39\di+ t5*
40List of relations
41-[ RECORD 1 ]------------
42Schema | public
43Name | t5_c2_idx
44Type | index
45Owner | postgres
46Table | t5
47Size | 16 kB
48Description |
49-[ RECORD 2 ]------------
50Schema | public
51Name | t5_expr_idx
52Type | index
53Owner | postgres
54Table | t5
55Size | 40 kB
56Description |
57
58\dt+ t5
59List of relations
60-[ RECORD 1 ]---------
61Schema | public
62Name | t5
63Type | table
64Owner | postgres
65Size | 1080 kB
66Description |
Puis le second test :
1pgbench -f test.sql -n -c6 -T 120
2transaction type: test.sql
3scaling factor: 1
4query mode: simple
5number of clients: 6
6number of threads: 1
7duration: 120 s
8number of transactions actually processed: 881434
9latency average = 0.817 ms
10tps = 7345.208875 (including connections establishing)
11tps = 7345.304797 (excluding connections establishing)
12
13select * from pg_stat_user_tables where relname = 't5';
14-[ RECORD 1 ]-------+--------
15relid | 8890651
16schemaname | public
17relname | t5
18seq_scan | 2
19seq_tup_read | 0
20idx_scan | 1762868
21idx_tup_fetch | 1762868
22n_tup_ins | 2
23n_tup_upd | 1762868
24n_tup_del | 0
25n_tup_hot_upd | 23
26n_live_tup | 2
27n_dead_tup | 1762845
28n_mod_since_analyze | 1762870
29last_vacuum |
30last_autovacuum |
31last_analyze |
32last_autoanalyze |
33vacuum_count | 0
34autovacuum_count | 0
35analyze_count | 0
36autoanalyze_count | 0
37
38\di+ t5*
39List of relations
40-[ RECORD 1 ]------------
41Schema | public
42Name | t5_c2_idx
43Type | index
44Owner | postgres
45Table | t5
46Size | 600 kB
47Description |
48-[ RECORD 2 ]------------
49Schema | public
50Name | t5_expr_idx
51Type | index
52Owner | postgres
53Table | t5
54Size | 56 MB
55Description |
56
57\dt+ t5*
58List of relations
59-[ RECORD 1 ]---------
60Schema | public
61Name | t5
62Type | table
63Owner | postgres
64Size | 55 MB
65Description |
| recheck_on_update | on | off | Gain |
|---|---|---|---|
| TPS | 22937 | 7345 | 212% |
| t5 size | 1080 kB | 55 MB | -98% |
| t5_c2_idx size | 16 kB | 600 kB | -97% |
| t5_expr_idx size | 40 kB | 56 MB | -99.9% |
A nouveau, l’écart de performance est important, il en est de même pour la taille des tables et index. On note également l’importance de laisser l’autovacuum activé.
Pourquoi avons-nous un tel écart de taille sur les index et la table ?
Pour les index, c’est dû au mécanisme expliqué plus haut. Le moteur a pu chaîner les enregistrements en évitant de mettre à jour l’index. L’index a quand même légèrement augmenté de taille, il arrive que le moteur ne peut pas faire de HOT, par exemple quand il n’y a plus de place dans le bloc.
Pour ce qui est de la taille de la table, lors du test avec autovacuum activé, l’autovacuum avait plus de difficultés à passer sur la table avec le HOT désactivé. L’index grossissant, cela engendrait plus de “travail”. Lors du test sans autovacuum, l’écart s’explique par le fait que même un simple SELECT peut nettoyer des blocs2.
Rappelons que cette fonctionnalité a été retirée avec la version 11.1. J’avais écrit ces articles peu après la sortie de la version 11.0 et j’ai tout de même choisit de les publier afin de présenter le fonctionnement des UPDATES HOT. Espérons que cette fonctionnalité sera corrigée dans les versions à venir.