My.cnf - Performance tweak, outperformed by slower server

TrebleMebbel

Registered
Sep 13, 2013
2
0
1
cPanel Access Level
Root Administrator
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.

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 unlimited
I hope someone can see the obvious things that I am unable to.

Thank you in advance
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
Pretty amazing setup, but config very weird, inconsistent, like a copy/paste from many different configs

Before optimizing it you need to mention at least :
- mysql version (and if you have possiblity to upgrade)
- used database engine (InnoDB or MyISAM)
- mysqltuner / mysqlreport results


All of those 3 are very important to help you
 

TrebleMebbel

Registered
Sep 13, 2013
2
0
1
cPanel Access Level
Root Administrator
It seems the output that I have is different from what I have seen in previous posts. So, although I am almost certain this is not (attachement) what is needed to identify the root cause of the problem.

I am just back from Holiday and see the issue persists, as stated previously I most certainly am not an expert on the matter, just looking for some enlightenment.

Thanks in advance and my apologies for any inconvenience caused.
 

Attachments

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
OK,

1. It seems like Hyper Threading on R910 is disabled, please enable it, you should have 80 cores
each E7 - 4870 got 10 physical cores + 10 virtual (HT) = 20 per CPU, you got 4 CPUs = 80 cores
btw. very nice setup

2. You are using Percona XtraDB, thats great, but do you have an option to upgrade to 5.6 ?


Can you run mysqltuner.pl
https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
and mysqlreport
http://hackmysql.com/scripts/mysqlreport
tools

since they provide info in better form,
From your current data it is also possible to read it, but going thru mysql extended variables and counting the ratios/sums etc would take quite a while

Once you send mysqltuner.pl and mysqlreport info, I will help you with the optimization :)

btw. may I ask what application you run on such powerful machine ?
It got 80 cores, how many mysql threads does your application usually use through the day ?

mysql shows Max_used_connections | 1 |
queries 651114
uptime 15072
gives 43 queries per second

very few for such powerful server

Best Regards