Drizzle

I promised to still post some general comments about the MySQL ecosystem, to conclude my outlook of State of the MySQL forks and Drizzle. I will do this now in the form of answering questions I got in the comments, twitter and some that I make up just myself. Oracle has not stopped updating bzr trees read more

I got several comments and questions on my previous blog "The State of the MySQL forks". One question was "Why didn't you mention Drizzle?" So I will say something about Drizzle here before concluding with other remarks. So why didn't you mention ... [More] Drizzle? Mainly because the post was already long and also I had to wrap up and call into a meeting. read more [Less]

Call for papers is still open for Percona Live London 2012, but only for few more days. We’re looking for great talks relevant for MySQL Ecosystem it could be about MySQL and its variants as well as technologies which are used together with MySQL. ... [More] Please consider submitting if you’re actively using MySQL, Percona Server, MariaDB or Drizzle in production – the practical war stories are always very interesting. If you’re practicing MySQL consultant or other service vendor we would like to hear about your experience with multiple environments which gives unique prospective. If you’re working with MySQL or its variants on code Level and can share some prospective about internals or explain how features work in depth it would be a gem. Do not feel intimidated. Many of the most attended and appreciated talks are talks on simple topics done well. Many people come to the conferences to learn and we need a content for people only starting at MySQL community not just advanced topics for veterans. The call for papers is extended until August 26th to give you a few more days to gather your thoughts. We recognize a lot of people have been on vacation mid August and could not submit in time. Looking to attend instead ? Registration is open now with super saver prices. [Less]

It's been some time since I last wrote an overview of the state of the MySQL forks, but the last few weeks have been eventful enough that it is a good time to again see how the competing variants are positioned against each other. I have written on ... [More] this topic 1-2 times a year. Here are links to the previous overviews: Map of MySQL forks and branches (2010) The state of MySQL forks: co-operating without co-operating (2010) Observations on Drizzle and PostgreSQL Percona.tv: State of the MySQL ecosystem (2011) State of the MySQL forks: via a particular example of authentication plugins As is the case with this post, many of those previous ones coincide with some events or discussion at the time they were written, and the posts contain links to other bloggers commenting on whatever was current then. read more [Less]

