aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
-rw-r--r--notes/cockroach_tuning.md96
1 files changed, 96 insertions, 0 deletions
diff --git a/notes/cockroach_tuning.md b/notes/cockroach_tuning.md
new file mode 100644
index 00000000..dc6acb4f
--- /dev/null
+++ b/notes/cockroach_tuning.md
@@ -0,0 +1,96 @@
+
+When doing a bulk import, could try:
+
+
+Probably want to set:
+
+ --cache=.25 --max-sql-memory=.25
+
+Check FD limits:
+
+ cat /proc/sys/fs/file-max
+ ulimit -a
+
+Set with:
+
+ echo 150000 > /proc/sys/fs/file-max
+
+TODO:
+- set `num_replicas=1` when testing
+- close browser to free up RAM
+- disable raft fsync
+ https://github.com/cockroachdb/cockroach/issues/19784
+- increase import batch size (10x current)
+
+## Log of Tests
+
+build: CCL v2.0.5 @ 2018/08/13 17:59:42 (go1.10)
+
+COCKROACH branch e386022ba051f46ff70c28b90d9a2fe1f856c57f
+
+cockroach start --insecure --store=fatcat-dev --host=localhost
+
+ time ./fatcat_import.py import-issn /home/bnewbold/code/oa-journals-analysis/upload-2018-04-05/journal_extra_metadata.csv
+
+ real 3m20.781s
+ user 0m6.900s
+ sys 0m0.336s
+
+ cat /data/crossref/crossref-works.2018-01-21.badsample_100k.json | time parallel -j4 --round-robin --pipe ./fatcat_import.py import-crossref - /data/issn/20180216.ISSN-to-ISSN-L.txt
+
+ => gave up after ~30 minutes with only ~40% done
+
+With cockroach, container lookups seem to be particularly slow, like 1000ms+,
+though not reflected in cockroach-reported stats . Very high CPU usage by
+cockroach.
+
+Cockroach is self-reporting P50 latency: 0.3 ms, P99 latency: 104.9 ms
+
+Seeing lots of Queue Processing Failures, of the "replication" type. Probably because there are no other nodes.
+
+CHANGES:
+- closed browser, restarted postgres, restarted fatcatd to free up RAM
+- cockroach start --insecure --store=fatcat-dev --host=localhost --cache=.25 --max-sql-memory=.25
+- cockroach zone set --insecure .default -f -
+ num_replicas: 1
+ ^D
+- SET CLUSTER SETTING kv.raft_log.synchronize=false;
+- batch size from default (50) to 500
+
+ time ./fatcat_import.py import-issn --batch-size 500 /home/bnewbold/code/oa-journals-analysis/upload-2018-04-05/journal_extra_metadata.csv
+
+ real 1m10.531s
+ user 0m5.204s
+ sys 0m0.128s
+
+Have 12 SQL connections
+
+ cat /data/crossref/crossref-works.2018-01-21.badsample_100k.json | time parallel -j4 --round-robin --pipe ./fatcat_import.py import-crossref --batch-size 500 - /data/issn/20180216.ISSN-to-ISSN-L.txt
+
+ GETs are still very slow end-to-end (~1sec)
+ during this time, though, cockroach is quoting very low latency
+ => halted
+
+It's the join performance (on lookups) that is killing things for release rev import:
+
+ select * from container_rev where issnl = '0010-7824';
+ Time: 2.16272ms
+
+ select * from container_rev inner join container_ident on container_rev.id = container_ident.rev_id where issnl = '0010-7824';
+ Time: 147.44647ms
+
+CHANGED: CCL v2.1.0-beta.20180904 @ 2018/09/04 03:51:03 (go1.10.3)
+
+ time ./fatcat_import.py import-issn --batch-size 500 /home/bnewbold/code/oa-journals-analysis/upload-2018-04-05/journal_extra_metadata.csv
+
+ real 0m59.903s
+ user 0m4.716s
+ sys 0m0.076s
+
+ cat /data/crossref/crossref-works.2018-01-21.badsample_100k.json | time parallel -j4 --round-robin --pipe ./fatcat_import.py import-crossref --batch-size 500 - /data/issn/20180216.ISSN-to-ISSN-L.txt
+
+ => still extremely slow API latency, though cockroach is reporting very
+ fast (P50 latency 1.0 ms, P99 latency 4.7 ms)
+ => maybe a timeout or something going on?
+ => single-level joins from CLI are ~200 ms
+