Create a tmp folder inside the /var/lib/mysql/ directory in case this is the only location configured on an SSD disk.
Set the correct permissions:
|
1 2 3 |
chown mysql:mysql /var/lib/mysql/tmp chmod 750 /var/lib/mysql/tmp |
Create the mysql-slow.log file inside the /var/log/ directory for logging slow queries.
Set the correct permissions:
|
1 2 3 |
chown mysql:mysql /var/log/mysql-slow.log chmod 640 /var/log/mysql-slow.log |
Optimized my.cnf:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 |
[mysqld] # ====================== # BASIC # ====================== tmpdir = /var/lib/mysql/tmp max_allowed_packet = 64M local_infile = 0 skip_name_resolve = 1 skip_host_cache max_connect_errors = 1000000 # ====================== # INNODB # ====================== innodb_buffer_pool_size = 36G innodb_log_file_size = 1G innodb_log_buffer_size = 64M innodb_flush_method = O_DIRECT innodb_flush_log_at_trx_commit = 1 innodb_io_capacity = 2000 innodb_io_capacity_max = 4000 innodb_flush_neighbors = 0 innodb_read_io_threads = 4 innodb_write_io_threads = 4 innodb_purge_threads = 4 innodb_file_per_table = 1 innodb_lock_wait_timeout = 50 innodb_max_dirty_pages_pct = 75 innodb_max_dirty_pages_pct_lwm = 10 # ====================== # CONNECTIONS # ====================== # Slightly more conservative without DB Governor. max_connections = 150 thread_cache_size = 128 back_log = 256 wait_timeout = 300 interactive_timeout = 600 # ====================== # TEMP TABLES # ====================== tmp_table_size = 64M max_heap_table_size = 64M # ====================== # PER-CONNECTION BUFFERS # ====================== sort_buffer_size = 2M join_buffer_size = 2M read_buffer_size = 1M read_rnd_buffer_size = 1M # ====================== # MYISAM # ====================== key_buffer_size = 1G concurrent_insert = 2 myisam_recover_options = BACKUP,FORCE # ====================== # TABLE CACHE # ====================== table_open_cache = 8000 table_definition_cache = 8000 table_open_cache_instances = 16 open_files_limit = 80000 # ====================== # QUERY CACHE # ====================== query_cache_type = 0 query_cache_size = 0 # ====================== # LOGGING # ====================== slow_query_log = 1 slow_query_log_file = /var/log/mysql-slow.log long_query_time = 3 min_examined_row_limit = 100 log_queries_not_using_indexes = 0 # ====================== # DURABILITY # ====================== sync_binlog = 1 # ====================== # MONITORING # ====================== performance_schema = ON |
After applying the changes, restart MySQL.