MYSQL 寫效能測試

 

以下是我在當前搭建的POC mysql cluster 環境裡，進行併發寫測試的資料。情況說明如下：

1.       模擬向10字元，10個數字欄位的表中插入資料。

2.       案例1~3, 都是同時插入1000條資料，但是併發的會話數依次增加。

3.       案例4~5, 同時插入10000條資料，併發會話數相同，但是４是使用搭建的POC環境，5是使用我在筆記本上搭建的虛擬環境。

4.       測試涵蓋了mysql中三種主要的儲存引擎，myisam是不支援事務的引擎，innodb是單節點資料引擎，ndbcluster是cluster資料引擎。

5.       每個測試案例測試了3次，所以資料應該是比較穩定的。

得到的結論如下，也請大家一起分析。　

1.       在現有的POC環境配置下，隨著併發數提高，innodb的每秒處理數隨之增大，這個是合理的,隨之併發再增大後,時間增大,或不明向減少,表明已經達到壓力區了.

2.       Ndbcluster，隨著併發數提高，每秒處理數有所增大，但是不大明顯，如案例1和案例2的情況；但是併發數再增大後，每秒的處理數反而降低了, 如案例3和案例4的情況。這是不正常的，估計可能跟機器的配置尤其是網路有關係。

3.       Myisam，隨著併發數提高，每秒處理數減少，這個是由於它對併發的支援比較弱，是正常的。

4.       相同的測試案例，案例4在POC的機器上執行，innodb 的處理次數是100, 而案例5在筆記本的VM上執行，處理次數達到了644. 估計是跟POC的機器配置有關係。 (或者單機上的INNODB,和NDB cluster 整合在一起的INNODB效能不一樣,準備向公司申請好的機器
再做POC,目前的機器是在是超執行緒的CPU, 一臺機器物理上一core, 執行ls 命令都慢.)

--效能測試結果

案例一: 併發2個會話,每秒鐘完成處理次數:myisam=365 innodb=36 ndbcluster=72

[mysql@shadcdmsq02 ~]$ mysqlslap --concurrency=2 --iterations=1 \

>             --number-int-cols=10 --number-char-cols=10 -a \

>             --auto-generate-sql-add-autoincrement --number-of-queries=1000 \

>             --auto-generate-sql-load-type=write --engine=myisam,innodb,ndbcluster

Benchmark

        Running for engine myisam

        Average number of seconds to run all queries: 2.773 seconds

        Minimum number of seconds to run all queries: 2.773 seconds

        Maximum number of seconds to run all queries: 2.773 seconds

        Number of clients running queries: 2

        Average number of queries per client: 500

Benchmark

        Running for engine innodb

        Average number of seconds to run all queries: 27.631 seconds

        Minimum number of seconds to run all queries: 27.631 seconds

        Maximum number of seconds to run all queries: 27.631 seconds

        Number of clients running queries: 2

        Average number of queries per client: 500

Benchmark

        Running for engine ndbcluster

        Average number of seconds to run all queries: 13.807 seconds

        Minimum number of seconds to run all queries: 13.807 seconds

        Maximum number of seconds to run all queries: 13.807 seconds

        Number of clients running queries: 2

        Average number of queries per client: 500

案例二: 併發20個回話,每秒鐘完成處理次數:myisam=305 innodb=99 ndbcluster=98

[mysql@shadcdmsq02 ~]$ mysqlslap --concurrency=20 --iterations=1 \

>              --number-int-cols=10 --number-char-cols=10 -a \

>              --auto-generate-sql-add-autoincrement --number-of-queries=1000 \

>              --auto-generate-sql-load-type=write --engine=myisam,innodb,ndbcluster

Benchmark

        Running for engine myisam

        Average number of seconds to run all queries: 3.274 seconds

        Minimum number of seconds to run all queries: 3.274 seconds

        Maximum number of seconds to run all queries: 3.274 seconds

        Number of clients running queries: 20

        Average number of queries per client: 50

