Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
my.cnf file optimized for InnoDB 64bit setups
# forked from http://forge.mysql.com/tools/tool.php?id=137
[client]
#password = [your_password]
port = 3306
socket = /tmp/mysqld.sock
# *** Application-specific options follow here ***
#
# The MySQL server
#
[mysqld]
# generic configuration options
port = 3306
socket = /tmp/mysqld.sock
# sync_binlog ensures that all writes to the binary log are immediately
# flushed to disk. This is important, especially for replication, because
# if the server crashes and has not written all of the binary log to disk (and flushed it)
# then some rows will not make it to the slave
sync_binlog=1
# 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
# if your OS supports it, you can lock the buffer pool into memory
# with this option to prevent swapping. I'm not sure if linux supports this
# but Solaris does. On linux, using the swapiness sysctl is probably nearly
# as effective.
#memlock
# 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
# Set the default transaction isolation level. Levels available are:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
# REPEATABLE-READ requires next-key locks. If your application isn't sensitive # to 'phantom rows', (it probably isn't) then read-committed is fine. Feel
# free to change this to REPEATABLE-READ at a small performance penalty if it
# makes you feel better.
transaction_isolation = READ-COMMITTED
# 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
# Enable binary logging. This is required for acting as a MASTER in a
# replication configuration. You also need the binary log if you need
# the ability to do point in time recovery from your latest backup.
# it is recommened to specify a filename for the binary log, hopefully
# something that is not host specific. I've chosen master-bin.
log-bin=master-bin
# If you're using replication with chained slaves (A->B->C), you need to
# enable this option on server B. It enables logging of updates done by
# the slave thread into the slave's binary log.
log_slave_updates
# Enable the full query log. Every query (even ones with incorrect
# syntax) that the server receives will be logged. This is useful for
# debugging, it is usually disabled in production use.
#log
# Log warnings to the error log
log_warnings
# Log slow queries. Slow queries are queries which take more than the
# amount of time defined in "long_query_time" or which do not use
# indexes well, if log_long_format is enabled. It is normally good idea
# to have this turned on if you frequently add new queries to the
# system.
log_slow_queries
# All queries taking more than this amount of time (in seconds) will be
# trated as slow. Do not use "1" as a value here, as this will result in
# even very fast queries being logged from time to time (as MySQL
# currently measures time with second accuracy only).
long_query_time = 1
# Log more information in the slow query log. Normally it is good to
# have this turned on. This will enable logging of queries that are not
# using indexes in addition to long running queries.
log_long_format
# The directory used by MySQL for storing temporary files. For example,
# it is used to perform disk based large sorts, as well as for internal
# and explicit temporary tables. It might be good to put it on a
# swapfs/tmpfs filesystem, if you do not create very large temporary
# files. Alternatively you can put it on dedicated disk. You can
# specify multiple paths here by separating them by ";" - they will then
# be used in a round-robin fashion.
# BE CAREFUL IF YOU PUT `tmpdir` ON tmpfs. You should set slave_load_tmpdir
# to a non-tmpfs filesystem if you do that
tmpdir = /tmp
slave_load_tmpdir = /tmp
# *** Replication related settings
# Unique server identification number between 1 and 2^32-1. This value
# is required for both master and slave hosts. It defaults to 1 if
# "master-host" is not set, but will MySQL will not function as a master
# if it is omitted.
server-id = 168291
# Make the slave read-only. Only users with the SUPER privilege and the
# replication slave thread will be able to modify data on it. You can
# use this to ensure that no applications will accidently modify data on
# the slave instead of the master
#read_only
# Size of the Key Buffer, used to cache index blocks for MyISAM tables.
# Do not set it larger than 30% of your available memory, as some memory
# is also required by the OS to cache rows. Even if you're not using
# MyISAM tables, you should still set it to 8-64M as it will also be
# used for internal temporary disk tables.
key_buffer_size = 32M
# Size of the buffer used for doing full table scans of MyISAM tables.
# Allocated per thread, if a full scan is needed.
read_buffer_size = 2M
# When reading rows in sorted order after a sort, the rows are read
# through this buffer to avoid disk seeks. You can improve ORDER BY
# performance a lot, if set this to a high value.
# Allocated per thread, when needed.
read_rnd_buffer_size = 16M
# MyISAM uses special tree-like cache to make bulk inserts (that is,
# INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA
# INFILE) faster. This variable limits the size of the cache tree in
# bytes per thread. Setting it to 0 will disable this optimisation. Do
# not set it larger than "key_buffer_size" for optimal performance.
# This buffer is allocated when a bulk insert is detected.
bulk_insert_buffer_size = 64M
# This buffer is allocated when MySQL needs to rebuild the index in
# REPAIR, OPTIMIZE, ALTER table statements as well as in LOAD DATA INFILE
# into an empty table. It is allocated per thread so be careful with
# large settings.
myisam_sort_buffer_size = 128M
# The maximum size of the temporary file MySQL is allowed to use while
# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.
# If the file-size would be bigger than this, the index will be created
# through the key cache (which is slower).
myisam_max_sort_file_size = 10G
# If the temporary file used for fast index creation would be bigger
# than using the key cache by the amount specified here, then prefer the
# key cache method. This is mainly used to force long character keys in
# large tables to use the slower key cache method to create the index.
myisam_max_extra_sort_file_size = 10G
# If a table has more than one index, MyISAM can use more than one
# thread to repair them by sorting in parallel. This makes sense if you
# have multiple CPUs and plenty of memory.
myisam_repair_threads = 1
# Automatically check and repair not properly closed MyISAM tables.
myisam_recover
# *** BDB Specific options ***
# Use this option if you run a MySQL server with BDB support enabled but
# you do not plan to use it. This will save memory and may speed up some
# things.
skip-bdb
# *** INNODB Specific options ***
# Use this option if you have a MySQL server with InnoDB support enabled
# but you do not plan to use it. This will save memory and disk space
# and speed up some things.
#skip-innodb
# 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 = 16
# 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
@yogademung

This comment has been minimized.

Copy link

@yogademung yogademung commented Aug 24, 2017

if my server just have 1gb RAM.. Which parts should be i changed?

@Nirjonadda

This comment has been minimized.

Copy link

@Nirjonadda Nirjonadda commented Dec 28, 2017

This my current my.cnf the following configuration with MySQL 5.6 and with 50 GB Ram. What you are recommended?

[mysqld]
local-infile = 0
bind-address=127.0.0.1
performance-schema = 1
default-storage-engine = InnoDB

max_connections = 800
max_user_connections = 1000
datadir = "/var/lib/mysql"
socket = "/var/lib/mysql/mysql.sock"

key_buffer = 256M
table_cache = 28560
open_files_limit = 50000
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 6M
tmp_table_size = 256M
read_rnd_buffer_size = 4M
max_heap_table_size = 256M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
thread_cache = 512
query_cache_limit = 12M
query_cache_size = 256M
thread_concurrency = 8
wait_timeout = 1000
connect_timeout = 1000
interactive_timeout = 1000
#log-slow-queries = /var/log/mysqlslowqueries.log
max_allowed_packet = 256M

innodb_file_per_table = 1
innodb_buffer_pool_size = 700M

[isamchk]
key_buffer = 64M
sort_buffer_size = 64M
read_buffer = 16M
write_buffer = 16M

[myisamchk]
key_buffer = 64M
sort_buffer_size = 64M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout
[mysqld_safe]

log-error="/var/log/mysqld.log"
@Lathanao

This comment has been minimized.

Copy link

@Lathanao Lathanao commented Oct 4, 2018

Thx !

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment