Drizzle

I’m excited by all of the recent developments surrounding the Percona Live MySQL Conference and Expo! Our own Baron Schwartz will moderate the Diamond Keynote Panel entitled “Future Perfect: The Road Ahead for MySQL” which will feature a panel of ... [More] MySQL industry leaders, including: Sundar Raghavan, director product management at Amazon; Paul Mikesell, CEO of Clustrix; a representative from HP; and, a representative from McAfee. The Diamond Sponsor Keynote Panel will take place at 9:30 a.m. on Thursday, April 12th and provide insight into the future of MySQL technology, adoption, and the ecosystem landscape. I am also very pleased to introduce two new sponsors including McAfee which recently joined as a Diamond Sponsor and AOL which recently agreed to participate as a lunch sponsor. We also recently announced the Birds of a Feather Sessions which will take place on Tuesday at 6:30 p.m. and Wednesday at 9:00 p.m. BOFs enable attendees with the same project or topic interests to enjoy some quality face time. Topics include “Query Optimization”, “MySQL and Ruby on Rails”, “MySQL in the Cloud”, and much more. We also published the list of Lightning Talks, a series of five-minute speaking opportunities during which attendees will propose, explain, exhort, and rant on a variety of MySQL-related topics. The one-hour Lightning Talks session will take place at 6:30 p.m. on Wednesday night during the Community Networking Reception. Topics include “The Five Minute Tour of Drizzle”, “How to Design a Scalable System”, and “How to Become a Rockstar DBA”. The Percona Live MySQL Conference and Expo promises to be a great event! If you already registered, I look forward to seeing you in Santa Clara next week. If you haven’t registered but plan to attend, register now using discount code PerLiveSC and receive 10% off your registration fees. If you just cannot make it this year, keep your eyes open for recordings of the keynote sessions which we plan to post online following those events. [Less]

A number of people have already mentioned this, but the Percona Live MySQL Conference and Expo is just around the corner. As Stewart has already blogged, there are a number of great sessions this year and I’m looking forward to several of them. I’ll ... [More] be giving a talk there as well - It’s essentially all in the abstract, but I’ll be speaking about various functional testing tools that exist for MySQL-based systems. Come to learn more about the random query generator, MTR, and kewpie and how they might be of use to you. Speaking of kewpie, I’ll also be presenting about it at Drizzle Developer Day, which is on 4/13, the day after the main conference. If you are interested in learning more about Drizzle, whether it be from hearing the various presentations to having a chance to chat with the developers and fellow enthusiasts, you should check it out.  Much like Stewart, I’m quite psyched about the event and doubly excited that my employer, Percona, is sponsoring it. Additionally, there are other events that day, as Peter mentions here: SkySQL Solutions Day, presented by SkySQL and MariaDB Sphinx Search Day, presented by Sphinx Technologies [Less]

So yesterday I introduced the newly committed HTTP JSON key-value interface in Drizzle. The next step of course is to create some simple application that would use this to store data, this serves both as an example use case as well as for myself to ... [More] get the feeling for whether this makes sense as a programming paradigm. Personally, I have been a fan of the schemaless key-value approach ever since I graduated university and started doing projects with dozens of tables and hundreds of columns in total. Especially in small projects I always found the array structures in languages like PHP and Perl and Python to be very flexible to develop with. As I was developing and realized I need a new variable or new data field somewhere, it was straightforward to just toss a new key-value into the array and continue with writing code. No need to go back and edit some class definition. If I ever needed to find out what is available in some struct, I could always do dump_var($obj) to find out. Even large projects like Drupal get along with this model very well. read more [Less]

The thing I really like with open source is the feeling you get when people just show up from nowhere and do great things to some code you originally wrote. Thanks to this miracle, I can now also present to you version 0.2 of the Drizzle JSON HTTP ... [More] support, featuring a "pure JSON key-value API" in addition to the original "SQL over HTTP" API in 0.1 version. Let's recap what happened: At Drizzle Day 2011, I proposed that Drizzle should make available a JSON NoSQL interface. Stewart took the bait and published json_server 0.1 a week later. This API still uses SQL, it's just that the client protocol is HTTP and JSON, into which the SQL is embedded. So I suppose it's not as sexy as real NoSQL, but pretty cool nevertheless. At the end of last Summer I had a lot of dead time so I started playing with Stewart's code to see what I could do. I added a new API in addition to Stewart's SQL-over-HTTP API that supports key-value operations in pure JSON, similar to what you see in CouchDB, MongoDB or Metabase. I got it working quite well, however, I never implemented a DELETE command, because I then drifted off to more important tasks, such as revamping the Drizzle version numbering and bringing DEB and RPM packaging up to date. Last week a new but very promising Drizzle hacker called Mohit came by, looking for things he could do. He had already fixed a simple low-hanging-bug and wanted something more. Since he was interested in the JSON API, I asked if he wants to finish the missing piece. With my helpful advice of "there is no documentation but if you look at the demo GUI you'll probably figure it out, then just look at the code for POST and implement DELETE instead". I was afraid that wasn't really helpful, but I was busy that day. No problem, the next day Mohit had pushed working DELETE implementation. The day after that he implemented the final missing piece, a CREATE TABLE functionality. I was both impressed and excited. read more [Less]

We’re very pleased with community support for Percona Live MySQL Conference and Expo. We’ve got wonderful speakers providing content of phenomenal session lineup for the conference this year. We have community helping by spreading the world, about ... [More] conference, picking talks and all king of other wonderful contributions. We also have series of events organized by Community and different organizations, taking place around the conference which you surely should not miss: Pythian is stepping in to organize traditional MySQL Community Dinner event during Tutorial Day of the conference (April 10th) SkySQL and MariaDB are hosting SkySQL Solutions Day (April 13) which will feature keynotes by Michael (Monty) Widenius and David Axmark. Drizzle will have its 4th Annual Drizzle day (April 13) Finally there is a first Sphinx Search Day hosted by Sphinx Technologies (April 13) All these events are free to attend (registration needed) whenever you’re attending MySQL Conference and Expo or not. Furthermore Birds of the Feather sessions on Percona Live MySQL Conference and Expo, taking place Tuesday and Wednesday in the evening are free for anyone to atten. [Less]

This is the third and hopefully final Release candidate for Drizzle 7.1 Tar ball, Debian package and RPMs available. Changes since previous release include: * libdrizzle is no longer installed by these packages. To install libdrizzle, see ... [More] lp:libdrizzle launchpad project. Libdrizzle so-version was bumped to 4.0.0. * Removed Transaction Log (includes drizzletrx command line utility) and Replication Dictionary (Brian Aker) Bugs and other minor fixes: * Bug fixed: The Regex Policy plugin could consume unbounded amounts of memory, which could be used for denial of service attacks. (Clint Byrum.) * Bug fixed: Drizzled could crash when filesystem permissions cause writing a pid file to fail. (Brian Aker) * Bug fixed: An undocumented ipv6_function was enabled and could crash drizzled if called - the function was disabled. (Fixed by Henrik Ingo.) * Bug fixed: In master master setup, slave plugin would try to apply transactions originating from itself. (Joe Daly) * Bug fixed: Fix performance regression introduced with catalog being added to key. (Brian Aker) * Bug fixed: actually return the next exception in Exception::getNextException() & Fix for execute parser to recognize escaped semicolons. (David Shrewsbury) * Bug fixed:  A typo in assertion causing false positive failure with log > 4GB. * Updated docs. Check out comprehensive replication documentation! (Daniel Nichter, Henrik Ingo, Muhammad Umair, Brian Aker, et.al.) * Cleanups to drizzle and drizzled --help output and error messages. Standardize plugin names. (Daniel Nichter, Mohit Srivastava) * Updates and cleanups to the Kewpie testing suite. Enable more automated CI process. (Patrick Crews, Olaf van der Spek) Changes in Drizzle 7.1 series since Drizzle 7 include: - Multi-master replication - Servers are identified with UUID in replication - HTTP JSON interface available (experimental) - Percona Innodb patches merged - Xtrabackup in-tree - IPV6 data type available - Query log plugin - Log output to syslog is enabled by default - Ability to publish transactions to zeromq and rabbitmq - Improvements to logging stats plugin - Native multi-tenancy (database virtualization) support (catalogs) - Removal of drizzleadmin utility (you can now do all administration from drizzle client itself) - Removal of HailDB engine - Revamped testing system Kewpie all-inclusive with suites of randgen, sysbench, sql-bench, and crashme tests - Continued code refactoring & various bug fixes Installing Drizzle 7.1.32-rc The source tar package is available from our Launchpad download area. Please see the manual on how to install Drizzle 7.1.32-rc from source tar package. The Ubuntu packages are available from the Drizzle PPA. Instructions on how to install from the PPA are in the Drizzle manual. CentOS 6 packages are available from download.drizzle.org. Instructions on how to install them with yum are in the Drizzle manual. Note: For Drizzle 7.1.32-rc, only 64-bit packages were built for RedHat/Centos. Please let us know if there is demand for 32-bit packages (such as by commenting to this blog post.) Known issues All of the optional plugins are packaged in their own package. These are named drizzle-plugin-*. If a plugin is installed, it is also configured to load by default. (see /etc/drizzle/conf.d/pluginname.cnf) However, not all plugins actually work without further configuration. These plugins will cause the server startup to fail. The workaround is to either configure the plugin to work, or uninstall it if you don't want to use it. (Related blueprint) In short, you should only install the plugins you want to use. Don't install all available plugins. Please post bugs on the Drizzle bug tracker and questions and general feedback to drizzle-discuss mailing list. [Less]

One of the most painful troubleshooting tasks with MySQL is troubleshooting memory usage. The problem usually starts like this – you have configured MySQL to use reasonable global buffers, such as innodb_buffer_size, key_buffer_size etc, you have ... [More] reasonable amount of connections but yet MySQL takes much more memory than you would expect, causing swapping or other problems. This simple problem on the surface becomes challenge with MySQL because there are no clear resource usage metrics available, and so in most cases you do not know where exactly memory is allocated. This was not much of the problem in MySQL 3.23 when there would only handful of places where memory could be allocated but it is a lot larger problems with MySQL 5.5 with addition of user variables, stored procedures, prepared statements etc which can be a memory hog. My intent with this post is dual. I would encourage MySQL Team at Oracle, MariaDB team or Drizzle team to take a look into solving this problem. You will be thanked by a lot of people running MySQL in wild. I also wanted to share some troubleshooting techniques I use. Plot Memory Usage First I would like to see MySQL memory consumption plotted. I use “VSZ” columns from “ps” output on Linux. It helps me to understand how this memory allocation happens – does it grows slowly and never ending which would correspond to memory leak or resource leak, does it spikes at certain times which I may be able to correlate to some events ? If you can’t get fancy graph quickly you can use this simple script: while true do date >> ps.log ps aux | grep mysqld >> ps.log sleep 60 done Check for Table Cache Related Allocations There are cases when MySQL will allocate a lot of memory for table cache, especially if you’re using large blobs. It is easy to check though. Run “FLUSH TABLES” and see whenever memory usage goes down. Note though because of how memory is allocated from OS you might not see “VSZ” going down. What you might see instead is flushing tables regularly or reducing table cache reduces memory consumption to be withing the reason. Connection Related Allocations Another set of buffers correspond to connections – orphaned prepared statements, user variables, huge network buffer (can grow up to max_packet_size per connection) are all connection buffers and so if you close connection MySQL can clean them up. Killing connections (or stopping related applications) and observing whenever memory usage shrinks can be very helpful in diagnostics. If you have many applications it might make sense to shut them down one by one so it is easier to understand which of the applications were responsible for memory allocation. If you figured out it is related to connections and identified application which causes excessive memory usage you might look at how it uses MySQL to identify potential causes. Is it working with large blobs ? Using user variables ? Prepared Statements ? memory tables ? In a lot of cases you have to guess and test, you can’t get information of how much memory connection uses and for which purposes it is allocated. Reducing various per connection variables is how you can test it, though it does not cover everything. For prepared statements you might want to look at Prepared_stmt_count to see how many prepared statements are allocated on server and Com_stmt_send_long_data to see whenever sending long data feature is used as it can increase amount of resources prepared statements take on server. There is no comparable variables of how many user variables are allocated (and how much memory they use). Memory Tables MEMORY tables can take memory. There are implicit MEMORY tables which are allocated for query execution, which size can be controlled by tmp_table_size and which also only exist for duration of query execution so it is usually easy to catch them. There are also explicit MEMORY tables you can create both as permanent and temporary. There is a max_heap_table_size variable which allows you to limit size of MEMORY tables (the limit applies both to implicit and explicit ones) but as there is no control of how many tables application can create it does not really allows to restrict memory usage. For Permanent tables it is easy. We can look at information_schema to see how much memory is being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine='memory'; +-------------------------------+ | sum(data_length+index_length) | +-------------------------------+ | 126984 | +-------------------------------+ 1 row in set (0.98 sec) This however will not cover TEMPORARY tables some of your connections might have created and forgot to remove (or still use for processing). Of course you will see these tables going away if you close connection. In Percona Server you can do better as you can query temporary tables too: mysql> select sum(data_length+index_length) from information_schema.global_temporary_tables where engine='memory'; +-------------------------------+ | sum(data_length+index_length) | +-------------------------------+ | 126984 | +-------------------------------+ 1 row in set (0.00 sec) You can even go deeper to check which sessions have created which temporary tables (both in memory and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE_NAME: my ENGINE: InnoDB NAME: #sql516_1c42_2 TABLE_ROWS: 0 AVG_ROW_LENGTH: 0 DATA_LENGTH: 16384 INDEX_LENGTH: 0 CREATE_TIME: NULL UPDATE_TIME: NULL *************************** 2. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE_NAME: tmp ENGINE: MEMORY NAME: #sql516_1c42_1 TABLE_ROWS: 2 AVG_ROW_LENGTH: 257 DATA_LENGTH: 126984 INDEX_LENGTH: 0 CREATE_TIME: 2012-03-21 05:52:02 UPDATE_TIME: NULL *************************** 3. row *************************** SESSION_ID: 7231 TABLE_SCHEMA: test TABLE_NAME: z ENGINE: InnoDB NAME: #sql516_1c3f_0 TABLE_ROWS: 0 AVG_ROW_LENGTH: 0 DATA_LENGTH: 16384 INDEX_LENGTH: 0 CREATE_TIME: NULL UPDATE_TIME: NULL 3 rows in set (0.00 sec) Innodb Memory Usage Finally it is often helpful to check how much memory Innodb has allocated. In fact this is often one of the first things I do as it is least intrusive. Run SHOW ENGINE INNODB STATUS and look for memory information block, which can use like this: ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 132183490560; in additional pool allocated 0 Internal hash tables (constant factor + variable factor) Adaptive hash index 4422068288 (2039977928 + 2382090360) Page hash 127499384 Dictionary cache 512619219 (509995888 + 2623331) File system 294352 (82672 + 211680) Lock system 318875832 (318747272 + 128560) Recovery system 0 (0 + 0) Threads 425080 (406936 + 18144) Dictionary memory allocated 2623331 Buffer pool size 7864319 Buffer pool size, bytes 128849002496 Free buffers 1 Database pages 8252672 Old database pages 3046376 Modified db pages 23419 I’m using the information from Percona Server again which provides a lot more insight about how memory is used. I would note though the output is a bit confusing as “Total Memory Allocated” is really not the total any more as Innodb has moved allocating memory from operation system directly not from addition memory pool (hence 0). Such allocations are not seen in “Total memory allocated” line yet they are seen in one of the lines below. The most likely cause for Innodb run away memory allocation is Dictionary cache though there could be other reasons too. Memory Leaks There are many kinds of memory leaks and I would say these are rather rare in MySQL. Most suspected memory leaks end up being some run away resource usage, though these can happen. Some memory leaks might happen per connection and they will be gone when connection is closed, other correspond to global memory allocation and will result in increased memory allocation until server is restarted. I would suspect memory leak when you see memory usage growing which can’t be connected to any known resource use. For example for global memory leaks you would see memory usage continues to grow even if you close connections and tables regularly. Another common thing about memory leaks is because it is memory which is allocated and forgotten about, unless it is very small blocks, it should be just swapped out and when never needed again. So if you see swap file used space gradually growing and there are “swap outs” but a lot less “swap ins” chances are it is caused by memory leak. Dealing with memory leaks is rather complicated as good tools to detect memory leaks like valgrind are too slow to run in production. So the best thing to do in this case is see whenever you can create isolated repeatable test cases based on your application, which can illustrate memory leak and when it can be found and fixed. This is where your MySQL Support contract can be handy. Conclusion Understanding where MySQL can allocate memory can help us to find the cause in most cases. It is not as straightforward as it should be and I’m very hopeful future releases of MySQL, MariaDB or Drizzle bring improvements in this space allowing us to see directly for what purpose memory is allocated and so detect all kinds of memory usage problems easier. [Less]

Earlier today I posted a Drizzle white paper we've been working on: Drizzle and IPv6. read more

Drizzle white papers highlight unique features and designs in Drizzle, and give you examples of how to use them. We are proud to announce that we have today published our first white paper: Drizzle and IPv6. This white paper will explain how Drizzle ... [More] supports IPv6. The support for IPv6 networking was added to Drizzle early on and is available already in the first Drizzle 7 release. The Drizzle 7.1 release also adds the support for an IPV6 Data Type, to provide a way of storing IPv6 addresses in the database that is both user friendly and efficient. As an introduction we will also cover the differences between IPv4 and IPv6 network addresses and the reasons for transitioning from IPv4 to IPv6 addresses. With this white paper we wish to thank the Google Summer of Code program for supporting Drizzle. The IPV6 data type was developed as a GSOC 2011 project by Muhammad Umair, with Mark Atwood mentoring. The IPv6 networking support was developed by Brian Aker and Eric Day, at the time employed by Sun Microsystems. [Less]

Great news for all Google Summer of Code aspirants. Drizzle has been accepted as a mentoring organization for the 4th time. Students are welcome to look at our wiki page link and find possible projects and mentors. We hangout at #drizzle on IRC ... [More] Freenode. Feel free to drop by and post your queries. We have devs from across the globe and so you may get your queries solved at the earliest. If you cant find us on the IRC channel then just shoot out a mail to our mailing list Drizzle-Discuss. We have good documentation on how to get started with code contribution. We would love to see you submit patches for low hanging fruit when you try to get a fix on your project proposals. We hope to have good talent at Drizzle this year. Put on those thinking hats and get those ready. Happy hacking fellow students!!! [Less]