maysamyabandeh/myrocks_writeprepared_sysbench_ssd.txt

## myrocks_writeprepared_sysbench_ssd.txt
# Setup
For each test there are 3 runs. All use a build from May 8 (8may18). The first is write-committed (wcmt) and the next is with write-prepared (wprep). These are listed as myrocks.8may18.none.wcmt and myrocks.8may18.none.wprep.
The test server has 56 cores with HT enabled, 3 SSDs and 256gb of RAM. For the insert benchmark I use a table per client. Each table has a PK and 3 secondary indexes.

# Benchmark 1
The first test is the insert benchmark load with 1 to 52 concurrent clients. 500m rows are inserted into N tables (table per client). Each insert transaction inserts 100 rows (~400 KV pairs given a PK and 3 secondary indexes). The insert rate is 7.8% better with write-prepared at 28 clients.
The first test is run 1, 2, 4, 8, ... 52 clients. The perf benefit from write-prepared starts at 8 clients.

-- 500m rows, n clients, n tables, in-memory
Average insert rate for 1 to 52 concurrent clients

1       2       4       8       12      16      20      24      28      32      36      40      44      48      52      engine
38447   71654   132837  135722  133298  133085  133014  134916  135943  136575  138160  139276  138850  140410  140568  myrocks.8may18.none.wcmt
38323   71023   132450  153657  150240  150557  148721  145391  146670  144760  148544  151240  150150  152207  148765  myrocks.8may18.none.wprep


# Benchmark 2
The next test uses 1 client to insert 100M rows and then do OLTP. Performance and HW efficiency metrics are similar for write-prepared and write-committed. This is good.

-- 100m rows, 1 table, 1 thread, mysql, in-memory

--- load

ips.av  ips.75  ips.95  ips.99  r/i     rkb/i   wkb/i   Mcpu/i  size    rss     r/s     rmb/s   wmb/s   cpu     engine
39124   38237   36001   33970   0       0       2.43    141     12       0.6    0       0        94.9   5.5     myrocks.8may18.none.wcmt
39604   38610   36387   34469   0       0       2.42    140     12       0.7    0       0        95.9   5.6     myrocks.8may18.none.wprep

legend:
* ips.av, ips.75, ips.95, ips.99 -- insert rates: average, p75, p95, p99
* r/i, rkb/i, wkb/i - iostat metrics, reads/insert, read KB/insert, write KB/insert
* Mcpu/i - normalized CPU overhead /insert
* size - database size in GB after load
* rss - mysqld RSS after load
* r/s, rmb/s, wmb/s - iostat metrics, reads/s, read MB/s, write MB/s
* cpu - vmstat us+sy

--- oltp - 1 reader, 1 writer at 1000 inserts/s

ips.av  ips.99  qps.av  qps.99  r/i     rkb/i   wkb/i   Mcpu/i  r/q     rkb/q   Mcpu/q  size    rss     r/s     rmb/s   wmb/s   cpu     engine
999     999     4265    4010    0       0        3.07   3221    0       0       754     14      15.8    0       0        3.1    3.2     myrocks.8may18.none.wcmt
999     998     4289    3962    0       0        3.31   3202    0       0       746     14      15.6    0       0        3.3    3.2     myrocks.8may18.none.wprep

Legend
* ips.av, ips.99 - insert/second average and p99. Note p99 is per client, thus about 1/16 of average
* qps.av, qpa.99 - queries/second average and p99. Note p99 is per client, thus about 1/16 of average
* r/i, rkb/i, wkb/i - from iostat, reads/insert, read KB/insert, write KB/insert - alas this includes IO done for inserts and queries
* Mcpu/i - CPU overhead/insert - alas this includes CPU for inserts and queries
* r/q, rkb/q, Mcpu/q - like r/i, rkb/i, wkb/i and Mcpu/i but per query - alas this includes IO/CPU for inserts and queries
* size - database size in GB at end of test step
* rss - mysqld RSS in GB at end of test step
* r/s, rmb/s, wmb/s - iostat r/s, rMB/s and wMB/s
* cpu - vmstat us+sy

--- oltp - 1 reader, 1 writer at 100 inserts/s

ips.av  ips.99  qps.av  qps.99  r/i     rkb/i   wkb/i   Mcpu/i  r/q     rkb/q   Mcpu/q  size    rss     r/s     rmb/s   wmb/s   cpu     engine
100     100     4749    4573    0       0         0.75  29085   0       0       611     14      15.5    0       0        0.1    2.9     myrocks.8may18.none
100     100     4129    3921    0       0         0.70  32393   0       0       783     14      15.8    0       0        0.1    3.2     myrocks.8may18.none.wcmt
100     100     4142    3948    0       0         0.68  30202   0       0       728     14      15.6    0       0        0.1    3.0     myrocks.8may18.none.wprep

