{"id":73,"date":"2014-05-03T13:31:09","date_gmt":"2014-05-03T08:01:09","guid":{"rendered":"http:\/\/blog.wnohang.net\/?p=73"},"modified":"2014-05-04T15:46:48","modified_gmt":"2014-05-04T10:16:48","slug":"mysql-file-limit-table-cache-max_connections","status":"publish","type":"post","link":"https:\/\/blog.wnohang.net\/index.php\/2014\/05\/03\/mysql-file-limit-table-cache-max_connections\/","title":{"rendered":"MySQL file limit, table cache and max_connections"},"content":{"rendered":"<span class=\"span-reading-time rt-reading-time\" style=\"display: block;\"><span class=\"rt-label rt-prefix\">Reading Time: <\/span> <span class=\"rt-time\"> 4<\/span> <span class=\"rt-label rt-postfix\">minutes<\/span><\/span><a href=\"http:\/\/blog.wnohang.net\/wp-content\/uploads\/2014\/05\/384027019_5e64727276_z.jpg\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/blog.wnohang.net\/wp-content\/uploads\/2014\/05\/384027019_5e64727276_z.jpg\" width=\"640\" height=\"480\" class=\"size-full wp-image-105\" srcset=\"https:\/\/blog.wnohang.net\/wp-content\/uploads\/2014\/05\/384027019_5e64727276_z.jpg 640w, https:\/\/blog.wnohang.net\/wp-content\/uploads\/2014\/05\/384027019_5e64727276_z-300x225.jpg 300w, https:\/\/blog.wnohang.net\/wp-content\/uploads\/2014\/05\/384027019_5e64727276_z-400x300.jpg 400w\" sizes=\"auto, (max-width: 640px) 100vw, 640px\" \/><\/a>\n<p>\n<strong>M<\/strong>ySQL variables open_files_limit, table_open_cache and max_connections are<br \/>\ninter-related, and this is for obvious reasons: all deal with file descriptors<br \/>\none way or another.<\/p>\n<p>If one of the value is provided but others are left out, mysqld calculates<br \/>\nothers using a formula and in some cases, emits a warning if not possible.<\/p>\n<p>The whole calculation behind obtaining the final file descriptor limit is a bit<br \/>\nbyzantine and is as follows (for Linux):<\/p>\n<p><strong>EDIT<\/strong>: This applies to MySQL 5.5, in 5.6, as Dani\u00ebl in comments pointed out,<br \/>\nfew things have changed, check comment for details. I will probably make a<br \/>\nfollowup post on the differences.<\/p>\n<ol>\n<li>First it tries to calculate a minimum requirement based on <code>max_connections<\/code><br \/>\nand <code>table_open_cache<\/code>. As for other variables, if it is not provided in cnf or<br \/>\non command line it uses defaults.<\/p>\n<pre class=\"brush: cpp; title: ; notranslate\" title=\"\">\n&amp;lt;br \/&amp;gt;    \/* MyISAM requires two file handles per table. *\/\n    wanted_files= 10+max_connections+table_cache_size*2;\n<\/pre>\n<p><!--more--><\/li>\n<li>Next, it checks how much it checks how much it can actually allocate based on<br \/>\n<code>open_files_limit<\/code>.<\/p>\n<ul>\n<li>Here, it tries to allocate based on the assumption that atmost 5 file<br \/>\nhandles will be used by a connection and then obtains the maxima from the<br \/>\nprevious <code>wanted_files<\/code> and <code>open_files_limit<\/code> (Note, here it is considered<br \/>\nto be 0 if not set).<\/li>\n<\/ul>\n<pre class=\"brush: cpp; title: ; notranslate\" title=\"\">\n&amp;lt;br \/&amp;gt;    max_open_files= max(max(wanted_files, max_connections*5),open_files_limit);\n    files= my_set_max_open_files(max_open_files);\n<\/pre>\n<\/li>\n<li>After this, it obtains minimum of <code>max_open_files<\/code> and maximum file limit<br \/>\nsupported by that platform. For Linux, it is UINT_MAX.<\/p>\n<pre class=\"brush: cpp; title: ; notranslate\" title=\"\">\nlimit = min(max_file_limit, OS_FILE_LIMIT);\n<\/pre>\n<\/li>\n<li>This is where it gets interesting<sup id=\"fnref-73-1\"><a href=\"#fn-73-1\" rel=\"footnote\">1<\/a><\/sup>.\n<ul>\n<li>Before setting with setrlimit<sup id=\"fnref-73-4\"><a href=\"#fn-73-4\" rel=\"footnote\">2<\/a><\/sup>, it obtains the current limit and if that<br \/>\nlimit is unlimited (in ulimit paralance), it tries to set it lower to limit<br \/>\nobtained earlier (good guy mysql!).<\/p>\n<\/li>\n<li>\n<p>If the current limit is not unlimited and is higher than limit, it<br \/>\nreturns.<\/p>\n<\/li>\n<li>\n<p>If both the above conditions are not satisified, it actually sets it with<br \/>\nsetrlimit.<\/p>\n<ul>\n<li>If this fails, it returns the value obtained with earlier getrlimit.<\/p>\n<\/li>\n<li>\n<p>If this succeeds, it does a getrlimit again<sup id=\"fnref-73-2\"><a href=\"#fn-73-2\" rel=\"footnote\">3<\/a><\/sup> and returns the limit<br \/>\nit returns (lets call it <code>plimit<\/code>).<\/p>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<li>\n<p>Now if the limit it received is lower than what it wanted <code>plimit &lt;<br \/>\nwanted_files<\/code>:<\/p>\n<ul>\n<li>If <code>open_files_limit<\/code> is not set, then it readjusts <code>max_connections<\/code> and<br \/>\n<code>table_cache_size<\/code> appropriately <em>based on the plimit<\/em> obtained before and<br \/>\nprints a warning (based on <code>log_warnings<\/code>).<\/li>\n<\/ul>\n<p><script src=\"https:\/\/gist.github.com\/ronin13\/6e77376967d5b21e51e0.js\"><\/script><\/p>\n<p>The math in the formula above is left as an exercise to reader             .<br \/>\n<code>TABLE_OPEN_CACHE_MIN<\/code> is 400                                              .<\/p>\n<ul>\n<li>If <code>open_files_limit<\/code> limit is set, then it prints the familiar warning<br \/>\n&#8220;Could not increase number of max_open_files to more&#8221;.<\/li>\n<\/ul>\n<\/li>\n<li>Finally, it sets the variable <code>open_files_limit<\/code> to <code>plimit<\/code>. This is <em>why<\/em><br \/>\nyou may often see <code>open_files_limit<\/code> different at runtime than what you set in<br \/>\nmy.cnf.<\/p>\n<\/li>\n<\/ol>\n<h2>Now, what are the implications of this?<\/h2>\n<ul>\n<li>\n<p>MySQL can only change the limit upto soft limit imposed by OS. If it hits the<br \/>\nhard limit, it falls back as in above (since it is not root, it cannot raise<br \/>\nthat by itself). On a capability based system, MySQL should be able to change<br \/>\nthe hard limit if it has CAP_SYS_RESOURCE. You can check both hard and soft<br \/>\nlimits with ulimit -H and ulimit -S for current user. You can set them with<br \/>\n\/etc\/security for daemons.<\/p>\n<\/li>\n<li>\n<p>If you set &#8211; (max_connections or table_open_cache or both) AND<br \/>\nopen_files_limit, and MySQL is not able to obtain the desired limit due to OS<br \/>\nrestrictions, your connections can <strong>fail<\/strong> unexpectedly<sup id=\"fnref-73-3\"><a href=\"#fn-73-3\" rel=\"footnote\">4<\/a><\/sup> later on if and<br \/>\nwhen it runs out of file descriptors (at max_connections or large number of<br \/>\ntables).<\/p>\n<ul>\n<li>Hence, one good thing to do here is to not set open_files_limit. Just look<br \/>\ninto global limits and MySQL is wise enough to figure out how much it needs<br \/>\n(for instance, it sets lower if getrlimit returns unlimited). For global<br \/>\nlimits, set a sane upper limit and set max_connections\/table_cache variables<br \/>\nappropriately based on application needs.<\/p>\n<\/li>\n<li>\n<p>Only downside with previous approach is that max_connections and<br \/>\ntable_cache will be adjusted accordingly (and is visible with log_warnings)<br \/>\nif higher than OS limits but it still better than failing later on!<\/p>\n<\/li>\n<\/ul>\n<\/li>\n<li>\n<p>You can set open_files_limit upto UINT_MAX (assuming OS allows it)! MySQL<br \/>\ndocumentation mentions &#8220;platform dependent&#8221; but for a 64 bit system, you can<br \/>\nset upto 4 million!<\/p>\n<\/li>\n<li>\n<p>This should be obvious but if you just set open_files_limit but not<br \/>\nmax_connections or table_open_cache, mysql won&#8217;t ramp these up to match<br \/>\nopen_files_limit (if the limit is higher than what is obtained with latter<br \/>\nvariables).<\/p>\n<\/li>\n<li>\n<p>You still may want to set open_files_limit in some cases. Xtrabackup, for<br \/>\ninstance, uses this variable to set its limits (it has to open all files at<br \/>\nstart due to DDL restrictions) and it doesn&#8217;t base this on max_connections<br \/>\nor table_cache (since it doesn&#8217;t make sense to xtrabackup). Refer to <a href=\"https:\/\/bugs.launchpad.net\/percona-xtrabackup\/+bug\/1183793\">Bug<br \/>\n#1183793<\/a> for<br \/>\ndetails.<\/p>\n<\/li>\n<li>\n<p>You may also want to set it if mysql is running in a shared environment with<br \/>\nother services like Apache etc. and you want to limit for some reason. In this<br \/>\ncase, pay attention to warning in the error log! (may be there should be an<br \/>\noption where it fails than just log to error log<sup id=\"fnref-73-5\"><a href=\"#fn-73-5\" rel=\"footnote\">5<\/a><\/sup>).<\/p>\n<\/li>\n<li>\n<p>max_connections and table_open_cache are a dynamic variable, open_files_limt<br \/>\nis not. If you change max_connections at runtime, no one of the above is done.<br \/>\nDon&#8217;t set them too high at runtime if your open_files_limit is low (check the<br \/>\nvariable at runtime).<\/p>\n<\/li>\n<li>\n<p>Based on previous point, you may also want to set open_files_limit if you want<br \/>\nto keep a buffer between what mysql feels it wants and what you feel may be<br \/>\nneeded later on (especially if you are going to change at runtime).<\/p>\n<\/li>\n<li>\n<p>In case of MariaDB and Percona Server, due to thread pool requirements (when<br \/>\nused), an additional parameter <code>extra_max_connections<\/code> is also used.<\/p>\n<\/li>\n<\/ul>\n<p><a href=\"https:\/\/www.flickr.com\/photos\/dominik99\/384027019\">Photo Credit<\/a><\/p>\n<div class=\"footnotes\">\n<hr \/>\n<ol>\n<li id=\"fn-73-1\">\nAssuming getrlimit (and RLIMIT_NOFILE) is available, which should<br \/>\nbe on any Linux. If not, it uses <code>min(max_file_limit, OS_FILE_LIMIT)<\/code> as a fallback.&#160;<a href=\"#fnref-73-1\" rev=\"footnote\">&#8617;<\/a>\n<\/li>\n<li id=\"fn-73-4\">\nhttp:\/\/linux.die.net\/man\/2\/setrlimit&#160;<a href=\"#fnref-73-4\" rev=\"footnote\">&#8617;<\/a>\n<\/li>\n<li id=\"fn-73-2\">\nThis looks redundant to me.&#160;<a href=\"#fnref-73-2\" rev=\"footnote\">&#8617;<\/a>\n<\/li>\n<li id=\"fn-73-3\">\nWith &#8220;EAGAIN: Resource temporarily unavailable&#8221;.&#160;<a href=\"#fnref-73-3\" rev=\"footnote\">&#8617;<\/a>\n<\/li>\n<li id=\"fn-73-5\">\nMySQL is notorious in this aspect, in case of SSL for <a href=\"https:\/\/gist.github.com\/ronin13\/b87d77e3b73f5919051d\">instance<\/a>.&#160;<a href=\"#fnref-73-5\" rev=\"footnote\">&#8617;<\/a>\n<\/li>\n<\/ol>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p><span class=\"span-reading-time rt-reading-time\" style=\"display: block;\"><span class=\"rt-label rt-prefix\">Reading Time: <\/span> <span class=\"rt-time\"> 4<\/span> <span class=\"rt-label rt-postfix\">minutes<\/span><\/span>MySQL variables open_files_limit, table_open_cache and max_connections are inter-related, and this is for obvious reasons: all deal with file descriptors one way or another. If one of the value is provided but others are left out, mysqld calculates others using a formula and in some cases, emits a warning if not possible. The whole calculation behind &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/blog.wnohang.net\/index.php\/2014\/05\/03\/mysql-file-limit-table-cache-max_connections\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;MySQL file limit, table cache and max_connections&#8221;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1],"tags":[39,38,61,37,40,19],"class_list":["post-73","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-eagain","tag-max_connections","tag-mysql","tag-open_files_limit","tag-table-open-cache","tag-ulimit"],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p3AlYV-1b","jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"jetpack-related-posts":[{"id":160,"url":"https:\/\/blog.wnohang.net\/index.php\/2015\/04\/29\/feedback-directed-optimization-with-gcc-and-perf\/","url_meta":{"origin":73,"position":0},"title":"Feedback directed optimization with GCC and Perf","author":"Raghavendra","date":"April 29, 2015","format":false,"excerpt":"Gcc 5.0 has added support for FDO which uses perf to generate profile. There is documentation for this in gcc manual, to quote: -fauto-profile=path Enable sampling-based feedback-directed optimizations, and the following optimizations which are generally profitable only with profile feedback available: -fbranch-probabilities, -fvpt, -funroll-loops, -fpeel-loops, -ftracer, -ftree-vectorize, -finline-functions, -fipa-cp, -fipa-cp-clone,\u2026","rel":"","context":"In \"fdo\"","block_context":{"text":"fdo","link":"https:\/\/blog.wnohang.net\/index.php\/tag\/fdo\/"},"img":{"alt_text":"feedback","src":"https:\/\/i0.wp.com\/blog.wnohang.net\/wp-content\/uploads\/2015\/04\/feedback.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":27,"url":"https:\/\/blog.wnohang.net\/index.php\/2013\/05\/19\/slides-from-past-conferences\/","url_meta":{"origin":73,"position":1},"title":"Slides from past conferences","author":"Raghavendra","date":"May 19, 2013","format":false,"excerpt":"Here are my slides from past conferences. They were generated from Org Mode and Beamer. You can read more about it here. You can get more info on the talks on their respective pages(linked). Talk at FOSDEM 2013: Feed me more: MySQL Memory analysed from Raghavendra Prabhu Talk at PLMCE\u2026","rel":"","context":"In &quot;blog&quot;","block_context":{"text":"blog","link":"https:\/\/blog.wnohang.net\/index.php\/category\/blog\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":146,"url":"https:\/\/blog.wnohang.net\/index.php\/2014\/05\/04\/slides-plmce-2014-breakout-session\/","url_meta":{"origin":73,"position":2},"title":"Slides from PLMCE 2014 breakout session","author":"Raghavendra","date":"May 4, 2014","format":false,"excerpt":"As many of you already know, PLMCE is an annual MySQL community conference and Expo organized by Percona in the month of April (usually). It is a great conference, not only to meet new and eminent people in MySQL and related database fields, but also to attend interesting talks, and\u2026","rel":"","context":"In \"ACID\"","block_context":{"text":"ACID","link":"https:\/\/blog.wnohang.net\/index.php\/tag\/acid\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":334,"url":"https:\/\/blog.wnohang.net\/index.php\/2020\/05\/22\/gossips-in-distributed-systems-physalia\/","url_meta":{"origin":73,"position":3},"title":"Gossips in Distributed Systems:  Physalia","author":"Raghavendra","date":"May 22, 2020","format":false,"excerpt":"I often take notes and jot down observations when I read academic\/industry papers. \u00a0 Thinking of a name for this series \u2018Gossips in Distributed Systems\u2019 seemed apt to me, inspired by the gossip protocol with which peers in these systems communicate with each other which mimics the spread of ideas\u2026","rel":"","context":"In \"availability\"","block_context":{"text":"availability","link":"https:\/\/blog.wnohang.net\/index.php\/tag\/availability\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/blog.wnohang.net\/wp-content\/uploads\/2020\/05\/Screen-Shot-2020-05-22-at-4.40.40-PM.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/blog.wnohang.net\/wp-content\/uploads\/2020\/05\/Screen-Shot-2020-05-22-at-4.40.40-PM.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/blog.wnohang.net\/wp-content\/uploads\/2020\/05\/Screen-Shot-2020-05-22-at-4.40.40-PM.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/blog.wnohang.net\/wp-content\/uploads\/2020\/05\/Screen-Shot-2020-05-22-at-4.40.40-PM.png?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/blog.wnohang.net\/wp-content\/uploads\/2020\/05\/Screen-Shot-2020-05-22-at-4.40.40-PM.png?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":59,"url":"https:\/\/blog.wnohang.net\/index.php\/2014\/04\/30\/saving-form-data\/","url_meta":{"origin":73,"position":4},"title":"Saving form data in firefox","author":"Raghavendra","date":"April 30, 2014","format":false,"excerpt":"When commenting on sites, I have sometimes, seen that the commenting system just swallows the comment, or there is a browser crash, or a system one. In these cases it would be great if you can recover it somehow, particularly when you typed quite a bit. There are plugins for\u2026","rel":"","context":"Similar post","block_context":{"text":"Similar post","link":""},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":126,"url":"https:\/\/blog.wnohang.net\/index.php\/2014\/05\/03\/twitter-setup\/","url_meta":{"origin":73,"position":5},"title":"My twitter setup","author":"Raghavendra","date":"May 3, 2014","format":false,"excerpt":"I have been using Twitter for a while under the handle randomsurfer. I tend to use web interface sometimes, but regularly I use the command-line\/ncurses interface. There are two main clients that I use regularly. One is ttytter which is a nice command-line client which offers advanced functionality such as\u2026","rel":"","context":"In \"bitlbee\"","block_context":{"text":"bitlbee","link":"https:\/\/blog.wnohang.net\/index.php\/tag\/bitlbee\/"},"img":{"alt_text":"2014-05-03-150028_1920x1060_scrot","src":"https:\/\/i0.wp.com\/blog.wnohang.net\/wp-content\/uploads\/2014\/05\/2014-05-03-150028_1920x1060_scrot-1024x565.png?resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/blog.wnohang.net\/wp-content\/uploads\/2014\/05\/2014-05-03-150028_1920x1060_scrot-1024x565.png?resize=350%2C200 1x, https:\/\/i0.wp.com\/blog.wnohang.net\/wp-content\/uploads\/2014\/05\/2014-05-03-150028_1920x1060_scrot-1024x565.png?resize=525%2C300 1.5x"},"classes":[]}],"_links":{"self":[{"href":"https:\/\/blog.wnohang.net\/index.php\/wp-json\/wp\/v2\/posts\/73","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.wnohang.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.wnohang.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.wnohang.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.wnohang.net\/index.php\/wp-json\/wp\/v2\/comments?post=73"}],"version-history":[{"count":38,"href":"https:\/\/blog.wnohang.net\/index.php\/wp-json\/wp\/v2\/posts\/73\/revisions"}],"predecessor-version":[{"id":145,"href":"https:\/\/blog.wnohang.net\/index.php\/wp-json\/wp\/v2\/posts\/73\/revisions\/145"}],"wp:attachment":[{"href":"https:\/\/blog.wnohang.net\/index.php\/wp-json\/wp\/v2\/media?parent=73"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.wnohang.net\/index.php\/wp-json\/wp\/v2\/categories?post=73"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.wnohang.net\/index.php\/wp-json\/wp\/v2\/tags?post=73"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}