数据库优化-基准测试（四）

bzr branch lp:sysbench $ cd sysbench $ ./autogen.sh $ ./configure $ make $ sudo make install

Sysbench CPU测试

例子1：处理素数的一个简单测试

<code class=" hljs livecodeserver">$ sysbench <span class="hljs-comment">--test=cpu --cpu-max-prime=20000 run</span>
Running <span class="hljs-operator">the</span> test <span class="hljs-operator">with</span> following options:
Number <span class="hljs-operator">of</span> threads: <span class="hljs-number">1</span>
Primer numbers limit: <span class="hljs-number">20000</span>
General statistics:
total <span class="hljs-built_in">time</span>: <span class="hljs-number">54.5050</span>s
total <span class="hljs-built_in">number</span> <span class="hljs-operator">of</span> events: <span class="hljs-number">10000</span>
total <span class="hljs-built_in">time</span> taken <span class="hljs-keyword">by</span> event execution: <span class="hljs-number">54.4840</span>s
response <span class="hljs-built_in">time</span>:
<span class="hljs-built_in">min</span>: <span class="hljs-number">5.20</span>ms
<span class="hljs-built_in">avg</span>: <span class="hljs-number">5.45</span>ms
<span class="hljs-built_in">max</span>: <span class="hljs-number">45.02</span>ms
approx. <span class="hljs-number">95</span> percentile: <span class="hljs-number">5.69</span>ms
Threads fairness:
events (<span class="hljs-built_in">avg</span>/stddev): <span class="hljs-number">10000.0000</span>/<span class="hljs-number">0.00</span>
execution <span class="hljs-built_in">time</span> (<span class="hljs-built_in">avg</span>/stddev): <span class="hljs-number">54.4840</span>/<span class="hljs-number">0.00</span>
</code>

例子2：处理素数的多线程测试，线程数为4

<code class=" hljs livecodeserver">$ sysbench <span class="hljs-comment">--num-threads=4 --test=cpu --cpu-max-prime=20000 run</span>
Running <span class="hljs-operator">the</span> test <span class="hljs-operator">with</span> following options:
Number <span class="hljs-operator">of</span> threads: <span class="hljs-number">4</span>
Primer numbers limit: <span class="hljs-number">20000</span>
General statistics:
total <span class="hljs-built_in">time</span>: <span class="hljs-number">16.5565</span>s
total <span class="hljs-built_in">number</span> <span class="hljs-operator">of</span> events: <span class="hljs-number">10000</span>
total <span class="hljs-built_in">time</span> taken <span class="hljs-keyword">by</span> event execution: <span class="hljs-number">66.1771</span>s
response <span class="hljs-built_in">time</span>:
<span class="hljs-built_in">min</span>: <span class="hljs-number">5.89</span>ms
<span class="hljs-built_in">avg</span>: <span class="hljs-number">6.62</span>ms
<span class="hljs-built_in">max</span>: <span class="hljs-number">101.86</span>ms
approx. <span class="hljs-number">95</span> percentile: <span class="hljs-number">8.43</span>ms
Threads fairness:
events (<span class="hljs-built_in">avg</span>/stddev): <span class="hljs-number">2500.0000</span>/<span class="hljs-number">136.15</span>
execution <span class="hljs-built_in">time</span> (<span class="hljs-built_in">avg</span>/stddev): <span class="hljs-number">16.5443</span>/<span class="hljs-number">0.00</span>
</code>

例子3：处理素数的多线程测试，线程数为8

<code class=" hljs livecodeserver">$ sysbench <span class="hljs-comment">--num-threads=8 --test=cpu --cpu-max-prime=20000 run</span>
Running <span class="hljs-operator">the</span> test <span class="hljs-operator">with</span> following options:
Number <span class="hljs-operator">of</span> threads: <span class="hljs-number">8</span>
Primer numbers limit: <span class="hljs-number">20000</span>
General statistics:
total <span class="hljs-built_in">time</span>: <span class="hljs-number">16.1659</span>s
total <span class="hljs-built_in">number</span> <span class="hljs-operator">of</span> events: <span class="hljs-number">10000</span>
total <span class="hljs-built_in">time</span> taken <span class="hljs-keyword">by</span> event execution: <span class="hljs-number">129.1411</span>s
response <span class="hljs-built_in">time</span>:
<span class="hljs-built_in">min</span>: <span class="hljs-number">5.89</span>ms
<span class="hljs-built_in">avg</span>: <span class="hljs-number">12.91</span>ms
<span class="hljs-built_in">max</span>: <span class="hljs-number">49.95</span>ms
approx. <span class="hljs-number">95</span> percentile: <span class="hljs-number">25.30</span>ms
Threads fairness:
events (<span class="hljs-built_in">avg</span>/stddev): <span class="hljs-number">1250.0000</span>/<span class="hljs-number">54.65</span>
execution <span class="hljs-built_in">time</span> (<span class="hljs-built_in">avg</span>/stddev): <span class="hljs-number">16.1426</span>/<span class="hljs-number">0.01</span>
</code>