# Benchmark 3
The final two tests use 16 clients and then 32 clients to load 500M rows and then do OLTP. The insert rate for write-prepared is 13% better at 16 clients and 4% better at 32 clients. The OLTP QPS is similar for write-prepared and write-committed -- that is good as those tests are not bound by writes. HW efficiency metrics are similar for write-prepared and write-committed. That is also good.

-- 500m rows, 16 tables, 16 clients, in-memory

--- load

ips.av  ips.75  ips.95  ips.99  r/i     rkb/i   wkb/i   Mcpu/i  size    rss     r/s     rmb/s   wmb/s   cpu     engine
133618  8291    8057    7846    0       0       3.07    139     61        1.7   0       0       409.8   18.6    myrocks.8may18.none.wcmt
150830  9246    8780    8504    0       0       2.99    139     65        1.8   0       0       450.2   20.9    myrocks.8may18.none.wprep

legend:
* ips.av, ips.75, ips.95, ips.99 -- insert rates: average, p75, p95, p99
* r/i, rkb/i, wkb/i - iostat metrics, reads/insert, read KB/insert, write KB/insert
* Mcpu/i - normalized CPU overhead /insert
* size - database size in GB after load
* rss - mysqld RSS after load
* r/s, rmb/s, wmb/s - iostat metrics, reads/s, read MB/s, write MB/s
* cpu - vmstat us+sy

--- oltp - 16 readers, 16 writers each at 1000 inserts/s

ips.av  ips.99  qps.av  qps.99  r/i     rkb/i   wkb/i   Mcpu/i  r/q     rkb/q   Mcpu/q  size    rss     r/s     rmb/s   wmb/s   cpu     engine
15845   996     56292   3299    0       0        4.76   1987    0       0       559      68     150.6   0       0        75.5   31.5    myrocks.8may18.none.wcmt
15845   996     56697   3319    0       0        4.67   1990    0       0       556      68     150.3   0       0        74.0   31.5    myrocks.8may18.none.wprep

Legend
* ips.av, ips.99 - insert/second average and p99. Note p99 is per client, thus about 1/16 of average
* qps.av, qpa.99 - queries/second average and p99. Note p99 is per client, thus about 1/16 of average
* r/i, rkb/i, wkb/i - from iostat, reads/insert, read KB/insert, write KB/insert - alas this includes IO done for inserts and queries
* Mcpu/i - CPU overhead/insert - alas this includes CPU for inserts and queries
* r/q, rkb/q, Mcpu/q - like r/i, rkb/i, wkb/i and Mcpu/i but per query - alas this includes IO/CPU for inserts and queries
* size - database size in GB at end of test step
* rss - mysqld RSS in GB at end of test step
* r/s, rmb/s, wmb/s - iostat r/s, rMB/s and wMB/s
* cpu - vmstat us+sy

--- oltp - 16 readers, 16 writers each at 100 inserts/s

ips.av  ips.99  qps.av  qps.99  r/i     rkb/i   wkb/i   Mcpu/i  r/q     rkb/q   Mcpu/q  size    rss     r/s     rmb/s   wmb/s   cpu     engine
1584    100     58213   3438    0       0        4.54   18552   0       0       505      70     159.6   0       0        7.2    29.4    myrocks.8may18.none.wcmt
1584    100     58572   3430    0       0        4.49   18488   0       0       500      70     158.9   0       0        7.1    29.3    myrocks.8may18.none.wprep

# Benchmark 4
The configuration is the same as in Benchmark 3.

-- 500m rows, 32 tables, 32 clients, in-memory

--- load

ips.av  ips.75  ips.95  ips.99  r/i     rkb/i   wkb/i   Mcpu/i  size    rss     r/s     rmb/s   wmb/s   cpu     engine
135648  4197    4115    4033    0       0       3.23    148     62        2.4   0       0       437.6   20.0    myrocks.8may18.none.wcmt
144467  4472    4353    4286    0       0       3.17    147     61        2.3   0       0       457.4   21.2    myrocks.8may18.none.wprep

