This post will discuss tips and strategies for creating quick and dependable MySQL databases. All of the ideas and recommendations in this post have been compiled from the extensive expertise of our professionals who have worked with massive MySQL databases for an extended period of time.
- OPTIMIZE TABLE
While this seems to be a straightforward procedure, the majority of MySQL newbies do not execute this command correctly. When programmers do several adjustments, such as inserting, updating, and deleting data, the table’s physical storage on the hard disk gets fragmented. OPTIMIZE TABLE may defragment data on a high level without requiring the use of specialized hard disk defragmentation software by completing the following actions:
- Table restoration in the event that the contents are destroyed
- If necessary, index pages will be sorted
- If necessary, updating statistical data
OPTIMIZE TABLE must be used after a substantial volume of data deletion or following data manipulation commands like as INSERT or UPDATE that operate on variable-size data (VARCHAR, BLOB or TEXT). One critical point to keep in mind is that this command locks the table.
- Query Cache
MySQL provides a mechanism for caching the results of numerous SELECT queries. Caching reduces the time required to get the results of a query, such that the next time the query is executed, it will not contact the MySQL server, but will instead retrieve previously stored data from the cache.
MySQL does not cache queries that return non-static data:
- queries that make use of system methods:NOW(), CURDATE(), CURRENT USER(), and CONNECTION ID ()
- functions specified by the user
- stored procedures
Set the ‘query cache size’ system variable to 0 to deactivate the query cache. Due to the fact that the default size is zero, the query cache is deactivated by default. The cache size should not be increased since big caches generate lock contention concerns owing to the necessity for threads to lock the cache during updates.
If you provide a number other than zero for ‘query cache size,’ bear in mind that the query cache requires a minimum size of around 40KB to create its structures.
For further information on customizing MySQL’s query cache, see the following:
http://dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html
- How to Log Slow MySQL Queries
Often, it is far more difficult to identify “problem” queries than it is to optimize them. To overcome this issue, MySQL has a function called “logging slow queries.”
To enable this functionality, follow these steps:
- Open the configuration file “my.cnf”
- Conduct a search for “slow”. This will take you to the area for tracking sluggish requests, which looks like this:
# This section contains queries with unusually large durations.
#log slow queries = /var/log/mysql/mysql-slow.log
#long query time = 2
#log-queries-not-using-indexes
- Uncomment “log slow queries” and “long query time” parameters by removing #
- Adjust the value of “long query time” appropriately. It is very dependent on your environment; for example, our system runs on 8.
- Restart the server after making changes to my.cnf.
- After restarting the server, the “mysql-slow.log” file will include information about slow queries. This file is found in the following directory on Linux platforms: /var/log/mysql/mysql-slow.log
- The following command may also be used to monitor sluggish MySQL queries:’showprocesslist’.
More articles about MySQL are available at https://www.convert-in.com/docs/mysql/contents.htm