Sysbench 线程测试(scheduler)

例子：Lock mutex => Yield CPU => Run queue => Unlock mutex

<code class=" hljs livecodeserver">$ sysbench <span class="hljs-comment">--num-threads=64 --test=threads run</span>
Running <span class="hljs-operator">the</span> test <span class="hljs-operator">with</span> following options:
Number <span class="hljs-operator">of</span> threads: <span class="hljs-number">64</span>
General statistics:
total <span class="hljs-built_in">time</span>: <span class="hljs-number">3.4055</span>s
total <span class="hljs-built_in">number</span> <span class="hljs-operator">of</span> events: <span class="hljs-number">10000</span>
total <span class="hljs-built_in">time</span> taken <span class="hljs-keyword">by</span> event execution: <span class="hljs-number">217.2030</span>s
response <span class="hljs-built_in">time</span>:
<span class="hljs-built_in">min</span>: <span class="hljs-number">0.56</span>ms
<span class="hljs-built_in">avg</span>: <span class="hljs-number">21.72</span>ms
<span class="hljs-built_in">max</span>: <span class="hljs-number">162.65</span>ms
approx. <span class="hljs-number">95</span> percentile: <span class="hljs-number">59.37</span>ms
Threads fairness:
events (<span class="hljs-built_in">avg</span>/stddev): <span class="hljs-number">156.2500</span>/<span class="hljs-number">10.98</span>
execution <span class="hljs-built_in">time</span> (<span class="hljs-built_in">avg</span>/stddev): <span class="hljs-number">3.3938</span>/<span class="hljs-number">0.01</span>
</code>

Sysbench mutexs测试

例子：线程试图获取相同的mutex集合，测试mutexe实现

<code class=" hljs livecodeserver">$ sysbench <span class="hljs-comment">--num-threads=128 --test=mutex --mutex-locks=100000 --mutex-loops=100</span>
<span class="hljs-comment">--mutex-num=2048 run</span>
Running <span class="hljs-operator">the</span> test <span class="hljs-operator">with</span> following options:
Number <span class="hljs-operator">of</span> threads: <span class="hljs-number">128</span>
General statistics:
total <span class="hljs-built_in">time</span>: <span class="hljs-number">10.4538</span>s
total <span class="hljs-built_in">number</span> <span class="hljs-operator">of</span> events: <span class="hljs-number">128</span>
total <span class="hljs-built_in">time</span> taken <span class="hljs-keyword">by</span> event execution: <span class="hljs-number">1274.8660</span>s
response <span class="hljs-built_in">time</span>:
<span class="hljs-built_in">min</span>: <span class="hljs-number">8069.55</span>ms
<span class="hljs-built_in">avg</span>: <span class="hljs-number">9959.89</span>ms
<span class="hljs-built_in">max</span>: <span class="hljs-number">10453.19</span>ms
approx. <span class="hljs-number">95</span> percentile: <span class="hljs-number">10436.39</span>ms
Threads fairness:
events (<span class="hljs-built_in">avg</span>/stddev): <span class="hljs-number">1.0000</span>/<span class="hljs-number">0.00</span>
execution <span class="hljs-built_in">time</span> (<span class="hljs-built_in">avg</span>/stddev): <span class="hljs-number">9.9599</span>/<span class="hljs-number">0.52</span>
</code>

Sysbench memory测试

例子1：顺序读或写内存

