search

cockroachdb / cockroachdb-cloudformation

Quickly setup dev/test CockroachDB clusters using AWS CloudFormation and Kubernetes
https://www.cockroachlabs.com/
Apache License 2.0
12 stars 4 forks source link

smallbank tuning parameter #32

Closed robert-s-lee closed 5 years ago

robert-s-lee commented 5 years ago

Smallbank has rather big batch inserts which requires below setting.

SET CLUSTER SETTING kv.transaction.max_intents_bytes = 1256000;
SET CLUSTER SETTING kv.transaction.max_refresh_spans_bytes = 1256000;

The defaut results in below

01:45:51,327 (DBWorkload.java:260) INFO  - ======================================================================
01:45:51,339 (DBWorkload.java:519) INFO  - Creating new SMALLBANK database...
01:45:51,630 (DBWorkload.java:521) INFO  - Finished!
01:45:51,631 (DBWorkload.java:522) INFO  - ======================================================================
01:45:51,631 (DBWorkload.java:545) INFO  - Loading data into SMALLBANK database...
01:45:57,807 (SmallBankLoader.java:123) ERROR - Failed to load data
java.sql.BatchUpdateException: Batch entry 7 INSERT INTO SAVINGS (CUSTID, BAL) VALUES (100896, 34035),(100897, 49797),(100898, 25827),(100899, 42478),(100900, 29609),(100901, 33970),(100902, 31931),(100903, 19338),(100904, 48136),(100905, 39966),(100906, 32430),(100907, 22824),(100908, 44514),(100909, 19688),(100910, 37365),(100911, 26590),(100912, 21153),(100913, 21124),(100914, 40085),(100915, 21244),(100916, 31025),(100917, 35496),(100918, 39595),(100919, 40526),(100920, 39989),(100921, 31657),(100922, 38473),(100923, 45907),(100924, 15240),(100925, 27855),(100926, 44454),(100927, 27474),(100928, 39086),(100929, 31246),(100930, 39634),(100931, 21918),(100932, 49305),(100933, 42442),(100934, 27754),(100935, 23143),(100936, 39630),(100937, 39865),(100938, 28264),(100939, 23409),(100940, 37632),(100941, 30918),(100942, 31893),(100943, 35350),(100944, 33235),(100945, 41242),(100946, 30010),(100947, 34869),(100948, 41073),(100949, 34395),(100950, 11722),(100951, 29290),(100952, 19965),(100953, 41371),(100954, 38357),(100955, 26696),(100956, 23402),(100957, 20653),(100958, 26259),(100959, 35102),(100960, 29643),(100961, 43268),(100962, 29573),(100963, 27907),(100964, 41726),(100965, 18437),(100966, 39209),(100967, 39572),(100968, 27954),(100969, 36134),(100970, 37294),(100971, 36417),(100972, 30899),(100973, 32222),(100974, 28365),(100975, 32466),(100976, 19627),(100977, 26314),(100978, 26940),(100979, 28843),(100980, 32917),(100981, 26891),(100982, 37670),(100983, 24323),(100984, 39249),(100985, 10806),(100986, 34083),(100987, 22059),(100988, 34729),(100989, 28411),(100990, 27401),(100991, 21493),(100992, 28942),(100993, 18424),(100994, 20169),(100995, 19675),(100996, 35011),(100997, 42175),(100998, 30525),(100999, 13904),(101000, 38831),(101001, 26517),(101002, 36768),(101003, 49140),(101004, 31834),(101005, 31854),(101006, 22307),(101007, 39790),(101008, 28101),(101009, 48541),(101010, 25317),(101011, 18833),(101012, 26925),(101013, 32741),(101014, 30679),(101015, 25156),(101016, 34766),(101017, 20330),(101018, 29213),(101019, 23306),(101020, 28046),(101021, 19748),(101022, 39475),(101023, 42916) was aborted: ERROR: transaction is too large to complete; try splitting into pieces  Call getNextException to see other errors in the batch.
    at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:148)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2184)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:481)
    at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:840)
    at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1567)
    at com.oltpbenchmark.benchmarks.smallbank.SmallBankLoader$Generator.loadTables(SmallBankLoader.java:130)
    at com.oltpbenchmark.benchmarks.smallbank.SmallBankLoader$Generator.load(SmallBankLoader.java:114)
    at com.oltpbenchmark.api.Loader$LoaderThread.run(Loader.java:65)
    at com.oltpbenchmark.util.ThreadUtil$LatchRunnable.run(ThreadUtil.java:343)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: org.postgresql.util.PSQLException: ERROR: transaction is too large to complete; try splitting into pieces
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
    ... 10 more