{"id":104,"date":"2013-04-17T15:49:18","date_gmt":"2013-04-17T13:49:18","guid":{"rendered":"http:\/\/www.olivierdoucet.info\/blog\/?p=104"},"modified":"2013-04-18T18:50:52","modified_gmt":"2013-04-18T16:50:52","slug":"story-mysql-crash-memory-issue","status":"publish","type":"post","link":"https:\/\/www.olivierdoucet.info\/blog\/2013\/04\/17\/story-mysql-crash-memory-issue\/","title":{"rendered":"Story of a MySQL crash (memory issue)"},"content":{"rendered":"

Today we faced an important issue with MySQL 5.5
\nServer stalled after aborting a TRUNCATE on a big table. Yes, this is bad to abort a TRUNCATE \ud83d\ude09 But this is not the issue I want to speak about. Server crashed, that’s a point. mysqld process restarted automatically, and started loading data into memory (massive use of InnoDB tables). This server has 128GB of memory, so buffer pool was set to 100GB (yes, that’s quite huge). Data set is ~ 100GB. After a few minutes, server crashed again, but this time complaining about memory.<\/p>\n

<\/p>\n

Error log gave this :
\n*** glibc detected *** \/usr\/sbin\/mysqld-5.5: malloc(): memory corruption: 0x00007f5ffc9affa0 ***
\n======= Backtrace: =========
\n\/lib64\/libc.so.6[0x7f7a46163e96]
\n\/lib64\/libc.so.6[0x7f7a46166b4e]
\n[...]
\n08:45:24 UTC - mysqld got signal 6 ;<\/code><\/p>\n

The biggest problem is that this behaviour happened three times in a row. As it needs ~ 30 minutes to warm buffer pool, this was quite a downtime.<\/p>\n

Memory usage looked like this (pink is pagecache) :
\n\"Memory<\/a><\/p>\n

And dmesg reported this : <\/p>\n

[28524726.820222] INFO: rcu_bh detected stall on CPU 32 (t=0 jiffies)
\n[28524726.820230] Pid: 53878, comm: mysqld-5.5 Not tainted 3.2.0-oxeva #8
\n[28524726.820237] Call Trace:
\n[28524726.820244] [] __rcu_pending+0x89\/0x35b
\n[28524726.820285] [] rcu_check_callbacks+0xef\/0x110
\n[28524726.820301] [] update_process_times+0x41\/0x77
\n[28524726.820314] [] tick_sched_timer+0x6f\/0x98
\n[28524726.820329] [] __run_hrtimer+0x95\/0x120
\n[28524726.820337] [] ? tick_nohz_handler+0xd8\/0xd8
\n[28524726.820341] [] hrtimer_interrupt+0xda\/0x1a4
\n[28524726.820350] [] smp_apic_timer_interrupt+0x7e\/0x91
\n[28524726.820365] [] apic_timer_interrupt+0x6b\/0x70
\n[28524726.820367] <\/code><\/p>\n

So RCU seems the one responsible for killing mysqld.<\/p>\n

What is RCU ? <\/strong>
\nLet’s explain this in a few words : when you deallocate memory, the memory is not released immediately but marked as \u00ab\u00a0can be deallocated\u00a0\u00bb. The deallocation itself is asynchronous and handled by RCU.
\n(Well, this is a big shortcut, but if you’re interested, you can have full explanation here : http:\/\/www.rdrop.com\/users\/paulmck\/RCU\/whatisRCU.html<\/a>)<\/p>\n

So, what is happening here ?
\nWhen we have allocated all the memory (user space + page cache), and Mysql makes a malloc() to ask for more, kernel is faced to a dilemma : he has allocated 100%, but he knows some memory can be reclaimed. This is RCU job, but deallocation takes time, and malloc() needs to have an answer fast. This is a zone of undefined behaviour, and in our case, this results to a corruption with malloc() call (and mysqld crashed).<\/p>\n

How can we fix this ? <\/strong>
\nAs we have a majority of InnoDB tables, page cache is not very useful (innodb handles cache itself). So we can disable it by opening files with flag O_DIRECT.
\nThis is handled with mysql variable innodb_flush_method<\/a><\/p>\n

We launch mysql again with this variable set to O_DIRECT, and result is OK :
\n\"Memory<\/a>
\nSee ? No more pagecache used, and plenty of memory available.<\/p>\n

We did not use O_DIRECT before because of incompatibilities with NFS, but this has been fixed since.<\/p>\n","protected":false},"excerpt":{"rendered":"

Today we faced an important issue with MySQL 5.5 Server stalled after aborting a TRUNCATE on a big table. Yes, this is bad to abort a TRUNCATE \ud83d\ude09 But this is not the issue I want to speak about. Server crashed, that’s a point. mysqld process restarted automatically, and started loading data into memory (massive … Continuer la lecture de Story of a MySQL crash (memory issue)<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[57,36],"class_list":["post-104","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-innodb_flush_method","tag-mysql"],"yoast_head":"\nStory of a MySQL crash (memory issue) - My thoughts<\/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:\/\/www.olivierdoucet.info\/blog\/2013\/04\/17\/story-mysql-crash-memory-issue\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Story of a MySQL crash (memory issue) - My thoughts\" \/>\n<meta property=\"og:description\" content=\"Today we faced an important issue with MySQL 5.5 Server stalled after aborting a TRUNCATE on a big table. Yes, this is bad to abort a TRUNCATE \ud83d\ude09 But this is not the issue I want to speak about. Server crashed, that’s a point. mysqld process restarted automatically, and started loading data into memory (massive … Continuer la lecture de Story of a MySQL crash (memory issue) →\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.olivierdoucet.info\/blog\/2013\/04\/17\/story-mysql-crash-memory-issue\/\" \/>\n<meta property=\"og:site_name\" content=\"My thoughts\" \/>\n<meta property=\"article:published_time\" content=\"2013-04-17T13:49:18+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-04-18T16:50:52+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.olivierdoucet.info\/blog\/wp-content\/uploads\/2013\/04\/memory.png\" \/>\n<meta name=\"author\" content=\"Olivier\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@odoucet\" \/>\n<meta name=\"twitter:site\" content=\"@odoucet\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"Olivier\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.olivierdoucet.info\/blog\/2013\/04\/17\/story-mysql-crash-memory-issue\/\",\"url\":\"https:\/\/www.olivierdoucet.info\/blog\/2013\/04\/17\/story-mysql-crash-memory-issue\/\",\"name\":\"Story of a MySQL crash (memory issue) - My thoughts\",\"isPartOf\":{\"@id\":\"https:\/\/www.olivierdoucet.info\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.olivierdoucet.info\/blog\/2013\/04\/17\/story-mysql-crash-memory-issue\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.olivierdoucet.info\/blog\/2013\/04\/17\/story-mysql-crash-memory-issue\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/www.olivierdoucet.info\/blog\/wp-content\/uploads\/2013\/04\/memory.png\",\"datePublished\":\"2013-04-17T13:49:18+00:00\",\"dateModified\":\"2013-04-18T16:50:52+00:00\",\"author\":{\"@id\":\"https:\/\/www.olivierdoucet.info\/blog\/#\/schema\/person\/d093cada9eddc4839cbae3de5c823a39\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.olivierdoucet.info\/blog\/2013\/04\/17\/story-mysql-crash-memory-issue\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.olivierdoucet.info\/blog\/2013\/04\/17\/story-mysql-crash-memory-issue\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/www.olivierdoucet.info\/blog\/2013\/04\/17\/story-mysql-crash-memory-issue\/#primaryimage\",\"url\":\"http:\/\/www.olivierdoucet.info\/blog\/wp-content\/uploads\/2013\/04\/memory.png\",\"contentUrl\":\"http:\/\/www.olivierdoucet.info\/blog\/wp-content\/uploads\/2013\/04\/memory.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.olivierdoucet.info\/blog\/2013\/04\/17\/story-mysql-crash-memory-issue\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.olivierdoucet.info\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Story of a MySQL crash (memory issue)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.olivierdoucet.info\/blog\/#website\",\"url\":\"https:\/\/www.olivierdoucet.info\/blog\/\",\"name\":\"My thoughts\",\"description\":\"En fran\u00e7ais and in english :)\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.olivierdoucet.info\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.olivierdoucet.info\/blog\/#\/schema\/person\/d093cada9eddc4839cbae3de5c823a39\",\"name\":\"Olivier\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/www.olivierdoucet.info\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/e75f77ace92ccb2dfab09f226ce3d329?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/e75f77ace92ccb2dfab09f226ce3d329?s=96&d=mm&r=g\",\"caption\":\"Olivier\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Story of a MySQL crash (memory issue) - My thoughts","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:\/\/www.olivierdoucet.info\/blog\/2013\/04\/17\/story-mysql-crash-memory-issue\/","og_locale":"fr_FR","og_type":"article","og_title":"Story of a MySQL crash (memory issue) - My thoughts","og_description":"Today we faced an important issue with MySQL 5.5 Server stalled after aborting a TRUNCATE on a big table. Yes, this is bad to abort a TRUNCATE \ud83d\ude09 But this is not the issue I want to speak about. Server crashed, that’s a point. mysqld process restarted automatically, and started loading data into memory (massive … Continuer la lecture de Story of a MySQL crash (memory issue) →","og_url":"https:\/\/www.olivierdoucet.info\/blog\/2013\/04\/17\/story-mysql-crash-memory-issue\/","og_site_name":"My thoughts","article_published_time":"2013-04-17T13:49:18+00:00","article_modified_time":"2013-04-18T16:50:52+00:00","og_image":[{"url":"http:\/\/www.olivierdoucet.info\/blog\/wp-content\/uploads\/2013\/04\/memory.png","type":"","width":"","height":""}],"author":"Olivier","twitter_card":"summary_large_image","twitter_creator":"@odoucet","twitter_site":"@odoucet","twitter_misc":{"\u00c9crit par":"Olivier","Dur\u00e9e de lecture estim\u00e9e":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.olivierdoucet.info\/blog\/2013\/04\/17\/story-mysql-crash-memory-issue\/","url":"https:\/\/www.olivierdoucet.info\/blog\/2013\/04\/17\/story-mysql-crash-memory-issue\/","name":"Story of a MySQL crash (memory issue) - My thoughts","isPartOf":{"@id":"https:\/\/www.olivierdoucet.info\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.olivierdoucet.info\/blog\/2013\/04\/17\/story-mysql-crash-memory-issue\/#primaryimage"},"image":{"@id":"https:\/\/www.olivierdoucet.info\/blog\/2013\/04\/17\/story-mysql-crash-memory-issue\/#primaryimage"},"thumbnailUrl":"http:\/\/www.olivierdoucet.info\/blog\/wp-content\/uploads\/2013\/04\/memory.png","datePublished":"2013-04-17T13:49:18+00:00","dateModified":"2013-04-18T16:50:52+00:00","author":{"@id":"https:\/\/www.olivierdoucet.info\/blog\/#\/schema\/person\/d093cada9eddc4839cbae3de5c823a39"},"breadcrumb":{"@id":"https:\/\/www.olivierdoucet.info\/blog\/2013\/04\/17\/story-mysql-crash-memory-issue\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.olivierdoucet.info\/blog\/2013\/04\/17\/story-mysql-crash-memory-issue\/"]}]},{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/www.olivierdoucet.info\/blog\/2013\/04\/17\/story-mysql-crash-memory-issue\/#primaryimage","url":"http:\/\/www.olivierdoucet.info\/blog\/wp-content\/uploads\/2013\/04\/memory.png","contentUrl":"http:\/\/www.olivierdoucet.info\/blog\/wp-content\/uploads\/2013\/04\/memory.png"},{"@type":"BreadcrumbList","@id":"https:\/\/www.olivierdoucet.info\/blog\/2013\/04\/17\/story-mysql-crash-memory-issue\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.olivierdoucet.info\/blog\/"},{"@type":"ListItem","position":2,"name":"Story of a MySQL crash (memory issue)"}]},{"@type":"WebSite","@id":"https:\/\/www.olivierdoucet.info\/blog\/#website","url":"https:\/\/www.olivierdoucet.info\/blog\/","name":"My thoughts","description":"En fran\u00e7ais and in english :)","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.olivierdoucet.info\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"fr-FR"},{"@type":"Person","@id":"https:\/\/www.olivierdoucet.info\/blog\/#\/schema\/person\/d093cada9eddc4839cbae3de5c823a39","name":"Olivier","image":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/www.olivierdoucet.info\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/e75f77ace92ccb2dfab09f226ce3d329?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/e75f77ace92ccb2dfab09f226ce3d329?s=96&d=mm&r=g","caption":"Olivier"}}]}},"_links":{"self":[{"href":"https:\/\/www.olivierdoucet.info\/blog\/wp-json\/wp\/v2\/posts\/104","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.olivierdoucet.info\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.olivierdoucet.info\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.olivierdoucet.info\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.olivierdoucet.info\/blog\/wp-json\/wp\/v2\/comments?post=104"}],"version-history":[{"count":8,"href":"https:\/\/www.olivierdoucet.info\/blog\/wp-json\/wp\/v2\/posts\/104\/revisions"}],"predecessor-version":[{"id":108,"href":"https:\/\/www.olivierdoucet.info\/blog\/wp-json\/wp\/v2\/posts\/104\/revisions\/108"}],"wp:attachment":[{"href":"https:\/\/www.olivierdoucet.info\/blog\/wp-json\/wp\/v2\/media?parent=104"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.olivierdoucet.info\/blog\/wp-json\/wp\/v2\/categories?post=104"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.olivierdoucet.info\/blog\/wp-json\/wp\/v2\/tags?post=104"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}