As every one knows that, Performance tuning is the prominent task of DBA’s. There could be different ways to do performance tuning,and you could see lot of blogs talking about couple of memory related variables to be tuned, and suggests that memory allocation should be in some % based on available memory.
I actually don’t believe that if we tune and allocate memory based on some % of selected variables is called the performance tuning or we can expert the performance as the docs says, its all depends on the kind of work load and the type of application and the system on which mysql is running.
Performance tuning is not just a task that you can do it in some time, actually it is a continuous process. As mentioned above there are different ways to do performance tuning, which comprises the following at a high level
1) Operating system tuning
2) Storage Tuning
3) MySQL Server Tuning
When it comes to Operating system and Storage side there could be different parameters that should consider, like the Operating system, server configuration (CPU,Memory,Disk .etc), type of storage (Local,SAN,NAS .etc) and type of application and workload etc. At this point of time I would like to focus on MySQL Server Tuning rather that Operating System and Storage Tuning, and very soon will write one post to cover those 2 topics with respect to MySQL.
Coming to the MySQL performance tuning, as discussed above performance tuning means allocating the right memory to the right parameters. We could see lot of blogs talking about performance tuning mainly focus on few MySQL parameters and % of memory to be allocate to each one of them in general. Just by allocating the memory to those parameters we can’t say that performance tuning is done or the DB server configured to with the best configuration, because that is just predicted configuration based on the type of application and the work load. Though the initial configuration performs better for your DB server while the data size growing the performance tuning issues will come for sure, thats the reason why I mention “Performance Tuning is a continuous process”.
You might be having a question that, then how to find out the bottleneck of MySQL server and do actual MySQL Performance Tuning. That was my question when I was started learning and now I have the answer and would like to share with you.
MySQL Server Performance Tuning : The best / right way of understanding performance tuning bottleneck of MySQL is to check the global status of MySQL Server and try to understand the status variables and the relationship between them, and start tuning corresponding variables.
Lets look at couple of MySQL status variables and understand some performance tuning tips.
InnoDB Buffer Pool Size : To decide whether the innodb buffer pool size is optimal or not for InnoDB workloads , look at the global status of these variables Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests calculate the % of reads that are going to disk and adjust the buffer pool size accordingly.
NOTE : Initially all the data has to read from the disk as part of warmup process you should consider that before calculate the % of disk reads, and you can’t provide as much memory as the DB size is the optimal to be selected based on the available resources and application workload. If 70-80% of reads served from the memory then that is good enough for a bigger Databases.
Key Buffer Size : To check whether key buffer size is optimal or not for MyISAM workload, look at the global status of Key_reads / Key_read_requests. As explained above if 70-80% of reads servers from the memory then it will be good for the bigger Databases.
InnoDB Log Buffer Size : To tune the optimal value for innodb log buffer, check the global status of Innodb_log_writes / Innodb_log_write_requests, calculate the % and adjust the log buffer size accordingly. Consider the limitation on configuring bigger innodb log buffers and tune accordingly.
Tmp Table Size / Max Heap Table Size : To get the optimal value for temporary table, check the global status of Created_tmp_disk_tables / Created_tmp_tables, calculate the % of tables gets created on the disk and tune this variable accordingly.
Thread Cache Size : To get the optimum value for the the thread cache, check the global status of Threads_created and adjust the threads_cache accordingly.
Table Open Cache : To get the optimum value for table open cache, check the global status of Open_tables / Opened_tables , calculate the % and adjust the Table Open Cache accordingly.
The same process applies to all other major variables, Make sure that you are completely understand how it works and the consequence of these changes then only touch the production systems.
I actually don’t believe that if we tune and allocate memory based on some % of selected variables is called the performance tuning or we can expert the performance as the docs says, its all depends on the kind of work load and the type of application and the system on which mysql is running.
Performance tuning is not just a task that you can do it in some time, actually it is a continuous process. As mentioned above there are different ways to do performance tuning, which comprises the following at a high level
1) Operating system tuning
2) Storage Tuning
3) MySQL Server Tuning
When it comes to Operating system and Storage side there could be different parameters that should consider, like the Operating system, server configuration (CPU,Memory,Disk .etc), type of storage (Local,SAN,NAS .etc) and type of application and workload etc. At this point of time I would like to focus on MySQL Server Tuning rather that Operating System and Storage Tuning, and very soon will write one post to cover those 2 topics with respect to MySQL.
Coming to the MySQL performance tuning, as discussed above performance tuning means allocating the right memory to the right parameters. We could see lot of blogs talking about performance tuning mainly focus on few MySQL parameters and % of memory to be allocate to each one of them in general. Just by allocating the memory to those parameters we can’t say that performance tuning is done or the DB server configured to with the best configuration, because that is just predicted configuration based on the type of application and the work load. Though the initial configuration performs better for your DB server while the data size growing the performance tuning issues will come for sure, thats the reason why I mention “Performance Tuning is a continuous process”.
You might be having a question that, then how to find out the bottleneck of MySQL server and do actual MySQL Performance Tuning. That was my question when I was started learning and now I have the answer and would like to share with you.
MySQL Server Performance Tuning : The best / right way of understanding performance tuning bottleneck of MySQL is to check the global status of MySQL Server and try to understand the status variables and the relationship between them, and start tuning corresponding variables.
Lets look at couple of MySQL status variables and understand some performance tuning tips.
InnoDB Buffer Pool Size : To decide whether the innodb buffer pool size is optimal or not for InnoDB workloads , look at the global status of these variables Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests calculate the % of reads that are going to disk and adjust the buffer pool size accordingly.
NOTE : Initially all the data has to read from the disk as part of warmup process you should consider that before calculate the % of disk reads, and you can’t provide as much memory as the DB size is the optimal to be selected based on the available resources and application workload. If 70-80% of reads served from the memory then that is good enough for a bigger Databases.
Key Buffer Size : To check whether key buffer size is optimal or not for MyISAM workload, look at the global status of Key_reads / Key_read_requests. As explained above if 70-80% of reads servers from the memory then it will be good for the bigger Databases.
InnoDB Log Buffer Size : To tune the optimal value for innodb log buffer, check the global status of Innodb_log_writes / Innodb_log_write_requests, calculate the % and adjust the log buffer size accordingly. Consider the limitation on configuring bigger innodb log buffers and tune accordingly.
Tmp Table Size / Max Heap Table Size : To get the optimal value for temporary table, check the global status of Created_tmp_disk_tables / Created_tmp_tables, calculate the % of tables gets created on the disk and tune this variable accordingly.
Thread Cache Size : To get the optimum value for the the thread cache, check the global status of Threads_created and adjust the threads_cache accordingly.
Table Open Cache : To get the optimum value for table open cache, check the global status of Open_tables / Opened_tables , calculate the % and adjust the Table Open Cache accordingly.
The same process applies to all other major variables, Make sure that you are completely understand how it works and the consequence of these changes then only touch the production systems.
MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers
