Configuring MySQL
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%';