TiDB を触ってみる
こんにちは。k-jun です。今週は雑多に見つけた library を試していきます。
https://github.com/pingcap/tidb
Spanner の分散データベースシステムの流派をもとにしており、OpenSource 化しようとする動きの一端ですね。 類似の BD として CockroachBD がありますが果たして。
Github を見る限りは簡単に起動できるようなので、ひとまず見てみます。
docker run --name tidb-server -d -p 4000:4000 pingcap/tidb:latest docker ps | grep tidb 6a0943cfc1fb pingcap/tidb:latest "/tidb-server" 16 minutes ago Up 16 minutes 0.0.0.0:4000->4000/tcp tidb-server
何やら動き出しましたね。接続。
mysql -h 127.0.0.1 -P 4000 -u root -D test --prompt="tidb> " Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 79 Server version: 5.7.25-TiDB-v5.0.1 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. tidb>
繋がりますねぇ...! Storage Engine と、トランザクション分離レベルも見てみる。
tidb> show engines; +--------+---------+------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------+---------+------------------------------------------------------------+--------------+------+------------+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | +--------+---------+------------------------------------------------------------+--------------+------+------------+ 1 row in set (0.01 sec) tidb> SELECT @@GLOBAL.tx_isolation, @@tx_isolation; +-----------------------+-----------------+ | @@GLOBAL.tx_isolation | @@tx_isolation | +-----------------------+-----------------+ | REPEATABLE-READ | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.00 sec)
今の処は完全に mysql。ファントムリードは発生するだろうか。
tidb1> CREATE TABLE parent ( id INT NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB; tidb1> select * from parent; +----+ | id | +----+ | 1 | | 2 | +----+ 2 rows in set (0.00 sec)
tidb1> begin; tidb2> begin; tidb1> insert parent(id) values(3); tidb2> select * from parent; +----+ | id | +----+ | 1 | | 2 | +----+ tidb1> commit; tidb2> select * from parent; +----+ | id | +----+ | 1 | | 2 | +----+ tidb2> commit; tidb2> select * from parent; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+
うーん。文句ない。ではいよいよ性能比較。データを容易。ひとまず 100万レコード程度。
sysbench oltp_read_write --table-size=1000000 --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=4000 prepare sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2) Creating table 'sbtest1'... Inserting 1000000 records into 'sbtest1' Creating a secondary index on 'sbtest1'...
実行
sysbench oltp_read_write --table-size=1000000 --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=4000 run sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options: Number of threads: 1 Initializing random number generator from current time Initializing worker threads... Threads started! SQL statistics: queries performed: read: 3276 write: 936 other: 468 total: 4680 transactions: 234 (23.30 per sec.) queries: 4680 (466.03 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 10.0410s total number of events: 234 Latency (ms): min: 36.28 avg: 42.90 max: 64.85 95th percentile: 49.21 sum: 10039.50 Threads fairness: events (avg/stddev): 234.0000/0.00 execution time (avg/stddev): 10.0395/0.00
466.03 QPS ぐらい。前に mysql v5.7 で同様の確認をした際には 800 QPS ぐらい出ていたので、少し遅い。 並列度を上げて実験してみる。
sysbench oltp_read_write --table-size=1000000 --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=4000 --threads=8 run sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options: Number of threads: 8 Initializing random number generator from current time Initializing worker threads... Threads started! FATAL: mysql_stmt_execute() returned error 9007 (Write conflict, txnStartTS=424790832799023104, conflictStartTS=424790832779624448, conflictCommitTS=424790832800858112, key={tableID=51, handle=502457} primary=[]byte(nil) [try again later]) for query 'COMMIT' FATAL: `thread_run' function failed: ...al/Cellar/sysbench/1.0.20/share/sysbench/oltp_common.lua:409: SQL error, errno = 9007, state = 'HY000': Write conflict, txnStartTS=424790832799023104, conflictStartTS=424790832779624448, conflictCommitTS=424790832800858112, key={tableID=51, handle=502457} primary=[]byte(nil) [try again later]
おろ...? コケた...。これはどういうことだろうか.... 調べたら以下がヒット。
https://github.com/pingcap/tidb/issues/5622
--oltp-auto-inc=offオプションが現在はどうも存在していないのでひとまずエラーを無視することにして対応。2件のみの発生なので問題ないと判断。
error を返すだけなので、速度的にも遅くはなっていないはず。
sysbench oltp_read_write --table-size=1000000 --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=4000 --threads=8 --mysql-ignore-errors=all run sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options: Number of threads: 8 Initializing random number generator from current time Initializing worker threads... Threads started! SQL statistics: queries performed: read: 13300 write: 3800 other: 1898 total: 18998 transactions: 948 (94.24 per sec.) queries: 18998 (1888.65 per sec.) ignored errors: 2 (0.20 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 10.0579s total number of events: 948 Latency (ms): min: 54.83 avg: 84.62 max: 196.36 95th percentile: 125.52 sum: 80216.28 Threads fairness: events (avg/stddev): 118.5000/1.41 execution time (avg/stddev): 10.0270/0.02
うーん.. 微妙。mysql は 3400 QPS ぐらい出たので。並列度をもっと上げてみる。
~/ghq/github.com/slidevjs/slidev/slidev(main ✗) sysbench oltp_read_write --table-size=1000000 --mysql-db=test --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=4000 --threads=128 --mysql-ignore-errors=all run sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options: Number of threads: 128 Initializing random number generator from current time Initializing worker threads... Threads started! SQL statistics: queries performed: read: 16926 write: 4836 other: 2325 total: 24087 transactions: 1116 (102.64 per sec.) queries: 24087 (2215.40 per sec.) ignored errors: 93 (8.55 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 10.8714s total number of events: 1116 Latency (ms): min: 367.49 avg: 1210.11 max: 3330.35 95th percentile: 2159.29 sum: 1350477.52 Threads fairness: events (avg/stddev): 8.7188/0.90 execution time (avg/stddev): 10.5506/0.21
エラーの数がいよいよ無視できない数に。が、どうも性能が頭打ってるな。 ということで少なくとも Local の PC では mysql のほうが良さそうな結果に。 分散 DB なので Kubernetes などに乗っけて検証してみないとわからんですね。
今回はこれぐらいで。ではでは。
