{"id":1847,"date":"2010-03-15T11:25:22","date_gmt":"2010-03-15T01:25:22","guid":{"rendered":"http:\/\/www.flamingspork.com\/blog\/?p=1847"},"modified":"2010-03-15T11:25:22","modified_gmt":"2010-03-15T01:25:22","slug":"thoughts-on-thoughts-on-drizzle","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2010\/03\/15\/thoughts-on-thoughts-on-drizzle\/","title":{"rendered":"Thoughts on Thoughts on Drizzle :)"},"content":{"rendered":"

Mark<\/a> has some good thoughts on drizzle<\/a>. I think they’re all valid… and have some extra thoughts too:<\/p>\n

“I have problems to solve today”<\/em>. This is (of course) an active concern in my brain… If we don’t have something out that solves some set of problems with reasonable stability and reliability (and soon), then we are failing. I feel we’re getting there, and will have a solid foundation to build upon.<\/p>\n

Drizzle replication, MySQL replication: “I can’t compare the two until Drizzle replication is running in production.<\/em>“. Completely agree. We need to only say replication is stable and reliable when it really is. Realistic test suites are needed. Very defensive programming of the replication system is needed (you want to know when something has gone wrong). We also need to have it constantly be verifying the right thing is going on. We want our problems to be user visible, not silent and invisible. Having high standards will hopefully pay off when people start running it in production….<\/p>\n

3 byte int: “Does this mean that some of my tables will grow from 3GB to 4GB on disk?”<\/em> I think we’re moving the responsibility down to the engines. The 3 byte int type says two things: use less storage space, limit the maximum value. Often you want the former, not the latter. There are many ways to more efficiently pack integers for storage when they are usually smaller than the maximum you want. The protobuf library does a good job of it<\/a>.<\/p>\n

I think it is the job of storage engines to do better here. Once you’re in memory, 3 byte numbers are horrible to work with.. copy out of a row buffer, convert into a 32bit number and then do foo. Modern CPUs favor 32 or 64bit alignment of data a *lot*. 3byte numbers do not align to 32 or 64bits very well… making things much slower for the common case of using cached data.<\/p>\n

“I need stored procedures. They are required for high-performance OLTP as they minimize transaction duration for multi-statement transactions.”<\/em> The reduction of network round trips is always crucial. I think a lot of round trips could go away if you could issue multiple statements at once (not via semicolon separating them, by protocol awesomeness).<\/p>\n

There should be a way to send a set of statements that should be executed. There should also be a way to specify that if no error\u00c2\u00a0occurred, commit. This could then be (in the common case) a single round trip to the database. You then only have to make round-trips when what statement to issue next depends on the result of a previous one. The next step being to reduce these round trips… which can either be solved by executing something inside the database server (e.g. stored procedures) or something closer to the database server so that the round trips aren’t as large. This would be where Gearman<\/a> enters.<\/p>\n

I’m interested to see where these two approaches (issuing in batches and executing closer to the DB server) fall down… I know that latency may not be as good… but throughput should be a lot better.<\/p>\n

I take heart with “I have yet to use them in MySQL”<\/em> though. I have my own theories as to why this is… my biggest thought is that it’s because the many, many programmers writing SQL that Mark sees aren’t SQL Stored Procedure programmers. They spend their days in a couple of languages (maybe Perl, Python, PHP, Java, C, C++) and never programmed SQL:2003 Stored Procedures and it just doesn’t come as quickly (or as bug free) as writing code in the languages you use every day.<\/p>\n

“Long Running insert, update and delete statements consume too many resources in InnoDB.” <\/em>I wonder if this desire for MyISAM could be filled by PBXT<\/a> or BlitzDB<\/a>? The main reason that MyISAM is currently a temporary table only engine is that MyISAM and the server core were never\u00c2\u00a0that\u00c2\u00a0well separated.<\/p>\n

My ultimate wish is that all engine authors take the approach of that there is an API to their engine and the Storage Engine is merely glue between the database server and their API.<\/p>\n

The BlitzDB engine has this, Innobase partially does (and my Embedded InnoDB work goes the whole way) and MySQL Cluster is likely the oldest example.<\/p>\n

As a side note, the BlitzDB plugin should go into the main Drizzle tree fairly soon. One of the joys of having an optional plugin that doesn’t touch the core of the server is that we can do this without much worry at all.<\/p>\n

“Does Drizzle build on Windows?”<\/em> Well… no. Funnily enough though, it is increasingly easy to make a Windows port. All the platform specific things are increasingly just plugins. The build system is a sticker… and no, we’re not going to switch to CMake. The C stands for something, and it’s something that even I may not print here… (I had never thought that being able to open up automake generated Makefiles and look at them would be a feature<\/strong>).<\/p>\n

This next Drizzle milestone release should be exciting though…<\/p>\n

I look forward to having Drizzle widely deployed and relied upon… I think we’ll do well..<\/p>\n","protected":false},"excerpt":{"rendered":"

Mark has some good thoughts on drizzle. I think they’re all valid… and have some extra thoughts too: “I have problems to solve today”. This is (of course) an active concern in my brain… If we don’t have something out … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_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},"jetpack_post_was_ever_published":false},"categories":[75,14],"tags":[70,628,256],"class_list":["post-1847","post","type-post","status-publish","format-standard","hentry","category-drizzle-work-et-al","category-mysql","tag-drizzle","tag-mysql","tag-thoughts"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-tN","jetpack-related-posts":[{"id":2907,"url":"https:\/\/www.flamingspork.com\/blog\/2012\/05\/25\/there-is-a-story\/","url_meta":{"origin":1847,"position":0},"title":"There is a story….","author":"Stewart Smith","date":"2012-05-25","format":false,"excerpt":"I have a friend who is fond of telling a story from way back in November 2008 at the OpenSQL camp in Charlottesville, Virgina. This was relatively shortly after we had announced to the public that we'd started something called Drizzle\u00c2\u00a0(we did that at OSCON) and was even closer to\u2026","rel":"","context":"In "drizzle"","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":2223,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/12\/06\/replication-log-inside-innodb\/","url_meta":{"origin":1847,"position":1},"title":"Replication log inside InnoDB","author":"Stewart Smith","date":"2010-12-06","format":false,"excerpt":"The MySQL replication system has always had the replication log (\"binlog\") as a separate set of files on disk. Originally, this really didn't matter as, well, MyISAM wasn't transactional or crash safe so the binlog didn't need to be either. If you crashed on a busy write workload, your replication\u2026","rel":"","context":"In "drizzle"","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":1738,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/10\/14\/return-of-the-top-5-mysql-wishlist-and-looking-at-drizzle\/","url_meta":{"origin":1847,"position":2},"title":"Return of the “Top 5 MySQL Wishlist” and looking at Drizzle","author":"Stewart Smith","date":"2009-10-14","format":false,"excerpt":"It's coming up on a year since I started working full time on Drizzle. So, I got a bit reflective... Have we done things that I (and others) really wanted done? Back in 2007, I wrote my top 5 wishlist for the MySQL Server. I am not going to pretend\u2026","rel":"","context":"In "drizzle"","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":3345,"url":"https:\/\/www.flamingspork.com\/blog\/2013\/05\/23\/mysql-vs-drizzle-plugin-api\/","url_meta":{"origin":1847,"position":3},"title":"MySQL vs Drizzle plugin APIs","author":"Stewart Smith","date":"2013-05-23","format":false,"excerpt":"There's a big difference in how plugins are treated in MySQL and how they are treated in Drizzle. The MySQL way has been to create a C API in front of the C++-like (I call it C- as it manages to take the worst of both worlds) internal \"API\". The\u2026","rel":"","context":"In "code"","block_context":{"text":"code","link":"https:\/\/www.flamingspork.com\/blog\/category\/code\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2303,"url":"https:\/\/www.flamingspork.com\/blog\/2011\/03\/16\/fixed-in-drizzle-no-more-gotchas\/","url_meta":{"origin":1847,"position":4},"title":"Fixed in Drizzle: No more “GOTCHA’s”","author":"Stewart Smith","date":"2011-03-16","format":false,"excerpt":"\u00a0 At the upcoming MySQL Conference and Expo, I'm going to give a Thursday afternoon (2pm) session entitled Fixed in Drizzle: No more \"GOTCHA's\". I plan to have a lot of fun with this session.. If you go back to the very start of when I started submitting code to\u2026","rel":"","context":"In "drizzle"","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":3664,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/02\/04\/ghosts-of-mysql-past-part-2\/","url_meta":{"origin":1847,"position":5},"title":"Ghosts of MySQL Past: Part 2","author":"Stewart Smith","date":"2014-02-04","format":false,"excerpt":"This continues on from my post yesterday and also contains content from my linux.conf.au 2014 talk (view video here). Way back in May in the year 2000, a feature was added to MySQL that would keep many people employed for many years - replication. In 3.23.15 you could replicate from\u2026","rel":"","context":"In "drizzle"","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":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/1847","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=1847"}],"version-history":[{"count":1,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/1847\/revisions"}],"predecessor-version":[{"id":1848,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/1847\/revisions\/1848"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=1847"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=1847"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=1847"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}