{"id":2289,"date":"2012-02-05T08:29:02","date_gmt":"2012-02-05T13:29:02","guid":{"rendered":"http:\/\/www.bytebot.net\/blog\/?p=2289"},"modified":"2012-02-05T08:29:06","modified_gmt":"2012-02-05T13:29:06","slug":"optimizing-your-innodb-buffer-pool-usage-by-steve-hardy","status":"publish","type":"post","link":"http:\/\/www.bytebot.net\/blog\/archives\/2012\/02\/05\/optimizing-your-innodb-buffer-pool-usage-by-steve-hardy","title":{"rendered":"Optimizing your InnoDB buffer pool usage by Steve Hardy"},"content":{"rendered":"<p>Steve Hardy of <a href=\"http:\/\/www.zarafa.com\/\">Zarafa<\/a>.<\/p>\n<p>Work that has been done to make Zarafa better. Why do you optimise your buffer pool? To decrease your I\/O load. How can you do it? Buy more RAM, page compression, less (smaller) data, rearrange data.<\/p>\n<p>MariaDB or Percona Server allows you to inspect your <a href=\"http:\/\/www.percona.com\/docs\/wiki\/percona-server:features:innodb_buffer_pool_pages\">buffer pool<\/a> (unsure if this is now available in MySQL 5.6). Giuseppe in the audience says this is available in MySQL 5.6, but Steve used this on MariaDB 5.2.<\/p>\n<p>Strategies to fix it: Make records smaller. Remove indexes if you can use others almost as efficiently. Make records that are accessed around the same time have a higher chance of being on the same page. Use page compression. Buy more RAM. Try Batched Key Access (BKA) in MariaDB 5.3+.<\/p>\n<p>Best to view the presentation since there are specific examples that speak about how Zarafa solves their problems like a user trying to sort their email, etc.<\/p>\n<div class=\"sharedaddy sd-sharing-enabled\"><div class=\"robots-nocontent sd-block sd-social sd-social-icon-text sd-sharing\"><h3 class=\"sd-title\">Share this:<\/h3><div class=\"sd-content\"><ul><li class=\"share-email\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"\" class=\"share-email sd-button share-icon\" href=\"mailto:?subject=%5BShared%20Post%5D%20Optimizing%20your%20InnoDB%20buffer%20pool%20usage%20by%20Steve%20Hardy&body=http%3A%2F%2Fwww.bytebot.net%2Fblog%2Farchives%2F2012%2F02%2F05%2Foptimizing-your-innodb-buffer-pool-usage-by-steve-hardy&share=email\" target=\"_blank\" title=\"Click to email a link to a friend\" data-email-share-error-title=\"Do you have email set up?\" data-email-share-error-text=\"If you&#039;re having problems sharing via email, you might not have email set up for your browser. You may need to create a new email yourself.\" data-email-share-nonce=\"d47a4ff6c7\" data-email-share-track-url=\"http:\/\/www.bytebot.net\/blog\/archives\/2012\/02\/05\/optimizing-your-innodb-buffer-pool-usage-by-steve-hardy?share=email\"><span>Email<\/span><\/a><\/li><li class=\"share-facebook\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"sharing-facebook-2289\" class=\"share-facebook sd-button share-icon\" href=\"http:\/\/www.bytebot.net\/blog\/archives\/2012\/02\/05\/optimizing-your-innodb-buffer-pool-usage-by-steve-hardy?share=facebook\" target=\"_blank\" title=\"Click to share on Facebook\" ><span>Facebook<\/span><\/a><\/li><li class=\"share-linkedin\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"sharing-linkedin-2289\" class=\"share-linkedin sd-button share-icon\" href=\"http:\/\/www.bytebot.net\/blog\/archives\/2012\/02\/05\/optimizing-your-innodb-buffer-pool-usage-by-steve-hardy?share=linkedin\" target=\"_blank\" title=\"Click to share on LinkedIn\" ><span>LinkedIn<\/span><\/a><\/li><li class=\"share-twitter\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"sharing-twitter-2289\" class=\"share-twitter sd-button share-icon\" href=\"http:\/\/www.bytebot.net\/blog\/archives\/2012\/02\/05\/optimizing-your-innodb-buffer-pool-usage-by-steve-hardy?share=twitter\" target=\"_blank\" title=\"Click to share on Twitter\" ><span>Twitter<\/span><\/a><\/li><li class=\"share-end\"><\/li><\/ul><\/div><\/div><\/div>","protected":false},"excerpt":{"rendered":"<p>Steve Hardy of Zarafa. Work that has been done to make Zarafa better. Why do you optimise your buffer pool? To decrease your I\/O load. How can you do it? Buy more RAM, page compression, less (smaller) data, rearrange data. MariaDB or Percona Server allows you to inspect your buffer pool (unsure if this is [&hellip;]<\/p>\n<div class=\"sharedaddy sd-sharing-enabled\"><div class=\"robots-nocontent sd-block sd-social sd-social-icon-text sd-sharing\"><h3 class=\"sd-title\">Share this:<\/h3><div class=\"sd-content\"><ul><li class=\"share-email\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"\" class=\"share-email sd-button share-icon\" href=\"mailto:?subject=%5BShared%20Post%5D%20Optimizing%20your%20InnoDB%20buffer%20pool%20usage%20by%20Steve%20Hardy&body=http%3A%2F%2Fwww.bytebot.net%2Fblog%2Farchives%2F2012%2F02%2F05%2Foptimizing-your-innodb-buffer-pool-usage-by-steve-hardy&share=email\" target=\"_blank\" title=\"Click to email a link to a friend\" data-email-share-error-title=\"Do you have email set up?\" data-email-share-error-text=\"If you&#039;re having problems sharing via email, you might not have email set up for your browser. You may need to create a new email yourself.\" data-email-share-nonce=\"d47a4ff6c7\" data-email-share-track-url=\"http:\/\/www.bytebot.net\/blog\/archives\/2012\/02\/05\/optimizing-your-innodb-buffer-pool-usage-by-steve-hardy?share=email\"><span>Email<\/span><\/a><\/li><li class=\"share-facebook\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"sharing-facebook-2289\" class=\"share-facebook sd-button share-icon\" href=\"http:\/\/www.bytebot.net\/blog\/archives\/2012\/02\/05\/optimizing-your-innodb-buffer-pool-usage-by-steve-hardy?share=facebook\" target=\"_blank\" title=\"Click to share on Facebook\" ><span>Facebook<\/span><\/a><\/li><li class=\"share-linkedin\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"sharing-linkedin-2289\" class=\"share-linkedin sd-button share-icon\" href=\"http:\/\/www.bytebot.net\/blog\/archives\/2012\/02\/05\/optimizing-your-innodb-buffer-pool-usage-by-steve-hardy?share=linkedin\" target=\"_blank\" title=\"Click to share on LinkedIn\" ><span>LinkedIn<\/span><\/a><\/li><li class=\"share-twitter\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"sharing-twitter-2289\" class=\"share-twitter sd-button share-icon\" href=\"http:\/\/www.bytebot.net\/blog\/archives\/2012\/02\/05\/optimizing-your-innodb-buffer-pool-usage-by-steve-hardy?share=twitter\" target=\"_blank\" title=\"Click to share on Twitter\" ><span>Twitter<\/span><\/a><\/li><li class=\"share-end\"><\/li><\/ul><\/div><\/div><\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_options":[]},"categories":[1064,23],"tags":[1332,1348],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p4vJD-AV","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":3199,"url":"http:\/\/www.bytebot.net\/blog\/archives\/2016\/04\/08\/tweet-summary-of-percona-live-2015","url_meta":{"origin":2289,"position":0},"title":"(tweet) Summary of Percona Live 2015","date":"8\/4\/2016","format":false,"excerpt":"The problem with Twitter is that we talk about something and before you know it, people forget. (e.g. does WebScaleSQL have an async client library?) How many blog posts are there about Percona Live Santa Clara 2015? This time (2016), I'm going to endeavour to write more than to just\u2026","rel":"","context":"In &quot;MySQL&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2250,"url":"http:\/\/www.bytebot.net\/blog\/archives\/2012\/02\/05\/sphinx-user-stories-by-stephane-varoqui","url_meta":{"origin":2289,"position":1},"title":"Sphinx user stories by St\u00c3\u00a9phane Varoqui","date":"5\/2\/2012","format":false,"excerpt":"Stephane Varoqui, Field Services SkySQL, Vlad Fedorkov, Director of PS, Sphinx Inc, Christophe Gesche, LAMP Expert, Delcampe, Herve Seignole, Web Architect, Groupe Pierre & Vacances Center Parcs - this is a big talk! Pros: Filtering takes place on attributes in separate tables. Rely on the optimizer choice. HASH JOIN can\u2026","rel":"","context":"In &quot;MariaDB&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2820,"url":"http:\/\/www.bytebot.net\/blog\/archives\/2013\/11\/20\/mysql-5-6-system-variables-in-the-mariadb-10-server","url_meta":{"origin":2289,"position":2},"title":"MySQL 5.6 system variables in the MariaDB 10 server","date":"20\/11\/2013","format":false,"excerpt":"Since MariaDB aims to be a compatible\/drop-in replacement to MySQL, its crucial that in 10.0 we support all the 5.6 options\/system variables, else we have to clearly document them in the Knowledgebase article MariaDB versus MySQL - Compatibility. To this extent,\u00a0Sergey Vojtovich (svoj) has created MDEV-5277 as a tracker. There\u2026","rel":"","context":"In &quot;MariaDB&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2914,"url":"http:\/\/www.bytebot.net\/blog\/archives\/2014\/04\/22\/ubuntu-14-04-some-mysql-ecosystem-notes","url_meta":{"origin":2289,"position":3},"title":"Ubuntu 14.04 &#8211; some MySQL ecosystem notes","date":"22\/4\/2014","format":false,"excerpt":"Following my previous post on the launch, I just rolled Ubuntu 14.04 LTS on an Amazon EC2 t1.micro instance (not something you expect to run a database server on, for sure - 1 vCPU, 0.613GiB RAM). If you do an apt-cache search mysql you get 435 return result sets with\u2026","rel":"","context":"In &quot;MariaDB&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2295,"url":"http:\/\/www.bytebot.net\/blog\/archives\/2012\/02\/05\/replication-features-of-2011-by-sergey-petrunia","url_meta":{"origin":2289,"position":4},"title":"Replication features of 2011 by Sergey Petrunia","date":"5\/2\/2012","format":false,"excerpt":"Sergey Petrunia of the\u00a0MariaDB project & Monty Program. MySQL 5.5 GA at the end of 2010. MariaDB 5.3 RC towards the end of 2011 (beta in June 2011). MySQL 5.5 is merged to Percona Server 5.5 which included semi-sync replication, slave fsync options, atuomatic relay log recovery, RBR slave type\u2026","rel":"","context":"In &quot;MariaDB&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2924,"url":"http:\/\/www.bytebot.net\/blog\/archives\/2014\/05\/02\/mysql-5-6-gtid-mariadb-10-replication","url_meta":{"origin":2289,"position":5},"title":"MySQL 5.6 + GTID &#038; MariaDB 10 replication","date":"2\/5\/2014","format":false,"excerpt":"While at the keynote of\u00a0Tomas Ulin\u00a0at Percona Live MySQL Conference & Expo Santa Clara 2014, he asked the audience what they were running, and most of the audience was on MySQL 5.5 while about 15% of the audience was on MySQL 5.6. This number is steadily increasing I\u2019m sure, so\u2026","rel":"","context":"In &quot;MariaDB&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"amp_enabled":true,"_links":{"self":[{"href":"http:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/posts\/2289"}],"collection":[{"href":"http:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/comments?post=2289"}],"version-history":[{"count":2,"href":"http:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/posts\/2289\/revisions"}],"predecessor-version":[{"id":2291,"href":"http:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/posts\/2289\/revisions\/2291"}],"wp:attachment":[{"href":"http:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/media?parent=2289"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/categories?post=2289"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.bytebot.net\/blog\/wp-json\/wp\/v2\/tags?post=2289"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}