Good Morning Folks (morning in my timezone),
I am looking for some advice on my current setup, we recently upgraded our hardware from a low budget R420 server to an R910 with the following specs:
4 x CPU Intel(R) Xeon(R) CPU E7- 4870 @ 2.40GHz - 10 Core
64 x 16GB DDR3 Memory
1 x ioDrive2 1.2 TB
Now, the R420 is outperforming the R910 by approx 30%. I have been asked to review this, however I have to admit I am new to MySQL on CentOS. Every bit of research that I have done on this, has brought me back to this forum, hence my post in the hope that someone is able to assist.
My apologies in advance if I am doing something incorrect or if I am asking questions I should not be asking.
If someone could review the cnf file to see whether anything is/ could obviously be causing the performance issues.
I hope someone can see the obvious things that I am unable to.
Thank you in advance
I am looking for some advice on my current setup, we recently upgraded our hardware from a low budget R420 server to an R910 with the following specs:
4 x CPU Intel(R) Xeon(R) CPU E7- 4870 @ 2.40GHz - 10 Core
64 x 16GB DDR3 Memory
1 x ioDrive2 1.2 TB
Now, the R420 is outperforming the R910 by approx 30%. I have been asked to review this, however I have to admit I am new to MySQL on CentOS. Every bit of research that I have done on this, has brought me back to this forum, hence my post in the hope that someone is able to assist.
My apologies in advance if I am doing something incorrect or if I am asking questions I should not be asking.
If someone could review the cnf file to see whether anything is/ could obviously be causing the performance issues.
Code:
datadir = /opt/mysql
tmpdir = /opt/tmp
#general_log = /var/log/mysqld.log
user = mysql
port = 3306
socket = /opt/mysql/mysql.sock
# default-storage-engine=MyISAM
sql_mode = NO_AUTO_CREATE_USER
sql-mode = NO_AUTO_CREATE_USER
init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'
#init_file=/opt/mysql/buffer_pool_heatup.sql
secure-auth = 0
#default-character-set=utf8
collation-server=utf8_unicode_ci
character-set-server=utf8
#default-collation=utf8_general_ci
#explicit_defaults_for_timestamp = 0
#skip-locking
skip-external-locking
# skip-bdb
# skip-innodb
myisam-recover
#memlock
skip-name-resolve
symbolic-links=0
skip-host-cache
# performance_schema=0
large-pages=1
key_buffer = 2048M # 1024M
max_allowed_packet = 32M
table_cache = 2048
table_definition_cache = 2048
myisam_sort_buffer_size = 16M #only for create tables
join_buffer_size = 3M #256K #4M
read_buffer_size = 8M #16M
read_rnd_buffer_size = 32M
sort_buffer_size = 32M #256K #1G
open_files_limit = 4096 #16384
max_heap_table_size = 512M #maxsize before dumping to disk
tmp_table_size = 512M
thread_stack = 256K
thread_cache_size = 16384 #128
query_cache_size = 0 #32M #query_cache_size = 64M
#query_cache_limit = 1M #query_cache_limit = 64M
query_cache_type = 0
#query_prealloc_size = 64M
#thread_concurrency = 24
;extension=mysql.so
log-output = TABLE
slow_query_log
log-queries-not-using-indexes
log-slow-admin-statements
#log-long-format
#set-variable = long_query_time=5
#set-variable = max_connections=200
#set-variable = max_connect_errors=64
max_connections = 255
#old_passwords = 1
expire_logs_days = 7
sync_binlog = 0 #1 is safer, but slower
# max_binlog_size = 104857600
# tuning script adaptations
concurrent_insert=2
low_priority_updates=1
max_write_lock_count=1
# skip-networking
# Replication Master Server (default)
log-bin = /binlog/mysqlbin/dbase.log
server-id = 2
binlog-format = MIXED
# Point the following paths to different dedicated disks
#log-update = /path-to-dedicated-directory/hostname
# Uncomment the following if you are using BDB tables
#bdb_cache_size = 384M
#bdb_max_lock = 100000
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /opt/mysql/
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /opt/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_checksums=0
innodb_flush_method=O_DIRECT
innodb_buffer_pool_size = 128G #40960M # 384G
innodb_buffer_pool_restore_at_startup = 600 # periodically dump the buffer pool
innodb_blocking_buffer_pool_restore = 1
#innodb_buffer_pool_instances = 64
innodb_additional_mem_pool_size = 8M
innodb_log_file_size = 1024M
innodb_log_buffer_size = 8M
#innodb_log_block_size=4096
innodb_flush_log_at_trx_commit = 2 #1 for ACID 2 for speed
innodb_lock_wait_timeout = 50
innodb_file_per_table = true
innodb_file_format=barracuda
#innodb_use_sys_malloc = 0
innodb_thread_concurrency=0
innodb_io_capacity=30000
innodb_doublewrite=0
#innodb_support_xa=0
#innodb_use_native_aio=1
#hash index makes mysql an in-memory-database !! needs a lot of ram
innodb_spin_wait_delay = 96 # default is 6 max 96 and 0 is disabled
innodb_adaptive_hash_index=1 #try 0 if no speedup
#innodb_lru_scan_depth=8192
#innodb_adaptive_checkpoint=keep_average
#innodb_read_ahead=0
innodb_read_io_threads=16
innodb_write_io_threads=16
innodb_purge_threads=1
innodb_flush_neighbor_pages=none
innodb_max_dirty_pages_pct=60
innodb_adaptive_flushing=0
#innodb_use_sys_malloc=1
innodb_old_blocks_pct=50
innodb_adaptive_flushing_method = keep_average
#NUMA
innodb_buffer_pool_populate=1
[mysqldump]
quick
max_allowed_packet = 16M
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#NUMA
flush_caches=1
numa_interleave=1
open_files_limit=25000
ulimit -l unlimitedThank you in advance
