{"id":643,"date":"2021-05-12T11:46:49","date_gmt":"2021-05-12T14:46:49","guid":{"rendered":"https:\/\/www.xkeysti.com.br\/wiki\/?p=643"},"modified":"2025-03-09T11:37:27","modified_gmt":"2025-03-09T14:37:27","slug":"mysql-ajustar-numero-de-conexoes-to-many-open-files","status":"publish","type":"post","link":"https:\/\/www.xkeysti.com.br\/wiki\/mysql-ajustar-numero-de-conexoes-to-many-open-files\/","title":{"rendered":"MySQL &#8211; Ajustar n\u00famero de conex\u00f5es (to many open files)"},"content":{"rendered":"<p>Verificamos que quando o cliente possui uma quantidade muito grande de usu\u00e1rios o MySQL pode passar a apresentar o erro\u00a0<strong><em>to many open files<\/em><\/strong>. Para resolver esta situa\u00e7\u00e3o siga os passos abaixo:<\/p>\n<ul>\n<li><span><span class=\"\">Edite o arquivo\u00a0\u00a0<\/span><\/span><strong><span><span class=\"\">\/etc\/security\/limits.conf\u00a0<\/span><\/span><\/strong><span><span class=\"\">\u00a0usando seu editor de texto favorito.<\/span><\/span><\/li>\n<li><span><span class=\"\">Adicione o seguinte para todos os usu\u00e1rios na parte inferior do arquivo e salve-o.<\/span><\/span><br \/>\n<code>* soft nofile 102400<\/code><br \/>\n<code>* hard nofile 102400<\/code><br \/>\n<code>* soft nproc 10240<\/code><br \/>\n<code>* hard nproc 10240<br \/>\n<\/code><\/li>\n<li><span><span class=\"\">Edite o arquivo\u00a0<\/span><\/span><strong><span><span class=\"\">\/etc\/security\/limits.d\/90-nproc.conf<\/span><\/span><\/strong><\/li>\n<li><span><span class=\"\">Adicione o seguinte para todos os usu\u00e1rios na parte inferior do arquivo e salve-o.<\/span><\/span><br \/>\n<code>* soft nofile 1024000<\/code><br \/>\n<code>* hard nofile 1024000<\/code><br \/>\n<code>* soft nproc 10240<\/code><br \/>\n<code>* hard nproc 10240<\/code><br \/>\n<code>root soft nproc unlimited<\/code><\/li>\n<\/ul>\n<h3><span><span class=\"\">Definir open_files_limit em my.cnf (MySQL)<\/span><\/span><\/h3>\n<ul>\n<li><span><span class=\"\">Editar\u00a0<\/span><strong><span class=\"\">arquivo<\/span><\/strong><\/span><strong><span><span class=\"\">\u00a0\/etc\/my.cnf<\/span><\/span><\/strong><\/li>\n<li><span>Insira o seguinte em\u00a0<\/span><strong><span>\u00a0[mysqld]\u00a0<\/span><\/strong><span>\u00a0e salve.<\/span><br \/>\n<code>[mysqld]<\/code><br \/>\n<code>open_files_limit = 102400<\/code><\/li>\n<li><span><span class=\"\">Descubra se algum outro arquivo .conf est\u00e1 sendo usado com o MySQL que substitui os valores para limites abertos:<\/span><\/span><br \/>\n<span><span class=\"\">Execute\u00a0\u00a0<\/span><\/span><code>systemctl status mysqld<\/code><span><span class=\"\">o comando e ele mostrar\u00e1 algo como este<\/span><\/span><br \/>\n<span><span class=\"\">Drop-In:<\/span><\/span><br \/>\n<span><span class=\"\">\/etc\/systemd\/system\/mariadb.service.d<\/span><\/span><br \/>\n<span><span class=\"\">\u2514\u2500limits.conf<\/span><\/span><br \/>\n<span><span class=\"\">Isso significa que h\u00e1 \/etc\/systemd\/system\/mariadb.service.d\/limts.conf que \u00e9 carregado com o MySQL Server.<\/span><\/span><br \/>\n<span><span class=\"\">Edite o arquivo e adicione o seguinte<\/span><\/span><br \/>\n<code>[Service]<\/code><br \/>\n<code>LimitNOFILE=102400<\/code><\/li>\n<li><span>Execute o seguinte comando para aplicar as altera\u00e7\u00f5es.<\/span><br \/>\n<code>systemctl daemon-reload &amp;&amp; \/scripts\/restartsrv_mysql<\/code><\/li>\n<li><span>Reinicie seu servidor.<\/span><br \/>\n<span>Ap\u00f3s a reinicializa\u00e7\u00e3o bem-sucedida do servidor, executaremos novamente as Consultas SQL abaixo.<\/span><br \/>\n<code>SHOW VARIABLES LIKE 'open_files_limit';<\/code><br \/>\n<span>Voc\u00ea dever\u00e1 ver o seguinte:<\/span>&nbsp;<\/p>\n<div class=\"geshifilter\">\n<div class=\"bash geshifilter-bash\">\n<pre class=\"de1\"><code>+------------------+--------+\r\n<span class=\"sy0\">|<\/span> Variable_name    <span class=\"sy0\">|<\/span> Value  <span class=\"sy0\">|<\/span>\r\n+------------------+--------+\r\n<span class=\"sy0\">|<\/span> open_files_limit <span class=\"sy0\">|<\/span> <span class=\"nu0\">102400<\/span> <span class=\"sy0\">|<\/span>\r\n+------------------+--------+\r\n<span class=\"nu0\">1<\/span> row <span class=\"kw1\">in<\/span> <span class=\"kw1\">set<\/span> <span class=\"br0\">(<\/span><span class=\"nu0\">0.00<\/span> sec<span class=\"br0\">)<\/span><\/code><\/pre>\n<\/div>\n<\/div>\n<\/li>\n<\/ul>\n<p><span>Com isso, aumentamos o ulimit open files e o MySQL open_files_limit.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Verificamos que quando o cliente possui uma quantidade muito grande de usu\u00e1rios o MySQL pode passar a apresentar o erro\u00a0to many open files. Para resolver esta situa\u00e7\u00e3o siga os passos abaixo: Edite o arquivo\u00a0\u00a0\/etc\/security\/limits.conf\u00a0\u00a0usando seu editor de texto favorito. Adicione o seguinte para todos os usu\u00e1rios na parte inferior do arquivo e salve-o. * soft&hellip;<\/p>\n","protected":false},"author":12,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[],"class_list":["post-643","post","type-post","status-publish","format-standard","hentry","category-parametros-e-configuracoes"],"_links":{"self":[{"href":"https:\/\/www.xkeysti.com.br\/wiki\/wp-json\/wp\/v2\/posts\/643","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.xkeysti.com.br\/wiki\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.xkeysti.com.br\/wiki\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.xkeysti.com.br\/wiki\/wp-json\/wp\/v2\/users\/12"}],"replies":[{"embeddable":true,"href":"https:\/\/www.xkeysti.com.br\/wiki\/wp-json\/wp\/v2\/comments?post=643"}],"version-history":[{"count":3,"href":"https:\/\/www.xkeysti.com.br\/wiki\/wp-json\/wp\/v2\/posts\/643\/revisions"}],"predecessor-version":[{"id":2415,"href":"https:\/\/www.xkeysti.com.br\/wiki\/wp-json\/wp\/v2\/posts\/643\/revisions\/2415"}],"wp:attachment":[{"href":"https:\/\/www.xkeysti.com.br\/wiki\/wp-json\/wp\/v2\/media?parent=643"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.xkeysti.com.br\/wiki\/wp-json\/wp\/v2\/categories?post=643"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.xkeysti.com.br\/wiki\/wp-json\/wp\/v2\/tags?post=643"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}