Hi there, This blog entry is to make you comfortable working with Drizzle’s RabbitMQ plugin. I will also demonstrate how this can be used dynamically. Introduction RabbitMQ is a message broker. It is like a postoffice which accepts and forwards the ... [More] messages to different queues. There may be applications built over these queues which receive the messages and process them. RabbitMQ plugin registers few system variable with drizzle client, namely, logging_enable: This shows if logging function is enabled or not. If disabled, messages will not be forwarded to rabbitMQ server. host: Specifies the host name for rabbitMQ server. IP address of server can also be provided. port: Port on which rabbitMQ is running. Default port is 5672 username and password: Logging credentials required for access to rabbitMQ server virtualhost: This specifies the virtual host in which the client intend to operate. (I am not very sure about this.). exchange: This specifies the name of the rabbitMQ exchange. No message is directly pushed to the desired queue. Instead, they are redirected to exchanges. It is the job of exchanges to decide which queue it wants to push the message into. routingkey: This is the name of the queue to which we wants to push the message into. Plugin in Action First make sure that rabbitmq server and corresponding libraries are installed on your system. Refer here for more details. Now you will have to rebuild drizzle to build rabbitmq plugin. Start drizzle server with rabbitmq plugin loaded. ansh@ubuntu:~/repos/drizzle/rabbitmq_dynamic$ drizzled/drizzled –plugin-add=rabbitmq Now we will start drizzle and check the corresponding variables registered. ansh@ubuntu:~$ drizzle –user ansh Welcome to the Drizzle client.. Commands end with ; or \g. Your Drizzle connection id is 2 Connection protocol: mysql Server version: 7.2.2.2569-snapshot Source distribution (rabbitmq_dynamic)Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.drizzle> SHOW VARIABLES LIKE “%rabbitmq%”; +————————-+———————–+ | Variable_name | Value | +————————-+———————–+ | rabbitmq_exchange | ReplicationExchange | | rabbitmq_host | localhost | | rabbitmq_logging_enable | ON | | rabbitmq_password | guest | | rabbitmq_port | 371725864 | | rabbitmq_routingkey | ReplicationRoutingKey | | rabbitmq_username | guest | | rabbitmq_virtualhost | / | +————————-+———————–+ 8 rows in set (0.001047 sec) These all are the default variables for the rabbitMQ plugin. We can also pass the desired values when starting the server. ansh@ubuntu:~/repos/drizzle/rabbitmq_dynamic$ drizzled/drizzled –plugin-add=rabbitmq –rabbitmq.port=5672 –rabbitmq.routingkey=ReplicationRoutingKey –rabbitmq.username=”guest” –rabbitmq.password=”guest” Now lets try changing some variable to new value. Lets change the name of exchange to “MyOwnExchange” drizzle> SET GLOBAL rabbitmq_exchange=”MyOwnExchange”; ERROR 1210 (HY000): Incorrect arguments to SET Whoa! What is this error? The variable value is perfectly valid. Well, on going to the server console, you will figure out the error. Value of rabbitmq_exchange cannot be changed as rabbitmq plugin is enabled. You need to disable the plugin first. Value of variables cannot be changed when plugin is loaded. So, as the plugin suggests, lets stop the plugin first and then change the value. drizzle> SET GLOBAL rabbitmq_logging_enable=false; Query OK, 0 rows affected (0.001198 sec)drizzle> SET GLOBAL rabbitmq_exchange=”MyOwnExchange”; Query OK, 0 rows affected (0.000236 sec) drizzle> SHOW VARIABLES LIKE “%rabbitmq%”; +————————-+———————–+ | Variable_name | Value | +————————-+———————–+ | rabbitmq_exchange | MyOwnExchange | | rabbitmq_host | localhost | | rabbitmq_logging_enable | OFF | | rabbitmq_password | guest | | rabbitmq_port | 371725864 | | rabbitmq_routingkey | ReplicationRoutingKey | | rabbitmq_username | guest | | rabbitmq_virtualhost | / | +————————-+———————–+ 8 rows in set (0.00106 sec) drizzle> SET GLOBAL rabbitmq_logging_enable=true; Query OK, 0 rows affected (0.003609 sec) No error! This is all from database point of view. But how to be sure, if messages are sent to rabbitMQ server or not? In the rabbitmq c library, there are few codes in /examples/ which can be used for this purpose. Open a separate terminal, go to the examples folder of c library and type ansh@ubuntu:~/Desktop/rabbitmq/rabbitmq-c/examples$ ./amqp_listen localhost 5672 MyOwnExchange ReplicationRoutingKey This program will now listen for all the messages sent to rabbitMq server’s MyOwnExchange. Now lets query something and check if it actually works. drizzle> INSERT INTO drizzleslap.t1(intcol1, charcol1) VALUES(123456, “Test Entry”); Query OK, 1 row affected (0.121521 sec) On the listener terminal.. ansh@ubuntu:~/Desktop/rabbitmq/rabbitmq-c/examples$ ./amqp_listen localhost 5672 MyOwnExchange ReplicationRoutingKey Result 0 Frame type 1, channel 1 Method AMQP_BASIC_DELIVER_METHOD Delivery 1, exchange MyOwnExchange routingkey ReplicationRoutingKey —- 00000000: 0A 18 08 01 10 83 BE 04 : 18 9D CC E7 B9 A4 D5 B1 ……………. 00000010: 02 20 F6 C7 EB B9 A4 D5 : B1 02 12 72 08 01 10 A6 . ………r…. 00000020: CC E7 B9 A4 D5 B1 02 18 : D7 CC E7 B9 A4 D5 B1 02 ……………. 00000030: 2A 36 0A 11 0A 0B 64 72 : 69 7A 7A 6C 65 73 6C 61 *6….drizzlesla 00000040: 70 12 02 74 31 12 06 08 : 05 12 02 69 64 12 0B 08 p..t1……id… 00000050: 04 12 07 69 6E 74 63 6F : 6C 31 12 0C 08 01 12 08 …intcol1…… 00000060: 63 68 61 72 63 6F 6C 31 : 32 24 08 01 10 01 1A 1E charcol12$…… 00000070: 0A 02 37 31 0A 06 31 32 : 33 34 35 36 0A 0A 54 65 ..71..123456..Te 00000080: 73 74 20 45 6E 74 72 79 : 10 00 10 00 10 00 20 01 st Entry…… . 00000090: 28 01 : (. 00000092: So, this actually works. Similar is the case with other variables. I could have demonstrated them as well, but it required creating new settings in rabbitMQ server (like creating new user, vhosts and setting the permissions etc), which may be a bit confusing. You can get back to me if in case you need that to be demonstrated as well. [Less]

From the release notes: Fix for CTRL-Z for shutdowns. Many updates for JSON server. Improvements completed on Catalog support. ZeroMQ and Gearman supported updated. Updates AUTH_HTTP authentication module. Documentation enhancements. Testing ... [More] framework updated. Regex Policy updates. Stewart can say a lot more about Catalog support, but this is one of the cooler bits of work that is happening ongoing. The original work was began for Rackspace and continues today. Virtualization inside of the database is long overdue. The JSON server is another one of the locations where we see a lot of effort. Being able to support JSON queries directly seems to resonate with a lot of folks today. For the record I wrote zero lines of codes in this release. Work continues, and in the case of Drizzle it is all about work coming in from the community :) [Less]

