さくらのVPS3台でMySQL接続のベンチマーク

MySQL


MySQL5.1を公式RPMでインストールして、my.cnfはmy-large.cnfをコピーしてskip-name-resolveオプションだけ追加しました。
SSL接続はmysqlslapで上手くテストできませんでした。(マニュアルには55%遅くなると書かれている)

仮想サーバA内でのソケット接続

$ mysqlslap --no-defaults --auto-generate-sql --auto-generate-sql-guid-primary --engine=myisam --number-int-cols=3 --number-char-cols=5 --concurrency=3 --auto-generate-sql-write-number=100 --auto-generate-sql-execute-number=1000 --auto-generate-sql-load-type=mixed -u root
Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 0.315 seconds
        Minimum number of seconds to run all queries: 0.315 seconds
        Maximum number of seconds to run all queries: 0.315 seconds
        Number of clients running queries: 3
        Average number of queries per client: 1000

仮想サーバA内でのTCP/IP接続

$ mysqlslap --no-defaults --auto-generate-sql --auto-generate-sql-guid-primary --engine=myisam --number-int-cols=3 --number-char-cols=5 --concurrency=3 --auto-generate-sql-write-number=100 --auto-generate-sql-execute-number=1000 --auto-generate-sql-load-type=mixed -u root -h 127.0.0.1 -P 3306
Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 0.352 seconds
        Minimum number of seconds to run all queries: 0.352 seconds
        Maximum number of seconds to run all queries: 0.352 seconds
        Number of clients running queries: 3
        Average number of queries per client: 1000

同一物理サーバの仮想サーバBから仮想サーバAにTCP/IP接続

$ mysqlslap --no-defaults --auto-generate-sql --auto-generate-sql-guid-primary --engine=myisam --number-int-cols=3 --number-char-cols=5 --concurrency=3 --auto-generate-sql-write-number=100 --auto-generate-sql-execute-number=1000 --auto-generate-sql-load-type=mixed -u root -h xxx.xxx.xxx.xxx -P 3306
Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 0.762 seconds
        Minimum number of seconds to run all queries: 0.762 seconds
        Maximum number of seconds to run all queries: 0.762 seconds
        Number of clients running queries: 3
        Average number of queries per client: 1000

同一物理サーバの仮想サーバBから仮想サーバAにSSH経由で接続

$ ssh -f -L 3320:localhost:3306 -N -4 hostA
$ mysqlslap --no-defaults --auto-generate-sql --auto-generate-sql-guid-primary --engine=myisam --number-int-cols=3 --number-char-cols=5 --concurrency=3 --auto-generate-sql-write-number=100 --auto-generate-sql-execute-number=1000 --auto-generate-sql-load-type=mixed -u root -h 127.0.0.1 -P 3320
Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 4.100 seconds
        Minimum number of seconds to run all queries: 4.100 seconds
        Maximum number of seconds to run all queries: 4.100 seconds
        Number of clients running queries: 3
        Average number of queries per client: 1000

別物理サーバの仮想サーバCから仮想サーバAにTCP/IP接続

$ mysqlslap --no-defaults --auto-generate-sql --auto-generate-sql-guid-primary --engine=myisam --number-int-cols=3 --number-char-cols=5 --concurrency=3 --auto-generate-sql-write-number=100 --auto-generate-sql-execute-number=1000 --auto-generate-sql-load-type=mixed -u root -h xxx.xxx.xxx.xxx -P 3306
Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 7.124 seconds
        Minimum number of seconds to run all queries: 7.124 seconds
        Maximum number of seconds to run all queries: 7.124 seconds
        Number of clients running queries: 3
        Average number of queries per client: 1000

参考サイト