Mysql Performance Tuning

MySQL Performance tuning [my.cnf file parameters + description]

[mysqld]# Maximum allowed number of connections to the MySQL Server
max_connections = 1500

# The key buffer is a variable that is shared amongst all MySQL clients on the server. A large setting is recomended, particularly helpful with tables that have unique keys.
key_buffer = 1024M

#The size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add #indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible.
join_buffer_size = 100M#Each thread that does a sequential scan allocates a buffer of this size (in bytes) for each table it scans. If you do many sequential scans, you might want to increase this value, which defaults to #131072. The value of this variable should be a multiple of 4KB. If it is set to a value that is not a multiple of 4KB
read_buffer_size = 1M

#Each session that needs to do a sort allocates a buffer of this size
sort_buffer_size = 2M

#This is the old name of table_open_cache. The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires.
table_cache = 1800

# Number of threads the server should cache for reuse. When a client disconnects, the client’s threads are put in the cache if there are fewer than thread_cache_size threads there.
thread_cache_size = 384

# The number of seconds the server waits for activity on a noninteractive connection before closing it.
wait_timeout = 300

# The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake.
connect_timeout = 10# The maximum size of internal in-memory temporary tables.
tmp_table_size = 64M

# This variable sets the maximum size to which MEMORY tables are allowed to grow
max_heap_table_size = 64M

# The maximum size of one packet or any generated/intermediate string.
max_allowed_packet = 64M

# If there are more than this number of interrupted connections from a host, that host is blocked from further connections
max_connect_errors = 1000

# When reading rows in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks.
read_rnd_buffer_size = 524288# Don’t cache results that are larger than this number of bytes.Default value is 1mb
query_cache_limit = 4M

#The size of the persistent buffer used for statement parsing and execution. This buffer is not freed between statements. If you are running complex queries, a larger query_prealloc_size value might be #helpful in improving performance, because it can reduce the need for the server to perform memory allocation during query execution operations.
query_prealloc_size = 65536

# The allocation size of memory blocks that are allocated for objects created during statement parsing and execution.
query_alloc_block_size = 131072

# The amount of memory allocated for caching query results. The default value is 0, which disables the query cache.
query_cache_size = 1024M;

#Set the query cache type
# 0 or OFF : Don’t cache results in or retrieve results from the query cache.
# 1 or ON   : Cache all cacheable query results except for those that begin with SELECT SQL_NO_CACHE.
# 2 or DEMAND : Cache results only for cacheable queries that begin with SELECT SQL_CACHE
query_cache_type = 1

# This variable is set from the value of the –skip-name-resolve option. If it is ON, mysqld resolves host names when checking client connections. If OFF, mysqld uses only IP numbers and all Host # column values in the grant tables must be IP numbers or localhost.
skip-name-resolve

[mysqld_safe]#The number of files that the operating system allows mysqld to open
open_files_limit = 8192

[mysqldump]# The maximum size of one packet or any generated/intermediate string.
max_allowed_packet = 16M