Valuable Guidelines For Performance Tuning Of MySQL Database Server
Any dynamically served website with enormous traffic and visitors normally depends on a strong and powerful Database Management System (DBMS) for storing data which can be reclaimed or retrieved by web servers in order to build web pages for the website visitors to read.
MySQL is one such Database Management System that is highly popular and widely used. Every time a visitor visits a website, the MySQL DB server is accessed at least once by the web server so as to assemble and display the web page to the reader.
It is very important that the MySQL server gives a superior performance which in turn improves the speed of the website, especially if the web host is loaded with traffic. Therefore, it is essential to learn the skill of performance enhancement of MySQL database server.
During one of the Google TechTalks, Jay Pipes who is the Relations Manager of MySQL Community for North America, gave a talk on the finest techniques and practices which can be implemented for enhancing MySQL server performance.
Jay Pipes is also the co-author of Pro MySQL which was published in 2005 by Apress. Pro MySQL comes with all the latest features of MySQL 5 along with a thorough analysis and discussion of MySQL server architecture, transaction processing, storage engines, benchmarking and other scenarios of advanced SQL.
Even though the talk by Jay Pipes is an old one, it definitely provides some valuable information on MySQL performance tuning. In the talk, Jay Pipes has spoken about the key areas that you need to focus on while fine-tuning the performance of database servers and applications.
He has also explained how webmasters or database administrators can get the best results by applying some useful intermediate and complex MySQL skills.
The topics discussed in the talk are mentioned as follows:
Core Concepts of Benchmarking and Profiling – Benchmarking provides you with a track record. While benchmarking, change just one thing at a time and re-run the benchmarks in order to efficiently keep a track of the outcome of the change made. Record everything related to the benchmarks including the configuration files, information on OS, hardware, MySQL etc.
Particularly with MySQL, remember to disable query cache every time you run the benchmarks. Profiling helps in diagnosing a running system. Get familiar with the access types and make use of Slow query Log and mysqldumpslow. Make use of mytop in order to catch long-running queries.
Common Issues/ Problems with Performance – The various sources of problems include poor indexing choices, inefficient schema design, improper coding practices, improper tuning of server variables and hardware or/and network bottlenecks. These issues need to be properly focused on and resolved.
Schema issues can be resolved and faster performance can be achieved by using smaller data types, narrower index records, more records per block and fewer reads. Making use of proper indexing (i.e. covering index opportunities, proper selection of index fields, removing redundant indexes, etc) plays an important role in performance enhancing.
Following proper coding guidelines (i.e. using stored procedures, using counter tables and calculated fields, using joins, eliminating correlated sub queries etc) is equally necessary.
Tuning of Server Parameters – Tuning of server parameters provides a speedy solution but a temporary one. Having awareness about global and per thread parameters is very much necessary. Try making small changes at a time and test to check the effect. Turn the query cache on and give it a size. Within the configuration files, set a size for “query_cache_size”.
Maximum memory has to be allocated for saving data and index pages. Memory is an affordable, fastest and easiest way to enhance MySQL performance.
You can watch the Video here:
If you are using Firefox, then you can download the video with the help of this extension.