ããã¯ããªã«ãããããŠæžãããã®ïŒ
- MySQLã«ãåçŽã§ãããã®ã§ç°¡åã«è² è·ããããããããŒã«ãæ¢ããŠãã
- sysbenchãšãããã®ãããããã
ãšããããã§ãsysbenchã䜿ã£ãŠMySQLã«è² è·âŠãšããããã³ãããŒã¯ãåã£ãŠã¿ãããšããããšã§ã
sysbenchãšã¯ïŒ
ãã¡ãã®ããŒã«ã§ãã
GitHub - akopytov/sysbench: Scriptable database and system performance benchmark
Luaã䜿ã£ãããã³ãããŒã¯ããŒã«ã§ããããŒã¿ããŒã¹ãžã®è² è·ããããã®ã«ããã䜿ãããããã§ãã
以äžã®ãããªãã³ãããŒã¯ãå®è¡ããããšãã§ããŸãã
- oltp_*.lua ⊠OLTPããŒã¿ããŒã¹ã®ãããªãã³ãããŒã¯
- fileio ⊠ãã¡ã€ã«ã·ã¹ãã ã«å¯Ÿãããã³ãããŒã¯
- cpu ⊠ã·ã³ãã«ãªCPUã«å¯Ÿãããã³ãããŒã¯
- memory ⊠ã¡ã¢ãªã¢ã¯ã»ã¹ã®ãã³ãããŒã¯
- threads ⊠ã¹ã¬ããã¹ã±ãžã¥ãŒã©ãŒã®ãã³ãããŒã¯
- mutex ⊠POSIX mutexã®ãã³ãããŒã¯
ä»åã¯ãoltp_*.luaã䜿çšããŠãMySQLã«OLTPç³»ã®è² è·ããããŸãã
ããŒã¿ããŒã¹ã¯ãMySQLãšPostgreSQLã䜿ããããã§ãã
ç°å¢
ä»åã®ç°å¢ã¯ãUbuntu Linux 18.04 LTSã§ãã
$ lsb_release -a No LSB modules are available. Distributor ID: Ubuntu Description: Ubuntu 18.04.1 LTS Release: 18.04 Codename: bionic
ãŸããMySQLã¯8.0.13ã䜿çšããŸãã
ã€ã³ã¹ãŒããŒã«
ã€ã³ã¹ããŒã«æ¹æ³ã¯ããã¡ãã
Installing from Binary Packages / Linux
å®è¡ã
$ curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash $ sudo apt install sysbench
ã€ã³ã¹ããŒã«ã§ãããããŸãã¯ãã«ããåç §ã
$ sysbench --help Usage: sysbench [options]... [testname] [command] Commands implemented by most tests: prepare run cleanup help ... Compiled-in tests: fileio - File I/O test cpu - CPU performance test memory - Memory functions speed test threads - Threads subsystem performance test mutex - Mutex performance test See 'sysbench <testname> help' for a list of options for each test.
ãããããšãªãã·ã§ã³ã衚瀺ãããŸããããã§è¡šç€ºããããã«ãã¯ããã¹ãã®çš®é¡ãè·šãã ã°ããŒãã«ã«äœ¿ãããã®ã
ãã°ã®ãªãã·ã§ã³ãããŒã¿ããŒã¹ã®ãã¹ãã«äœ¿ãããã®ãªã©ããã«ããŽãªå¥ã«è¡šç€ºãããŸãã
ãã¹ãã®æå®ã¯ããfileioãããcpuãããmemoryãããthreadsãããmutexãããããŠã/usr/share/sysbenchããã£ã¬ã¯ããªé
äžã«
ã€ã³ã¹ããŒã«ãããLuaã¹ã¯ãªããããæå®ããããšãã§ããŸãã
åãã¹ãã«å¯ŸããŠããhelpããæå®ããããšã§ããã¹ãããšã®ãã«ãã衚瀺ããããšãã§ããŸãã
$ sysbench cpu help sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2) cpu options: --cpu-max-prime=N upper limit for primes generator [10000]
ãoltp_read_writeãã®å Žåã
$ sysbench oltp_read_write help sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2) oltp_read_write options: --auto_inc[=on|off] Use AUTO_INCREMENT column as Primary Key (for MySQL), or its alternatives in other DBMS. When disabled, use client-generated IDs [on] --create_secondary[=on|off] Create a secondary index in addition to the PRIMARY KEY [on] --delete_inserts=N Number of DELETE/INSERT combinations per transaction [1] --distinct_ranges=N Number of SELECT DISTINCT queries per transaction [1] --index_updates=N Number of UPDATE index queries per transaction [1] --mysql_storage_engine=STRING Storage engine, if MySQL is used [innodb] --non_index_updates=N Number of UPDATE non-index queries per transaction [1] --order_ranges=N Number of SELECT ORDER BY queries per transaction [1] --pgsql_variant=STRING Use this PostgreSQL variant when running with the PostgreSQL driver. The only currently supported variant is 'redshift'. When enabled, create_secondary is automatically disabled, and delete_inserts is set to 0 --point_selects=N Number of point SELECT queries per transaction [10] --range_selects[=on|off] Enable/disable all range SELECT queries [on] --range_size=N Range size for range SELECT queries [100] --secondary[=on|off] Use a secondary index in place of the PRIMARY KEY [off] --simple_ranges=N Number of simple range SELECT queries per transaction [1] --skip_trx[=on|off] Don't start explicit transactions and execute all queries in the AUTOCOMMIT mode [off] --sum_ranges=N Number of SELECT SUM() queries per transaction [1] --table_size=N Number of rows per table [10000] --tables=N Number of tables [1]
ããã§ããoltp_read_writeãã¯ã©ãããåºãŠããã®ãïŒã§ãããUbuntu Linuxã®å Žåã/usr/share/sysbenchããã£ã¬ã¯ããªé
äžã«ãã
Luaã¹ã¯ãªãããããã.luaãæ¡åŒµåãé€ãããã®ãæå®ããããšã«ãªããŸãã
$ find /usr/share/sysbench -maxdepth 1 -type f /usr/share/sysbench/oltp_write_only.lua /usr/share/sysbench/select_random_ranges.lua /usr/share/sysbench/oltp_update_index.lua /usr/share/sysbench/oltp_read_only.lua /usr/share/sysbench/oltp_common.lua /usr/share/sysbench/oltp_read_write.lua /usr/share/sysbench/oltp_delete.lua /usr/share/sysbench/select_random_points.lua /usr/share/sysbench/oltp_update_non_index.lua /usr/share/sysbench/bulk_insert.lua /usr/share/sysbench/oltp_insert.lua /usr/share/sysbench/oltp_point_select.lua
ãªãããã¹ãã®çš®é¡ãæå®ãããšããã«ããã¡ã€ã«ãã¹ãçŽæ¥æå®ããŠãOKã§ãã
MySQLã«è² è·ããããŠã¿ã
ã§ã¯ãsysbenchã䜿ã£ãŠMySQLã«è² è·ããããŠã¿ãŸãããã
MySQLãµãŒããŒãçšæããããŒã¿ããŒã¹ããsbtestãããŠãŒã¶ãŒãšãã¹ã¯ãŒãããmyuser / passwordããšããŸãã
ä»åã¯ããã¹ãã·ããªãªã®ãã¡ãoltp_read_writeãã䜿çšããããšã«ããŸãã
åãã¹ãã®å 容ã¯ãLuaã¹ã¯ãªããã®ã³ã¡ã³ããªã©ãèŠãæ¹ãããã®ãããããŸããã
https://github.com/akopytov/sysbench/tree/1.0.15/src/lua
äŸãã°âŠ
https://github.com/akopytov/sysbench/blob/1.0.15/src/lua/oltp_read_write.lua
-- ---------------------------------------------------------------------- -- Read/Write OLTP benchmark -- ----------------------------------------------------------------------
ãŸãæåã«ããprepareãã§ããŒãã«ãäœæããŸããããŒã¿ã100äžè¡ãšããŠå®è¡ã
$ sysbench oltp_read_write \ --db-driver=mysql --table-size=1000000 \ --mysql-host=172.17.0.3 \ --mysql-db=sbtest \ --mysql-user=myuser \ --mysql-password=password \ prepare sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2) Creating table 'sbtest1'... Inserting 1000000 records into 'sbtest1' Creating a secondary index on 'sbtest1'...
ãsbtest1ããšããããŒãã«ãã€ã³ããã¯ã¹ãã§ãããããã§ãã
ç¶ããŠããrunãã§ãã¹ããå®è¡ããŸãã8ã¹ã¬ããã§ã120ç§éãè² è·ãããã€ã¥ããŸãã
$ sysbench oltp_read_write \ --threads=8 \ --time=120 \ --db-driver=mysql \ --table-size=1000000 \ --mysql-host=172.17.0.3 \ --mysql-db=sbtest \ --mysql-user=myuser \ --mysql-password=password \ run sysbench 1.0.15 (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: 266462 write: 76132 other: 38066 total: 380660 transactions: 19033 (158.54 per sec.) queries: 380660 (3170.86 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 120.0471s total number of events: 19033 Latency (ms): min: 19.74 avg: 50.44 max: 513.93 95th percentile: 110.66 sum: 960040.61 Threads fairness: events (avg/stddev): 2379.1250/3.14 execution time (avg/stddev): 120.0051/0.01
120ç§ã§ã19,033ãã©ã³ã¶ã¯ã·ã§ã³ïŒç§é158ãã©ã³ã¶ã¯ã·ã§ã³ïŒãå®è¡ã§ããããã§ããreadïŒwriteã®å
èš³ãåºãŠããŸããã
â»MySQLã«ã¯ããã¥ãŒãã³ã°ãªã©ã¯ãªã«ãããŠããŸãã
ã©ããªSQLã䜿ãããŠãããã¯ããã®ããããèŠãã°ãããããªæãã§ãã
https://github.com/akopytov/sysbench/blob/1.0.15/src/lua/oltp_common.lua
ãã¹ãã§äœ¿ã£ãããŒãã«ãåé€ããå Žåã¯ããcleanupããæå®ããŸãã
$ sysbench oltp_read_write \ --db-driver=mysql \ --mysql-host=172.17.0.3 \ --mysql-db=sbtest \ --mysql-user=myuser \ --mysql-password=password \ cleanup sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2) Dropping table 'sbtest1'...
ããã§ãåŸçä»ãå®äºã§ãã
ç°¡åã«ã§ããã䜿ãæ¹ã¯ãªããšãªãããã£ãæ°ãããŸãã