{"id":3359,"date":"2010-07-11T07:20:28","date_gmt":"2010-07-11T11:20:28","guid":{"rendered":"http:\/\/g33kinfo.com\/info\/?p=3359"},"modified":"2010-07-11T07:20:28","modified_gmt":"2010-07-11T11:20:28","slug":"quick-and-dirty-mysql-performance-troubleshooting","status":"publish","type":"post","link":"https:\/\/g33kinfo.com\/info\/quick-and-dirty-mysql-performance-troubleshooting\/","title":{"rendered":"Quick and Dirty MySQL Performance Troubleshooting"},"content":{"rendered":"<p>From<a href=\"http:\/\/www.linux-mag.com\/cache\/7473\/1.html\" target=\"_blank\" rel=\"noopener noreferrer\"> linux-mag.com<\/a><\/p>\n<p>Quick and Dirty MySQL Performance Troubleshooting &#8211; What are the first things you should look at after learning of a sudden change in MySQL server performance?<\/p>\n<p>Sooner or later, you\u2019re going to get that phone call (or email, SMS, instant message, tweet, or whatever): The database is slow! Fix ASAP! And you\u2019ll be expected to very quickly figure out what\u2019s slowing things down\u2013often wither very little context, background, or sense of what may have changed recently at the application layer.<\/p>\n<p>It\u2019s just a server. How hard could it be?<\/p>\n<p>Well, as we all know \u201cthat depends.\u201d It depends on a lot of things that you likely have little time to investigate in the middle of a crisis. So where should you focus your time and attention?<\/p>\n<p>This article assumes that you have little to no good monitoring on the server and virtually no idea of what \u201ctypical\u201d usage looks like either. Sadly, that\u2019s a real-world situation for a lot of MySQL administators\u2013especially those in smaller organizations who are expected to wear half a dozen different hats on any given day.<\/p>\n<p>In other words, let\u2019s go back to basics and talk about high-level performance troubleshooting\u2013not fancy high end hardware or new releases of software that you haven\u2019t had a chance to try yet.<\/p>\n<p>Hardware Bottlenecks<\/p>\n<p>The first thing I tend to do is check for hardware bottlenecks. That means logging into the box and running a small list of commands to get a quick idea of what\u2019s happening. Mainly I\u2019m looking for stress points. Which resources are most constrained right now? CPU? Memory? Disk I\/O? Something else?<\/p>\n<p>There are three main utilities I\u2019ll run to in a situation like this:<\/p>\n<p>top<br \/>\nvmstat<br \/>\niostat<\/p>\n<p>First I\u2019m going to use top to see if anything is hogging CPU on the machine. If there are non-mysql processes using a substantial percentage of the CPU cores, I\u2019m going to want to have a look at what that is and see about limiting its use or moving it a dedicated server. If I see mysqld using up a lot of CPU, I know it\u2019s working hard and will have to drill into what\u2019s happening inside of MySQL (maybe some poorly written queries). If nothing is apparently chewing up the CPU time, I know that the problem is likely elsewhere.<\/p>\n<p>Next I\u2019ll run vmstat over a fairly short interval\u2013typically 5 or 10 seconds.<\/p>\n<p><code>$ vmstat 5<\/code><\/p>\n<p>it will generally run this for at least two or three minutes to get a sense of what the CPU and memory use are like. I\u2019m also watching to see how much time the CPU is stalled waiting for I\/O requests. Doing this for several minutes will make the occasional spikes really stand out and also allow for more time to catch those cron jobs that fire up every few minutes.<\/p>\n<p>The last thing I\u2019ll check before poking at MySQL itself is iostat. Just as with vmstat, I\u2019m going to run it with a short interval (5 or 10 seconds) and do so for several minutes. I\u2019ll likely filter the output so that I only see the output for the most active disk or array (the one where all of MySQL\u2019s data lives).<\/p>\n<p><code>$ iostat -x 5 | grep sdb<\/code><\/p>\n<p>I\u2019m looking closely at the % busy and tps to get a qualitative feel for how \u201cbusy\u201d the I\/O subsystem is, and I\u2019ll watch both Blk_wrtn\/s and Blk_read\/s to figure out how many blocks are being written and read every second.<\/p>\n<p>Once I have a basic feel for what the system is doing, I\u2019ll start digging into MySQL itself a bit.<\/p>\n<p><strong>Probing MySQL<\/strong><\/p>\n<p>Looking inside MySQL is a sort of two-layer problem for me. First I want a high-level picture of what it seems to be doing and then I want to dig into the storage engine(s) doing all the I\/O work, since I\/O is a very common bottleneck.<\/p>\n<p>For the highest level view, I want to see a number of things quickly:<\/p>\n<p>how many queries per second is the server handling?<br \/>\nhow many clients are connected (and active)?<br \/>\nare there many \u201cslow\u201d queries being executed?<br \/>\nwhat, if any, unusual errors are being logged?<\/p>\n<p>The first few items can be answered by looking at the results of a few SHOW PROCESSLIST commands along with some SHOW GLOBAL STATUS. Or, better yet, by using a tool that is able to summarize and prevent that data in a more friendly and efficient manner. innotop and mytop both do that. (I wrote mytop but fully admit that innotop is more feature rich and frequently maintained. But either will handle the basics.) Sorting the running queries by execution time is often revealing.<\/p>\n<p>To find out about slow queries I\u2019m going hope that the slow query log is enabled and the server has a sane long_query_time. But even the default of 10 seconds is helpful in truly bad situations.<\/p>\n<p>I\u2019ll also want to glance through MySQL\u2019s error log to make sure nothing bad-looking has started to appear.<\/p>\n<p>With that out of the way, I\u2019ll generally dive into the storage engine statistics. And nowadays that almost always means asking InnoDB to cough up some information with SHOW ENGINE INNODB STATUS. While there\u2019s a wealth of information to process in that output, I\u2019m mainly interested in a few high-level stats at the end.<\/p>\n<p><code><br \/>\n----------------------<br \/>\nBUFFER POOL AND MEMORY<br \/>\n----------------------<br \/>\nTotal memory allocated 23904405026; in additional pool allocated 37084160<br \/>\nBuffer pool size   1310720<br \/>\nFree buffers       1<br \/>\nDatabase pages     1274443<br \/>\nModified db pages  770518<br \/>\nPending reads 1<br \/>\nPending writes: LRU 0, flush list 1, single page 0<br \/>\nPages read 733227814, created 65128628, written 1679994934<br \/>\n98.63 reads\/s, 3.09 creates\/s, 227.34 writes\/s<br \/>\nBuffer pool hit rate 999 \/ 1000<br \/>\n<\/code><\/p>\n<p>I\u2019m usually keen to see that the buffer pool hit rate is good. The closer to 1000 \/ 1000 things get, the happier I am. I also want to see how mange pages are being read and written per second. If either of those seems high, it should correspond to a high volume of I\/O seen earlier in iostat.<\/p>\n<p>The Culprit?<\/p>\n<p>In nine out of ten cases, the culprit is obvious by now\u2013or was obvious half way through this exercise. The reality is that most of the time changes that are very easy to spot are responsible for a sudden decline in performance. The trick is this: you have to be looking in order to see them!<\/p>\n<p>Silly as that sounds, it\u2019s been my experience that most problems go undetected until someone complains\u2013especially in smaller IT groups where there isn\u2019t someone regularly looking after the MySQL server that runs just fine 99.9% of the time. And since it fails so infrequently, nobody bothers to setup decent monitoring or performance logging infrastructure to detect problems before end users are impacted.<\/p>\n<p>Sound familiar?<\/p>\n<p>Just a Start\u2026<\/p>\n<p>The tasks I\u2019ve presented here are just the beginning. Once you\u2019ve got a good sense of what\u2019s happening on a MySQL server, you can really start to dig in and think about how to improve the situation. Do you add or change hardware? Modify the application? Adjust some of MySQL or InnoDB\u2019s run-time or start-time parameters?<\/p>\n","protected":false},"excerpt":{"rendered":"<p>From linux-mag.com Quick and Dirty MySQL Performance Troubleshooting &#8211; What are the first things you should look at after learning of a sudden change in MySQL server performance? Sooner or later, you\u2019re going to get that phone call (or email, SMS, instant message, tweet, or whatever): The database is slow! Fix ASAP! And you\u2019ll be&#8230; <\/p>\n<div class=\"read-more navbutton\"><a href=\"https:\/\/g33kinfo.com\/info\/quick-and-dirty-mysql-performance-troubleshooting\/\">Read More<i class=\"fa fa-angle-double-right\"><\/i><\/a><\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[],"class_list":["post-3359","post","type-post","status-publish","format-standard","hentry","category-info"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.8 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Quick and Dirty MySQL Performance Troubleshooting - Linux Shtuff<\/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:\/\/g33kinfo.com\/info\/quick-and-dirty-mysql-performance-troubleshooting\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Quick and Dirty MySQL Performance Troubleshooting - Linux Shtuff\" \/>\n<meta property=\"og:description\" content=\"From linux-mag.com Quick and Dirty MySQL Performance Troubleshooting &#8211; What are the first things you should look at after learning of a sudden change in MySQL server performance? Sooner or later, you\u2019re going to get that phone call (or email, SMS, instant message, tweet, or whatever): The database is slow! Fix ASAP! And you\u2019ll be... Read More\" \/>\n<meta property=\"og:url\" content=\"https:\/\/g33kinfo.com\/info\/quick-and-dirty-mysql-performance-troubleshooting\/\" \/>\n<meta property=\"og:site_name\" content=\"Linux Shtuff\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/fb.me\/g33kinf0\" \/>\n<meta property=\"article:author\" content=\"https:\/\/fb.me\/g33kinf0\" \/>\n<meta property=\"article:published_time\" content=\"2010-07-11T11:20:28+00:00\" \/>\n<meta name=\"author\" content=\"g33kadmin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/drsinger1111\" \/>\n<meta name=\"twitter:site\" content=\"@drsinger1111\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/quick-and-dirty-mysql-performance-troubleshooting\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/quick-and-dirty-mysql-performance-troubleshooting\\\/\"},\"author\":{\"name\":\"g33kadmin\",\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/#\\\/schema\\\/person\\\/c022e4c40b13ea1b678e6f020756f547\"},\"headline\":\"Quick and Dirty MySQL Performance Troubleshooting\",\"datePublished\":\"2010-07-11T11:20:28+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/quick-and-dirty-mysql-performance-troubleshooting\\\/\"},\"wordCount\":1169,\"publisher\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/#\\\/schema\\\/person\\\/c022e4c40b13ea1b678e6f020756f547\"},\"articleSection\":[\"General Info\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/quick-and-dirty-mysql-performance-troubleshooting\\\/\",\"url\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/quick-and-dirty-mysql-performance-troubleshooting\\\/\",\"name\":\"Quick and Dirty MySQL Performance Troubleshooting - Linux Shtuff\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/#website\"},\"datePublished\":\"2010-07-11T11:20:28+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/quick-and-dirty-mysql-performance-troubleshooting\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/g33kinfo.com\\\/info\\\/quick-and-dirty-mysql-performance-troubleshooting\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/quick-and-dirty-mysql-performance-troubleshooting\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Quick and Dirty MySQL Performance Troubleshooting\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/#website\",\"url\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/\",\"name\":\"Linux Shtuff\",\"description\":\"Because I have CRS Syndrome...\",\"publisher\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/#\\\/schema\\\/person\\\/c022e4c40b13ea1b678e6f020756f547\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/#\\\/schema\\\/person\\\/c022e4c40b13ea1b678e6f020756f547\",\"name\":\"g33kadmin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/wp-content\\\/uploads\\\/2022\\\/07\\\/minion-researchA.gif\",\"url\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/wp-content\\\/uploads\\\/2022\\\/07\\\/minion-researchA.gif\",\"contentUrl\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/wp-content\\\/uploads\\\/2022\\\/07\\\/minion-researchA.gif\",\"width\":512,\"height\":512,\"caption\":\"g33kadmin\"},\"logo\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/wp-content\\\/uploads\\\/2022\\\/07\\\/minion-researchA.gif\"},\"description\":\"I am a g33k, Linux blogger, developer, student and Tech Writer for Liquidweb.com\\\/kb. My passion for all things tech drives my hunt for all the coolz. I often need a vacation after I get back from vacation....\",\"sameAs\":[\"https:\\\/\\\/thelinuxreport.com\",\"https:\\\/\\\/fb.me\\\/g33kinf0\",\"https:\\\/\\\/x.com\\\/https:\\\/\\\/twitter.com\\\/drsinger1111\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Quick and Dirty MySQL Performance Troubleshooting - Linux Shtuff","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:\/\/g33kinfo.com\/info\/quick-and-dirty-mysql-performance-troubleshooting\/","og_locale":"en_US","og_type":"article","og_title":"Quick and Dirty MySQL Performance Troubleshooting - Linux Shtuff","og_description":"From linux-mag.com Quick and Dirty MySQL Performance Troubleshooting &#8211; What are the first things you should look at after learning of a sudden change in MySQL server performance? Sooner or later, you\u2019re going to get that phone call (or email, SMS, instant message, tweet, or whatever): The database is slow! Fix ASAP! And you\u2019ll be... Read More","og_url":"https:\/\/g33kinfo.com\/info\/quick-and-dirty-mysql-performance-troubleshooting\/","og_site_name":"Linux Shtuff","article_publisher":"https:\/\/fb.me\/g33kinf0","article_author":"https:\/\/fb.me\/g33kinf0","article_published_time":"2010-07-11T11:20:28+00:00","author":"g33kadmin","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/drsinger1111","twitter_site":"@drsinger1111","schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/g33kinfo.com\/info\/quick-and-dirty-mysql-performance-troubleshooting\/#article","isPartOf":{"@id":"https:\/\/g33kinfo.com\/info\/quick-and-dirty-mysql-performance-troubleshooting\/"},"author":{"name":"g33kadmin","@id":"https:\/\/g33kinfo.com\/info\/#\/schema\/person\/c022e4c40b13ea1b678e6f020756f547"},"headline":"Quick and Dirty MySQL Performance Troubleshooting","datePublished":"2010-07-11T11:20:28+00:00","mainEntityOfPage":{"@id":"https:\/\/g33kinfo.com\/info\/quick-and-dirty-mysql-performance-troubleshooting\/"},"wordCount":1169,"publisher":{"@id":"https:\/\/g33kinfo.com\/info\/#\/schema\/person\/c022e4c40b13ea1b678e6f020756f547"},"articleSection":["General Info"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/g33kinfo.com\/info\/quick-and-dirty-mysql-performance-troubleshooting\/","url":"https:\/\/g33kinfo.com\/info\/quick-and-dirty-mysql-performance-troubleshooting\/","name":"Quick and Dirty MySQL Performance Troubleshooting - Linux Shtuff","isPartOf":{"@id":"https:\/\/g33kinfo.com\/info\/#website"},"datePublished":"2010-07-11T11:20:28+00:00","breadcrumb":{"@id":"https:\/\/g33kinfo.com\/info\/quick-and-dirty-mysql-performance-troubleshooting\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/g33kinfo.com\/info\/quick-and-dirty-mysql-performance-troubleshooting\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/g33kinfo.com\/info\/quick-and-dirty-mysql-performance-troubleshooting\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/g33kinfo.com\/info\/"},{"@type":"ListItem","position":2,"name":"Quick and Dirty MySQL Performance Troubleshooting"}]},{"@type":"WebSite","@id":"https:\/\/g33kinfo.com\/info\/#website","url":"https:\/\/g33kinfo.com\/info\/","name":"Linux Shtuff","description":"Because I have CRS Syndrome...","publisher":{"@id":"https:\/\/g33kinfo.com\/info\/#\/schema\/person\/c022e4c40b13ea1b678e6f020756f547"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/g33kinfo.com\/info\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/g33kinfo.com\/info\/#\/schema\/person\/c022e4c40b13ea1b678e6f020756f547","name":"g33kadmin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/g33kinfo.com\/info\/wp-content\/uploads\/2022\/07\/minion-researchA.gif","url":"https:\/\/g33kinfo.com\/info\/wp-content\/uploads\/2022\/07\/minion-researchA.gif","contentUrl":"https:\/\/g33kinfo.com\/info\/wp-content\/uploads\/2022\/07\/minion-researchA.gif","width":512,"height":512,"caption":"g33kadmin"},"logo":{"@id":"https:\/\/g33kinfo.com\/info\/wp-content\/uploads\/2022\/07\/minion-researchA.gif"},"description":"I am a g33k, Linux blogger, developer, student and Tech Writer for Liquidweb.com\/kb. My passion for all things tech drives my hunt for all the coolz. I often need a vacation after I get back from vacation....","sameAs":["https:\/\/thelinuxreport.com","https:\/\/fb.me\/g33kinf0","https:\/\/x.com\/https:\/\/twitter.com\/drsinger1111"]}]}},"_links":{"self":[{"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/posts\/3359","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/comments?post=3359"}],"version-history":[{"count":0,"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/posts\/3359\/revisions"}],"wp:attachment":[{"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/media?parent=3359"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/categories?post=3359"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/tags?post=3359"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}