{"id":24,"date":"2008-10-19T23:00:59","date_gmt":"2008-10-19T21:00:59","guid":{"rendered":"http:\/\/vladimir-shapiro.com\/en\/blog\/2008\/10\/23\/mysql-parameters-performance-optimization\/"},"modified":"2016-08-18T11:33:27","modified_gmt":"2016-08-18T09:33:27","slug":"mysql-parameters-performance-optimization","status":"publish","type":"post","link":"https:\/\/vladimir-shapiro.com\/en\/blog\/2008\/10\/19\/mysql-parameters-performance-optimization\/","title":{"rendered":"MySQL Parameters: Performance Optimization"},"content":{"rendered":"<p>The first part of this post was devoted to the <a href=\"http:\/\/vladimir-shapiro.com\/en\/blog\/2007\/11\/14\/mysql-query-optimization\/\">tools for SQL optimization in MySQL<\/a>. Here I would like to stop at several database parameters which are relevant for MySQL performance.<\/p>\n<p>There are at least three areas to take a look at: Thread Cache, Table Cache and Query Cache.<\/p>\n<p><!--more--><\/p>\n<p><strong>Thread Cache<\/strong><\/p>\n<p>According to MySQL manual it is recommended to use a persistent connection (function pconnect())  in your applications. If it is not possible for some reason, you have to pay attention how many database threads are cached.<\/p>\n<p>Check the following parameters:<\/p>\n<ul>\n<li><strong>threads_cached<br \/>\n<\/strong>number of threads reserved<\/li>\n<li><strong>threads_created<br \/>\n<\/strong>number of threads, create since database start<\/li>\n<li><strong>max_connections<\/strong><br \/>\nmaximum number of connections at the same time since db start<\/li>\n<\/ul>\n<p>Sample Commands:<\/p>\n<pre>\r\nmysql&gt; show status like 'threads_%';mysql&gt; show variables like 'max_connections';<\/pre>\n<p>You can also use the commands like these to check other parameters listed below.<\/p>\n<p><strong>Table Cache<\/strong><\/p>\n<ul>\n<li><strong>table_cache<\/strong><br \/>\nnumber of tables in the cache<\/li>\n<li><strong>open_tables<\/strong><br \/>\ncurrently opened tables<\/li>\n<li><strong>opened_tables<\/strong><br \/>\nnumber of tables opened since db start<\/li>\n<li><strong>uptime<\/strong><br \/>\ntime in seconds since db start<\/li>\n<\/ul>\n<p>First check the first two variables. If the number of open_tables is equal to tabel_cache it a sign that you table cache is full. It is not bad, however if you see it combined with the growing number of opened_tables (simpy do a refresh several times) combined with a short uptime &#8211; this could mean your table buffer is not sufficient. Ian Gilfillan provides here a <a href=\"http:\/\/www.databasejournal.com\/features\/mysql\/article.php\/10897_1402311_3\/Optimizing-MySQL-Hardware-and-the-Mysqld-Variables.htm\">detailed explanation with different scenarios<\/a>.<\/p>\n<p><strong>Query Cache<\/strong><\/p>\n<p>Similar to its Big Brothers Oracle and DB2, MySQL also has a query cache. tores the text of a SELECT statement together with         the corresponding result that was sent to the client. The query cache is extremely useful in an environment where you         have tables that do not change very often and for which the server receives many identical queries. Read more on this in the <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/query-cache.html\">official documentation<\/a>.<\/p>\n<p>The most interesting variables for performance monitoring are:<\/p>\n<ul>\n<li> <strong>Qcache_hits<\/strong><br \/>\nCombined with the total number of SELECT-queries sent,  it gives us an impression about the cache efficiency. The normal hit ratio on the well-tuned system shouldn&#8217;t be below 98%. Please take a look to the official docs to find out <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/query-cache-status-and-maintenance.html\">how to calculate the total number of selects sent to MySQL server<\/a>.<\/li>\n<li><strong>Qcache_total_blocks<\/strong> and <strong>Qcache_free_blocks<\/strong><br \/>\nAs the query cache uses variable-length blocks these two variables can indicate a query cache fragmentation. Make a <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/flush.html\">FLUSH QUERY CACHE<\/a> to get one single free block and monitor the changes (e.g. growth of the free blocks) during the database runtime.<\/li>\n<li>           <strong>Qcache_lowmem_prunes<\/strong><br \/>\nThis status           variable can help to tune the query cache size. It counts the           number of queries that have been removed from the cache to           free up memory for caching new queries.<\/li>\n<\/ul>\n<p>The relevant variables could be obtained with the following commands (result samples included):<\/p>\n<pre>\r\nmysql&gt; SHOW VARIABLES LIKE 'query_cache%';\r\n\r\n+------------------------------+---------+\r\n| Variable_name                | Value   |\r\n+------------------------------+---------+\r\n\r\n| query_cache_limit            | 1048576 |\r\n\r\n| query_cache_min_res_unit     | 4096    |\r\n\r\n| query_cache_size             | 8388608 |\r\n\r\n| query_cache_type             | ON      |\r\n\r\n| query_cache_wlock_invalidate | OFF     |\r\n\r\n+------------------------------+---------+<\/pre>\n<pre>mysql&gt; SHOW STATUS LIKE 'Qcache%';\r\n\r\n+-------------------------+-----------+\r\n| Variable_name           | Value     |\r\n+-------------------------+-----------+\r\n\r\n| Qcache_free_blocks      | 852       |\r\n\r\n| Qcache_free_memory      | 2758736   |\r\n\r\n| Qcache_hits             | 191931713 |\r\n\r\n| Qcache_inserts          | 41449892  |\r\n\r\n| Qcache_lowmem_prunes    | 14429263  |\r\n\r\n| Qcache_not_cached       | 93857     |\r\n\r\n| Qcache_queries_in_cache | 2186      |\r\n\r\n| Qcache_total_blocks     | 5436      |\r\n\r\n+-------------------------+-----------+<\/pre>\n<p>Please not that this overview doesn&#8217;t pretend for a comprehensive coverage of all performance tuning measures related to MySQ. I&#8217;ve taken these variables based on my experience to provide you the starting points for further analysis.  Please take a look to the knowledge sources below, which I&#8217;ve used for creating this material:<\/p>\n<ul>\n<li><a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/optimizing-the-server.html\">Optimizing the MySQL Server<\/a><br \/>\nOfficial documentation on the subject.<\/li>\n<li><a href=\"http:\/\/www.databasejournal.com\/features\/mysql\/article.php\/10897_1402311_3\/Optimizing-MySQL-Hardware-and-the-Mysqld-Variables.htm\">Optimizing MySQL: Hardware and the Mysqld Variables<br \/>\n<\/a>Very good article by Ian Gilfillan, written in 2001 but remains topical<a href=\"http:\/\/www.databasejournal.com\/features\/mysql\/article.php\/10897_1402311_3\/Optimizing-MySQL-Hardware-and-the-Mysqld-Variables.htm\"><br \/>\n<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>The first part of this post was devoted to the tools for SQL optimization in MySQL. Here I would like to stop at several database parameters which are relevant for MySQL performance. There are at least three areas to take a look at: Thread Cache, Table Cache and Query Cache.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[6],"tags":[21],"_links":{"self":[{"href":"https:\/\/vladimir-shapiro.com\/en\/blog\/wp-json\/wp\/v2\/posts\/24"}],"collection":[{"href":"https:\/\/vladimir-shapiro.com\/en\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vladimir-shapiro.com\/en\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vladimir-shapiro.com\/en\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/vladimir-shapiro.com\/en\/blog\/wp-json\/wp\/v2\/comments?post=24"}],"version-history":[{"count":1,"href":"https:\/\/vladimir-shapiro.com\/en\/blog\/wp-json\/wp\/v2\/posts\/24\/revisions"}],"predecessor-version":[{"id":120,"href":"https:\/\/vladimir-shapiro.com\/en\/blog\/wp-json\/wp\/v2\/posts\/24\/revisions\/120"}],"wp:attachment":[{"href":"https:\/\/vladimir-shapiro.com\/en\/blog\/wp-json\/wp\/v2\/media?parent=24"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vladimir-shapiro.com\/en\/blog\/wp-json\/wp\/v2\/categories?post=24"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vladimir-shapiro.com\/en\/blog\/wp-json\/wp\/v2\/tags?post=24"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}