{"id":5194,"date":"2015-08-19T11:39:42","date_gmt":"2015-08-19T09:39:42","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/mysql-fatal-error-1040-too-many-connections\/"},"modified":"2015-08-19T11:39:42","modified_gmt":"2015-08-19T09:39:42","slug":"mysql-fatal-error-1040-too-many-connections","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/mysql-fatal-error-1040-too-many-connections\/","title":{"rendered":"MySQL FATAL: error 1040: Too many connections &amp; 1135 Can&#8217;t create a new thread"},"content":{"rendered":"<p>Hi folks,<\/p>\n<p>Some of you probably already got error 1040 by playing with sysbench for instance and multiplying the number of MySQL threads. Documentation and blogs usually simply state that you have to increase the number of max_connections and the open files limit. If you tried and you didn&#8217;t succeed this blog is for you.<\/p>\n<p>Despite the following entries in \/etc\/my.cnf the max_connections and open_files_limit are not set correctly:<\/p>\n<pre class=\"brush: shell; gutter: true; first-line: 1\">max_connections = 8000\nopen_files_limit = 8192<\/pre>\n<p>You have the following variables set in MySQL<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">mysql&gt; show variables like '%max_connections%';\n+-----------------+-------+\n| Variable_name   | Value |\n+-----------------+-------+\n| max_connections | 214   |\n+-----------------+-------+\n1 row in set (0.01 sec)\n\n<\/pre>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">mysql&gt; show variables like '%open_files_limit%';\n+------------------+-------+\n| Variable_name    | Value |\n+------------------+-------+\n| open_files_limit | 1024  |\n+------------------+-------+\n1 row in set (0.00 sec)<\/pre>\n<p>If you reach the max_connections limits, you will for sure get the error below and the connection will abort:<\/p>\n<pre class=\"brush: shell; gutter: true; first-line: 1\">FATAL: error 1040: Too many connections\n(last message repeated 1 times)\n\nor \n\nFATAL: pthread_create() for thread #4016 failed. errno = 11 (Resource temporarily unavailable)\nFATAL: unable to connect to MySQL server, aborting...\nFATAL: error 1135: Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug<\/pre>\n<p>You can test it with sysbench for instance by starting 512 parallel threads.<\/p>\n<p>This behavior can be easily changed by setting some parameters in the configuration file for the pam_limits module (limits.conf)<\/p>\n<pre class=\"brush: shell; gutter: true; first-line: 1\">[root@myhost ~]# vi \/etc\/security\/limits.conf\n\nmysql   soft   nofile    8192\nmysql   hard   nofile    8192\nmysql   soft   nproc     8192\nmysql   hard   nproc     32768\nmysql   soft   core    unlimited\nmysql   hard   core    unlimited<\/pre>\n<p>When you log on with the mysql user, the new parameters will be set to the new value.<\/p>\n<pre class=\"brush: shell; gutter: true; first-line: 1\">mysql@mysql:\/home\/mysql\/ [mysqld1] ulimit -a mysql\ncore file size          (blocks, -c) unlimited\ndata seg size           (kbytes, -d) unlimited\nscheduling priority             (-e) 0\nfile size               (blocks, -f) unlimited\npending signals                 (-i) 63476\nmax locked memory       (kbytes, -l) 64\nmax memory size         (kbytes, -m) unlimited\nopen files                      (-n) 8192\npipe size            (512 bytes, -p) 8\nPOSIX message queues     (bytes, -q) 819200\nreal-time priority              (-r) 0\nstack size              (kbytes, -s) 8192\ncpu time               (seconds, -t) unlimited\nmax user processes              (-u) 8192\nvirtual memory          (kbytes, -v) unlimited\nfile locks                      (-x) unlimited\nmysql@qual-neos-data1:\/home\/mysql\/ [mysqld1]<\/pre>\n<p>Afterwards you simply have to restart your mysql servers in order to take changes into consideration.<\/p>\n<pre class=\"brush: shell; gutter: true; first-line: 1\">mysql@myhost:\/home\/mysql\/ [mysqld1] mysqld_multi stop\nmysqld_multi log file version 2.16; run: Wed Aug 19 12:13:09 2015\n\nStopping MySQL servers\nWarning: Using a password on the command line interface can be insecure.\nWarning: Using a password on the command line interface can be insecure.\nmysql@qual-neos-data1:\/home\/mysql\/ [mysqld1]\n\nmysql&gt; show variables like '%open_files_limit%';\n+------------------+-------+\n| Variable_name    | Value |\n+------------------+-------+\n| open_files_limit | 8192  |\n+------------------+-------+\n1 row in set (0.00 sec)\n\nmysql&gt; show variables like '%max_connections%';\n+-----------------+-------+\n| Variable_name   | Value |\n+-----------------+-------+\n| max_connections | 7382  |\n+-----------------+-------+\n1 row in set (0.00 sec)<\/pre>\n<p>You can now set up the number of threads to 512 in sysbench and test !<\/p>\n<p>Have fun!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hi folks, Some of you probably already got error 1040 by playing with sysbench for instance and multiplying the number of MySQL threads. Documentation and blogs usually simply state that you have to increase the number of max_connections and the open files limit. If you tried and you didn&#8217;t succeed this blog is for you. [&hellip;]<\/p>\n","protected":false},"author":10,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[],"type_dbi":[],"class_list":["post-5194","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>MySQL FATAL: error 1040: Too many connections &amp; 1135 Can&#039;t create a new thread - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/mysql-fatal-error-1040-too-many-connections\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySQL FATAL: error 1040: Too many connections &amp; 1135 Can&#039;t create a new thread\" \/>\n<meta property=\"og:description\" content=\"Hi folks, Some of you probably already got error 1040 by playing with sysbench for instance and multiplying the number of MySQL threads. Documentation and blogs usually simply state that you have to increase the number of max_connections and the open files limit. If you tried and you didn&#8217;t succeed this blog is for you. [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/mysql-fatal-error-1040-too-many-connections\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-08-19T09:39:42+00:00\" \/>\n<meta name=\"author\" content=\"Gr\u00e9gory Steulet\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Gr\u00e9gory Steulet\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/mysql-fatal-error-1040-too-many-connections\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/mysql-fatal-error-1040-too-many-connections\/\"},\"author\":{\"name\":\"Gr\u00e9gory Steulet\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/7609faada8e4d63e04a28ae29e227098\"},\"headline\":\"MySQL FATAL: error 1040: Too many connections &amp; 1135 Can&#8217;t create a new thread\",\"datePublished\":\"2015-08-19T09:39:42+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/mysql-fatal-error-1040-too-many-connections\/\"},\"wordCount\":199,\"commentCount\":0,\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/mysql-fatal-error-1040-too-many-connections\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/mysql-fatal-error-1040-too-many-connections\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/mysql-fatal-error-1040-too-many-connections\/\",\"name\":\"MySQL FATAL: error 1040: Too many connections &amp; 1135 Can't create a new thread - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2015-08-19T09:39:42+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/7609faada8e4d63e04a28ae29e227098\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/mysql-fatal-error-1040-too-many-connections\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/mysql-fatal-error-1040-too-many-connections\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/mysql-fatal-error-1040-too-many-connections\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL FATAL: error 1040: Too many connections &amp; 1135 Can&#8217;t create a new thread\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/7609faada8e4d63e04a28ae29e227098\",\"name\":\"Gr\u00e9gory Steulet\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/e1531483285469fe17ea7a769ce5d8a8a01847185e4245d9c4d22c575c7c6d3e?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/e1531483285469fe17ea7a769ce5d8a8a01847185e4245d9c4d22c575c7c6d3e?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/e1531483285469fe17ea7a769ce5d8a8a01847185e4245d9c4d22c575c7c6d3e?s=96&d=mm&r=g\",\"caption\":\"Gr\u00e9gory Steulet\"},\"description\":\"Gr\u00e9gory Steulet has more than ten years of experience in database and infrastructure management, engineering, and optimization. He is specialized in Oracle technologies and high availability solutions (Oracle DataGuard, Data Replication Block Device). His expertise also includes Avaloq banking applications, as well as the open source field (MySQL, Unix\/Linux, etc.). Gr\u00e9gory Steulet is \\\"Oracle Certified Professional 10g\\\", \\\"MySQL Cluster 5.1 Certified\\\", and \\\"Avaloq Certified Professional 2.6\\\". Prior to joining dbi services, Gr\u00e9gory Steulet was Senior Consultant at Trivadis in Lausanne. He also worked as IT Administrator at Box Telecom in Miami Beach, Florida (USA). Gr\u00e9gory Steulet has an Executive MBA from the International Institute of Management in Technology, Fribourg (CH). He also holds a Bachelor's Degree in Business Administration and Computer Science from the University of Applied Sciences Western Switzerland. His branch-related experience covers Telecommunications, Financial Services \/ Banking, Logistics, Pharma etc.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/gregory-steulet\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"MySQL FATAL: error 1040: Too many connections &amp; 1135 Can't create a new thread - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/mysql-fatal-error-1040-too-many-connections\/","og_locale":"en_US","og_type":"article","og_title":"MySQL FATAL: error 1040: Too many connections &amp; 1135 Can't create a new thread","og_description":"Hi folks, Some of you probably already got error 1040 by playing with sysbench for instance and multiplying the number of MySQL threads. Documentation and blogs usually simply state that you have to increase the number of max_connections and the open files limit. If you tried and you didn&#8217;t succeed this blog is for you. [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/mysql-fatal-error-1040-too-many-connections\/","og_site_name":"dbi Blog","article_published_time":"2015-08-19T09:39:42+00:00","author":"Gr\u00e9gory Steulet","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Gr\u00e9gory Steulet","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/mysql-fatal-error-1040-too-many-connections\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/mysql-fatal-error-1040-too-many-connections\/"},"author":{"name":"Gr\u00e9gory Steulet","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/7609faada8e4d63e04a28ae29e227098"},"headline":"MySQL FATAL: error 1040: Too many connections &amp; 1135 Can&#8217;t create a new thread","datePublished":"2015-08-19T09:39:42+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/mysql-fatal-error-1040-too-many-connections\/"},"wordCount":199,"commentCount":0,"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/mysql-fatal-error-1040-too-many-connections\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/mysql-fatal-error-1040-too-many-connections\/","url":"https:\/\/www.dbi-services.com\/blog\/mysql-fatal-error-1040-too-many-connections\/","name":"MySQL FATAL: error 1040: Too many connections &amp; 1135 Can't create a new thread - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-08-19T09:39:42+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/7609faada8e4d63e04a28ae29e227098"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/mysql-fatal-error-1040-too-many-connections\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/mysql-fatal-error-1040-too-many-connections\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/mysql-fatal-error-1040-too-many-connections\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"MySQL FATAL: error 1040: Too many connections &amp; 1135 Can&#8217;t create a new thread"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/7609faada8e4d63e04a28ae29e227098","name":"Gr\u00e9gory Steulet","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/e1531483285469fe17ea7a769ce5d8a8a01847185e4245d9c4d22c575c7c6d3e?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/e1531483285469fe17ea7a769ce5d8a8a01847185e4245d9c4d22c575c7c6d3e?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/e1531483285469fe17ea7a769ce5d8a8a01847185e4245d9c4d22c575c7c6d3e?s=96&d=mm&r=g","caption":"Gr\u00e9gory Steulet"},"description":"Gr\u00e9gory Steulet has more than ten years of experience in database and infrastructure management, engineering, and optimization. He is specialized in Oracle technologies and high availability solutions (Oracle DataGuard, Data Replication Block Device). His expertise also includes Avaloq banking applications, as well as the open source field (MySQL, Unix\/Linux, etc.). Gr\u00e9gory Steulet is \"Oracle Certified Professional 10g\", \"MySQL Cluster 5.1 Certified\", and \"Avaloq Certified Professional 2.6\". Prior to joining dbi services, Gr\u00e9gory Steulet was Senior Consultant at Trivadis in Lausanne. He also worked as IT Administrator at Box Telecom in Miami Beach, Florida (USA). Gr\u00e9gory Steulet has an Executive MBA from the International Institute of Management in Technology, Fribourg (CH). He also holds a Bachelor's Degree in Business Administration and Computer Science from the University of Applied Sciences Western Switzerland. His branch-related experience covers Telecommunications, Financial Services \/ Banking, Logistics, Pharma etc.","url":"https:\/\/www.dbi-services.com\/blog\/author\/gregory-steulet\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5194","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/10"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=5194"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5194\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=5194"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=5194"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=5194"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=5194"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}