<code class=" hljs livecodeserver">$ sysbench <span class="hljs-comment">--num-threads=1 --test=memory run –memory-block-size=16k</span>
<span class="hljs-comment">--memory-total-size=4G --memory-oper=read</span>
Running <span class="hljs-operator">the</span> test <span class="hljs-operator">with</span> following options:
Number <span class="hljs-operator">of</span> threads: <span class="hljs-number">1</span>
Operations performed: <span class="hljs-number">262144</span> (<span class="hljs-number">302035.23</span> ops/<span class="hljs-built_in">sec</span>)
<span class="hljs-number">4096.00</span> MB transferred (<span class="hljs-number">4719.30</span> MB/<span class="hljs-built_in">sec</span>)
General statistics:
total <span class="hljs-built_in">time</span>: <span class="hljs-number">0.8679</span>s
total <span class="hljs-built_in">number</span> <span class="hljs-operator">of</span> events: <span class="hljs-number">262144</span>
total <span class="hljs-built_in">time</span> taken <span class="hljs-keyword">by</span> event execution: <span class="hljs-number">0.3994</span>s
response <span class="hljs-built_in">time</span>:
<span class="hljs-built_in">min</span>: <span class="hljs-number">0.00</span>ms
<span class="hljs-built_in">avg</span>: <span class="hljs-number">0.00</span>ms
<span class="hljs-built_in">max</span>: <span class="hljs-number">0.11</span>ms
approx. <span class="hljs-number">95</span> percentile: <span class="hljs-number">0.00</span>ms
Threads fairness:
events (<span class="hljs-built_in">avg</span>/stddev): <span class="hljs-number">262144.0000</span>/<span class="hljs-number">0.00</span>
execution <span class="hljs-built_in">time</span> (<span class="hljs-built_in">avg</span>/stddev): <span class="hljs-number">0.3994</span>/<span class="hljs-number">0.00</span>
</code>

内存OPS：

数字的谎言！
前一个测试报告在8MB块大小时读速度1.8TB/s
32MB块大小时读速度上升到7.2TB/s
恒定的OPS是真正的读瓶颈。

内存写OPS：

内存写带宽MB/S：

Sysbench 文件IO测试

IO测试包括两个阶段：

• 准备：在文件系统中创建文件
• 运行：在之前创建的文件上执行测试

支持测试的I/O模式( –file-test-mode)，包括：

• seqwr : 顺序写
• seqrewr : 顺序重写
• seqrd : 顺序读
• rndrd : 随机读
• rndwr : 随机写
• rndrw : 随机读/写

其它选项：

<code class=" hljs haml">-<span class="ruby">-file-num <span class="hljs-symbol">:</span> 文件数量
</span>-<span class="ruby">-file-block-size：文件块大小
</span>-<span class="ruby">-file-total-size <span class="hljs-symbol">:</span> 所有合并的文件
</span>-<span class="ruby">-file-io-mode <span class="hljs-symbol">:</span> sync , async, mmap
</span>-<span class="ruby">-file-extra-flags (odirect)
</span>-<span class="ruby">-file-fsync-freq <span class="hljs-symbol">:</span> fsync()的频率
</span>-<span class="ruby">-file-fsync-all
</span>-<span class="ruby">-file-rw-ratio <span class="hljs-symbol">:</span> rndrw模式中的<span class="hljs-constant">R</span>/<span class="hljs-constant">W</span>比例
</span></code>

准备文件：

<code class=" hljs livecodeserver">$ sysbench <span class="hljs-comment">--test=fileio --file-num=4 --file-total-size=1G prepare</span>
<span class="hljs-number">4</span> <span class="hljs-built_in">files</span>, <span class="hljs-number">262144</span>Kb <span class="hljs-keyword">each</span>, <span class="hljs-number">1024</span>Mb total
Creating <span class="hljs-built_in">files</span> <span class="hljs-keyword">for</span> <span class="hljs-operator">the</span> test...
Extra <span class="hljs-built_in">file</span> <span class="hljs-built_in">open</span> flags: <span class="hljs-number">0</span>
Creating <span class="hljs-built_in">file</span> test_file<span class="hljs-number">.0</span>
Creating <span class="hljs-built_in">file</span> test_file<span class="hljs-number">.1</span>
Creating <span class="hljs-built_in">file</span> test_file<span class="hljs-number">.2</span>
Creating <span class="hljs-built_in">file</span> test_file<span class="hljs-number">.3</span>
<span class="hljs-number">1073741824</span> <span class="hljs-keyword">bytes</span> written <span class="hljs-operator">in</span> <span class="hljs-number">40.32</span> <span class="hljs-built_in">seconds</span> (<span class="hljs-number">25.40</span> MB/<span class="hljs-built_in">sec</span>).
</code>