Just for the pure insane fun of it, I accepted the challenge of “what can you do with the text format of the schedule?” for BarCampMel. I’m a database guy, so I wanted to load it into a database (which would be Drizzle), and I wanted it to be easy to ... [More] keep it up to date (this is an unconference after all). So… the text file itself isn’t in any standard format, so I’d have to parse it. I’m lazy and didn’t want to leave the comfort of the database. Luckily, inside Drizzle, we have a js plugin that lets you execute arbitrary JavaScript. Parsing solved. I needed to get the program and luckily we have the http_functions plugin that uses libcurl to allow us to perform HTTP GET requests. I also wanted it in a table so I could query it when not online, so I needed to load the data. Luckily, in Drizzle we have the built in EXECUTE functionality, so I could just use the JavaScript to parse the response from the HTTP GET request and construct SQL to load the data into a table to then query. So, grab your Drizzle server with “plugin-add=js” and “plugin-add=http_functions” in the config file or as options to drizzled (prefixed with –) and…. This simple one liner pulls the current schedule and puts it into a table called ‘schedule’: SELECT EXECUTE(JS("function sql_quote(s) {return s ? '\"'+ s.replace('\"', '\\\"') + '\"' : 'NULL'} function DrizzleDateString(d) { function pad(n) { return n<10 ? '0'+n : n } return d.getFullYear()+'-'+pad(d.getMonth()+1)+'-'+pad(d.getDate())+' '+pad(d.getHours())+':'+pad(d.getMinutes())+':'+pad(d.getSeconds()) } var sql = 'COMMIT;CREATE TABLE IF NOT EXISTS schedule (start_time datetime, stage varchar(1000), mr2 varchar(1000), mr1 varchar(1000), duration int); begin; delete from schedule;' ; var time= new Date;var input= arguments[0].split(\"\\n\"); var entry = new Array(); var stage, mr2, mr1; for(var i=0; i < input.length; i++) { var p= input[i].match('^(.*?) (.*)$'); if(p) {if(p[1]=='Time') { time=new Date(Date.parse(p[2]));} if(p[1]=='Duration') { sql+='INSERT INTO schedule (start_time,stage,mr2,mr1,duration) VALUES (\"' + DrizzleDateString(time) + '\", ' + sql_quote(stage) + ', ' + sql_quote(mr2) + ',' + sql_quote(mr1) + ',' + p[2] + '); '; time= new Date(time.getTime()+p[2]*60*1000); stage= mr2= mr1= ''; } if(p[1]=='stage') {stage=p[2]} if (p[1]=='mr2') {mr2=p[2]} if (p[1]=='mr1') {mr1=p[2]} }}; sql+='COMMIT;'; sql", (select http_get('https://dl.dropbox.com/s/01yh7ji7pswjwwk/live-schedule.txt?dl=1')))); Which you can then find out “what’s on now and coming up” with this query: select * from schedule where start_time > DATE_ADD(now(), INTERVAL 9 HOUR) ORDER BY start_time limit 2\G But it’s totally not fun having to jump to the command line all the time, and you may want it in JSON format for consuming with some web thing…. so you can load the json_server plugin and browse to the port that it’s running on (default 8086) and type the SQL in there and get a JSON response, or just look at the pretty table there. [Less]

I’m glad to announce the third Percona Playback release – another alpha release of a new software package designed to replay database server load. The first two versions were released in April, just in time for my talk at the Percona Live MySQL ... [More] Conference and Expo: Replaying Database Load with Percona Playback. This is still very much under development, so there’s likely going to be bugs. Please feel free to report bugs here: https://bugs.launchpad.net/percona-playback Percona Playback is designed to replay database load captured either in a MySQL slow query log or a tcpdump capture of the MySQL protocol exchange between client and server. It can replay the load either as fast as possible or in accurate mode, where it tries to replay load over the same wall time as the capture. Current Notable Limitations: tcpdump replay: IPv4 only tcpdump replay: no support for server side prepared statements Build requirements: libtbb-dev (Intel Threading Building blocks) boost (including boost program_options) intltool gettext libpcap-dev libcloog-ppl (if using gcc 4.6) libmysqlclient-dev libdrizzle-dev pkg-config Source Tarball: percona-playback-0.3.tar.gz (md5, sig) We’ve tested building on CentOS/RHEL 6, Ubuntu 10.04LTS (lucid), Ubuntu 12.04 (precise) and Debian 6. You can build using the standard: ./configure && make && make install Run the test suite: make check We are planning binary packages for the next development milestone. [Less]

