{"id":2437,"date":"2015-07-23T06:06:57","date_gmt":"2015-07-23T06:06:57","guid":{"rendered":"https:\/\/cpanelplesk.com\/wp62\/?p=2437"},"modified":"2021-02-18T01:10:53","modified_gmt":"2021-02-18T01:10:53","slug":"identifying-mysql-slow-queries","status":"publish","type":"post","link":"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/","title":{"rendered":"Identifying MySQL Slow Queries"},"content":{"rendered":"<p style=\"text-align: justify;\"><span style=\"color: #000000;\">One of the most important steps in improving and adjusting MySQL database is to identify the queries that are causing problems. How can we find out what queries are taking a long time to complete? How can we check what queries are slowing down the MySQL database server? MySQL has the answer for us and we only need to know where to look for it.<!--more--><\/span><\/p>\n<p style=\"text-align: justify;\"><span style=\"color: #000000;\">Today in this article we will show you\u00a0to identify which <a href=\"https:\/\/cpanelplesk.com\/wp62\/407\/\">MySQL database<\/a> creating the problem on your server.<\/span><\/p>\n<p style=\"text-align: justify;\"><span style=\"color: #000000;\">First let&#8217;s make sure that the MySQL server will log \u2018slow\u2019 queries and for this we have to check if slow query logging enabled on MySQL server:<\/span><\/p>\n<div class=\"command-sh\">\n<pre class=\"lang:default decode:true\">mysqladmin var |grep log_slow_queries\r\n| log_slow_queries | OFF<\/pre>\n<\/div>\n<p style=\"text-align: justify;\"><span style=\"color: #000000;\">If log_slow_queries is <strong>ON<\/strong> then its already enabled. The MySQL variable long_query_time (default 1) defines what is considered as a slow query. In the default case, any query that takes more than 1 second will be considered a slow query.<\/span><\/p>\n<p style=\"text-align: justify;\"><span style=\"color: #000000;\">In order to enable the MySQL slow query log:<\/span><\/p>\n<p style=\"text-align: justify;\"><span style=\"color: #000000;\">Login to the server as root.<\/span><\/p>\n<p style=\"text-align: justify;\"><span style=\"color: #000000;\">Open your MySQL server config file <strong>\/etc\/my.cnf<\/strong> with your editor.<\/span><\/p>\n<div class=\"cpanel-files\"><\/div>\n<pre class=\"lang:default decode:true\">nano \/etc\/my.cnf<\/pre>\n<div class=\"space1\"><\/div>\n<p style=\"text-align: justify;\"><span style=\"color: #000000;\">In the [mysqld] section, add the line as below:<\/span><\/p>\n<div class=\"cpanel-files\"><\/div>\n<pre class=\"lang:default decode:true\">[mysqld]\r\nlong_query_time = 1\r\nlog-slow-queries = \/var\/log\/mysql\/mysql-slow.log<\/pre>\n<div class=\"space1\"><\/div>\n<p><span style=\"color: #000000;\">This configuration will log all queries that take more than 1 sec in the file <strong>\/var\/log\/mysql\/mysql-slow.log<\/strong>.<\/span><\/p>\n<p style=\"text-align: justify;\"><span style=\"color: #000000;\">To investigate the MySQL slow queries log, we can look inside the log file for each slow query that was executed by the server.<\/span><\/p>\n<div class=\"cpanel-files\"><\/div>\n<pre class=\"lang:default decode:true  \">tail -f \/var\/log\/mysql-slow.log<\/pre>\n<div class=\"space1\"><\/div>\n<p style=\"text-align: justify;\"><span style=\"color: #000000;\">This information allows us to see what queries need to be optimized.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>One of the most important steps in improving and adjusting MySQL database is to identify the queries that are causing problems. How can we find out what queries are taking a long time to complete? How can we check what queries are slowing down the MySQL database server? MySQL has the answer for us and [&#8230;]<\/p>\n<p><a class=\"understrap-read-more-link\" href=\"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/\">Continue Reading&#8230;<\/a><\/p>\n","protected":false},"author":2,"featured_media":914,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-2437","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cpanel"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v24.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Identifying MySQL Slow Queries - cPanel Plesk<\/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:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:title\" content=\"Identifying MySQL Slow Queries - cPanel Plesk\" \/>\n<meta name=\"twitter:description\" content=\"One of the most important steps in improving and adjusting MySQL database is to identify the queries that are causing problems. How can we find out what queries are taking a long time to complete? How can we check what queries are slowing down the MySQL database server? MySQL has the answer for us and [...]Continue Reading...\" \/>\n<meta name=\"twitter:image\" content=\"https:\/\/cpanelplesk.com\/wp62\/wp-content\/uploads\/2014\/09\/MySQL-e1443513523164.jpg\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Seemab Saleem\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/\"},\"author\":{\"name\":\"Seemab Saleem\",\"@id\":\"https:\/\/cpanelplesk.com\/wp62\/#\/schema\/person\/73d573cc7eaaf6625cf0dbd72191a648\"},\"headline\":\"Identifying MySQL Slow Queries\",\"datePublished\":\"2015-07-23T06:06:57+00:00\",\"dateModified\":\"2021-02-18T01:10:53+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/\"},\"wordCount\":249,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/cpanelplesk.com\/wp62\/#\/schema\/person\/c78ae1cf9451a09592fb9697d69c0c13\"},\"image\":{\"@id\":\"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/cpanelplesk.com\/wp62\/wp-content\/uploads\/2014\/09\/MySQL-e1443513523164.jpg\",\"articleSection\":[\"Cpanel\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/\",\"url\":\"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/\",\"name\":\"Identifying MySQL Slow Queries - cPanel Plesk\",\"isPartOf\":{\"@id\":\"https:\/\/cpanelplesk.com\/wp62\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/cpanelplesk.com\/wp62\/wp-content\/uploads\/2014\/09\/MySQL-e1443513523164.jpg\",\"datePublished\":\"2015-07-23T06:06:57+00:00\",\"dateModified\":\"2021-02-18T01:10:53+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/#primaryimage\",\"url\":\"https:\/\/cpanelplesk.com\/wp62\/wp-content\/uploads\/2014\/09\/MySQL-e1443513523164.jpg\",\"contentUrl\":\"https:\/\/cpanelplesk.com\/wp62\/wp-content\/uploads\/2014\/09\/MySQL-e1443513523164.jpg\",\"width\":200,\"height\":203},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/cpanelplesk.com\/wp62\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Identifying MySQL Slow Queries\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/cpanelplesk.com\/wp62\/#website\",\"url\":\"https:\/\/cpanelplesk.com\/wp62\/\",\"name\":\"cPanel Plesk\",\"description\":\"Blog on famous hosting control panels\",\"publisher\":{\"@id\":\"https:\/\/cpanelplesk.com\/wp62\/#\/schema\/person\/c78ae1cf9451a09592fb9697d69c0c13\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/cpanelplesk.com\/wp62\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/cpanelplesk.com\/wp62\/#\/schema\/person\/c78ae1cf9451a09592fb9697d69c0c13\",\"name\":\"Farooq Omer\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/cpanelplesk.com\/wp62\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/cpanelplesk.com\/wp62\/wp-content\/uploads\/2020\/11\/cpanelplesk.png\",\"contentUrl\":\"https:\/\/cpanelplesk.com\/wp62\/wp-content\/uploads\/2020\/11\/cpanelplesk.png\",\"width\":300,\"height\":44,\"caption\":\"Farooq Omer\"},\"logo\":{\"@id\":\"https:\/\/cpanelplesk.com\/wp62\/#\/schema\/person\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/cpanelplesk.com\/wp62\/#\/schema\/person\/73d573cc7eaaf6625cf0dbd72191a648\",\"name\":\"Seemab Saleem\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/cpanelplesk.com\/wp62\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6a87ff2e53b03e6839250e5278a6bd46?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6a87ff2e53b03e6839250e5278a6bd46?s=96&d=mm&r=g\",\"caption\":\"Seemab Saleem\"},\"description\":\"Linux Server Administrator, Web Hosting engineer. I'm dealing with Linux servers since 2014. I started this blog to share the work i love with the world . let me know if you need any assistance. Thanks!!\",\"sameAs\":[\"https:\/\/cpanelplesk.com\/wp62\"],\"url\":\"https:\/\/cpanelplesk.com\/wp62\/author\/cmb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Identifying MySQL Slow Queries - cPanel Plesk","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:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/","twitter_card":"summary_large_image","twitter_title":"Identifying MySQL Slow Queries - cPanel Plesk","twitter_description":"One of the most important steps in improving and adjusting MySQL database is to identify the queries that are causing problems. How can we find out what queries are taking a long time to complete? How can we check what queries are slowing down the MySQL database server? MySQL has the answer for us and [...]Continue Reading...","twitter_image":"https:\/\/cpanelplesk.com\/wp62\/wp-content\/uploads\/2014\/09\/MySQL-e1443513523164.jpg","twitter_misc":{"Written by":"Seemab Saleem","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/#article","isPartOf":{"@id":"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/"},"author":{"name":"Seemab Saleem","@id":"https:\/\/cpanelplesk.com\/wp62\/#\/schema\/person\/73d573cc7eaaf6625cf0dbd72191a648"},"headline":"Identifying MySQL Slow Queries","datePublished":"2015-07-23T06:06:57+00:00","dateModified":"2021-02-18T01:10:53+00:00","mainEntityOfPage":{"@id":"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/"},"wordCount":249,"commentCount":0,"publisher":{"@id":"https:\/\/cpanelplesk.com\/wp62\/#\/schema\/person\/c78ae1cf9451a09592fb9697d69c0c13"},"image":{"@id":"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/#primaryimage"},"thumbnailUrl":"https:\/\/cpanelplesk.com\/wp62\/wp-content\/uploads\/2014\/09\/MySQL-e1443513523164.jpg","articleSection":["Cpanel"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/","url":"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/","name":"Identifying MySQL Slow Queries - cPanel Plesk","isPartOf":{"@id":"https:\/\/cpanelplesk.com\/wp62\/#website"},"primaryImageOfPage":{"@id":"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/#primaryimage"},"image":{"@id":"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/#primaryimage"},"thumbnailUrl":"https:\/\/cpanelplesk.com\/wp62\/wp-content\/uploads\/2014\/09\/MySQL-e1443513523164.jpg","datePublished":"2015-07-23T06:06:57+00:00","dateModified":"2021-02-18T01:10:53+00:00","breadcrumb":{"@id":"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/#primaryimage","url":"https:\/\/cpanelplesk.com\/wp62\/wp-content\/uploads\/2014\/09\/MySQL-e1443513523164.jpg","contentUrl":"https:\/\/cpanelplesk.com\/wp62\/wp-content\/uploads\/2014\/09\/MySQL-e1443513523164.jpg","width":200,"height":203},{"@type":"BreadcrumbList","@id":"https:\/\/cpanelplesk.com\/wp62\/identifying-mysql-slow-queries\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/cpanelplesk.com\/wp62\/"},{"@type":"ListItem","position":2,"name":"Identifying MySQL Slow Queries"}]},{"@type":"WebSite","@id":"https:\/\/cpanelplesk.com\/wp62\/#website","url":"https:\/\/cpanelplesk.com\/wp62\/","name":"cPanel Plesk","description":"Blog on famous hosting control panels","publisher":{"@id":"https:\/\/cpanelplesk.com\/wp62\/#\/schema\/person\/c78ae1cf9451a09592fb9697d69c0c13"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/cpanelplesk.com\/wp62\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/cpanelplesk.com\/wp62\/#\/schema\/person\/c78ae1cf9451a09592fb9697d69c0c13","name":"Farooq Omer","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/cpanelplesk.com\/wp62\/#\/schema\/person\/image\/","url":"https:\/\/cpanelplesk.com\/wp62\/wp-content\/uploads\/2020\/11\/cpanelplesk.png","contentUrl":"https:\/\/cpanelplesk.com\/wp62\/wp-content\/uploads\/2020\/11\/cpanelplesk.png","width":300,"height":44,"caption":"Farooq Omer"},"logo":{"@id":"https:\/\/cpanelplesk.com\/wp62\/#\/schema\/person\/image\/"}},{"@type":"Person","@id":"https:\/\/cpanelplesk.com\/wp62\/#\/schema\/person\/73d573cc7eaaf6625cf0dbd72191a648","name":"Seemab Saleem","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/cpanelplesk.com\/wp62\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6a87ff2e53b03e6839250e5278a6bd46?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6a87ff2e53b03e6839250e5278a6bd46?s=96&d=mm&r=g","caption":"Seemab Saleem"},"description":"Linux Server Administrator, Web Hosting engineer. I'm dealing with Linux servers since 2014. I started this blog to share the work i love with the world . let me know if you need any assistance. Thanks!!","sameAs":["https:\/\/cpanelplesk.com\/wp62"],"url":"https:\/\/cpanelplesk.com\/wp62\/author\/cmb\/"}]}},"_links":{"self":[{"href":"https:\/\/cpanelplesk.com\/wp62\/wp-json\/wp\/v2\/posts\/2437","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/cpanelplesk.com\/wp62\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cpanelplesk.com\/wp62\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cpanelplesk.com\/wp62\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/cpanelplesk.com\/wp62\/wp-json\/wp\/v2\/comments?post=2437"}],"version-history":[{"count":0,"href":"https:\/\/cpanelplesk.com\/wp62\/wp-json\/wp\/v2\/posts\/2437\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/cpanelplesk.com\/wp62\/wp-json\/wp\/v2\/media\/914"}],"wp:attachment":[{"href":"https:\/\/cpanelplesk.com\/wp62\/wp-json\/wp\/v2\/media?parent=2437"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cpanelplesk.com\/wp62\/wp-json\/wp\/v2\/categories?post=2437"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cpanelplesk.com\/wp62\/wp-json\/wp\/v2\/tags?post=2437"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}