legend:
* ips.av, ips.75, ips.95, ips.99 -- insert rates: average, p75, p95, p99
* r/i, rkb/i, wkb/i - iostat metrics, reads/insert, read KB/insert, write KB/insert
* Mcpu/i - normalized CPU overhead /insert
* size - database size in GB after load
* rss - mysqld RSS after load
* r/s, rmb/s, wmb/s - iostat metrics, reads/s, read MB/s, write MB/s
* cpu - vmstat us+sy

--- oltp - 32 readers, 32 writers each at 1000 inserts/s

ips.av  ips.99  qps.av  qps.99  r/i     rkb/i   wkb/i   Mcpu/i  r/q     rkb/q   Mcpu/q  size    rss     r/s     rmb/s   wmb/s   cpu     engine
31391   990      85483  2426    0       0.02     4.59   1884    0       0.01     692     80     193.5    9      0.6     144.1   59.1    myrocks.8may18.none.wcmt
31391   988      85042  2368    0       0.02     4.66   1901    0       0        702     80     193.5    9      0.7     146.3   59.7    myrocks.8may18.none.wprep

Legend
* ips.av, ips.99 - insert/second average and p99. Note p99 is per client, thus about 1/32 of average
* qps.av, qpa.99 - queries/second average and p99. Note p99 is per client, thus about 1/32 of average
* r/i, rkb/i, wkb/i - from iostat, reads/insert, read KB/insert, write KB/insert - alas this includes IO done for inserts and queries
* Mcpu/i - CPU overhead/insert - alas this includes CPU for inserts and queries
* r/q, rkb/q, Mcpu/q - like r/i, rkb/i, wkb/i and Mcpu/i but per query - alas this includes IO/CPU for inserts and queries
* size - database size in GB at end of test step
* rss - mysqld RSS in GB at end of test step
* r/s, rmb/s, wmb/s - iostat r/s, rMB/s and wMB/s
* cpu - vmstat us+sy

--- oltp - 32 readers, 32 writers each at 100 inserts/s

