lmj0011/my.cnf

## my.cnf
# forked from https://gist.github.com/chrisjlee/2973469

[client]
#password     = [your_password]
#port          = 3306
#socket        = /tmp/mysqld.sock

# *** Application-specific options follow here ***

#
# The MySQL server
#
[mysqld]

# back_log is the number of connections the operating system can keep in
# the listen queue, before the MySQL connection manager thread has
# processed them. If you have a very high connection rate and experience
# "connection refused" errors, you might need to increase this value.
# Check your OS documentation for the maximum value of this parameter.
# Attempting to set back_log higher than your operating system limit
# will have no effect.
back_log = 50
max_connections = 301

# I don't know why 0 doesn't disable max_connect_errors checking
# but it doesn't, so set it to a high value to prevent MySQL from
# refusing to accept connections from a flaky host, especially if you
# are using a load balancer!
max_connect_errors = 9999999

# The number of open tables for all threads.
# make sure that the open file limit is at least twice this in the
# mysqld_safe section
table_cache = 4096

# The maximum size of a query packet the server can handle as well as
# maximum query size server can process (Important when working with
# large BLOBs).  enlarged dynamically, for each connection.
max_allowed_packet = 16M

# The size of the cache to hold the SQL statements for the binary log
# during a transaction. If you often use big, multi-statement
# transactions you can increase this value to get more performance. All
# statements from transactions are buffered in the binary log cache and
# are being written to the binary log at once after the COMMIT.  If the
# transaction is larger than this value, temporary file on disk is used
# instead.  This buffer is allocated per connection on first update
# statement in transaction
binlog_cache_size = 1M

# Maximum allowed size for a single HEAP (in memory) table. This option
# is a protection against the accidential creation of a very large HEAP
# table which could otherwise use up all memory resources.
max_heap_table_size = 64M

# Sort buffer is used to perform sorts for some ORDER BY and GROUP BY
# queries. If sorted data does not fit into the sort buffer, a disk
# based merge sort is used instead - See the "Sort_merge_passes"
# status variable. Allocated per thread if sort is needed.
sort_buffer_size = 8M

# This buffer is used for the optimization of full JOINs (JOINs without
# indexes). Such JOINs are very bad for performance in most cases
# anyway, but setting this variable to a large value reduces the
# performance impact. See the "Select_full_join" status variable for a
# count of full JOINs. Allocated per thread if full join is found
join_buffer_size = 8M

# How many threads we should keep in a cache for reuse. When a client
# disconnects, the client's threads are put in the cache if there aren't
# more than thread_cache_size threads from before.  This greatly reduces
# the amount of thread creations needed if you have a lot of new
# connections. (Normally this doesn't give a notable performance
# improvement if you have a good thread implementation.)
#thread_cache_size = 16

# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again. Having the query
# cache enabled may result in significant speed improvements, if your
# have a lot of identical queries and rarely changing tables. See the
# "Qcache_lowmem_prunes" status variable to check if the current value
# is high enough for your load.
# Note: In case your tables change very often or if your queries are
# textually different every time, the query cache may result in a
# slowdown instead of a performance improvement.
query_cache_size = 128M

# Only cache result sets that are smaller than this limit. This is to
# protect the query cache of a very large result set overwriting all
# other query results.
query_cache_limit = 4M

# query_alloc_block_size controls how much memory is reserved for
# parsing SQL statements and some other junk.  I increase it on boxes
# that run complex queries to reduce possible memory fragmentation.  YMMV
# default is 8k
query_alloc_block_size = 16K


# Table type which is used by default when creating new tables, if not
# specified differently during the CREATE TABLE statement.
default_table_type = InnoDB

# Thread stack size to use. This amount of memory is always reserved at
# connection time. MySQL itself usually needs no more than 64K of
# memory, while if you use your own stack hungry UDF functions or your
# OS requires more stack for some operations, you might need to set this
# to a higher value.
thread_stack = 192K


# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.  Also, if max_heap_table_size < tmp_table_size, it will be used
# as the limit instead, so making it bigger than that  is not useful.
tmp_table_size = 64M


# *** INNODB Specific options ***

# Additional memory pool that is used by InnoDB to store metadata
# information.  If InnoDB requires more memory for this purpose it will
# start to allocate it from the OS.  As this is fast enough on most
# recent operating systems, you normally do not need to change this
# value. SHOW INNODB STATUS will display the current amount used.
innodb_additional_mem_pool_size = 32M

# This config file assumes a main memory of at least 8G
innodb_buffer_pool_size = 6.5G

# InnoDB stores data in one or more data files forming the tablespace.
# If you have a single logical drive for your data, a single
# autoextending file would be good enough. In other cases, a single file
# per device is often a good choice. You can configure InnoDB to use raw
# disk partitions as well - please refer to the manual for more info
# about this.

