MySQL server configuration
MySQL server configuration
December 23, 2022
Logs
About slow queries
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow_query.log
long_query_time = 0
log_queries_not_using_indexesThe field long_query_time is the time to output a query as a slow log. The meanings of other fields are obvious.
Performance
Recommended configuration
configurations should be changed based on server or application resources.
[mysqld]
# The max query size
query_cache_limit=16M
# The memory size for query cache
query_cache_size=512M
# The type of query cache (0:off, 1:ON except SELECT SQL_NO_CACHE, 2:only DEMAND SELECT SQL_CACHE)
query_cache_type=1
# The max size to open files simultanously
table_open_cache = 1M
# The buffer when using sort
sort_buffer_size=4M
# The buffer to cache rows for sorting by keys
read_rnd_buffer_size=2MCache
Query cache
[mysqld]
# The max query size
query_cache_limit=16M
# The memory size for query cache
query_cache_size=512M
# The type of query cache (0:off, 1:ON except SELECT SQL_NO_CACHE, 2:only DEMAND SELECT SQL_CACHE)
query_cache_type=1
# The max size to open files simultanously
table_open_cache = 1MSee following pages for more details.
- Performance Tuning (in Japanese)
- Table cache (in Japanese)
Buffer
Buffer sizes
[mysqld]
# The buffer when using sort
sort_buffer_size=4M
# The buffer to cache rows for sorting by keys
read_rnd_buffer_size=2MSee following pages for more details
- Performance Tuning (in Japanese)
SQL mode
Traditional SQL mode
It’s equal to followings after 5.7.7
- STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION
Strict SQL mode
Configuration
STRICT_TRANS_TABLE: Strict mode
Last updated on