执行测试1（文件数1，顺序重写，文件块大小4K）：

<code class=" hljs livecodeserver">sysbench <span class="hljs-comment">--test=fileio --file-num=1 --file-total-size=2G --file-fsync-all=on --file-test-mode=seqrewr --max-time=100 –file-block-size=4096 --max-requests=0 run</span>
Number <span class="hljs-operator">of</span> threads: <span class="hljs-number">1</span>
Extra <span class="hljs-built_in">file</span> <span class="hljs-built_in">open</span> flags: <span class="hljs-number">0</span>
<span class="hljs-number">1</span> <span class="hljs-built_in">files</span>, <span class="hljs-number">2</span>Gb <span class="hljs-keyword">each</span>
<span class="hljs-number">2</span>Gb total <span class="hljs-built_in">file</span> size
Block size <span class="hljs-number">4</span>Kb
Calling fsync() <span class="hljs-keyword">after</span> <span class="hljs-keyword">each</span> <span class="hljs-built_in">write</span> operation.
Using synchronous I/O mode
Doing sequential rewrite test
Operations performed: <span class="hljs-number">0</span> Read, <span class="hljs-number">524288</span> Write, <span class="hljs-number">524288</span> Other = <span class="hljs-number">1048576</span> Total
Read <span class="hljs-number">0</span>b Written <span class="hljs-number">2</span>Gb Total transferred <span class="hljs-number">2</span>Gb (<span class="hljs-number">52.616</span>Mb/<span class="hljs-built_in">sec</span>)
<span class="hljs-number">13469.70</span> Requests/<span class="hljs-built_in">sec</span> executed
Test execution summary:
total <span class="hljs-built_in">time</span>: <span class="hljs-number">38.9235</span>s
total <span class="hljs-built_in">number</span> <span class="hljs-operator">of</span> events: <span class="hljs-number">524288</span>
total <span class="hljs-built_in">time</span> taken <span class="hljs-keyword">by</span> event execution: <span class="hljs-number">38.2213</span>
per-request statistics:
<span class="hljs-built_in">min</span>: <span class="hljs-number">0.06</span>ms
<span class="hljs-built_in">avg</span>: <span class="hljs-number">0.07</span>ms
<span class="hljs-built_in">max</span>: <span class="hljs-number">8.60</span>ms
approx. <span class="hljs-number">95</span> percentile: <span class="hljs-number">0.12</span>ms
</code>

执行测试2（文件数29，随机读写，文件块大小16K）：

<code class=" hljs livecodeserver">sysbench <span class="hljs-comment">--test=fileio --file-num=29 --file-total-size=290G --file-fsync-all=on --file-test-mode=rndrw --max-time=100 --file-block-size=16384 --max-requests=0 run</span>
Number <span class="hljs-operator">of</span> threads: <span class="hljs-number">1</span>
Extra <span class="hljs-built_in">file</span> <span class="hljs-built_in">open</span> flags: <span class="hljs-number">0</span>
<span class="hljs-number">29</span> <span class="hljs-built_in">files</span>, <span class="hljs-number">10</span>Gb <span class="hljs-keyword">each</span>
<span class="hljs-number">290</span>Gb total <span class="hljs-built_in">file</span> size
Block size <span class="hljs-number">16</span>Kb
Number <span class="hljs-operator">of</span> <span class="hljs-built_in">random</span> requests <span class="hljs-keyword">for</span> <span class="hljs-built_in">random</span> IO: <span class="hljs-number">0</span>
Read/Write ratio <span class="hljs-keyword">for</span> combined <span class="hljs-built_in">random</span> IO test: <span class="hljs-number">1.50</span>
Calling fsync() <span class="hljs-keyword">after</span> <span class="hljs-keyword">each</span> <span class="hljs-built_in">write</span> operation.
Using synchronous I/O mode
Doing <span class="hljs-built_in">random</span> r/w test
Operations performed: <span class="hljs-number">449824</span> Read, <span class="hljs-number">299883</span> Write, <span class="hljs-number">299883</span> Other = <span class="hljs-number">1049590</span> Total
Read <span class="hljs-number">6.8638</span>Gb Written <span class="hljs-number">4.5759</span>Gb Total transferred <span class="hljs-number">11.44</span>Gb (<span class="hljs-number">117.14</span>Mb/<span class="hljs-built_in">sec</span>)
<span class="hljs-number">7497.05</span> Requests/<span class="hljs-built_in">sec</span> executed
Test execution summary:
total <span class="hljs-built_in">time</span>: <span class="hljs-number">100.0003</span>s
total <span class="hljs-built_in">number</span> <span class="hljs-operator">of</span> events: <span class="hljs-number">749707</span>
total <span class="hljs-built_in">time</span> taken <span class="hljs-keyword">by</span> event execution: <span class="hljs-number">98.9223</span>
per-request statistics:
<span class="hljs-built_in">min</span>: <span class="hljs-number">0.01</span>ms
<span class="hljs-built_in">avg</span>: <span class="hljs-number">0.13</span>ms
<span class="hljs-built_in">max</span>: <span class="hljs-number">7.34</span>ms
approx. <span class="hljs-number">95</span> percentile: <span class="hljs-number">0.16</span>ms
</code>

