performance tuning of my sql in 12 GB ram with 21 Intel(R) Xeon(R) CPU E5-2620 0 @ 2.00GHz

Question

Hi are trying to optimize MySQL for a huge traffic web server, we are using Apache 2.4 , and MySQL 5.6 version.

We have put these parameters in MySQL configuration file

cat /etc/my.cnf

[mysqld]

skip-external-locking

key_buffer = 500M

max_allowed_packet=64M

max_connections = 2000

max_user_connections = 50000

wait_timeout=180

connect_timeout=120

table_open_cache=4096

table_definition_cache=4096

myisam_sort_buffer_size = 64M

thread_cache_size = 128

query_cache_size = 190M

query_cache_limit = 3M

open_files_limit=10000

log_error = /var/log/mysql/error.log

default-storage-engine=MyISAM

[mysqld]

log-bin=mysql-bin

binlog-do-db=******

server-id=1

[mysql]

no-auto-rehash

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

replicate-do-db=*****

auto_increment_increment      = 2

auto_increment_offset         = 1

replicate-same-server-id = 0

slave-skip-errors=all

[mysqld]
slave-skip-errors=1062

skip-slave-start

query_cache_type=1

[mysqld]

innodb_lock_wait_timeout=120

slave_compressed_protocol=1

Answer 1

query_cache_size = 190M

The QC is inefficient when it is big; limit to about 50M.

slave-skip-errors=1062

This is a flavor of "sweeping bugs under the rug".

default-storage-engine=MyISAM

passé.

key_buffer = 500M

If you do stick with MyISAM, and this server is running just MySQL, then use 1000M.

server-id=1

Make sure each server has a different value.

But that is only configuration. If you have slow queries, let's see them and try to improve them.