MySQL Forums
Forum List  »  Performance

mysql oltp test, the result no stable ?
Posted by: jared ZHU
Date: January 01, 2018 08:53PM

I use sysbench oltp benchmark to test mysql perfomance,but the test result can not be stable .
the test server consist with 128G memmory, six SAS SSD with raid0 and the skylake 6148 cpu.
the sysbench and the mysql running on the same server without network.

the test commond is :
sysbench tests/include/oltp_legacy/oltp.lua --db-driver=mysql --oltp-tables-count=100 --oltp-read-only=on --oltp-point-selects=10 --oltp-range-size=100 --oltp-simple-ranges=0 --oltp-sum-ranges=0 --oltp-order-ranges=0 --oltp-distinct-ranges=0 --mysql-password=123456 --mysql-db=sysbench --oltp-table-size=100000 --num-threads=512 --max-requests=0 --max-time=100 --oltp-auto-inc=off --mysql-engine-trx=yes --oltp-test-mod=complex --mysql-host=localhost --mysql-user=root --oltp-user-delay-min=10 --oltp-user-delay-max=100 --report-interval=1 --mysql-password=123456 run


the result is :
[ 22s ] thds: 512 tps: 22178.00 qps: 267245.86 (r/w/o: 222881.87/0.00/44363.99) lat (ms,95%): 123.28 err/s: 0.00 reconn/s: 0.00
[ 23s ] thds: 512 tps: 18424.15 qps: 221683.83 (r/w/o: 184829.52/0.00/36854.31) lat (ms,95%): 137.35 err/s: 0.00 reconn/s: 0.00
[ 24s ] thds: 512 tps: 24334.80 qps: 291174.08 (r/w/o: 242503.53/0.00/48670.56) lat (ms,95%): 70.55 err/s: 0.00 reconn/s: 0.00
[ 25s ] thds: 512 tps: 22329.21 qps: 267770.88 (r/w/o: 223139.77/0.00/44631.11) lat (ms,95%): 164.45 err/s: 0.00 reconn/s: 0.00
[ 26s ] thds: 512 tps: 23295.44 qps: 280240.26 (r/w/o: 233632.39/0.00/46607.88) lat (ms,95%): 97.55 err/s: 0.00 reconn/s: 0.00
[ 27s ] thds: 512 tps: 21032.76 qps: 251820.16 (r/w/o: 209752.63/0.00/42067.53) lat (ms,95%): 116.80 err/s: 0.00 reconn/s: 0.00
[ 28s ] thds: 512 tps: 20952.29 qps: 251623.46 (r/w/o: 209720.88/0.00/41902.58) lat (ms,95%): 97.55 err/s: 0.00 reconn/s: 0.00
[ 29s ] thds: 512 tps: 21316.32 qps: 254692.66 (r/w/o: 212060.03/0.00/42632.63) lat (ms,95%): 144.97 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 512 tps: 24456.11 qps: 294330.24 (r/w/o: 245425.02/0.00/48905.22) lat (ms,95%): 92.42 err/s: 0.00 reconn/s: 0.00
[ 31s ] thds: 512 tps: 23858.89 qps: 286780.71 (r/w/o: 239049.92/0.00/47730.79) lat (ms,95%): 58.92 err/s: 0.00 reconn/s: 0.00
[ 32s ] thds: 512 tps: 24319.63 qps: 291041.49 (r/w/o: 242407.24/0.00/48634.25) lat (ms,95%): 87.56 err/s: 0.00 reconn/s: 0.00
[ 33s ] thds: 512 tps: 24073.05 qps: 287716.63 (r/w/o: 239561.52/0.00/48155.11) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00
[ 34s ] thds: 512 tps: 23659.12 qps: 284315.50 (r/w/o: 237020.25/0.00/47295.25) lat (ms,95%): 116.80 err/s: 0.00 reconn/s: 0.00
[ 35s ] thds: 512 tps: 25425.75 qps: 305164.95 (r/w/o: 254303.46/0.00/50861.49) lat (ms,95%): 125.52 err/s: 0.00 reconn/s: 0.00
[ 36s ] thds: 512 tps: 24802.23 qps: 297476.73 (r/w/o: 247876.28/0.00/49600.46) lat (ms,95%): 50.11 err/s: 0.00 reconn/s: 0.00
[ 37s ] thds: 512 tps: 28651.61 qps: 343649.32 (r/w/o: 286335.09/0.00/57314.22) lat (ms,95%): 99.33 err/s: 0.00 reconn/s: 0.00
[ 38s ] thds: 512 tps: 25139.76 qps: 302748.04 (r/w/o: 252483.51/0.00/50264.53) lat (ms,95%): 75.82 err/s: 0.00 reconn/s: 0.00
[ 39s ] thds: 512 tps: 23119.90 qps: 277268.85 (r/w/o: 231018.04/0.00/46250.81) lat (ms,95%): 106.75 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 512 tps: 26384.04 qps: 315902.49 (r/w/o: 263145.41/0.00/52757.08) lat (ms,95%): 92.42 err/s: 0.00 reconn/s: 0.00
[ 41s ] thds: 512 tps: 23792.82 qps: 287575.79 (r/w/o: 239972.16/0.00/47603.63) lat (ms,95%): 80.03 err/s: 0.00 reconn/s: 0.00
[ 42s ] thds: 512 tps: 22873.76 qps: 273278.06 (r/w/o: 227545.54/0.00/45732.52) lat (ms,95%): 75.82 err/s: 0.00 reconn/s: 0.00
[ 43s ] thds: 512 tps: 19624.99 qps: 235709.87 (r/w/o: 196448.89/0.00/39260.98) lat (ms,95%): 144.97 err/s: 0.00 reconn/s: 0.00
[ 44s ] thds: 512 tps: 25714.69 qps: 307322.92 (r/w/o: 255899.53/0.00/51423.39) lat (ms,95%): 95.81 err/s: 0.00 reconn/s: 0.00
[ 45s ] thds: 512 tps: 22385.47 qps: 269611.64 (r/w/o: 224832.69/0.00/44778.96) lat (ms,95%): 92.42 err/s: 0.00 reconn/s: 0.00
[ 46s ] thds: 512 tps: 28399.32 qps: 339731.96 (r/w/o: 282945.31/0.00/56786.65) lat (ms,95%): 94.10 err/s: 0.00 reconn/s: 0.00
[ 47s ] thds: 512 tps: 26137.37 qps: 313769.41 (r/w/o: 261484.67/0.00/52284.73) lat (ms,95%): 41.85 err/s: 0.00 reconn/s: 0.00
[ 48s ] thds: 512 tps: 23678.89 qps: 285051.79 (r/w/o: 237694.01/0.00/47357.78) lat (ms,95%): 95.81 err/s: 0.00 reconn/s: 0.00
[ 49s ] thds: 512 tps: 22542.22 qps: 269487.81 (r/w/o: 224404.36/0.00/45083.45) lat (ms,95%): 97.55 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 512 tps: 27091.02 qps: 325375.24 (r/w/o: 271205.20/0.00/54170.04) lat (ms,95%): 66.84 err/s: 0.00 reconn/s: 0.00
[ 51s ] thds: 512 tps: 26848.01 qps: 323055.21 (r/w/o: 269351.19/0.00/53704.03) lat (ms,95%): 86.00 err/s: 0.00 reconn/s: 0.00
[ 52s ] thds: 512 tps: 20179.15 qps: 242252.73 (r/w/o: 201896.44/0.00/40356.29) lat (ms,95%): 112.67 err/s: 0.00 reconn/s: 0.00

