Performance Tuning in MySQL
Why we changing mysql default configuration?
- Do not even attempt to use the default MySQL configuration. It won’t be enough; the default config file is really weak and small.
1. Enable InnoDB file-per-table SolutionStore
2. MySQL Database Data on Separate Partition
3. Optimizing InnoDB buffer pool Usage
4. Set MySQL Max Connections
5. Avoid Swappiness in MySQL
Daily Activity Checklist
- Database size check command.
SELECT table_schema AS “Database”, SUM(data_length + index_length) / 1024 / 1024 / 1024 AS “Size (GB)” FROM information_schema.TABLES GROUP BY table_schema
- Slow query log need to set in all server for checking long Queries.
- Finding query block if block remains for more than one hour. We want to consider the query with the respective user and kill the query.
- Monitoring tool for mysql server if nagios is there. We need separate page for monitoring mysql servers. ( Monitoring memory, cpu, ram, network and services ).
5. Enable MySQL Slow query Logs
Logging slow queries can help you determine issues with your database and help you debug them. This can be easily enabled by adding the following values in your MySQL configuration file:
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
long_query_time = 1
The first directive enables the logging of slow queries, while the second one tells MySQL where to store the actual log file. Use long_query_time to define the amount of time that is considered long for MySQL query to be completed.