执行测试3（文件数29，随机读写，读写比率为4，文件块大小16K，线程数8）：

<code class=" hljs livecodeserver">sysbench <span class="hljs-comment">--num-threads=8 --test=fileio --file-num=29 –file-total-size=290G --file-fsync-all=on --file-test-mode=rndrw --max-time=100 --file-rw-ratio=4 --file-block-size=16384 --max-requests=0 run</span>
Number <span class="hljs-operator">of</span> threads: <span class="hljs-number">8</span>
Extra <span class="hljs-built_in">file</span> <span class="hljs-built_in">open</span> flags: <span class="hljs-number">0</span>
<span class="hljs-number">29</span> <span class="hljs-built_in">files</span>, <span class="hljs-number">10</span>Gb <span class="hljs-keyword">each</span>
<span class="hljs-number">290</span>Gb total <span class="hljs-built_in">file</span> size
Block size <span class="hljs-number">16</span>Kb
Number <span class="hljs-operator">of</span> <span class="hljs-built_in">random</span> requests <span class="hljs-keyword">for</span> <span class="hljs-built_in">random</span> IO: <span class="hljs-number">0</span>
Read/Write ratio <span class="hljs-keyword">for</span> combined <span class="hljs-built_in">random</span> IO test: <span class="hljs-number">4.00</span>
Calling fsync() <span class="hljs-keyword">after</span> <span class="hljs-keyword">each</span> <span class="hljs-built_in">write</span> operation.
Using synchronous I/O mode
Doing <span class="hljs-built_in">random</span> r/w test
Operations performed: <span class="hljs-number">1734215</span> Read, <span class="hljs-number">433552</span> Write, <span class="hljs-number">433552</span> Other = <span class="hljs-number">2601319</span> Total
Read <span class="hljs-number">26.462</span>Gb Written <span class="hljs-number">6.6155</span>Gb Total transferred <span class="hljs-number">33.077</span>Gb (<span class="hljs-number">338.71</span>Mb/<span class="hljs-built_in">sec</span>)
<span class="hljs-number">21677.60</span> Requests/<span class="hljs-built_in">sec</span> executed
Test execution summary:
total <span class="hljs-built_in">time</span>: <span class="hljs-number">100.0003</span>s
total <span class="hljs-built_in">number</span> <span class="hljs-operator">of</span> events: <span class="hljs-number">2167767</span>
total <span class="hljs-built_in">time</span> taken <span class="hljs-keyword">by</span> event execution: <span class="hljs-number">796.3734</span>
per-request statistics:
<span class="hljs-built_in">min</span>: <span class="hljs-number">0.01</span>ms
<span class="hljs-built_in">avg</span>: <span class="hljs-number">0.37</span>ms
<span class="hljs-built_in">max</span>: <span class="hljs-number">15.18</span>ms
approx. <span class="hljs-number">95</span> percentile: <span class="hljs-number">1.77</span>ms
</code>

执行测试4（文件数29，随机读写，读写比率为4，文件块大小16K，线程数16, –file-fsync-all 每执行完一次写操作,就执行一次fsync,默认是off）：

