Magento | Magento MySQL my.cnf perfect setup

Magento MySQL my.cnf perfect setup

Here is the perfect my.cnf configuration which worked for me.
You can use this for the running magento properly.

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

 

#innodb settings

innodb_log_file_size=100M

innodb_additional_mem_pool_size=20M

innodb_flush_log_at_trx_commit=2

innodb_lock_wait_timeout=1800

innodb_buffer_pool_size=20G

 

#other vars

net_read_timeout=120

skip-locking

skip-name-resolve

table_cache=2048

thread_cache_size=16

back_log=100

max_connect_errors=10000

open-files-limit=20000

interactive_timeout=3600

wait_timeout=1800

max_connections=200

key_buffer_size=1G

connect_timeout=120

 

#skip-name-resolve

max_allowed_packet=16M

tmp_table_size=64M

max_heap_table_size=64M

query_cache_size=256M

sort_buffer_size=1M

read_buffer_size=1M

read_rnd_buffer_size=8M

join_buffer_size=3M

 

old_passwords=1