Skip to main content

My.cnf - Performance tweak, outperformed by slower server

TrebleMebbel
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. 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

Comments

4 comments

Date Votes

Please sign in to leave a comment.

Didn't find what you were looking for?

New post