Benchmark

        Running for engine innodb

        Average number of seconds to run all queries: 10.030 seconds

        Minimum number of seconds to run all queries: 10.030 seconds

        Maximum number of seconds to run all queries: 10.030 seconds

        Number of clients running queries: 20

        Average number of queries per client: 50

Benchmark

        Running for engine ndbcluster

        Average number of seconds to run all queries: 10.174 seconds

        Minimum number of seconds to run all queries: 10.174 seconds

        Maximum number of seconds to run all queries: 10.174 seconds

        Number of clients running queries: 20

        Average number of queries per client: 50

案例三. 併發100個會話,每秒鐘完成處理次數:myisam=229 innodb=75 ndbcluster=76

[mysql@shadcdmsq02 ~]$ mysqlslap --concurrency=100 --iterations=1 \

>               --number-int-cols=10 --number-char-cols=10 -a \

>               --auto-generate-sql-add-autoincrement --number-of-queries=1000 \

>               --auto-generate-sql-load-type=write --engine=myisam,innodb,ndbcluster

Benchmark

        Running for engine myisam

        Average number of seconds to run all queries: 4.348 seconds

        Minimum number of seconds to run all queries: 4.348 seconds

        Maximum number of seconds to run all queries: 4.348 seconds

        Number of clients running queries: 100

        Average number of queries per client: 10

Benchmark

        Running for engine innodb

        Average number of seconds to run all queries: 13.303 seconds

        Minimum number of seconds to run all queries: 13.303 seconds

        Maximum number of seconds to run all queries: 13.303 seconds

        Number of clients running queries: 100

        Average number of queries per client: 10

Benchmark

        Running for engine ndbcluster

        Average number of seconds to run all queries: 13.101 seconds

        Minimum number of seconds to run all queries: 13.101 seconds

        Maximum number of seconds to run all queries: 13.101 seconds

        Number of clients running queries: 100

        Average number of queries per client: 10

--測試案例四: 併發1４８個會話,插入10000條資料,每秒鐘完成處理次數:myisam=195 innodb=100 ndbcluster=49

[mysql@shadcdmsq02 ~]$ mysqlslap --concurrency=148 --iterations=1  \            

                 --number-int-cols=10 --number-char-cols=10 -a      \        

                 --auto-generate-sql-add-autoincrement --number-of-queries=10000  \            

                 --auto-generate-sql-load-type=write --engine=myisam,innodb,ndbcluster

Benchmark

        Running for engine myisam

        Average number of seconds to run all queries: 51.124 seconds

        Minimum number of seconds to run all queries: 51.124 seconds

        Maximum number of seconds to run all queries: 51.124 seconds

        Number of clients running queries: 148

        Average number of queries per client: 67

Benchmark

        Running for engine innodb

        Average number of seconds to run all queries: 99.555 seconds

        Minimum number of seconds to run all queries: 99.555 seconds

        Maximum number of seconds to run all queries: 99.555 seconds

        Number of clients running queries: 148

        Average number of queries per client: 67

Benchmark

        Running for engine ndbcluster

        Average number of seconds to run all queries: 201.856 seconds

        Minimum number of seconds to run all queries: 201.856 seconds

        Maximum number of seconds to run all queries: 201.856 seconds

        Number of clients running queries: 148

        Average number of queries per client: 67

--測試案例五: 一臺筆記本上安裝的虛擬機器,記憶體512M,測試案例同案例四,每一秒執行次數:innodb=644

[mysql@vm1 ~]$ mysqlslap --concurrency=148 --iterations=1 \

            --number-int-cols=10 --number-char-cols=10 -a \

            --auto-generate-sql-add-autoincrement --number-of-queries=10000 \

            --auto-generate-sql-load-type=write --engine=innodb

Benchmark

        Running for engine innodb

        Average number of seconds to run all queries: 15.512 seconds

        Minimum number of seconds to run all queries: 15.512 seconds

        Maximum number of seconds to run all queries: 15.512 seconds

        Number of clients running queries: 148

        Average number of queries per client: 67