ips.av  ips.99  qps.av  qps.99  r/i     rkb/i   wkb/i   Mcpu/i  r/q     rkb/q   Mcpu/q  size    rss     r/s     rmb/s   wmb/s   cpu     engine
3138    100      95850  2698    0       0.15     5.57   17729   0       0.01     580     80     193.5    6      0.5      17.5   55.6    myrocks.8may18.none.wcmt
3138    100      96682  2820    0       0.17     5.53   17740   0       0.01     576     79     193.5    5      0.5      17.3   55.7    myrocks.8may18.none.wprep
	# Setup
	For each test there are 3 runs. All use a build from May 8 (8may18). The first is write-committed (wcmt) and the next is with write-prepared (wprep). These are listed as myrocks.8may18.none.wcmt and myrocks.8may18.none.wprep.
	The test server has 56 cores with HT enabled, 3 SSDs and 256gb of RAM. For the insert benchmark I use a table per client. Each table has a PK and 3 secondary indexes.

	# Benchmark 1
	The first test is the insert benchmark load with 1 to 52 concurrent clients. 500m rows are inserted into N tables (table per client). Each insert transaction inserts 100 rows (~400 KV pairs given a PK and 3 secondary indexes). The insert rate is 7.8% better with write-prepared at 28 clients.
	The first test is run 1, 2, 4, 8, ... 52 clients. The perf benefit from write-prepared starts at 8 clients.

	-- 500m rows, n clients, n tables, in-memory
	Average insert rate for 1 to 52 concurrent clients

	1 2 4 8 12 16 20 24 28 32 36 40 44 48 52 engine
	38447 71654 132837 135722 133298 133085 133014 134916 135943 136575 138160 139276 138850 140410 140568 myrocks.8may18.none.wcmt
	38323 71023 132450 153657 150240 150557 148721 145391 146670 144760 148544 151240 150150 152207 148765 myrocks.8may18.none.wprep


	# Benchmark 2
	The next test uses 1 client to insert 100M rows and then do OLTP. Performance and HW efficiency metrics are similar for write-prepared and write-committed. This is good.

	-- 100m rows, 1 table, 1 thread, mysql, in-memory

	--- load

	ips.av ips.75 ips.95 ips.99 r/i rkb/i wkb/i Mcpu/i size rss r/s rmb/s wmb/s cpu engine
	39124 38237 36001 33970 0 0 2.43 141 12 0.6 0 0 94.9 5.5 myrocks.8may18.none.wcmt
	39604 38610 36387 34469 0 0 2.42 140 12 0.7 0 0 95.9 5.6 myrocks.8may18.none.wprep

	legend:
	* ips.av, ips.75, ips.95, ips.99 -- insert rates: average, p75, p95, p99
	* r/i, rkb/i, wkb/i - iostat metrics, reads/insert, read KB/insert, write KB/insert
	* Mcpu/i - normalized CPU overhead /insert
	* size - database size in GB after load
	* rss - mysqld RSS after load
	* r/s, rmb/s, wmb/s - iostat metrics, reads/s, read MB/s, write MB/s
	* cpu - vmstat us+sy

	--- oltp - 1 reader, 1 writer at 1000 inserts/s

	ips.av ips.99 qps.av qps.99 r/i rkb/i wkb/i Mcpu/i r/q rkb/q Mcpu/q size rss r/s rmb/s wmb/s cpu engine
	999 999 4265 4010 0 0 3.07 3221 0 0 754 14 15.8 0 0 3.1 3.2 myrocks.8may18.none.wcmt
	999 998 4289 3962 0 0 3.31 3202 0 0 746 14 15.6 0 0 3.3 3.2 myrocks.8may18.none.wprep

	Legend
	* ips.av, ips.99 - insert/second average and p99. Note p99 is per client, thus about 1/16 of average
	* qps.av, qpa.99 - queries/second average and p99. Note p99 is per client, thus about 1/16 of average
	* r/i, rkb/i, wkb/i - from iostat, reads/insert, read KB/insert, write KB/insert - alas this includes IO done for inserts and queries
	* Mcpu/i - CPU overhead/insert - alas this includes CPU for inserts and queries
	* r/q, rkb/q, Mcpu/q - like r/i, rkb/i, wkb/i and Mcpu/i but per query - alas this includes IO/CPU for inserts and queries
	* size - database size in GB at end of test step
	* rss - mysqld RSS in GB at end of test step
	* r/s, rmb/s, wmb/s - iostat r/s, rMB/s and wMB/s
	* cpu - vmstat us+sy

	--- oltp - 1 reader, 1 writer at 100 inserts/s

	ips.av ips.99 qps.av qps.99 r/i rkb/i wkb/i Mcpu/i r/q rkb/q Mcpu/q size rss r/s rmb/s wmb/s cpu engine
	100 100 4749 4573 0 0 0.75 29085 0 0 611 14 15.5 0 0 0.1 2.9 myrocks.8may18.none
	100 100 4129 3921 0 0 0.70 32393 0 0 783 14 15.8 0 0 0.1 3.2 myrocks.8may18.none.wcmt
	100 100 4142 3948 0 0 0.68 30202 0 0 728 14 15.6 0 0 0.1 3.0 myrocks.8may18.none.wprep

	# Benchmark 3
	The final two tests use 16 clients and then 32 clients to load 500M rows and then do OLTP. The insert rate for write-prepared is 13% better at 16 clients and 4% better at 32 clients. The OLTP QPS is similar for write-prepared and write-committed -- that is good as those tests are not bound by writes. HW efficiency metrics are similar for write-prepared and write-committed. That is also good.

	-- 500m rows, 16 tables, 16 clients, in-memory

	--- load

	ips.av ips.75 ips.95 ips.99 r/i rkb/i wkb/i Mcpu/i size rss r/s rmb/s wmb/s cpu engine
	133618 8291 8057 7846 0 0 3.07 139 61 1.7 0 0 409.8 18.6 myrocks.8may18.none.wcmt
	150830 9246 8780 8504 0 0 2.99 139 65 1.8 0 0 450.2 20.9 myrocks.8may18.none.wprep

	legend:
	* ips.av, ips.75, ips.95, ips.99 -- insert rates: average, p75, p95, p99
	* r/i, rkb/i, wkb/i - iostat metrics, reads/insert, read KB/insert, write KB/insert
	* Mcpu/i - normalized CPU overhead /insert
	* size - database size in GB after load
	* rss - mysqld RSS after load
	* r/s, rmb/s, wmb/s - iostat metrics, reads/s, read MB/s, write MB/s
	* cpu - vmstat us+sy

	--- oltp - 16 readers, 16 writers each at 1000 inserts/s

	ips.av ips.99 qps.av qps.99 r/i rkb/i wkb/i Mcpu/i r/q rkb/q Mcpu/q size rss r/s rmb/s wmb/s cpu engine
	15845 996 56292 3299 0 0 4.76 1987 0 0 559 68 150.6 0 0 75.5 31.5 myrocks.8may18.none.wcmt
	15845 996 56697 3319 0 0 4.67 1990 0 0 556 68 150.3 0 0 74.0 31.5 myrocks.8may18.none.wprep

	Legend
	* ips.av, ips.99 - insert/second average and p99. Note p99 is per client, thus about 1/16 of average
	* qps.av, qpa.99 - queries/second average and p99. Note p99 is per client, thus about 1/16 of average
	* r/i, rkb/i, wkb/i - from iostat, reads/insert, read KB/insert, write KB/insert - alas this includes IO done for inserts and queries
	* Mcpu/i - CPU overhead/insert - alas this includes CPU for inserts and queries
	* r/q, rkb/q, Mcpu/q - like r/i, rkb/i, wkb/i and Mcpu/i but per query - alas this includes IO/CPU for inserts and queries
	* size - database size in GB at end of test step
	* rss - mysqld RSS in GB at end of test step
	* r/s, rmb/s, wmb/s - iostat r/s, rMB/s and wMB/s
	* cpu - vmstat us+sy

	--- oltp - 16 readers, 16 writers each at 100 inserts/s

	ips.av ips.99 qps.av qps.99 r/i rkb/i wkb/i Mcpu/i r/q rkb/q Mcpu/q size rss r/s rmb/s wmb/s cpu engine
	1584 100 58213 3438 0 0 4.54 18552 0 0 505 70 159.6 0 0 7.2 29.4 myrocks.8may18.none.wcmt
	1584 100 58572 3430 0 0 4.49 18488 0 0 500 70 158.9 0 0 7.1 29.3 myrocks.8may18.none.wprep

	# Benchmark 4
	The configuration is the same as in Benchmark 3.

	-- 500m rows, 32 tables, 32 clients, in-memory

	--- load

	ips.av ips.75 ips.95 ips.99 r/i rkb/i wkb/i Mcpu/i size rss r/s rmb/s wmb/s cpu engine
	135648 4197 4115 4033 0 0 3.23 148 62 2.4 0 0 437.6 20.0 myrocks.8may18.none.wcmt
	144467 4472 4353 4286 0 0 3.17 147 61 2.3 0 0 457.4 21.2 myrocks.8may18.none.wprep

	legend:
	* ips.av, ips.75, ips.95, ips.99 -- insert rates: average, p75, p95, p99
	* r/i, rkb/i, wkb/i - iostat metrics, reads/insert, read KB/insert, write KB/insert
	* Mcpu/i - normalized CPU overhead /insert
	* size - database size in GB after load
	* rss - mysqld RSS after load
	* r/s, rmb/s, wmb/s - iostat metrics, reads/s, read MB/s, write MB/s
	* cpu - vmstat us+sy

	--- oltp - 32 readers, 32 writers each at 1000 inserts/s

	ips.av ips.99 qps.av qps.99 r/i rkb/i wkb/i Mcpu/i r/q rkb/q Mcpu/q size rss r/s rmb/s wmb/s cpu engine
	31391 990 85483 2426 0 0.02 4.59 1884 0 0.01 692 80 193.5 9 0.6 144.1 59.1 myrocks.8may18.none.wcmt
	31391 988 85042 2368 0 0.02 4.66 1901 0 0 702 80 193.5 9 0.7 146.3 59.7 myrocks.8may18.none.wprep

	Legend
	* ips.av, ips.99 - insert/second average and p99. Note p99 is per client, thus about 1/32 of average
	* qps.av, qpa.99 - queries/second average and p99. Note p99 is per client, thus about 1/32 of average
	* r/i, rkb/i, wkb/i - from iostat, reads/insert, read KB/insert, write KB/insert - alas this includes IO done for inserts and queries
	* Mcpu/i - CPU overhead/insert - alas this includes CPU for inserts and queries
	* r/q, rkb/q, Mcpu/q - like r/i, rkb/i, wkb/i and Mcpu/i but per query - alas this includes IO/CPU for inserts and queries
	* size - database size in GB at end of test step
	* rss - mysqld RSS in GB at end of test step
	* r/s, rmb/s, wmb/s - iostat r/s, rMB/s and wMB/s
	* cpu - vmstat us+sy

	--- oltp - 32 readers, 32 writers each at 100 inserts/s

	ips.av ips.99 qps.av qps.99 r/i rkb/i wkb/i Mcpu/i r/q rkb/q Mcpu/q size rss r/s rmb/s wmb/s cpu engine
	3138 100 95850 2698 0 0.15 5.57 17729 0 0.01 580 80 193.5 6 0.5 17.5 55.6 myrocks.8may18.none.wcmt
	3138 100 96682 2820 0 0.17 5.53 17740 0 0.01 576 79 193.5 5 0.5 17.3 55.7 myrocks.8may18.none.wprep