Thursday, July 31, 2014

Must read topics before working on performance improvement of MySQL based applications

Are you going to work on improving performance of an MySQL based applications? If yes, this post is for you.
MySQL has a very good documentation. On this left panel of this page, you will find reference manual list for each available version. If you are reading something in MySQL-5.1 and want to check the same topic in MySQL-5.6 you have to just change a single value in URL. Below is the example:
Below page describe the server status variable in MySQL-5.1
http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html
while below page describe the server status variable in MySQL-5.6
http://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html
There is a single difference between these two URLs (5.1 Vs 5.6)

I am providing below URL list which must be covered before or during performance improvement project. I am mentioning for 5.6, if required you can change the version in URL:

1. List of URLs for MySQL Performance Tuning: Benchmarks, Best Practices, FAQs
http://forums.mysql.com/read.php?24,92131,92131#msg-92131
2. Find information about all status variables
http://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html
3. Find information about all server system variables
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html
4. MySQL Glossary - very important to understand the database terms
http://dev.mysql.com/doc/refman/5.6/en/glossary.html
5. Optimization - contains multiple chapters
http://dev.mysql.com/doc/refman/5.6/en/optimization.html
6. Understanding Query by Explain command
http://dev.mysql.com/doc/refman/5.6/en/using-explain.html
7. Partition Management
http://dev.mysql.com/doc/refman/5.6/en/partitioning-management.html
8. Server SQL modes
http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html
9. How MySQL Uses Threads for Client Connections
http://dev.mysql.com/doc/refman/5.6/en/connection-threads.html
10. How MySQL Opens and Closes Tables
http://dev.mysql.com/doc/refman/5.6/en/table-cache.html
11. General Thread States
http://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html
12. Thread Command Values
http://dev.mysql.com/doc/refman/5.6/en/thread-commands.html
13. Query Cache Thread States
http://dev.mysql.com/doc/refman/5.6/en/query-cache-thread-states.html
14. The MySQL Query Cache
http://dev.mysql.com/doc/refman/5.6/en/query-cache.html
15. MySQL Cluster Overview - only if you have implemented  MySQL cluster database
http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-overview.html
16. MySQL option files - information about configuration file and group mentioned in that file
http://dev.mysql.com/doc/refman/5.6/en/option-files.html
17. Query Cache Status and Maintenance
http://dev.mysql.com/doc/refman/5.6/en/query-cache-status-and-maintenance.html
18. Query Cache Configuration
http://dev.mysql.com/doc/refman/5.6/en/query-cache-configuration.html
19. How MySQL Uses Internal Temporary Tables
http://dev.mysql.com/doc/refman/5.6/en/internal-temporary-tables.html
20. Optimizing Disk I/O
https://dev.mysql.com/doc/refman/5.6/en/disk-issues.html

Hopefully you would have enough understanding after going through these document pages of MySQL. Feel free to drop your valuable comment if you want to discuss about anything.





No comments:

Post a Comment

Thanks for your valuable comments.

Important queries based formulas to watch performance of a MySQL based application

(1) Session Vs global status variables value select G.variable_name,G.variable_value GlobalValue,S.variable_value SessionValue from (selec...