I talked about the different schemas used by MySQL and briefly mentioned why you might want to turn off MySQL DNS lookups and the configuration variable to do that.
Last week I began to talk about MySQL as part of a larger series about the performance of dynamic websites. When it comes to MySQL, you’ll probably get along just fine if you never change a thing, but you can probably do better than the defaults with a few tweaks. Other times they aren’t and you need to make a few changes. New MySQL fine-tuned settings will be activated after MySQL server restart.Sometimes the defaults that come with a product are exactly what you want. Of 1392672 temp tables, 23% were created on diskĪfter this you will need to change your MySQL configuration file (my.cnf) according to Tuning script recommendations and restart MySQL engine: You should probably increase your table_definition_cache value. You should probably increase your table_cache
Your open_files_limit value seems to be fineĬurrent table_definition_cache = 256 tablesĬurrent table_cache hit rate is 0%, while 100% of your table cache is in use That of table_cache if you have heavy MyISAM usage. The open_files_limit should typically be set to at least 2x-3x Join_buffer_size to accommodate larger joins in one pass. If you are unable to optimize your queries you may want to increase your Then look for non indexed joins in the slow query log. You should enable "log-queries-not-using-indexes" You have had 16 joins without keys that check for key usage after each row You have had 36574 queries where a join could not use an index properly Perhaps you should raise query_cache_size MySQL won't cache query results that are larger than query_cache_limit in size However, 2878688 queries have been removed from the query cache due to lack of memory Max memory limit seem to be within acceptable normsĬurrent Query cache Memory fill ratio = 96.30 % To increase this value to up to 2 / 3 of total system memoryĬonfigured Max Per-thread Buffers : 1.31 G Your max_connections variable seems to be fine.ĭepending on how much space your innodb indexes take up it may be safe The number of used connections is 13% of the configured maximum.
You will not be able to do point in time recovery You have 53 out of 20231077 that take longer than 10 sec. It should be safe to follow these recommendations Here is a real script output for a Magento installation on dedicated server:
Enter MySQL access from your Magento store (you can find it in Magento configuration file: /app/etc/local.xml).Ĥ - After script execution it will show you optimization recommendations.
How to speed up your Magento database using the Tuning Primer shell scriptĢ - Upload it to your server and execute it using SSH console:ģ - Script will ask MySQL login and password. What settings could be fine-tuned using the Tuning Primer scriptĬurrently the script handles recommendations for the following: Tuning Primer script is compatible with all versions of MySQL starting from 3.23. Only in this case there will be enough information for analysis. The uptime of your MySQL should be more than 48 hours for correct script work. The script will analyze MySQL statistics and provide you with recommendations on how to fine-tune your MySQL server. The Tuning Primer is a shell script that allows you to review the MySQL settings and make adjustments to increase Magento database performance and stability.