[client]
port=3306
[mysqld_safe]
local-infile=0
#nice = -5
[mysqld]
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
performance-schema=0
port=3306
#myisam_max_sort_file_size=6341787648
#tmpdir=/var/mysqltmpdir
#log-slow-queries = /tmp/mysql-slow.log
#log-queries-not-using-indexes
#key_buffer=64M
max_allowed_packet=268435456
table_open_cache=2048
#sort_buffer=1M
net_buffer_length=8K
myisam_sort_buffer_size=8M
# max_connections and max_user_connections are very high, they shouldn't be more than a few hundred
max_connections=200
max_user_connections=200
# Too high, an interactive (command line) program shouldn't be waiting 1000 seconds between queries or something is wrong with it
interactive_timeout=300
# The timeouts below are too high, and can cause idle connections to use up the available connection pool
wait_timeout=60
connect_timeout=60
# Too high. Make it at most number of CPU cores * 2
thread_cache_size=100
#thread_cache=8
#thread_concurrency=4
myisam_sort_buffer_size=64M
query_cache_limit=M
query_cache_size=0
query_cache_type=0
query_cache_type=OFF
#join_buffer=1M
sort_buffer_size=4M
read_rnd_buffer_size=4M
read_buffer_size=2M
#max_connect_errors=10
datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
innodb_flush_log_at_trx_commit=2
# 500 seconds is long time for a table to be locked, I'd lower this to about 60-120 seconds.
innodb_lock_wait_timeout=500
innodb_support_xa=ON
innodb_file_per_table=ON
#skip-innodb
#skip-name-resolv
#skip-networking
# Why such a high open files limit? No need unless you have thousands of tables
open_files_limit=50000
# For a database your size, the buffer pool is too large. The memory can be better used
# by the operating system for other things. Set it to the total size of all your Innodb tables plus
# about 20%. The 70% RAM rule you generally see talked about is when the databases exceed
# the size of RAM.
innodb_buffer_pool_size=16G
default-storage-engine=MyISAM
# Too high. Make is 4-8M
join_buffer_size=125M
# This is WAY too high. Make it about 64M - 96M max
tmp_table_size=4G
# This is WAY too high. Make it about 64M - 96M max
max_heap_table_size=4G
# Unless you're using tools which need performance_schama data, or know what to query manually
# to find bottlenecks, turn off performance_schema as it ats a little bit of overhead.
performance_schema = ON
# Why such a large key_buffer_size, are you running another app with large MYISAM tables?
key_buffer_size=2G
# You're likely not benefiting at all from 24 buffer pool instances on a VPS. If it was a
# dedicated server where you had 12-24 cores available, then you'd want one buffer pool
# per every 2-4 cores (depending on load)
innodb_buffer_pool_instances=24
# I'd scale this down to about 1G-2G unless its an SSD VPS. A large log file size on traditional
# harddrives is going to cause longer start/restart times.
innodb_log_file_size=3G
innodb_log_buffer_size=16M
innodb_flush_log_at_trx_commit=0
innodb_flush_method=O_DIRECT
# Make this the number of CPUs x 2
thread_cache_size=100
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_dump_pct=75
innodb_flush_neighbors=1
innodb_page_cleaners=8
table_definition_cache=5500
table_open_cache_instances=16
# Don't fool around with thread stack unless you know what you're doing.
#thread_stack=256K
[mysqldump]
quick
max_allowed_packet=64M
[mysql]
no-auto-rehash
#safe-updates
[isamchk]
key_buffer=20M
sort_buffer=20M
read_buffer=2M
write_buffer=2M
[myisamchk]
key_buffer=512M
sort_buffer=512M
read_buffer=8M
write_buffer=8M
[mysqlhotcopy]
interactive-timeout
