{"id":2465,"date":"2010-11-03T08:38:03","date_gmt":"2010-11-03T06:38:03","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=2465"},"modified":"2010-11-03T08:38:03","modified_gmt":"2010-11-03T06:38:03","slug":"mysql-terminology-processes-threads-connections","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/mysql-terminology-processes-threads-connections","title":{"rendered":"MySQL terminology: processes, threads &#038; connections"},"content":{"rendered":"<p>There&#8217;s some confusion in the MySQL terminology for processes, threads &amp; connections, which I will try to resolve. I can&#8217;t explain the full nature of what processes and threads are; please see Wikipedia <a href=\"http:\/\/en.wikipedia.org\/wiki\/Process_%28computing%29\">[1]<\/a> <a href=\"http:\/\/en.wikipedia.org\/wiki\/Thread_%28computer_science%29\">[2]<\/a> for that. But here&#8217;s some basics with regard to MySQL:<\/p>\n<ul>\n<li>MySQL server is a single process application.<\/li>\n<li>It is multithreaded.<\/li>\n<li>It (usually) acts as a TCP\/IP server, accepting connections.<\/li>\n<li>Each connection gets a dedicated thread.<\/li>\n<li>These threads are sometimes named processes, and sometimes they&#8217;re referred to as connections.<\/li>\n<\/ul>\n<p>The last part is where confusion arises, so let me discuss again the use of threads and connections in MySQL.<\/p>\n<p><!--more-->MySQL truly is a single process server. It is multi threaded, in that there are many obvious and less obvious threads comprising the server. Such threads are the InnoDB I\/O threads, the DELAYED INSERT thread, etc. Oh, and of course: the connection threads. More on this in a short while.<\/p>\n<p>On older Linux versions or on glibc-static versions, one may view MySQL as a multi-process server. This is not so: it is merely because threads are mapped to OS processes. For the sake of this discussion this is irrelevant. mysqld is a single process.<\/p>\n<p>So, every new connection gets its own thread. Assuming no thread pool is in use, every new connection makes for the creation of a new thread, and a disconnect causes for that thread&#8217;s destruction. Hence, there is a 1-1 mapping between connections and active threads. But then, there <em>is<\/em> a thread pool, which means there can be threads which are not associated with any connection. So, the number of threads is greater than or equal to the number of connections.<\/p>\n<p>Here&#8217;s where terminology gets confusing. When you want to see what&#8217;s executing on the server, you issue <strong>SHOW PROCESSLIST<\/strong>:<\/p>\n<blockquote>\n<pre>mysql&gt; SHOW PROCESSLIST\\G\r\n*************************** 1. row ***************************\r\n     Id: 4\r\n   User: root\r\n   Host: localhost\r\n     db: mycheckpoint\r\nCommand: Query\r\n   Time: 0\r\n  State: NULL\r\n   Info: SHOW PROCESSLIST\r\n1 row in set (0.02 sec)<\/pre>\n<\/blockquote>\n<p>Perhaps this should have been called SHOW THREADLIST; the acting queries are not really processes.<\/p>\n<p>OK, so there&#8217;s process #4 which is executing a query. What&#8217;s <em>my<\/em> process id? Turns out I don&#8217;t have a process id. I do get to have a <strong>CONNECTION_ID()<\/strong>:<\/p>\n<blockquote>\n<pre>mysql&gt; SELECT CONNECTION_ID();\r\n+-----------------+\r\n| CONNECTION_ID() |\r\n+-----------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4 |\r\n+-----------------+<\/pre>\n<\/blockquote>\n<p>So how many processes or connections are now actually doing anything? We now must check for <strong>&#8216;Threads_running&#8217;<\/strong>.<\/p>\n<blockquote>\n<pre>mysql&gt; SHOW GLOBAL STATUS LIKE 'Threads_running';\r\n+-----------------+-------+\r\n| Variable_name\u00a0\u00a0 | Value |\r\n+-----------------+-------+\r\n| Threads_running | 1\u00a0\u00a0\u00a0\u00a0 |\r\n+-----------------+-------+<\/pre>\n<\/blockquote>\n<p>And so we have <strong>&#8216;Threads_cached&#8217;<\/strong>, <strong>&#8216;Threads_connected&#8217;<\/strong> &amp; <strong>&#8216;Max_used_connections&#8217;<\/strong>.<\/p>\n<p>Confusing?<\/p>\n<p>Most of the time one can simply think of processes, threads and connections as 1-1-1 mapped, and not bother with it.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>There&#8217;s some confusion in the MySQL terminology for processes, threads &amp; connections, which I will try to resolve. I can&#8217;t explain the full nature of what processes and threads are; please see Wikipedia [1] [2] for that. But here&#8217;s some basics with regard to MySQL: MySQL server is a single process application. It is multithreaded. [&hellip;]<\/p>\n","protected":false},"author":2,"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":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[11,20],"class_list":["post-2465","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-configuration","tag-syntax"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-DL","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2465","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=2465"}],"version-history":[{"count":21,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2465\/revisions"}],"predecessor-version":[{"id":3065,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2465\/revisions\/3065"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=2465"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=2465"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=2465"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}