I am trying to tweak my server's my.cnf file because I have been receiving numerous "down server" warnings. I know I also need to tweak other aspects of my server as well.
Here is my my.cnf
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
#skip-external-locking
local-infile=0
#skip-grant-tables
#bind-address = *
table_open_cache = 3000
#table_cache = 3000
read_buffer_size = 1M
read_rnd_buffer_size = 1M
join_buffer_size= 1M
sort_buffer_size = 2M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 256M
myisam_repair_threads= 4
myisam_recover = BACKUP
thread_cache_size = 100
query_cache_type= 1
query_cache_size= 128M
query_cache_limit= 7M
thread_concurrency = 8
tmp_table_size= 128M
max_heap_table_size= 128M
#bulk_insert_buffer_size 1G
#old_passwords= 1
log-slow-queries=/var/lib/mysql/slow.log
#slow_query_log_file=mysql-slow.log
log_queries_not_using_indexes = 1
slow_query_log = 1
long_query_time= 0.1
#other vars
net_read_timeout = 120
#skip-locking
skip-name-resolve
back_log = 100
max_connect_errors = 100
concurrent_insert= 2
open_files_limit = 8192
thread_stack = 256K
interactive_timeout = 3600
wait_timeout = 1800
max_connections = 2000
#max_user_connections = 100
key_buffer_size= 1G
connect_timeout = 30
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_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_buffer_pool_size = 12G
innodb_additional_mem_pool_size = 4M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
innodb_table_locks = 0
innodb_file_per_table
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 300
skip-innodb-doublewrite
innodb_support_xa = 0
innodb_commit_concurrency = 8
innodb_thread_concurrency= 8
[mysqldump]
quick
quote-names
max_allowed_packet = 128M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer = 384M
sort_buffer = 384M
read_buffer = 256M
write_buffer = 256M
[mysqlhotcopy]
interactive-timeout
Here is mysql tuner output
>> MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
[OK] Currently running supported MySQL version 5.5.27-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 103M (Tables: 201)
[--] Data in InnoDB tables: 1G (Tables: 3076)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 7M (Tables: 119)
[!!] Total fragmented tables: 179
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 4d 23h 16m 1s (29M q [68.013 qps], 277K conn, TX: 78B, RX: 9B)
[--] Reads / Writes: 63% / 37%
[--] Total buffers: 13.3G global + 5.2M per thread (2000 max threads)
[OK] Maximum possible memory usage: 23.5G (75% of installed RAM)
[OK] Slow queries: 5% (1M/29M)
[OK] Highest usage of available connections: 1% (21/2000)
[OK] Key buffer size / total MyISAM indexes: 1.0G/38.6M
[OK] Key buffer hit rate: 99.8% (16M cached / 34K reads)
[OK] Query cache efficiency: 79.2% (17M cached / 22M selects)
[!!] Query cache prunes per day: 208318
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1M sorts)
[!!] Joins performed without indexes: 18586
[!!] Temporary tables created on disk: 31% (316K on disk / 1M total)
[OK] Thread cache hit rate: 99% (21 created / 277K connections)
[!!] Table cache hit rate: 7% (3K open / 40K opened)
[OK] Open file limit used: 3% (391/10K)
[OK] Table locks acquired immediately: 99% (13M immediate / 13M locks)
[OK] InnoDB buffer pool / data size: 12.0G/1.0G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Read this before increasing table_cache over 64:
http://bit.ly/1mi7c4C
Variables to adjust:
query_cache_size (> 128M)
join_buffer_size (> 1.0M, or always use indexes with joins)
tmp_table_size (> 128M)
max_heap_table_size (> 128M)
table_cache (> 3000)