Tips: Optimize MySQL ~5.7 Cheat Sheet + Vid

Hi this is gonna be my first post here, hope you find it useful. I wanted to post this on an older thread which came up in Google which I wanted to update but the forum wouldn't let me. It's pretty annoying when info about optimisations at the top of Google is all 6 years out of date. So for anybody like me that's looking for tips on how to do this I wanted to share an awesome a video tutorial I found from an MySQL "guru" Laurynas Biveinis (His website link for other tips) and he provides you with a cheat sheet (posted below) with what he says are the 17 most important settings and explanations/guides of where to set them for optimal performance depending on your SQL version. I noticed a pretty massive improvement straight off the bat, a PHP video import script I was testing against importing a fixed number of videos the time to completion went from 1:40+ to about 35 seconds. It's a long video at almost an hour and a half long, admittedly, I only watched up to 50 minutes before I'm fairly sure I got everything I needed to from it and I did skip bits to as there's a lot of elaboration on parts that will only be relevant to specific setups and users, so don't let how long it is put you off. Anyway, Enjoy and may your server run awesome! :D [MEDIA=youtube]0CqMv0ucqFA[/MEDIA]
The my.cnf file template ### 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] ### ### 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. datadir = /var/lib/mysql ### This must point to the main MySQL data directory. ### ### General Server Options: ### max_allowed_packet = 32M ### Default packet limit is almost always too small. max_connections = 2000 ### Max connections as well (sleeping threads are okay to have) #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 = 16 ### 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 open_files_limit = 10000 ### Increase the number of file descriptors, we're not in stone age now. tmp_table_size = 64M max_heap_table_size = 64M ### Incease limits for implicit and explicit temporary tables. 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_type=0 query_cache_size=0 ### 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 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 = 7 ### 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: #key_buffer_size = 128M ### ### InnoDB Specific Options: ### #innodb_buffer_pool_size = 96G ### 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 = 2047M ### 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 = 4 ### 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 = 16 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 ### 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 = 1.0 ### Log queries that take > 1s to execute slow_query_log = 1 ### 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: ### END CONFIG FILE
To the OP of the my.cnf file I know you put this thing behind a email subwall so I apologise for posting it publically, but being one of my first posts here I don't want to be marked as spam for posting too many links and I'm still giving your video a massive plug on the top Google search for "optimise my.cnf" so I hope that makes up for it.