{"id":363,"date":"2013-09-30T16:31:42","date_gmt":"2013-09-30T19:31:42","guid":{"rendered":"http:\/\/wagnerbianchi.com\/blog\/?p=363"},"modified":"2013-09-30T16:35:12","modified_gmt":"2013-09-30T19:35:12","slug":"mysql-5-6-thread-pool","status":"publish","type":"post","link":"http:\/\/wagnerbianchi.com\/blog\/?p=363","title":{"rendered":"MySQL 5.6 Thread Pool"},"content":{"rendered":"<p>Tendo em vista o problema j\u00e1 discutido aqui neste blog com rela\u00e7\u00e3o \u00e0 escala de conex\u00e3o de usu\u00e1rios versus cria\u00e7\u00e3o de threads no MySQL versus sistema operacional &#8211; no caso, um CentOS 6.0 &#8211; decidi recentemente parar para dar uma lida no manual do MySQL e verificar nos m\u00ednimos detalhes o que a <em>feature<\/em> promete. J\u00e1 havia feito alguns testes h\u00e1 algum tempo atr\u00e1s, mas, recordar \u00e9 viver.<\/p>\n<p>O Thread Pool, plugin que integra a vers\u00e3o Enterprise do MySQL oferecida pela Oracle, veio com a inten\u00e7\u00e3o de aumentar o poder de escala quando o assunto \u00e9 quantidade de usu\u00e1rios. Por mais que eu considere que \u00e9 melhor voc\u00ea resolver consultas mais rapidamente com boa performance do que ficar acumulando usu\u00e1rios no sistema de gerenciamento de bancos de dados e assim, causar um processamento muito mais acentuado por via da cria\u00e7\u00e3o de threads, ainda assim temos que contar com os <em>long-running-statements<\/em> que podem tomar grande parte dos recursos do host de servidor de bancos de dados.<\/p>\n<p>A inten\u00e7\u00e3o do plugin \u00e9 fazer com que o MySQL escala mais com mais quantidade de conex\u00f5es realizadas. Segundo o que diz o manual, quanto mais conex\u00f5es, mais est\u00e1vel e mais r\u00e1pido ser\u00e1 a resposta do engine (mysqld). Antes ent\u00e3o do que mais interessa, alguns pontos de aten\u00e7\u00e3o:<\/p>\n<ul>\n<li>O Thread Pool n\u00e3o vem habilitado por padr\u00e3o, voc\u00ea precisa configurar a leitura do plugin;<\/li>\n<li>Suas vari\u00e1veis de ambiente somente ser\u00e3o carregadas caso o plugin seja carregado;<\/li>\n<\/ul>\n<p>Ap\u00f3s habiltar o plugin, verifique as vari\u00e1veis de ambiente e entenda o que cada uma delas faz.<\/p>\n<p>Saiba mais atrav\u00e9s do <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/thread-pool-plugin.html\" target=\"_blank\">manual<\/a>. N\u00e3o vou tratar dos detalhes pois, minha ansiedade aqui \u00e9 exibir que o recursos realmente tem um resultado muito bom e com isso, exibo abaixo os resultados de um pequeno benchmark com o mysqlslap&#8230;<\/p>\n<p><code>[root@threadpool ~]# mysqlslap --user=root --password=123456 --auto-generate-sql --concurrency=100,150,200,250,300 --number-of-queries=2000<br \/>\nWarning: Using a password on the command line interface can be insecure.<br \/>\nBenchmark<br \/>\nAverage number of seconds to run all queries: 2.675 seconds<br \/>\nMinimum number of seconds to run all queries: 2.675 seconds<br \/>\nMaximum number of seconds to run all queries: 2.675 seconds<br \/>\nNumber of clients running queries: 100<br \/>\nAverage number of queries per client: 20<\/code><\/p>\n<p><code>Benchmark<br \/>\nAverage number of seconds to run all queries: 2.224 seconds<br \/>\nMinimum number of seconds to run all queries: 2.224 seconds<br \/>\nMaximum number of seconds to run all queries: 2.224 seconds<br \/>\nNumber of clients running queries: 150<br \/>\nAverage number of queries per client: 13<\/code><\/p>\n<p><code>Benchmark<br \/>\nAverage number of seconds to run all queries: 2.363 seconds<br \/>\nMinimum number of seconds to run all queries: 2.363 seconds<br \/>\nMaximum number of seconds to run all queries: 2.363 seconds<br \/>\nNumber of clients running queries: 200<br \/>\nAverage number of queries per client: 10<\/code><\/p>\n<p><code>Benchmark<br \/>\nAverage number of seconds to run all queries: 2.035 seconds<br \/>\nMinimum number of seconds to run all queries: 2.035 seconds<br \/>\nMaximum number of seconds to run all queries: 2.035 seconds<br \/>\nNumber of clients running queries: 250<br \/>\nAverage number of queries per client: 8<\/code><\/p>\n<p><code>Benchmark<br \/>\nAverage number of seconds to run all queries: 1.984 seconds<br \/>\nMinimum number of seconds to run all queries: 1.984 seconds<br \/>\nMaximum number of seconds to run all queries: 1.984 seconds<br \/>\nNumber of clients running queries: 300<br \/>\nAverage number of queries per client: 6<\/code><\/p>\n<p>&nbsp;<\/p>\n<p>O pr\u00f3ximo passo \u00e9 verificar a quantidade de consultas estagnadas (stalled) atrav\u00e9s da tabela INFORMATION_SCHEMA.TP_THREAD_GROUP_STATS, que somente estar\u00e1 dispon\u00edvel caso o servidor esteja utilizando o Thread Pool plugin.<\/p>\n<p><code>mysql&gt; call test.stalledThreads;<br \/>\n+-------------------------------------------------------+<br \/>\n| SUM(STALLED_QUERIES_EXECUTED) \/ SUM(QUERIES_EXECUTED) |<br \/>\n+-------------------------------------------------------+<br \/>\n| 0.0000 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|<br \/>\n+-------------------------------------------------------+<br \/>\n1 row in set (0.00 sec)<br \/>\nQuery OK, 0 rows affected (0.00 sec)<\/code><\/p>\n<p>Sem stalled queries, em breve vou postar o Thread Pool in action, at\u00e9!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Tendo em vista o problema j\u00e1 discutido aqui neste blog com rela\u00e7\u00e3o \u00e0 escala de conex\u00e3o de usu\u00e1rios versus cria\u00e7\u00e3o de threads no MySQL versus sistema operacional &#8211; no caso, um CentOS 6.0 &#8211; decidi recentemente parar para dar uma lida no manual do MySQL e verificar nos m\u00ednimos detalhes o que a feature promete. [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[3,1],"tags":[],"_links":{"self":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/363"}],"collection":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=363"}],"version-history":[{"count":2,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/363\/revisions"}],"predecessor-version":[{"id":365,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/363\/revisions\/365"}],"wp:attachment":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=363"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=363"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=363"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}