{"id":227,"date":"2009-11-16T21:41:28","date_gmt":"2009-11-16T21:41:28","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=227"},"modified":"2023-08-21T10:20:56","modified_gmt":"2023-08-21T10:20:56","slug":"calculte-mysql-memory-usage-quick-stored-proc","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/calculte-mysql-memory-usage-quick-stored-proc","title":{"rendered":"Calculate MySQL Memory Usage \u2013 Quick Stored Procedure"},"content":{"rendered":"\n<p>In this post we will look into the MySQL memory utilization estimation or calculation based on the global variables. Using a simple stored procedure call you can get the memory usage estimation for the present MySQL instance.<\/p>\n\n\n\n<p>We have global buffers which are allocated irrespective of connections as and when mysql&nbsp;server is started. Along with that mysql server allocates memory to each thread to perform respective tasks.<\/p>\n\n\n\n<p>So the formula goes:<\/p>\n\n\n\n<p>Mysql Server Memory Usage = Sum of Global Buffers + (number of Connection * Per thread memory variables).<br><\/p>\n\n\n\n<!--more Continue Reading...-->\n\n\n\n<p><br><strong>Global buffers include:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>key_buffer_size: key_buffer_size is the size of the buffer used for index blocks.<\/li>\n\n\n\n<li>innodb_buffer_pool_size: The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables.<\/li>\n\n\n\n<li>innodb_additional_mem_pool_size: The size in bytes of a memory pool InnoDB uses to store data dictionary information and other internal data structures.<\/li>\n\n\n\n<li>innodb_log_buffer_size: The size in bytes of the buffer that InnoDB uses to write to the log files on disk.<\/li>\n\n\n\n<li>query_cache_size: The amount of memory allocated for caching query results.<\/li>\n<\/ul>\n\n\n\n<p><strong>Each thread for client connection uses:<\/strong><\/p>\n\n\n\n<p>thread_stack &#8211; The stack size for each thread.<br>net_buffer_length &#8211; conncetion buffer<br>max_allowed_packet &#8211; up to this size net_buffer_length can grow<br>read_buffer_size &#8211; used for sequential table scan<br>rean_rnd_buffer_size &#8211; used for random read buffer \/ sorting<br>tmp_table_size &#8211; temporary \/ hash tables in mysql<br>sort_buffer_size &#8211; for sorting<\/p>\n\n\n\n<p><strong>Per thread variables include:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>read_buffer_size: Buffer memory used for sequential table scan.<\/li>\n\n\n\n<li>read_rnd_buffer_size: Memory used for random read buffer \/ sorting.<\/li>\n\n\n\n<li>sort_buffer_size: Memory allocated for sorting, Group By, Order By.<\/li>\n\n\n\n<li>join_buffer_size: The size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans.<\/li>\n\n\n\n<li>thread_stack: The stack size for each thread.<\/li>\n\n\n\n<li>net_buffer_length: Conncetion buffer<\/li>\n\n\n\n<li>max_allowed_packet: Up to this size net_buffer_length can grow.<\/li>\n<\/ul>\n\n\n\n<p>Note that,<\/p>\n\n\n\n<p>If size increases or if table have blog columns, instead of heap tables on-disk tables created.<\/p>\n\n\n\n<p>Memories for variables read_buffer_size, sort_buffer_size, read_rnd_buffer_size, tmp_table_size are allocated as &amp; when required. They are also de-allocated once the task is accomplished.<\/p>\n\n\n\n<p>Of course this barely gives you an idea regarding MySQL Server memory usage!<\/p>\n\n\n\n<p>Below is a stored procedure to calculate the same estimates of memory consuption using Global Varibles.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\"><a href=\"http:\/\/kedar.nitty-witty.com\/wp-content\/uploads\/2015\/02\/mysql-memory-utilization.sql\" target=\"_blank\" rel=\"noopener\">Download MySQL Memory Utilization Stored Procedure<\/a>.<\/h1>\n\n\n\n<p><a><strong>Note: <\/strong><\/a><span style=\"text-decoration: underline;\">This will work for MySQL 5.0.*. For MySQL 5.1.* it will throw error 1064.<\/span><\/p>\n\n\n\n<p>Consider following bug report:\u00a0<a href=\"http:\/\/bugs.mysql.com\/bug.php?id=49758\" target=\"_blank\" rel=\"noopener nofollow\" title=\"\">http:\/\/bugs.mysql.com\/bug.php?id=49758<\/a> ]<\/p>\n\n\n\n<p><a><strong>Solution for MySQL 5.1+:<\/strong><\/a><\/p>\n\n\n\n<p><strong><span style=\"font-weight: normal;\">Consider changing the cursor declaration from <\/span><\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><span style=\"color: #808080;\"><em>DECLARE CUR_GBLVAR CURSOR FOR&nbsp;SHOW GLOBAL VARIABLES;<\/em><\/span><\/code><\/pre>\n\n\n\n<p>to<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><span style=\"color: #808080;\"><em>DECLARE CUR_GBLVAR CURSOR FOR SELECT * FROM information_schema.GLOBAL_VARIABLES;<\/em><\/span><\/code><\/pre>\n\n\n\n<p>And the stored procedure will work fine.<\/p>\n\n\n\n<p><strong>For calculating MySQL Memory Usage:<\/strong><\/p>\n\n\n\n<p>Create Following Stored Procedure and execute.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">mysql&gt; call my_memory();\n\n+---------------------+------------+\n\n| Parameter           | Value (M)  |\n\n+---------------------+------------+\n\n| Global Buffers      | 531 M      |\n\n| Per Thread          | 1.890625 M |\n\n| Maximum Connections | 160        |\n\n| Total Memory Usage  | 833.5 M    |\n\n| + Per Heap Table    | 16 M       |\n\n| + Per Temp Table    | 26 M       |\n\n+---------------------+------------+\n<\/pre>\n\n\n\n<p># Code MySQL Memory Utilization &#8211; Stored procedure.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">DELIMITER $\n\nDROP PROCEDURE IF EXISTS `my_memory` $\nCREATE PROCEDURE `my_memory` ()\nBEGIN\n\nDECLARE var VARCHAR(100);\nDECLARE val VARCHAR(100);\nDECLARE done INT;\n\n#Variables for storing calculations\nDECLARE GLOBAL_SUM DOUBLE;\nDECLARE PER_THREAD_SUM DOUBLE;\nDECLARE MAX_CONN DOUBLE;\nDECLARE HEAP_TABLE DOUBLE;\nDECLARE TEMP_TABLE DOUBLE;\n\n#Cursor for Global Variables\n\n#### For &lt; MySQL 5.1 \n#### DECLARE CUR_GBLVAR CURSOR FOR SHOW GLOBAL VARIABLES;\n\n#### For MySQL 5.1+\nDECLARE CUR_GBLVAR CURSOR FOR SELECT * FROM information_schema.GLOBAL_VARIABLES;\n#### Ref: http:\/\/bugs.mysql.com\/bug.php?id=49758\n\nDECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;\n\n\nSET GLOBAL_SUM=0;\nSET PER_THREAD_SUM=0;\nSET MAX_CONN=0;\nSET HEAP_TABLE=0;\nSET TEMP_TABLE=0;\n\nOPEN CUR_GBLVAR;\n\nmylp:LOOP\n      FETCH CUR_GBLVAR INTO var,val;\n  IF done=1 THEN\n    LEAVE mylp;\n  END IF;\n    IF var in ('key_buffer_size','innodb_buffer_pool_size','innodb_additional_mem_pool_size','innodb_log_buffer_size','query_cache_size') THEN\n    #Summing Up Global Memory Usage\n      SET GLOBAL_SUM=GLOBAL_SUM+val;\n    ELSEIF var in ('read_buffer_size','read_rnd_buffer_size','sort_buffer_size','join_buffer_size','thread_stack','max_allowed_packet','net_buffer_length') THEN\n    #Summing Up Per Thread Memory Variables\n      SET PER_THREAD_SUM=PER_THREAD_SUM+val;\n    ELSEIF var in ('max_connections') THEN\n    #Maximum allowed connections\n      SET MAX_CONN=val;\n    ELSEIF var in ('max_heap_table_size') THEN\n    #Size of Max Heap tables created\n      SET HEAP_TABLE=val;\n    #Size of possible Temporary Table = Maximum of tmp_table_size \/ max_heap_table_size.\n    ELSEIF var in ('tmp_table_size','max_heap_table_size') THEN\n      SET TEMP_TABLE=if((TEMP_TABLE&gt;val),TEMP_TABLE,val);\n    END IF;\n\nEND LOOP;\nCLOSE CUR_GBLVAR;\n#Summerizing:\nselect \"Global Buffers\" as \"Parameter\",CONCAT(GLOBAL_SUM\/(1024*1024),' M') as \"Value\" union\nselect \"Per Thread\",CONCAT(PER_THREAD_SUM\/(1024*1024),' M')  union\nselect \"Maximum Connections\",MAX_CONN union\nselect \"Total Memory Usage\",CONCAT((GLOBAL_SUM + (MAX_CONN * PER_THREAD_SUM))\/(1024*1024),' M') union\nselect \"+ Per Heap Table\",CONCAT(HEAP_TABLE \/ (1024*1024),' M') union\nselect \"+ Per Temp Table\",CONCAT(TEMP_TABLE \/ (1024*1024),' M') ;\n\nEND $\nDELIMITER ;\n<\/pre>\n\n\n\n<p>I referred <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/memory-use.html\" target=\"_blank\" rel=\"nofollow noopener\">mysql doc<\/a> and <a href=\"http:\/\/www.mysqlperformanceblog.com\/2006\/05\/17\/mysql-server-memory-usage\/\" target=\"_blank\" rel=\"nofollow noopener\">MySQL Server Memory Usage<\/a> and scripted it for ease.<\/p>\n\n\n\n<p>Hope this helps.<\/p>\n","protected":false},"excerpt":{"rendered":"In this post we will look into the MySQL memory utilization estimation or calculation based on the global variables. Using a simple stored procedure call you can get the memory&hellip;\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,378],"tags":[254,427,680,108],"class_list":{"0":"post-227","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mysql","7":"category-mysql-scripts-mysql","8":"tag-calculate-mysql-memory-usage","9":"tag-mysql","10":"tag-mysql-memory-usage","11":"tag-stored-procedure"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/227","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=227"}],"version-history":[{"count":7,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/227\/revisions"}],"predecessor-version":[{"id":3020,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/227\/revisions\/3020"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=227"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=227"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=227"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}