# to prevent fragmentation of the InnoDB tablespace, either create a
# very big initial datafile, or set the autoextend amount to a large
# value.  The disadvantage of using a large autoextend size is that the
# server may take some time to extend the file when needed

# can't specify tablespace sizes for innodb-file-per-table tablespaces
# so using a big autoextend is preferable in those cases.
innodb_data_file_path = ibdata1:1G:autoextend
innodb_autoextend_increment=128M
innodb_file_per_table

# Set this option if you would like the InnoDB tablespace files to be
# stored in another location. By default this is the MySQL datadir.
#innodb_data_home_dir = <directory>

# Number of threads allowed inside the InnoDB kernel. The optimal value
# depends highly on the application, hardware as well as the OS
# scheduler properties. A too high value may lead to thread thrashing.
innodb_thread_concurrency = 0

# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit = 1

# Speed up InnoDB shutdown. This will disable InnoDB to do a full purge
# and insert buffer merge on shutdown. It may increase shutdown time a
# lot, but InnoDB will have to do it on the next startup instead.
#innodb_fast_shutdown

# The size of the buffer InnoDB uses for buffering log data. As soon as
# it is full, InnoDB will have to flush it to disk. As it is flushed
# once per second anyway, it does not make sense to have it very large
# (even with long transactions).
innodb_log_buffer_size = 8M

# Size of each log file in a log group. You should set the combined size
# of log files to about 25%-100% of your buffer pool size to avoid
# unneeded buffer pool flush activity on log file overwrite. However,
# note that a larger logfile size will increase the time needed for the
# recovery process.

# make sure the log files are large enough that you don't hold up
# checkpoints when the logs rotate!
innodb_log_file_size = 1G

# Total number of files in the log group. A value of 2-3 is usually good
# enough.
innodb_log_files_in_group = 2

# Location of the InnoDB log files. Default is the MySQL datadir. You
# may wish to point it to a dedicated hard drive or a RAID1 volume for
# improved performance

# be careful if you use LVM and plan to snapshot your filesystem for hot
# backup.  your log files must be on the same logical volume as your data
# files in order for this to work.

#innodb_log_group_home_dir

# Maximum allowed percentage of dirty pages in the InnoDB buffer pool.
# If it is reached, InnoDB will start flushing them out agressively to
# not run out of clean pages at all. This is a soft limit, not
# guaranteed to be held.
innodb_max_dirty_pages_pct = 80

# The flush method InnoDB will use for Log. The tablespace always uses
# doublewrite flush logic. The default value is "fdatasync", another
# option is "O_DSYNC".

# use directIO to bypass filesystem cache where possible
innodb_flush_method=O_DIRECT

# How long an InnoDB transaction should wait for a lock to be granted
# before being rolled back. InnoDB automatically detects transaction
# deadlocks in its own lock table and rolls back the transaction. If you
# use the LOCK TABLES command, or other transaction-safe storage engines
# than InnoDB in the same transaction, then a deadlock may arise which
# InnoDB cannot notice. In cases like this the timeout is useful to
# resolve the situation.
innodb_lock_wait_timeout = 120

# Let as many clients commit at once as necessary
# If you have a very intensive write application or if you have
# innodb_flush_logs_at_trx <> 1 it may make sense to play with this.

# with this configuration it probably won't matter anyway, because binary
# logging is enabled, which enforces serialized commits, even when the
# isolation level isn't serializable.
innodb_commit_concurrency=0

innodb_open_files=2000
#
[mysqldump]
# Do not buffer the whole result set in memory before writing it to
# file. Required for dumping very large tables
quick

max_allowed_packet = 16M

[mysql]
no-auto-rehash

# Only allow UPDATEs and DELETEs that use keys.
#safe-updates

