← Back to team overview

maria-discuss team mailing list archive

MariaDB / Sysbench TPS question

 

Hello all,


I am running MariaDB 10.1.5 with Sysbench 0.5 (lua) against an IBM Blade
(Haswell) using Hitachi 15K SAS Hdd's.

Operating system is RHEL 7. Hyperthreading is turned off. System memory is
48GB.

Up to now, I have ran extensive Sysbench tests against MariaDB using RAM
as the datastore with no appreciable problems.

Now I have switched the datastore to normal disk (see above) and am seeing
the Sysbench R/W TPS rate take a hit after approximately 3 minutes (180
seconds) into the run (runtime is 600 seconds) and every three minutes
thereafter. I am seeing nothing in the MariaDB logs (error and Slow
queries), nor in Linux itself, nor is Sysbench telling me anything (I have
debug rurned on and verbosity set to 5).



Here is an excerpt from one of the R/W Sysbench runs



[ 168s] threads: 128, tps: 26262.00, reads: 367679.02, writes: 105188.51,
response time: 50.12ms (99%), errors: 0.00, reconnects:  0.00
[ 170s] threads: 128, tps: 25913.49, reads: 362826.86, writes: 103670.96,
response time: 49.08ms (99%), errors: 0.00, reconnects:  0.00
[ 172s] threads: 128, tps: 26271.51, reads: 367736.58, writes: 105009.52,
response time: 50.34ms (99%), errors: 0.00, reconnects:  0.00
[ 174s] threads: 128, tps: 26059.52, reads: 364822.73, writes: 104187.07,
response time: 44.69ms (99%), errors: 0.00, reconnects:  0.00
[ 176s] threads: 128, tps: 26394.00, reads: 369573.99, writes: 105652.50,
response time: 52.28ms (99%), errors: 0.00, reconnects:  0.00
[ 178s] threads: 128, tps: 26248.49, reads: 367422.90, writes: 104927.97,
response time: 49.85ms (99%), errors: 0.00, reconnects:  0.00
[ 180s] threads: 128, tps: 26549.00, reads: 371726.52, writes: 106166.01,
response time: 60.01ms (99%), errors: 0.00, reconnects:  0.00
[ 182s] threads: 128, tps: 21493.92, reads: 301020.88, writes: 86033.18,
response time: 47.29ms (99%), errors: 0.00, reconnects:  0.00
[ 184s] threads: 128, tps: 174.00, reads: 2435.96, writes: 706.99,
response time: 2245.86ms (99%), errors: 0.00, reconnects:  0.00
[ 186s] threads: 128, tps: 185.50, reads: 2597.00, writes: 760.50,
response time: 2334.28ms (99%), errors: 0.00, reconnects:  0.00
[ 188s] threads: 128, tps: 269.00, reads: 3766.00, writes: 1066.50,
response time: 2314.11ms (99%), errors: 0.00, reconnects:  0.00
[ 190s] threads: 128, tps: 153.50, reads: 2149.00, writes: 619.50,
response time: 2121.06ms (99%), errors: 0.00, reconnects:  0.00
[ 192s] threads: 128, tps: 552.00, reads: 7728.00, writes: 2199.50,
response time: 1440.33ms (99%), errors: 0.00, reconnects:  0.00
[ 194s] threads: 128, tps: 615.00, reads: 8610.00, writes: 2450.00,
response time: 1414.69ms (99%), errors: 0.00, reconnects:  0.00
[ 196s] threads: 128, tps: 908.00, reads: 12712.00, writes: 3650.00,
response time: 950.93ms (99%), errors: 0.00, reconnects:  0.00
[ 198s] threads: 128, tps: 1579.00, reads: 22052.47, writes: 6283.99,
response time: 932.61ms (99%), errors: 0.00, reconnects:  0.00
[ 200s] threads: 128, tps: 1562.00, reads: 21921.53, writes: 6257.01,
response time: 748.43ms (99%), errors: 0.00, reconnects:  0.00
[ 202s] threads: 128, tps: 4166.00, reads: 58323.95, writes: 16652.49,
response time: 445.78ms (99%), errors: 0.00, reconnects:  0.00
[ 204s] threads: 128, tps: 11574.99, reads: 161931.90, writes: 46264.97,
response time: 263.77ms (99%), errors: 0.00, reconnects:  0.00
[ 206s] threads: 128, tps: 17420.50, reads: 244004.98, writes: 69714.50,
response time: 103.69ms (99%), errors: 0.00, reconnects:  0.00
[ 208s] threads: 128, tps: 19833.49, reads: 277626.90, writes: 79424.97,
response time: 93.88ms (99%), errors: 0.00, reconnects:  0.00
[ 210s] threads: 128, tps: 24994.00, reads: 349910.48, writes: 99975.49,
response time: 48.12ms (99%), errors: 0.00, reconnects:  0.00
[ 212s] threads: 128, tps: 25129.50, reads: 351825.03, writes: 100549.51,
response time: 52.12ms (99%), errors: 0.00, reconnects:  0.00
[ 214s] threads: 128, tps: 24982.99, reads: 349739.36, writes: 99821.46,
response time: 47.66ms (99%), errors: 0.00, reconnects:  0.00

My.cnf -

[mysqld_safe]
user=root
password=''
[mysqld]
open_files_limit = 102400
max_allowed_packet = 768M
connect_timeout = 120   # was 60
performance-schema = false
net_read_timeout = 720
log-warnings=2
#####files and sockets
pid-file=/tmp/mysqld.pid.sysbench
log_warnings=2
log-error=/var/log/mariadb/mariadb-error-john.log
long_query_time = 1
slow_query_log = 1
slow_query_log_file = /var/log/mariadb/mariadb-slowquery.log
log_queries_not_using_indexes = 1
# general-log
# general-log-file=/var/log/mariadb/mariadb-general.log
# general_log_file=/var/log/mariadb/mariadb-general.log
##### MariaDB temporary tables
tmpdir = /mnt/ramdisk

#####non innodb options (fixed)
max_connections = 8000 # was 4000
back_log = 150
table_open_cache = 4000 # was 8000
key_buffer_size = 16M
query_cache_type = 1 # was 0
join_buffer_size = 32K
sort_buffer_size = 32K

#####use InnoDB plugin
#ignore-builtin-innodb
#plugin-load=innodb=ha_innodb.so

#####fixed innodb options
innodb_file_per_table = true
innodb_open_files = 8192 # was 4096
innodb_data_file_path = ibdata1:250M:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT_NO_FSYNC
innodb_log_buffer_size = 256M
innodb_log_file_size = 4G
innodb_log_files_in_group = 2
innodb_buffer_pool_size = 40G
innodb_buffer_pool_instances = 32
innodb_adaptive_hash_index_partitions = 32
innodb_thread_concurrency = 0

#####Power8 specific
#innodb_spin_wait_delay = 0

#####tuning for SAN storage
innodb_adaptive_flushing = 1
innodb_flush_neighbors = 1
innodb_io_capacity = 4000
innodb_io_capacity_max = 6000
innodb_lru_scan_depth = 4096
innodb_purge_threads = 2
innodb_read_io_threads = 8    # was 8
innodb_write_io_threads = 16

####tuning for RAM disk
#innodb_adaptive_flushing = 1
#innodb_flush_neighbors = 0
#innodb_io_capacity = 20000
#innodb_io_capacity_max = 40000
#innodb_lru_scan_depth = 4096
#innodb_purge_threads = 2
#innodb_read_io_threads = 2
#innodb_write_io_threads = 2

Would anyone be able to enlighten me as to why I am getting this TPS
performance hit after approximately three minutes?


Any pointers much appreciated.


Regards,


Aubrey




Follow ups