<code class=" hljs livecodeserver">sysbench <span class="hljs-comment">--num-threads=16 --test=fileio --file-num=29 –file-total-size=290G --file-fsync-all=on --file-test-mode=rndrw --max-time=100 –file-rw-ratio=4 --file-block-size=16384 --max-requests=0 run</span>
Number <span class="hljs-operator">of</span> threads: <span class="hljs-number">16</span>
Extra <span class="hljs-built_in">file</span> <span class="hljs-built_in">open</span> flags: <span class="hljs-number">0</span>
<span class="hljs-number">29</span> <span class="hljs-built_in">files</span>, <span class="hljs-number">10</span>Gb <span class="hljs-keyword">each</span>
<span class="hljs-number">290</span>Gb total <span class="hljs-built_in">file</span> size
Block size <span class="hljs-number">16</span>Kb
Number <span class="hljs-operator">of</span> <span class="hljs-built_in">random</span> requests <span class="hljs-keyword">for</span> <span class="hljs-built_in">random</span> IO: <span class="hljs-number">0</span>
Read/Write ratio <span class="hljs-keyword">for</span> combined <span class="hljs-built_in">random</span> IO test: <span class="hljs-number">4.00</span>
Calling fsync() <span class="hljs-keyword">after</span> <span class="hljs-keyword">each</span> <span class="hljs-built_in">write</span> operation.
Using synchronous I/O mode
Doing <span class="hljs-built_in">random</span> r/w test
Operations performed: <span class="hljs-number">2028938</span> Read, <span class="hljs-number">507238</span> Write, <span class="hljs-number">507238</span> Other = <span class="hljs-number">3043414</span> Total
Read <span class="hljs-number">30.959</span>Gb Written <span class="hljs-number">7.7398</span>Gb Total transferred <span class="hljs-number">38.699</span>Gb (<span class="hljs-number">396.26</span>Mb/<span class="hljs-built_in">sec</span>)
<span class="hljs-number">25360.46</span> Requests/<span class="hljs-built_in">sec</span> executed
Test execution summary:
total <span class="hljs-built_in">time</span>: <span class="hljs-number">100.0051</span>s
total <span class="hljs-built_in">number</span> <span class="hljs-operator">of</span> events: <span class="hljs-number">2536176</span>
total <span class="hljs-built_in">time</span> taken <span class="hljs-keyword">by</span> event execution: <span class="hljs-number">1594.7509</span>
per-request statistics:
<span class="hljs-built_in">min</span>: <span class="hljs-number">0.01</span>ms
<span class="hljs-built_in">avg</span>: <span class="hljs-number">0.63</span>ms
<span class="hljs-built_in">max</span>: <span class="hljs-number">17.41</span>ms
approx. <span class="hljs-number">95</span> percentile: <span class="hljs-number">1.84</span>ms
</code>

结论

• 更多的线程 = 更多的吞吐量
  1个线程：117.14MB/sec
  8个线程：338.71 MB/s ( 2.89x )
  16个线程：396.26 MB/s ( 3.41x )
• 更多的线程 = 更长的响应时间
  1个线程：平均0.13ms , 95% 0.16ms
  8个线程：平均0.37ms , 95% 1.77ms
  16个线程：平均0.63ms , 95% 1.84ms

Sysbench – OLTP

OLTP工作负载：

<code class=" hljs haml"> -<span class="ruby">-oltp-tables-count：模拟多个表(<span class="hljs-constant">Percona</span>扩展)
</span> -<span class="ruby">-oltp-test-modes： [complex] 
</span>    simple : 非常简单的查询模式，PK查找
    complex : 事务模式
    nontrx : 非事务查询模式
 -<span class="ruby">-oltp-read-only：on为只读测试，off为读写混合模式[off]
</span> -<span class="ruby">-oltp-skip-trx：忽略<span class="hljs-constant">BEGIN</span>/<span class="hljs-constant">COMMIT</span>语句[off]</span></code>

延迟：

<code class=" hljs haml">-<span class="ruby">-oltp-reconnect-mode <span class="hljs-symbol">:</span> [session] 
</span>    session : 从不断开
    query : 每次查询后断开
    transaction : 每次事务后断开
    random : 随即地
-<span class="ruby">-oltp-connect-delay：在连接数据库后延迟的时间（毫秒）[<span class="hljs-number">10000</span>] 
</span>-<span class="ruby">-oltp-user-delay-min：每次请求后延迟的最小时间（毫秒）[<span class="hljs-number">0</span>]
</span>-<span class="ruby">-oltp-user-delay-max：每次请求后延迟的最大时间（毫秒）[<span class="hljs-number">0</span>]</span></code>

例子1：表数量4，线程数1