the my.cnf is:
[mysqld]
socket = /u01/my3306/run/mysql.sock
datadir = /u01/my3306/data
tmpdir = /u01/my3306/tmp
port = 3306
back_log = 3000
character_set_server = utf8
max_connect_errors = 100
max_connections = 16050
max_user_connections = 16050
max_heap_table_size = 64M
max_allowed_packet = 1024M
max_binlog_size = 500M
thread_stack = 256K
interactive_timeout = 7200
wait_timeout = 86400
sort_buffer_size = 848KB
read_buffer_size = 848KB
read_rnd_buffer_size = 432KB
join_buffer_size = 432KB
net_buffer_length = 16K
thread_cache_size = 100
ft_min_word_len = 4
transaction_isolation = READ-COMMITTED
tmp_table_size = 262144
table_open_cache = 1024
skip_name_resolve
core-file
lower_case_table_names = 1
log_bin_trust_function_creators = 1
log-bin = /u01/my3306/mysql-bin.log
log-bin-index = /u01/my3306/log/master-log-bin.index
log-error = /u01/my3306/log/master-error.log
relay-log = /u01/my3306/log/slave-relay.log
relay-log-info-file = /u01/my3306/log/slave-relay-log.info
relay-log-index = /u01/my3306/log/slave-relay-log.index
master-info-file = /u01/my3306/log/master.info
log-slave-updates = 1
binlog_cache_size = 2048KB
sync_binlog = 1000
log_warnings
slow_query_log_file = /u01/my3306/log/slow_query.log
slow_query_log = 1
log_output = TABLE
long_query_time = 1
binlog_format = ROW
server_id = 710621770
auto_increment_increment = 1
auto_increment_offset = 1
slave_net_timeout = 60
key_buffer_size = 16M
bulk_insert_buffer_size = 4M
myisam_sort_buffer_size = 262144
myisam_max_sort_file_size = 2048K
myisam_repair_threads = 1
myisam_recover_options = FORCE
innodb_data_home_dir = /u01/my3306/data
innodb_log_group_home_dir = /u01/my3306
innodb_additional_mem_pool_size = 2097152
innodb_buffer_pool_size = 52429M
innodb_data_file_path = ibdata1:200M:autoextend
innodb_file_per_table
innodb_file_io_threads = 4
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 8M
innodb_log_file_size = 1500M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 75
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 5000
innodb_doublewrite = 1
innodb_rollback_on_timeout = OFF
innodb_autoinc_lock_mode = 1
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_io_capacity = 2000
innodb_purge_threads = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE
query_cache_type = 0
concurrent_insert = 1
query_cache_limit = 1048576
query_cache_min_res_unit = 1K
log-slow-admin-statements
innodb_stats_on_metadata = OFF
innodb_file_format = Barracuda
innodb_read_ahead = 0
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30
default_storage_engine = InnoDB
innodb_stats_sample_pages = 8
open_files_limit = 65535
gtid_mode = ON
enforce-gtid-consistency = 1
innodb_ft_max_token_size = 84
log_bin_use_v1_row_events = 1
delay_key_write = ON
key_cache_division_limit = 100
innodb_old_blocks_pct = 37
ft_query_expansion_limit = 20
init_connect = ''
innodb_print_all_deadlocks = OFF
delayed_insert_timeout = 300
connect_timeout = 10
binlog_stmt_cache_size = 32768
net_retry_count = 10
binlog_checksum = CRC32
low_priority_updates = 0
autocommit = 1
key_cache_age_threshold = 300
innodb_concurrency_tickets = 5000
table_definition_cache = 512
innodb_use_native_aio = 0
net_write_timeout = 60
innodb_table_locks = ON
query_alloc_block_size = 8192
max_prepared_stmt_count = 16382
innodb_thread_sleep_delay = 10000
net_read_timeout = 30
max_write_lock_count = 102400
innodb_old_blocks_time = 1000
innodb_stats_method = nulls_equal
max_length_for_sort_data = 1024
query_prealloc_size = 8192
innodb_large_prefix = OFF
delayed_insert_limit = 100
group_concat_max_len = 1024
innodb_disable_sort_file_cache = ON
innodb_ft_min_token_size = 3
key_cache_block_size = 1024
slow_launch_time = 2
innodb_online_alter_log_max_size = 134217728
innodb_open_files = 300
eq_range_index_dive_limit = 10
div_precision_increment = 4
binlog_row_image = full
innodb_strict_mode = OFF
read_only = 1
delayed_queue_size = 1000
default_week_format = 0
log_queries_not_using_indexes = OFF
innodb_read_ahead_threshold = 56
default_time_zone = SYSTEM
sql_mode = ''
slave_exec_mode = strict
query_cache_size = 0
innodb_adaptive_hash_index = ON
performance_schema = OFF
innodb_purge_batch_size = 300

[mysqldump]
quick
max_allowed_packet = 64M

[mysql]
no-auto-rehash
prompt = "\\u@\\h : \\d \\R:\\m:\\s> "

Options: ReplyQuote


Subject
Views
Written By
Posted
mysql oltp test, the result no stable ?
968
January 01, 2018 08:53PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.