{"id":2335,"date":"2011-03-21T16:47:07","date_gmt":"2011-03-21T06:47:07","guid":{"rendered":"http:\/\/www.flamingspork.com\/blog\/?p=2335"},"modified":"2011-03-21T16:47:07","modified_gmt":"2011-03-21T06:47:07","slug":"the-problems-with-multi-tenant-mysql","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2011\/03\/21\/the-problems-with-multi-tenant-mysql\/","title":{"rendered":"The problems with multi-tenant MySQL"},"content":{"rendered":"<p>Just about every web host in the world gives people &#8220;mysql databases&#8221;. Usually one. Why? because that&#8217;s how permissions work in MySQL and it&#8217;s a relatively simple way to set it up (each of your web host clients is a user with access to their one database).<\/p>\n<p>This has a lot of limitations for the end user:<\/p>\n<ul>\n<li>You also only get one user (that can do anything). This means you can forget enhancing security of your web app by not letting the front end DROP TABLE (or DROP SCHEMA). This is probably one of the main reasons that so few web apps use any of the access controls available in MySQL.<\/li>\n<li>You only get one database. Typically this means you get to run one application. Or, you start to get applications that allow you to specify a table name prefix as a lovely hack around this kind of situation (ick).<\/li>\n<li>If you get more than one database, it&#8217;s going to be all prefixed with your username &#8211; and it&#8217;s going to be via a web UI to create\/drop them &#8211; not via the normal way.<\/li>\n<li>You&#8217;ll never get replication.<\/li>\n<li>If you need to scale &#8211; it&#8217;s migrating hosting solutions, there isn&#8217;t an easy to use &#8220;now i need read-only slaves&#8221; button.<\/li>\n<li>Backups are only ever going to be SQL dumps from the master (if you don&#8217;t completely trust your hosting provider)<\/li>\n<li>If your provider does do replication, one other bad user in the system could introduce EPIC amounts of replication latency.<\/li>\n<\/ul>\n<p>One solution is to give everybody their own MySQL instance. Ick. Why ick? Well&#8230; you now have a lot more MySQL servers to administer. That&#8217;s not the problem however: you&#8217;ve just screwed yourself on IOPs &#8211; each mysqld gets to compete to sync its data to disk.<\/p>\n<p>The next step people go to is running MySQL inside a virtual machine. You are again screwing yourself on IOPs. Every VM on the box can now fight each other for a limited number of sync operations to make the data safe on disk. Forget if group_commit works for your MySQL version, having many VMs running MySQL on the same physical box will screw you much more than lack of group_commit ever will. You can probably kiss consistent performance and latency goodbye too (this will largely depend on how VMs are being run by your hosting provider).<\/p>\n<p>The best way to get screwed is to get &#8220;free&#8221; extra CPU cycles and IOPs that are excess on the physical machine and then to suddenly switch to not getting any &#8220;free&#8221; ones and instead only the ones you pay for&#8230; wonder why your site is suddenly slow to respond where the number of visitors is the same and you&#8217;ve changed <strong>NOTHING<\/strong>?<\/p>\n<p>Even running MySQL inside a VM that is the only VM on the box has a performance impact. You want to be using each physical machine to its fullest. If you&#8217;ve got a bunch of MySQL servers running inside VMs &#8211; you are not doing that.<\/p>\n<p>(you can substitute just about any other database server for &#8220;MySQL&#8221; in all of the above&#8230; interestingly enough I have been told that a certain proprietary database server has a very low performance drop when run inside a VM)<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Just about every web host in the world gives people &#8220;mysql databases&#8221;. Usually one. Why? because that&#8217;s how permissions work in MySQL and it&#8217;s a relatively simple way to set it up (each of your web host clients is a &hellip; <a href=\"https:\/\/www.flamingspork.com\/blog\/2011\/03\/21\/the-problems-with-multi-tenant-mysql\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/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,"font":"","enabled":false},"version":2}},"categories":[14],"tags":[],"class_list":["post-2335","post","type-post","status-publish","format-standard","hentry","category-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-BF","jetpack-related-posts":[{"id":3674,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/02\/06\/ghosts-of-mysql-past-part-4-a-million-features-for-enterprise\/","url_meta":{"origin":2335,"position":0},"title":"Ghosts of MySQL Past: Part 4, A million features for Enterprise","author":"Stewart Smith","date":"2014-02-06","format":false,"excerpt":"Continuing on from Part 3.... SAP is all about Enterprises and as such, used all the Enterprise features of databases. This is a much different application than every user of MySQL so far (which were often web applications, and increasingly being used at scale). This was \"MySQL focuses on Enterprise\".\u2026","rel":"","context":"In &quot;mysql&quot;","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":913,"url":"https:\/\/www.flamingspork.com\/blog\/2007\/11\/08\/speaking-at-vitta-victorian-it-teachers-association-inc-conference\/","url_meta":{"origin":2335,"position":1},"title":"Speaking at VITTA (Victorian IT Teachers Association Inc) Conference","author":"Stewart Smith","date":"2007-11-08","format":false,"excerpt":"I'm speaking at the upcoming VITTA conference. Title:MySQL database administration for non DBAs Abstract: MySQL is incredibly ubiquitous. MySQL database administrators are not everywhere; MySQL is. Often MySQL is run to power a small web site or two, an application or two, or run on a machine purely for someone\u2026","rel":"","context":"In &quot;mysql&quot;","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2362,"url":"https:\/\/www.flamingspork.com\/blog\/2011\/04\/21\/http-json-alsosql-interface-to-drizzle\/","url_meta":{"origin":2335,"position":2},"title":"HTTP JSON AlsoSQL interface to Drizzle","author":"Stewart Smith","date":"2011-04-21","format":false,"excerpt":"So... I had another one of those \"hrrm... this shouldn't be hard to hack a proof-of-concept\" moments. Web apps are increasingly speaking JSON all around the place. Why can't we speak JSON to\/from the database? Why? Seriously, why not? One reason why MongoDB has found users is that JSON is\u2026","rel":"","context":"In &quot;drizzle&quot;","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.flamingspork.com\/blog\/wp-content\/uploads\/2011\/04\/Screenshot-11-300x230.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":3697,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/03\/11\/ghosts-of-mysql-past-part-8-the-first-fork\/","url_meta":{"origin":2335,"position":3},"title":"Ghosts of MySQL Past, Part 8: The First Fork.","author":"Stewart Smith","date":"2014-03-11","format":false,"excerpt":"This is the 8th installment in the rather long series that started with Part 1 about a month ago. Back in 2006, we were in the situation where MySQL 5.0 had taken forever, and the first \"GA\" release was not suitable for production. Looking towards MySQL 5.1, it was also\u2026","rel":"","context":"In &quot;mysql&quot;","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1262,"url":"https:\/\/www.flamingspork.com\/blog\/2008\/11\/22\/drop-table-fail-on-the-road-to-removing-the-frm\/","url_meta":{"origin":2335,"position":4},"title":"drop table fail (on the road to removing the FRM)","author":"Stewart Smith","date":"2008-11-22","format":false,"excerpt":"So... in removing the FRM file in Drizzle, I found a bit of a nugget on how drop table works (currently in the MySQL server and now \"did\" in Drizzle). If you DROP TABLE t1; this is what happens open the .frm file read first 10bytes (oh, and if you\u2026","rel":"","context":"In &quot;drizzle&quot;","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3801,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/09\/19\/mysql-architecture\/","url_meta":{"origin":2335,"position":5},"title":"Some current MySQL Architecture writings","author":"Stewart Smith","date":"2014-09-19","format":false,"excerpt":"So, I've been looking around for a while (and a few times now) for any good resources that cover a bunch of MySQL architecture and technical details aimed towards the technically proficient but not MySQL literate audience. I haven't really found anything. I mean, there's the (huge and very detailed)\u2026","rel":"","context":"In &quot;mysql&quot;","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/2335","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/comments?post=2335"}],"version-history":[{"count":1,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/2335\/revisions"}],"predecessor-version":[{"id":2336,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/2335\/revisions\/2336"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=2335"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=2335"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=2335"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}