<code class=" hljs livecodeserver"><span class="hljs-comment">#--test：指定Lua脚本</span>
<span class="hljs-comment">#--oltp-table-size：指定表的大小，即表的行数</span>
$ sysbench <span class="hljs-comment">--test=tests/db/oltp.lua --oltp-tables-count=4 --oltp-table-size=500000 --oltp-test-mode=simple run</span>
Number <span class="hljs-operator">of</span> threads: <span class="hljs-number">1</span>
OLTP test statistics:
queries performed:
<span class="hljs-built_in">read</span>: <span class="hljs-number">140000</span>
<span class="hljs-built_in">write</span>: <span class="hljs-number">40000</span>
other: <span class="hljs-number">20000</span>
total: <span class="hljs-number">200000</span>
transactions: <span class="hljs-number">10000</span> (<span class="hljs-number">217.87</span> per <span class="hljs-built_in">sec</span>.)
deadlocks: <span class="hljs-number">0</span> (<span class="hljs-number">0.00</span> per <span class="hljs-built_in">sec</span>.)
<span class="hljs-built_in">read</span>/<span class="hljs-built_in">write</span> requests: <span class="hljs-number">180000</span> (<span class="hljs-number">3921.61</span> per <span class="hljs-built_in">sec</span>.)
other operations: <span class="hljs-number">20000</span> (<span class="hljs-number">435.73</span> per <span class="hljs-built_in">sec</span>.)
General statistics:
total <span class="hljs-built_in">time</span>: <span class="hljs-number">45.8995</span>s
total <span class="hljs-built_in">number</span> <span class="hljs-operator">of</span> events: <span class="hljs-number">10000</span>
total <span class="hljs-built_in">time</span> taken <span class="hljs-keyword">by</span> event execution: <span class="hljs-number">45.7667</span>s
response <span class="hljs-built_in">time</span>:
<span class="hljs-built_in">min</span>: <span class="hljs-number">2.62</span>ms
<span class="hljs-built_in">avg</span>: <span class="hljs-number">4.58</span>ms
<span class="hljs-built_in">max</span>: <span class="hljs-number">1273.69</span>ms
approx. <span class="hljs-number">95</span> percentile: <span class="hljs-number">4.86</span>ms
Threads fairness:
events (<span class="hljs-built_in">avg</span>/stddev): <span class="hljs-number">10000.0000</span>/<span class="hljs-number">0.00</span>
execution <span class="hljs-built_in">time</span> (<span class="hljs-built_in">avg</span>/stddev): <span class="hljs-number">45.7667</span>/<span class="hljs-number">0.00</span>
</code>

例子2：表数量16，线程数16

<code class=" hljs livecodeserver">$ sysbench <span class="hljs-comment">--test=tests/db/oltp.lua –oltp-tables-count=16</span>
<span class="hljs-comment">--oltp-table-size=500000 --oltp-test-mode=simple –num-threads=16 run</span>
Number <span class="hljs-operator">of</span> threads: <span class="hljs-number">16</span>
OLTP test statistics:
queries performed:
<span class="hljs-built_in">read</span>: <span class="hljs-number">140000</span>
<span class="hljs-built_in">write</span>: <span class="hljs-number">40000</span>
other: <span class="hljs-number">20000</span>
total: <span class="hljs-number">200000</span>
transactions: <span class="hljs-number">10000</span> (<span class="hljs-number">777.78</span> per <span class="hljs-built_in">sec</span>.)
deadlocks: <span class="hljs-number">0</span> (<span class="hljs-number">0.00</span> per <span class="hljs-built_in">sec</span>.)
<span class="hljs-built_in">read</span>/<span class="hljs-built_in">write</span> requests: <span class="hljs-number">180000</span> (<span class="hljs-number">13999.96</span> per <span class="hljs-built_in">sec</span>.)
other operations: <span class="hljs-number">20000</span> (<span class="hljs-number">1555.55</span> per <span class="hljs-built_in">sec</span>.)
General statistics:
total <span class="hljs-built_in">time</span>: <span class="hljs-number">12.8572</span>s
total <span class="hljs-built_in">number</span> <span class="hljs-operator">of</span> events: <span class="hljs-number">10000</span>
total <span class="hljs-built_in">time</span> taken <span class="hljs-keyword">by</span> event execution: <span class="hljs-number">204.7219</span>s
response <span class="hljs-built_in">time</span>:
<span class="hljs-built_in">min</span>: <span class="hljs-number">3.07</span>ms
<span class="hljs-built_in">avg</span>: <span class="hljs-number">20.47</span>ms
<span class="hljs-built_in">max</span>: <span class="hljs-number">178.74</span>ms
approx. <span class="hljs-number">95</span> percentile: <span class="hljs-number">44.42</span>ms
Threads fairness:
events (<span class="hljs-built_in">avg</span>/stddev): <span class="hljs-number">625.0000</span>/<span class="hljs-number">10.07</span>
execution <span class="hljs-built_in">time</span> (<span class="hljs-built_in">avg</span>/stddev): <span class="hljs-number">12.7951</span>/<span class="hljs-number">0.01</span>
</code>

