Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Mysql sysbench 1.0.X

2025-04-02 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

1. Install sysbench

Yum install sysbench

2.download source code

Sysbench-1.0.14\ src\ lua, upload all files to the Linux / opt directory

3. Prepare mysql

CREATE DATABASE tdb01

CREATE USER 'root'@'192.168.0.130' IDENTIFIED BY' root123'

GRANT ALL PRIVILEGES ON tdb01.* TO''root'@'192.168.0.130'

FLUSH PRIVILEGES

4. Execution steps

/ prepare /

Sysbench / opt/lua/oltp_read_write.lua-db-driver=mysql-mysql-host=192.168.0.130-mysql-db=tdb01-mysql-user=root-mysql-password=root123-table_size=5000000-tables=10-threads=300-time=60-report-interval=10 prepare

/ run /

Sysbench / opt/lua/oltp_read_write.lua-db-driver=mysql-mysql-host=192.168.0.130-mysql-db=tdb01-mysql-user=root-mysql-password=root123-table_size=5000000-tables=10-threads=300-time=60-report-interval=10 run

/ clean /

Sysbench / opt/lua/oltp_read_write.lua-db-driver=mysql-mysql-host=192.168.0.130-mysql-db=tdb01-mysql-user=root-mysql-password=root123-table_size=5000000-tables=10-threads=300-time=60-report-interval=10 cleanup

5. Execution process

[root@mdb01 lua] # sysbench / opt/lua/oltp_read_write.lua-- db-driver=mysql-- mysql-host=192.168.0.130-- mysql-db=tdb01-- mysql-user=root-- mysql-password=root123-table_size=5000000-- tables=10-- threads=300-- time=60-- report-interval=10 prepare

Sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)

Initializing worker threads...

Creating table 'sbtest10'...

Creating table 'sbtest1'...

Creating table 'sbtest7'...

Creating table 'sbtest9'...

Creating table 'sbtest6'...

Creating table 'sbtest8'...

Creating table 'sbtest3'...

Creating table 'sbtest2'...

Creating table 'sbtest5'...

Creating table 'sbtest4'...

Inserting 5000000 records into 'sbtest1'

Inserting 5000000 records into 'sbtest2'

Inserting 5000000 records into 'sbtest7'

Inserting 5000000 records into 'sbtest5'

Inserting 5000000 records into 'sbtest10'

Inserting 5000000 records into 'sbtest4'

Inserting 5000000 records into 'sbtest3'

Inserting 5000000 records into 'sbtest9'

Inserting 5000000 records into 'sbtest6'

Inserting 5000000 records into 'sbtest8'

Creating a secondary index on 'sbtest4'...

Creating a secondary index on 'sbtest6'...

Creating a secondary index on 'sbtest5'...

Creating a secondary index on 'sbtest1'...

Creating a secondary index on 'sbtest9'...

Creating a secondary index on 'sbtest8'...

Creating a secondary index on 'sbtest2'...

Creating a secondary index on 'sbtest7'...

Creating a secondary index on 'sbtest10'...

Creating a secondary index on 'sbtest3'...

[root@mdb01 lua] #

[root@mdb01 opt] # sysbench / opt/lua/oltp_read_write.lua-- db-driver=mysql-- mysql-host=192.168.0.130-- mysql-db=tdb01-- mysql-user=root-- mysql-password=root123-table_size=5000000-- tables=10-- threads=300-- time=60-- report-interval=10 run

Sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:

Number of threads: 300

Report intermediate results every 10 second (s)

Initializing random number generator from current time

Initializing worker threads...

Threads started!

[10s] thds: 300 tps: 28.98 qps: 998.95 (r/w/o: 753.86 tps 157.17) lat (ms,95%): 9799.46 err/s: 0.00 reconn/s: 0.00

[20s] thds: 300 tps: 44.61 qps: 921.51 (r/w/o: 644.08 tps 188.22) lat (ms,95%): 12163.09 err/s: 0.00 reconn/s: 0.00

[30s] thds: 300 tps: 43.02 qps: 892.44 (r/w/o: 643.05 tps 163.36) lat (ms,95%): 12163.09 err/s: 0.00 reconn/s: 0.00

[40s] thds: 300 tps: 51.40 qps: 997.46 (r/w/o: 686.07 tps 208.59 lat 102.80) lat (ms,95%): 16819.24 err/s: 0.00 reconn/s: 0.00

[50s] thds: 300 tps: 47.00 qps: 978.98 (r/w/o: 680.65) lat (ms,95%): 14302.94 err/s: 0.00 reconn/s: 0.00

[60s] thds: 300 tps: 58.69 qps: 1103.34 (r/w/o: 783.19 tps 202.77 reconn/s 117.38) lat (ms,95%): 10531.32 err/s: 0.00 reconn/s: 0.00

SQL statistics:

Queries performed:

Read: 42518

Write: 12148

Other: 6074

Total: 60740

Transactions: 3037 (48.99per sec)

Queries: 60740 (979.77 per sec.)

Ignored errors: 0 (0.00 per sec.)

Reconnects: 0 (0.00 per sec.)

General statistics:

Total time: 61.9920s

Total number of events: 3037

Latency (ms):

Min: 114.27

Avg: 6085.46

Max: 26580.90

95th percentile: 12609.11

Sum: 18481536.44

Threads fairness:

Events (avg/stddev): 10.1233Universe 1.86

Execution time (avg/stddev): 61.6051 + 0.23

[root@mdb01 opt] #

Sysbench / opt/lua/oltp_read_write.lua-db-driver=mysql-mysql-host=192.168.0.130-mysql-db=tdb01-mysql-user=root-mysql-password=root123-table_size=5000000-tables=10-threads=300-time=60-report-interval=10 cleanup

Sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)

Dropping table 'sbtest1'...

Dropping table 'sbtest2'...

Dropping table 'sbtest3'...

Dropping table 'sbtest4'...

Dropping table 'sbtest5'...

Dropping table 'sbtest6'...

Dropping table 'sbtest7'...

Dropping table 'sbtest8'...

Dropping table 'sbtest9'...

Dropping table 'sbtest10'...

[root@mdb01 opt] #

6. Matters needing attention

1). The max_prepared_stmt_count parameter should be set higher or it will prompt you to exceed the threshold.

2)。 Because database data needs to be loaded into memory to become hot data, run should be executed multiple times, the first TPS,QPS is generally low, and the value after several executions should be taken as a reference.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report