Sometimes we have a strong pizza craving, so we decided to create a nutritious, guilt-free version that delights our taste buds. This is a very basic version, but feel free to add your favorite veggie toppings. We often add chopped broccoli ... [More] , tomatoes, and sometimes zucchini - whatever we have on hand! We usually use the nut-cheese topping below to keep it 100% whole-foods, but once in a while we'll replace it with a store-bought non-dairy cheese (good for first-time vegan pizza eaters). Crust ingredients: 1 cup brown rice flour 1 cup whole wheat pastry flour (or oat flour for a gluten-free option) 2 teaspoons baking powder pinch of salt, pepper, garlic powder, and onion powder 3/4 cup water Preheat the oven to 400 degrees F. Mix all dry ingredients in a bowl. Add water and combine thoroughly. Use hands to form a dough ball. Sprinkle flour on a greased cookie sheet or pie plate and roll out dough to 1/4" thick. Bake for 15 minutes. Topping ingredients: 1 batch nut-cheese sauce (below)* 1 cup mushrooms (sliced) 1/2 medium onion (chopped) 10-12 oz extra firm tofu (grated) 4 oz tomato paste Spread tomato paste evenly on pre-baked crust. Top with onions and mushrooms, then tofu. Drizzle on nut-cheese sauce and spread evenly. Lower oven to 350 degrees F. Bake for 25-35 minutes until onions are done. * You can replace the nut-cheese sauce with 1-2 cups of shredded non-dairy cheese (i.e. Daiya, Follow Your Heart, etc.) Nut-cheese Sauce ingredients: 1 cup cashews (or other nuts) 3/4 cup water 1/4 cup nutritional yeast 1/2 teaspoon salt Place all ingredients in blender and blend on high until sauce is smooth. Add more water if needed. Yields: About 6 servings [Less]

For Drizzle and for all of the projects we work on at Percona we use the Bazaar revision control system (largely because it’s what we were using at MySQL and it’s what MySQL still uses). We also use Jenkins. We have a lot of jobs in our Jenkins. A ... [More] lot. We build upstream MySQL 5.1, 5.5 and 5.6, Percona Server 5.1, Percona Server 5.5, XtraBackup 1.6, 2.0 and 2.1. For each of these we also have the normal trunk builds as well as parameterised ones that allow a developer to test out a tree before they ask for it to be merged. We also have each of these products across seven operating systems and for each of those both x86 32bit and 64bit. If we weren’t already in the hundreds of jobs, we certainly are once you multiply out between release and debug and XtraBackup being across so many MySQL and Percona Server versions. I honestly would not be surprised if we had the most jobs of any user of the Bazaar plugin to Jenkins, and we’re probably amongst the top few of all Jenkins installations. So, in August last year we discovered a file descriptor leak in the Bazaar plugin. Basically, garbage collection doesn’t get kicked off when you run out of file descriptors. This prevented us from even starting back up Jenkins until I found and fixed the bug. Good times. We later hit a bug that was triggered in the parallel loading of jobs during startup. We could get stuck in an infinite loop during Jenkins starting that would just eat CPU and get nowhere. Luckily Jenkins provides a workaround: specify “-Djenkins.model.Jenkins.parallelLoad=false” as an argument and it just does it single threaded. For us, this solves that problem. We were also hitting another problem. If you kill bzr at just the wrong time, you can leave the repository in not an entirely happy state. An initial branch can be killed at a time where it’ll think it’s a repository rather than a checkout and there’s a bunch of other weirdness (including file system corruption if you happen to use bad VM software). The way we were solving this was to sometimes go and “clean workspace” on the jobs that needed it (annoying with matrix builds). We’d switched to just doing “clean tree” for a bunch of builds. The problem with doing a clean tree was that “bzr branch” to check out the source code could take a very long time – especially for Percona Server which is a branch of MySQL and hence has hundreds of megabytes of history. We couldn’t use bzr shared repositories as we kept hitting concurrency bugs when more than one jenkins job was trying to do a bzr operation at the same time (common when matrix builds kick off builds for release and debug for example). So.. I fixed that in the Jenkins bazaar plugin too (which should be in an upcoming release) and we’ve been running it on our Jenkins instance for the past ~2 months. Basically, if we fail to check out the Bazaar tree, we wipe it clean and try again (Jenkins has a “retry count” for source checkouts). This is a really awesome form of self healing. Even if the bazaar team fixed all the bugs, we’d still have to go and get that new version of bzr on all our build machines – including ancient systems such as CentOS 5. Not as much fun as bashing your head into a vice. After all of that, I seem to now be the maintainer of the Bazaar plugin for Jenkins as Monty pointed out I was using it a lot more than him and kept finding and fixing bugs. Soooo… say hello to the new Jenkins Bazaar plugin maintainer, me. Yes, I maintain Java code now. Be afraid. Be very afraid. [Less]