例子3：更复杂的测试

<code class=" hljs livecodeserver"><span class="hljs-comment">#--oltp-point-selects：在一个事务里面Point select的数量，默认为10</span>
<span class="hljs-comment">#--oltp-range-size：range查询的范围大小，默认100，应该小于oltp-table-size</span>
<span class="hljs-comment">#--oltp-index-updates：在单个事务中index update的数量，默认1</span>
<span class="hljs-comment">#--max-time：限制最大的整体执行时间（秒）</span>
$ sysbench <span class="hljs-comment">--test=sysbench/sysbench/sysbench/tests/db/oltp.lua --oltp-tables-count=4 --oltp-table-size=500000 --oltp-test-mode=complex –mysql-user=root --mysql-password=password --num-threads=16 --oltp-point-selects=1000 --oltp-range-size=1000 --oltp-index-updates=1000 --max-time=300 run</span>
Number <span class="hljs-operator">of</span> threads: <span class="hljs-number">16</span>
OLTP test statistics:
queries performed:
<span class="hljs-built_in">read</span>: <span class="hljs-number">5803120</span>
<span class="hljs-built_in">write</span>: <span class="hljs-number">2015732</span>
other: <span class="hljs-number">7585</span>
total: <span class="hljs-number">7826437</span>
transactions: <span class="hljs-number">1805</span> (<span class="hljs-number">5.98</span> per <span class="hljs-built_in">sec</span>.)
deadlocks: <span class="hljs-number">3975</span> (<span class="hljs-number">13.18</span> per <span class="hljs-built_in">sec</span>.)
<span class="hljs-built_in">read</span>/<span class="hljs-built_in">write</span> requests: <span class="hljs-number">7818852</span> (<span class="hljs-number">25916.23</span> per <span class="hljs-built_in">sec</span>.)
other operations: <span class="hljs-number">7585</span> (<span class="hljs-number">25.14</span> per <span class="hljs-built_in">sec</span>.)
General statistics:
total <span class="hljs-built_in">time</span>: <span class="hljs-number">301.6972</span>s
total <span class="hljs-built_in">number</span> <span class="hljs-operator">of</span> events: <span class="hljs-number">1805</span>
total <span class="hljs-built_in">time</span> taken <span class="hljs-keyword">by</span> event execution: <span class="hljs-number">4815.5190</span>s
response <span class="hljs-built_in">time</span>:
<span class="hljs-built_in">min</span>: <span class="hljs-number">383.54</span>ms
<span class="hljs-built_in">avg</span>: <span class="hljs-number">2667.88</span>ms
<span class="hljs-built_in">max</span>: <span class="hljs-number">18964.65</span>ms
approx. <span class="hljs-number">95</span> percentile: <span class="hljs-number">6059.93</span>ms
Threads fairness:
events (<span class="hljs-built_in">avg</span>/stddev): <span class="hljs-number">112.8125</span>/<span class="hljs-number">6.89</span>
execution <span class="hljs-built_in">time</span> (<span class="hljs-built_in">avg</span>/stddev): <span class="hljs-number">300.9699</span>/<span class="hljs-number">0.48</span>
</code>

查询的类型

• 单记录查询
  SELECT c FROM sbtest WHERE id=N
• 索引列UPDATE
  UPDATE sbtest SET k=k+1 WHERE id=N
• 非索引列UPDATE
  UPDATE sbtest SET c=N WHERE id=M
• 范围查询
  SELECT c FROM sbtest WHERE id BETWEEN N AND M
• SUM()范围查询
  SELECT SUM(k) FROM sbtest WHERE id between N and M
• ORDER BY范围查询
  SELECT c FROM sbtest WHERE id between N and M ORDER BY c
• DISTINCT范围查询
  SELECT DISTINCT c FROM sbtest WHERE id BETWEEN N and M
  ORDER BY c

数据库优化-基准测试（四）

标签：性能