[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
# Increase the amount of open files allowed per process. Warning: Make
# sure you have set the global system limit high enough! The high value
# is required for a large number of opened tables
open-files-limit = 16384
	# forked from https://gist.github.com/chrisjlee/2973469

	[client]
	#password = [your_password]
	#port = 3306
	#socket = /tmp/mysqld.sock

	# * Application-specific options follow here *

	#
	# The MySQL server
	#
	[mysqld]

	# back_log is the number of connections the operating system can keep in
	# the listen queue, before the MySQL connection manager thread has
	# processed them. If you have a very high connection rate and experience
	# "connection refused" errors, you might need to increase this value.
	# Check your OS documentation for the maximum value of this parameter.
	# Attempting to set back_log higher than your operating system limit
	# will have no effect.
	back_log = 50
	max_connections = 301

	# I don't know why 0 doesn't disable max_connect_errors checking
	# but it doesn't, so set it to a high value to prevent MySQL from
	# refusing to accept connections from a flaky host, especially if you
	# are using a load balancer!
	max_connect_errors = 9999999

	# The number of open tables for all threads.
	# make sure that the open file limit is at least twice this in the
	# mysqld_safe section
	table_cache = 4096

	# The maximum size of a query packet the server can handle as well as
	# maximum query size server can process (Important when working with
	# large BLOBs). enlarged dynamically, for each connection.
	max_allowed_packet = 16M

	# The size of the cache to hold the SQL statements for the binary log
	# during a transaction. If you often use big, multi-statement
	# transactions you can increase this value to get more performance. All
	# statements from transactions are buffered in the binary log cache and
	# are being written to the binary log at once after the COMMIT. If the
	# transaction is larger than this value, temporary file on disk is used
	# instead. This buffer is allocated per connection on first update
	# statement in transaction
	binlog_cache_size = 1M

	# Maximum allowed size for a single HEAP (in memory) table. This option
	# is a protection against the accidential creation of a very large HEAP
	# table which could otherwise use up all memory resources.
	max_heap_table_size = 64M

	# Sort buffer is used to perform sorts for some ORDER BY and GROUP BY
	# queries. If sorted data does not fit into the sort buffer, a disk
	# based merge sort is used instead - See the "Sort_merge_passes"
	# status variable. Allocated per thread if sort is needed.
	sort_buffer_size = 8M

	# This buffer is used for the optimization of full JOINs (JOINs without
	# indexes). Such JOINs are very bad for performance in most cases
	# anyway, but setting this variable to a large value reduces the
	# performance impact. See the "Select_full_join" status variable for a
	# count of full JOINs. Allocated per thread if full join is found
	join_buffer_size = 8M

	# How many threads we should keep in a cache for reuse. When a client
	# disconnects, the client's threads are put in the cache if there aren't
	# more than thread_cache_size threads from before. This greatly reduces
	# the amount of thread creations needed if you have a lot of new
	# connections. (Normally this doesn't give a notable performance
	# improvement if you have a good thread implementation.)
	#thread_cache_size = 16

	# Query cache is used to cache SELECT results and later return them
	# without actual executing the same query once again. Having the query
	# cache enabled may result in significant speed improvements, if your
	# have a lot of identical queries and rarely changing tables. See the
	# "Qcache_lowmem_prunes" status variable to check if the current value
	# is high enough for your load.
	# Note: In case your tables change very often or if your queries are
	# textually different every time, the query cache may result in a
	# slowdown instead of a performance improvement.
	query_cache_size = 128M

	# Only cache result sets that are smaller than this limit. This is to
	# protect the query cache of a very large result set overwriting all
	# other query results.
	query_cache_limit = 4M

	# query_alloc_block_size controls how much memory is reserved for
	# parsing SQL statements and some other junk. I increase it on boxes
	# that run complex queries to reduce possible memory fragmentation. YMMV
	# default is 8k
	query_alloc_block_size = 16K


	# Table type which is used by default when creating new tables, if not
	# specified differently during the CREATE TABLE statement.
	default_table_type = InnoDB

	# Thread stack size to use. This amount of memory is always reserved at
	# connection time. MySQL itself usually needs no more than 64K of
	# memory, while if you use your own stack hungry UDF functions or your
	# OS requires more stack for some operations, you might need to set this
	# to a higher value.
	thread_stack = 192K


	# Maximum size for internal (in-memory) temporary tables. If a table
	# grows larger than this value, it is automatically converted to disk
	# based table This limitation is for a single table. There can be many
	# of them. Also, if max_heap_table_size < tmp_table_size, it will be used
	# as the limit instead, so making it bigger than that is not useful.
	tmp_table_size = 64M


	# * INNODB Specific options *

	# Additional memory pool that is used by InnoDB to store metadata
	# information. If InnoDB requires more memory for this purpose it will
	# start to allocate it from the OS. As this is fast enough on most
	# recent operating systems, you normally do not need to change this
	# value. SHOW INNODB STATUS will display the current amount used.
	innodb_additional_mem_pool_size = 32M

	# This config file assumes a main memory of at least 8G
	innodb_buffer_pool_size = 6.5G

	# InnoDB stores data in one or more data files forming the tablespace.
	# If you have a single logical drive for your data, a single
	# autoextending file would be good enough. In other cases, a single file
	# per device is often a good choice. You can configure InnoDB to use raw
	# disk partitions as well - please refer to the manual for more info
	# about this.

	# to prevent fragmentation of the InnoDB tablespace, either create a
	# very big initial datafile, or set the autoextend amount to a large
	# value. The disadvantage of using a large autoextend size is that the
	# server may take some time to extend the file when needed

	# can't specify tablespace sizes for innodb-file-per-table tablespaces
	# so using a big autoextend is preferable in those cases.
	innodb_data_file_path = ibdata1:1G:autoextend
	innodb_autoextend_increment=128M
	innodb_file_per_table

	# Set this option if you would like the InnoDB tablespace files to be
	# stored in another location. By default this is the MySQL datadir.
	#innodb_data_home_dir = <directory>

	# Number of threads allowed inside the InnoDB kernel. The optimal value
	# depends highly on the application, hardware as well as the OS
	# scheduler properties. A too high value may lead to thread thrashing.
	innodb_thread_concurrency = 0

	# If set to 1, InnoDB will flush (fsync) the transaction logs to the
	# disk at each commit, which offers full ACID behavior. If you are
	# willing to compromise this safety, and you are running small
	# transactions, you may set this to 0 or 2 to reduce disk I/O to the
	# logs. Value 0 means that the log is only written to the log file and
	# the log file flushed to disk approximately once per second. Value 2
	# means the log is written to the log file at each commit, but the log
	# file is only flushed to disk approximately once per second.
	innodb_flush_log_at_trx_commit = 1

	# Speed up InnoDB shutdown. This will disable InnoDB to do a full purge
	# and insert buffer merge on shutdown. It may increase shutdown time a
	# lot, but InnoDB will have to do it on the next startup instead.
	#innodb_fast_shutdown

	# The size of the buffer InnoDB uses for buffering log data. As soon as
	# it is full, InnoDB will have to flush it to disk. As it is flushed
	# once per second anyway, it does not make sense to have it very large
	# (even with long transactions).
	innodb_log_buffer_size = 8M

	# Size of each log file in a log group. You should set the combined size
	# of log files to about 25%-100% of your buffer pool size to avoid
	# unneeded buffer pool flush activity on log file overwrite. However,
	# note that a larger logfile size will increase the time needed for the
	# recovery process.

	# make sure the log files are large enough that you don't hold up
	# checkpoints when the logs rotate!
	innodb_log_file_size = 1G

	# Total number of files in the log group. A value of 2-3 is usually good
	# enough.
	innodb_log_files_in_group = 2

	# Location of the InnoDB log files. Default is the MySQL datadir. You
	# may wish to point it to a dedicated hard drive or a RAID1 volume for
	# improved performance

	# be careful if you use LVM and plan to snapshot your filesystem for hot
	# backup. your log files must be on the same logical volume as your data
	# files in order for this to work.

	#innodb_log_group_home_dir

	# Maximum allowed percentage of dirty pages in the InnoDB buffer pool.
	# If it is reached, InnoDB will start flushing them out agressively to
	# not run out of clean pages at all. This is a soft limit, not
	# guaranteed to be held.
	innodb_max_dirty_pages_pct = 80

	# The flush method InnoDB will use for Log. The tablespace always uses
	# doublewrite flush logic. The default value is "fdatasync", another
	# option is "O_DSYNC".

	# use directIO to bypass filesystem cache where possible
	innodb_flush_method=O_DIRECT

	# How long an InnoDB transaction should wait for a lock to be granted
	# before being rolled back. InnoDB automatically detects transaction
	# deadlocks in its own lock table and rolls back the transaction. If you
	# use the LOCK TABLES command, or other transaction-safe storage engines
	# than InnoDB in the same transaction, then a deadlock may arise which
	# InnoDB cannot notice. In cases like this the timeout is useful to
	# resolve the situation.
	innodb_lock_wait_timeout = 120

	# Let as many clients commit at once as necessary
	# If you have a very intensive write application or if you have
	# innodb_flush_logs_at_trx <> 1 it may make sense to play with this.

	# with this configuration it probably won't matter anyway, because binary
	# logging is enabled, which enforces serialized commits, even when the
	# isolation level isn't serializable.
	innodb_commit_concurrency=0

	innodb_open_files=2000
	#
	[mysqldump]
	# Do not buffer the whole result set in memory before writing it to
	# file. Required for dumping very large tables
	quick

	max_allowed_packet = 16M

	[mysql]
	no-auto-rehash

	# Only allow UPDATEs and DELETEs that use keys.
	#safe-updates

	[isamchk]
	key_buffer = 512M
	sort_buffer_size = 512M
	read_buffer = 8M
	write_buffer = 8M

	[myisamchk]
	key_buffer = 512M
	sort_buffer_size = 512M
	read_buffer = 8M
	write_buffer = 8M

	[mysqlhotcopy]
	interactive-timeout

	[mysqld_safe]
	# Increase the amount of open files allowed per process. Warning: Make
	# sure you have set the global system limit high enough! The high value
	# is required for a large number of opened tables
	open-files-limit = 16384