blob: ff8fcb3be26d8e6046e5213a4d11d5b4cea32840 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
|
## Setup
Add to postgres.conf:
shared_preload_libraries = 'auto_explain,pg_stat_statements'
# Increase the max size of the query strings Postgres records
track_activity_query_size = 2048
# Track statements generated by stored procedures as well
pg_stat_statements.track = all
Also:
track_counts (already default)
autovacuum (already default?)
log_min_error = warning
log_min_duration_statement = 5000
Then from shell:
create extension pg_stat_statements;
Regularly want to run:
VACUUM ANALYZE
## Tuning Values
postgres config:
max_connections = 100 (default)
shared_buffers = 128MB -> 10GB (while elastic on same machine; later 16 or more)
effective_cache_size = 4GB -> 24GB (while elastic on same machine)
work_mem = 4MB -> 128MB # relatively few connections/box
fsync = on
commit_delay = ??? (and siblings)
random_page_cost = 1 (for SSD)
default_statistics_target = 100 -> 200
maintenance_work_mem = 64MB -> 8GB
synchronous_commit = off (during dev only! switch to on for production!)
wal_sync_method (keep default)
max_wal_size = 64 -> 128 (based on above HINT message)
# didn't mess with commit_delay/commit_siblings
system:
sysctl -w vm.overcommit_memory=2
TODO: ulimit -n 65536
TODO: ulimit -p 800
LimitNOFILE
/lib/systemd/system/postgresql.service
## Resources
https://www.geekytidbits.com/performance-tuning-postgres/
Could try pgbadger to handle auto_explain type output.
https://www.postgresql.org/docs/10/static/runtime-config-wal.html
IA-specific resources:
https://git.archive.org/ia/mint/blob/master/postgres/postgres_9_2.yml
https://git.archive.org/ia/mint/blob/master/postgres/put_datadir_on_ssd.sh
https://git.archive.org/ia/mint/blob/master/postgres/templates/postgresql.conf.j2
For bulk inserts:
- make write-ahead-log larger (eg, 16MB. done.)
- transactions of ~1000+ inserts
- https://www.postgresql.org/docs/current/static/populate.html
- https://www.depesz.com/2007/07/05/how-to-insert-data-to-database-as-fast-as-possible/
- https://stackoverflow.com/questions/12206600/how-to-speed-up-insertion-performance-in-postgresql
## 2018-06-27 Measurements (pre-tuning)
fatcat_prod=# select count(*) from release_ident; 20983019
fatcat_prod=# select count(*) from work_ident; 20988140
fatcat_prod=# select count(*) from file_ident; 1482335
fatcat_prod=# select count(*) from creator_ident; 4167419
fatcat_prod=# select count(*) from container_ident; 61793
select count(*) from release_contrib; 59798133
bnewbold@wbgrp-svc500$ sudo du -sh /var/lib/postgresql/
43G
running import-crossref with 20 threads, and manifest importer with one (at 33%
complete). had already imported ~7million works+releases previously.
PostgreSQL 10.4 - wbgrp-svc500.us.archive.org - postgres@localhost:5432/postgre
Size: 41.38G - 323.40K/s | TPS: 885
Mem.: 50.80% - 23.86G/49.14G | IO Max: 79539/s
Swap: 0.80% - 408.89M/50.00G | Read : 67.04K/s - 16/s
Load: 6.69 7.41 7.69 | Write: 1.93M/s - 493/s
----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
32 6 62 0 0 0| 296k 3880k| 334k 3144B| 0 0 | 21k 65k
31 6 62 0 0 0| 0 3072k| 391k 318B| 0 0 | 51k 141k
31 6 63 0 0 0| 16k 1080k| 344k 1988B| 0 0 | 35k 104k
29 6 65 0 0 0| 136k 2608k| 175k 332B| 0 0 |9835 15k
28 5 67 0 0 0| 408k 4368k| 285k 832B| 0 0 | 14k 17k
33 5 62 0 0 0| 56k 3256k| 219k 99B| 0 0 | 22k 49k
31 6 63 0 0 0| 188k 5120k| 158k 318B| 0 0 | 17k 29k
30 6 64 0 0 0| 200k 6984k| 239k 988B| 0 0 | 16k 24k
30 6 64 0 0 0| 168k 5504k| 159k 152B| 0 0 | 14k 20k
28 7 65 0 0 0| 440k 12M| 236k 420B| 0 0 | 15k 18k
29 6 65 0 0 0| 428k 6968k| 352k 310B| 0 0 | 19k 31k
32 6 62 0 0 0| 64k 3480k| 288k 318B| 0 0 | 18k 55k
32 6 62 0 0 0| 32k 2080k| 155k 318B| 0 0 | 20k 52k
bnewbold@wbgrp-svc500$ uptime
22:00:42 up 28 days, 22:31, 6 users, load average: 7.94, 7.56, 7.72
2018-06-27 21:57:36.102 UTC [401] LOG: checkpoints are occurring too frequently (13 seconds apart)
2018-06-27 21:57:36.102 UTC [401] HINT: Consider increasing the configuration parameter "max_wal_size".
relname | too_much_seq | case | rel_size | seq_scan | idx_scan
-----------------+--------------+----------------+-------------+----------+-----------
changelog | 1274670 | Missing Index? | 39411712 | 1274670 | 0
file_edit | 612386 | Missing Index? | 108298240 | 612386 | 0
creator_edit | 612386 | Missing Index? | 285540352 | 612386 | 0
container_edit | 612386 | Missing Index? | 4784128 | 612386 | 0
release_edit | 612386 | Missing Index? | 1454489600 | 612386 | 0
work_edit | 612386 | Missing Index? | 1454415872 | 612386 | 0
release_contrib | 296675 | Missing Index? | 4725645312 | 296675 | 0
release_ref | 296663 | Missing Index? | 8999837696 | 296663 | 0
file_release | -113 | OK | 13918208 | 110 | 223
container_rev | -979326 | OK | 16285696 | 63 | 979389
file_ident | -3671516 | OK | 109002752 | 362 | 3671878
file_rev | -3944155 | OK | 302940160 | 95 | 3944250
creator_rev | -8420205 | OK | 318283776 | 1226 | 8421431
creator_ident | -9525338 | OK | 309141504 | 52330 | 9577668
container_ident | -20581876 | OK | 4833280 | 272457 | 20854333
release_ident | -40548858 | OK | 1440948224 | 4160919 | 44709777
work_rev | -42534913 | OK | 1124671488 | 1161 | 42536074
editgroup | -48864662 | OK | 34136064 | 1 | 48864663
work_ident | -65008911 | OK | 1503313920 | 1239 | 65010150
release_rev | -185735794 | OK | 13649428480 | 128 | 185735922
## 2018-06-28 (after basic tuning + indexes)
Early loading (manifest and 20x release):
PostgreSQL 10.4 - wbgrp-svc500.us.archive.org - postgres@localhost:5432/postgres - Ref.: 2s
Size: 4.57G - 6.45M/s | TPS: 18812
Mem.: 59.70% - 23.62G/49.14G | IO Max: 3601/s
Swap: 1.30% - 675.05M/50.00G | Read : 0.00B/s - 0/s
Load: 12.98 10.58 5.25 | Write: 2.65M/s - 677/s
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
24045 webcrawl 20 0 908872 204948 11756 S 153.3 0.4 16:13.03 fatcatd
24328 webcrawl 20 0 78148 45220 4324 R 87.1 0.1 8:44.16 perl
24056 postgres 20 0 10.441g 3.906g 3.886g R 69.9 7.9 6:57.47 postgres
24063 postgres 20 0 10.447g 3.899g 3.873g S 67.9 7.9 6:55.89 postgres
24059 postgres 20 0 10.426g 3.888g 3.883g R 67.5 7.9 6:59.15 postgres
24057 postgres 20 0 10.430g 3.883g 3.874g S 67.2 7.9 6:58.68 postgres
24061 postgres 20 0 10.448g 3.909g 3.881g R 66.2 8.0 6:54.30 postgres
24058 postgres 20 0 10.428g 3.883g 3.876g R 65.9 7.9 6:59.35 postgres
24062 postgres 20 0 10.426g 5.516g 5.511g R 64.9 11.2 6:58.29 postgres
24055 postgres 20 0 10.426g 3.878g 3.873g R 64.2 7.9 6:59.38 postgres
24054 postgres 20 0 10.430g 5.499g 5.491g R 63.6 11.2 6:57.27 postgres
24060 postgres 20 0 10.448g 3.900g 3.873g R 61.9 7.9 6:55.45 postgres
21711 postgres 20 0 10.419g 5.762g 5.760g D 16.6 11.7 3:00.67 postgres
21713 postgres 20 0 10.419g 21432 19512 S 11.3 0.0 3:25.11 postgres
24392 webcrawl 20 0 5309636 400912 8696 S 7.9 0.8 0:53.18 python3
24383 webcrawl 20 0 5309436 400628 8648 S 7.6 0.8 0:52.29 python3
24387 webcrawl 20 0 5309776 402968 8620 S 7.3 0.8 0:52.81 python3
24394 webcrawl 20 0 5309624 400732 8644 S 7.3 0.8 0:53.30 python3
24384 webcrawl 20 0 5309916 400948 8600 S 7.0 0.8 0:53.18 python3
Still get a *lot* of:
2018-06-29 00:14:05.948 UTC [21711] LOG: checkpoints are occurring too frequently (1 second apart)
2018-06-29 00:14:05.948 UTC [21711] HINT: Consider increasing the configuration parameter "max_wal_size".
VACUUM is running basically continuously; should prevent that? 6 hours or
longer on release_rev and release_ref tables. An auto-approve batch method
would resovle this, I think (no update after insert).
max_wal_size wasn't getting set correctly.
The statements taking the most time are the complex inserts (multi-table
inserts); they take a fraction of a second though (mean less than a
millisecond).
Manifest import runs really slow if release import is concurrent; much faster
to wait until release import is done first (like a factor of 10x or more).
With some 60 million releases:
bnewbold@wbgrp-svc500$ sudo du -sh /var/lib/postgresql/
184G /var/lib/postgresql/
TODO: slow query log doesn't seem to be working (let alone auto_explain)
|
