### This my.cnf file is based on the following speedemy.com blog post:
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof
[client]
###
### client section - used for "mysql", "mysqladmin" and similar command line
### clients.
###
#port = 3306
#socket = /tmp/mysql.sock
### Just in case your current configuration is not using default values.
[mysqld]
default-storage-engine=MyISAM
###
### mysqld section - used by MySQL Server (also applies to Percona Server,
### MariaDB etc.)
###
#port = 3306
#socket = /tmp/mysql.sock
#user = mysql
### Just in case your current configuration is not using default values.
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
#basedir = /usr/share/mysql
datadir = /var/lib/mysql
tmpdir = /tmp
#lc-messages-dir = /usr/share/mysql/german
skip-external-locking
disable-partition-engine-check
#datadir = /var/lib/mysql
### This must point to the main MySQL data directory.
log_error = /var/log/mysql/error.log
###
### General Server Options:
###
max_allowed_packet = 64M
### Default packet limit is almost always too small.
#table_open_cache = 2000
### Table open cache - defaults only good in MySQL 5.7 and 5.6
### On 5.1 or 5.5 set the above.
#table_open_cache_instances = 8
### Table open cache instances - be sure to set this on MySQL 5.6.
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#table_open_cache_instances
#tmpdir = /tmp
### Don't use tmpfs for tmpdir, or AIO will be disabled. And make sure
### There's enough room for temporary tables and files (usually 2-5GB)
#thread_cache_size = 100
### Thread cache - this is now autosized in MySQL 5.6 and 5.7
### But on MySQL 5.1 and 5.5 do set the above
#default_storage_engine = InnoDB
### Default storage engine in most cases should be InnoDB. If in doubt:
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#default_storage_engine
#skip_name_resolve
### Disabling DNS resolution - DNS based logins will stop working:
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#skip_name_resolve
query_cache_size=0
query_cache_type=0
query_cache_type=OFF
### Explicitly disabling the query cache. If you have a light workload, you
### may reconsider:
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#query_cache_type
back_log = 512
### Consider increasing this if you have a high number of new connections
### (> 1000/sec) and you are running on MySQL 5.6 or older
### https://www.percona.com/blog/2011/04/19/mysql-connection-timeouts/
#thread_concurrency = 0
### Do not tune this. This does nothing. And I have included it here only
### because I've seen too many people obsess over it.
#join_buffer_size = 256k
#sort_buffer_size = 256k
#read_buffer_size = 128k
#read_rnd_buffer_size = 256k
### Leave these at their defaults, do not change server-wide settings for them
### Instead, use session variable when you really need it like that:
### mysql> SET session read_buffer_size = 2 * 1024 * 1024;
### mysql> RUN YOUR QUERY;
###
### Binary logging section
###
log-bin=ipx14465-bin
server_id = 1
### Enabling binlog as well as unique server_id for
### point in time recovery and, potentially, replication.
max_binlog_size = 100M
### Don't have large binary logs, with file systems like ext3 this could
### cause stalls.
master_info_repository = TABLE
relay_log_info_repository = TABLE
### If you're on MySQL 5.6 or MySQL 5.7, store the binlog position
### to TABLE rather than FILE (that way position is in sync with db)
expire_logs_days = 5
### Control the amount of binary logs server is allowed to keep (in days)
sync_binlog = 0
### Disabling sync-binlog for better performance, but do consider the
### durability issues:
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#sync_binlog
binlog_format = MIXED
### For the binary log format, I recommend MIXED, but it's up to you.
###
### MyISAM Specific Options:
###
### Assuming MyISAM is not going to be used, therefore defaults used for MyISAM
### Still if you do plan to use it, this is probably the only variable you
### want to tune:
explicit_defaults_for_timestamp = 1
key_buffer_size = 1G
max_allowed_packet = 64M
open_files_limit = 50000
thread_stack = 256K
thread_cache_size = 100
table_definition_cache = 5500
table_open_cache = 5500
table_open_cache_instances = 8
join_buffer_size = 125M
sort_buffer_size = 4M
read_rnd_buffer_size = 4M
max_connections = 80
tmp_table_size = 4G
max_heap_table_size = 4G
wait_timeout = 14400
# interactive_timeout = 14400
###
### InnoDB Specific Options:
###
innodb_support_xa = ON
innodb_file_per_table = ON
innodb_buffer_pool_size = 3G
### Set the innodb buffer pool size to 75-80% of total memory (dedicated):
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_buffer_pool_size
### The example above would be used on a dedicated 128GB MySQL server.
innodb_log_file_size = 384M
### Allow 1-2h worth of writes to be buffered in transaction logs:
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_log_file_size
### Helper command:
### mysql> pager grep seq
### mysql> show engine innodb status\G select sleep(60); show engine innodb status\G
innodb_log_buffer_size = 16M
### Don't sweat about about this, just set it to 16M.
innodb_flush_log_at_trx_commit = 0
### This, OTOH is really important. See:
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_flush_log_at_trx_commit
innodb_flush_method = O_DIRECT
### On Linux, just leave it set to O_DIRECT.
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_flush_method
innodb_buffer_pool_instances = 3
### Set this depending on MySQL version. Available since 5.6
### In 5.6, set to 4
### In 5.7, set to 8
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_buffer_pool_instances
innodb_thread_concurrency = 8
### Yes, this is not an error. Use 0 only for benchmarks and if you're nowhere
### near saturating your server.
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_thread_concurrency
innodb_io_capacity = 1000
innodb_io_capacity_max = 3000
### Base these on your server radom write IO capabilities
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_io_capacity
innodb_stats_on_metadata = 0
### On 5.6 and 5.7 this is already turned off by default.
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_stats_on_metadata
innodb_buffer_pool_dump_at_shutdown = 1 # MySQL 5.6+
innodb_buffer_pool_load_at_startup = 1 # MySQL 5.6+
innodb_buffer_pool_dump_pct = 75 # MySQL 5.7 only
### Enable these for faster warm-up
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_buffer_pool_dump_at_shutdown
#innodb_adaptive_hash_index_parts = 16 # MySQL 5.7 only
#innodb_adaptive_hash_index_partitions = 16 # Percona Server only
### AHI is a common bottle-neck, however few versions of MySQL
### support AHI partitions:
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_adaptive_hash_index_parts
#innodb_checksum_algorithm = crc32 # MySQL 5.6 or newer
### Hardware acceleratorion for checksums
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_checksum_algorithm
innodb_read_io_threads = 32
innodb_write_io_threads = 16
### This isn't super important to fine tune, but it's good to aling
### it to number of availble read / write spindles
### http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof#innodb_read_io_threads
innodb_file_per_table = ON
### There's a number of reasons to use file per table, but beware that
### it doesn't convert tables automatically, and ibdata1 will never shrink anyways
#innodb_open_files = 3000
### Set this on MySQL 5.1 and 5.5. On 5.6 and beyond it's autotuned
### specifies max number of .ibd files that MySQL can keep open at one time
innodb_flush_neighbors = 0
### Set to 0 if you're using SSD. For magnetic drives, keep it at 1
### http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_neighbors
innodb_flushing_avg_loops = 100
### Use this if you don't like how flushing activity behaves
### (e.g. if you get stalls)
### http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flushing_avg_loops
innodb_page_cleaners = 8 # MySQL 5.7 only
### On a write-heavy environment, use this to gain even more control
### over the flushing activity. This is not yet tested too wildly.
### http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_page_cleaners
#innodb_old_blocks_time = 1000
### Use this only on 5.1 and 5.5 since 5.6 and 5.7 already have 1000 as a default
### This basically protects your buffer pool from occasional scans
### Although the way it works is quite complex, definitely not for my.cnf
#innodb_max_dirty_pages_pct
### If you're in a write-heavy environment, but want to limit how much of it
### you use for dirty pages, this is the variable to configure. Defaults are
### version specific, but range in 75-90 %
###
### Slow query log (mostly Percona XtraDB Specific)
###
long_query_time = 9.0
### Log queries that take > 1s to execute
slow_query_log = 0
### Enable the slow query logging
### Only to be used on Percona Server:
#log_slow_slave_statements = ON
#log_slow_verbosity = full
#slow_query_log_timestamp_always = ON
#slow_query_log_timestamp_precision = microsecond
#slow_query_log_use_global_control = all
### See here for more information:
### https://www.percona.com/doc/percona-server/5.6/diagnostics/slow_extended.html
jira.mariadb.org
