The result of MySQL's high concurrency and very large data performance benchmark.
Yesterday, we used DELL 2650, 16G Memory ，2 CPU *4 Cores,SAN storage to test MySQL performance. and we passed the test of around 2000 TPS in database simulation load test.
At that time the CPU load is very high and the customer accepted our database performance in this level hardware(The configuration file is as in following.).
So today in order to improve the performance, we changed hardware to DELL 6850, 4CPU * 4cores (3.0 G),32G memory, same SAN storage. First time, we used the same mysql.cnf
as successful 30W in DELL 2650, and test failed quickly (low CPU usage) because of long database response time. In the later, we also tried to
modified innodb_thread_concurrency = 16 etc, but actually the failure point of the test is not stable and some times even 4W online users, some times 10W online users with the same
configuration;
So our question is do we have any benchmark in 4CPU *4 Cores DELL hardware? We could understand if the configuration file is not the best one for this DELL 6850, but we
couldn't explain why the performance is so poor(lower 1/3 performance of DELL 2650) and the database is not stable(some times response time is low).
It will be great appreciated if you could give some comments.
[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set=utf8
[mysqld]
connect_timeout = 20
port = 3306
socket = /tmp/mysql.sock
init_connect='SET AUTOCOMMIT=0'
default-character-set=utf8
back_log = 200
skip-name-resolve
max_connections = 1200
max_connect_errors = 10
table_cache = 8192
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 2G
sort_buffer_size = 1M
join_buffer_size = 2M
thread_cache_size = 8
thread_concurrency = 32
query_cache_size = 0
default_table_type = MYISAM
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 2G
log-bin=/mysql/logs/mysql-bin
binlog_format=mixed
log_slow_queries
log_queries_not_using_indexes = 0
long_query_time = 3
log_long_format
tmpdir = /dev/shm
server-id = 1
key_buffer_size = 64M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 32M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
innodb_checksums=0
innodb_doublewrite=0
innodb_flush_method=O_DIRECT
table_definition_cache = 1024
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 10G
innodb_data_file_path = ibdata1:2000M;ibdata2:122852212736;ibdata3:5G:autoextend
innodb_data_home_dir = /mnt/xfs/mysql/data
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 0
innodb_support_xa = 0
innodb_log_buffer_size = 48M
innodb_log_file_size = 1G
innodb_log_files_in_group = 3
innodb_log_group_home_dir = /mysql/logs
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 28192
I'm a mysql DBA in china.
To know more details about me can visit:
http://yueliangdao0608.cublog.cn