Performance Tuning in MySQL

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

 

  1. 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

 

  1. Slow query log need to set in all server for checking long Queries.

 

  1. 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.

 

 

  1. 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.

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s