/
Configuring MySQL

Configuring MySQL

Owned by bcarlsen (Unlicensed)
Last updated: Oct 23, 2020 by Rick Wood

Overview

Documents configuration parameters and settings for optimizing MySQL.

Details

There are some parameter settings in the "my.ini" or "my.cnf" that may help optimize performance.  Following are recommended values assuming use of "innodb" storage engine.

[mysqld]
# Set timeouts
wait_timeout = 230400
interactive_timeout = 230400

# use a file per table (easier to manage space this way)
innodb_file_per_table

# memory usage (make as big as you can)
innodb_buffer_pool_size=512M
 
# flushes once per second rather than every commit
innodb_flush_log_at_trx_commit=0
 
# Log file settings
innodb_log_buffer_size=8M
innodb_log_file_size=1024M

# disable the query cache
query_cache_size=0
#  make sure tables are not case-sensitive
lower_case_table_names=1
 
# this is mostly for importing mysql dump data, packets may be very large
max_allowed_packet      = 32M


Finding the my.ini or my.cnf can be a little tricky.  Here are some locations where we've found the file:

  • Linux
    • /etc/my.cnf
    • /etc/mysql/my.cnf
  • Windows
    • C:\ProgramData\MySQL\MySQL Server 5.6\my.ini


Make sure you bounce the server after making changes.  Double-check your new settings using:

show variables like '%innodb%';


Related content