summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorBryan Newbold <bnewbold@robocracy.org>2018-06-28 12:25:21 -0700
committerBryan Newbold <bnewbold@robocracy.org>2018-06-28 12:25:23 -0700
commitd6baafc770f97e6b57c849a845de860437328c3b (patch)
tree5448492a099512be6cc359f00a7b4fbb3413f081
parent6b19abf279ff3aef44615a87f4ab671a8e515ebb (diff)
downloadfatcat-d6baafc770f97e6b57c849a845de860437328c3b.tar.gz
fatcat-d6baafc770f97e6b57c849a845de860437328c3b.zip
postgres performance/tuning notes
Results have been added to the journal-infra ansible repo.
-rw-r--r--notes/postgres_performance.txt138
1 files changed, 138 insertions, 0 deletions
diff --git a/notes/postgres_performance.txt b/notes/postgres_performance.txt
new file mode 100644
index 00000000..8f87d5c6
--- /dev/null
+++ b/notes/postgres_performance.txt
@@ -0,0 +1,138 @@
+
+## 2018-06-27 Measurements
+
+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
+
+## 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