The main reason to optimize the MySQL server is to use minimum server resources and give maximum output. This blog will help to understand the factors that plays role in optimizing the MySQL server.
To manually optimize the MySQL database server, there are several factors that needs to be properly configured in it. Values set in those factors are very much important to get MySQL database server optimized otherwise it will create negative impact on the server.
You can tweak MySQL server configuration and get the performance as under:
- Slow query log – If enabled, it generates the logs according to the time set in. The query which took more time than the time set in slow query log will be recorded.
- Max connections – maximum number of connections that are allowed to connect to the server.
- Memory Usage – maximum amount of memory that MySQL can use.
- Key Buffer – used in indexing of blocks mostly in MyISAM specific.
- Query Cache – stores results of select query to use it in future if same types of query come in future for faster processing.
- Table cache – number of open tables that stores in cache.
An example of a MySQL server with 4 GB and quad core installed can get us superior performance using below configuration.
The above values will be a quick starter for your MySQL server optimization. There are other factors too which can be tweaked to optimize performance of MySQL server, which I will cover in further detail in my next blog.