It has been quite some time since I posted.  This is largely because I have been busy digging into the wonderful world of OpenStack over at HP Cloud Services.

One of the things I have been working on is the new Load Balancing as a Service (LBaaS) offering with former Drizzle teammates Andrew Hutchings and David Shrewsbury.  You can find out more details and whatnot for LBaaS here, but this post isn’t so much about the details of load balancing as it is about the neat CI / CD tricks we are accomplishing via Jenkins and Saltstack.

What we needed was a way to spin up, configure, and manage test slaves without necessarily wanting to manage them forever and always.  I’m sure many Jenkins users out there have dealt with borked, messy, misconfigured slave machines that cost a lot of time and effort to get sorted.  So how to solve this? Hey, I know!  We have a cloud, so let’s use it.  Let’s create on-demand machines that we can configure as needed and then blow away.  Sounds good, right? Well, like many things in life it is easier said than done.

One of the first things we tried was the jclouds plugin for Jenkins.  While this is capable of some interesting tricks, we never really felt it integrated nicely into Jenkins (or maybe I didn’t properly grok its paradigm).  One can create Jenkins jobs to create vm’s, but those new vm’s kind of just float there.  You have to pre-configure the vm (creating a base image) at least a little bit (to allow Jenkins or anyone else to get into it) and perform a variety of other tricks to really ‘own’ the machines.  My experiments with this were rather messy and frustrating.

Enter Saltstack + salt-cloud.  Much like Puppet or Chef (or any number of other tools), salt aims to provide a means of creating, configuring, and controlling other machines.

What I like about it is:

• it is written in Python
• it is rather easy to grasp (ymmv)
• provides a one-stop shop for this
• it is written in Python

What salt-cloud does is provide cloud control / integration for salt.  One can define config files with your cloud credentials, profile files that define vms, and map files that define sets of vm’s based on the profiles.  From there, one can use the tool to create individual vm’s from the command line, or a number of vm’s from a map file.  The vm’s that are created are auto-registered to the salt-master and once they are up and running, they are full and proper salt-minions that you can use to do your bidding immediately >:)

We use the tool for a variety of purposes, but one of the more interesting applications is for testing.  Below is sample output from one of our Jenkins jobs.  What we do is create a vm via a salt-master slave, then configure it to have the python-libraclient and the lbaas test suite.  From there, we use salt to call the test suite and report on the results.  Once the test is done, we can blow away the vm and repeat the process as needed.  We no longer have to manage a lot of Jenkins slaves, just key ones and we can have a practically unlimited array of virtual machines for whatever purposes we may have.

Here, at the start of our test, we simply create a new base vm. The -p is the profile to use. One may have a variety of profiles using different OS’s, sizes, etc.


+ sudo salt-cloud -p hp_az3_large lbaas-client-install-tester1
[INFO ] Loaded configuration file: /etc/salt/cloud
[INFO ] salt-cloud starting
[WARNING ] 'AWS.id' not found in options. Not loading module.
[WARNING ] 'EC2.id' not found in options. Not loading module.
[INFO ] Creating Cloud VM lbaas-client-install-tester1
[WARNING ] Private IPs returned, but not public... checking for misidentified IPs
[WARNING ] 10.2.154.133 is a private ip
[WARNING ] 15.185.228.34 is a public ip
Pseudo-terminal will not be allocated because stdin is not a terminal.
Warning: Permanently added '15.185.228.34' (ECDSA) to the list of known hosts.
Pseudo-terminal will not be allocated because stdin is not a terminal.
Warning: Permanently added '15.185.228.34' (ECDSA) to the list of known hosts.
Pseudo-terminal will not be allocated because stdin is not a terminal.
Warning: Permanently added '15.185.228.34' (ECDSA) to the list of known hosts.
* INFO: /tmp/deploy.sh -- Version 1.5.1
* WARN: Running the unstable version of bootstrap-salt.sh
* INFO: System Information:
* INFO: CPU: GenuineIntel
* INFO: CPU Arch: x86_64
* INFO: OS Name: Linux
* INFO: OS Version: 3.2.0-32-virtual
* INFO: Distribution: Ubuntu 12.04
* INFO: Installing minion
* INFO: Found function install_ubuntu_deps
* INFO: Found function config_salt
* INFO: Found function install_ubuntu_stable
* INFO: Found function install_ubuntu_restart_daemons
* INFO: Running install_ubuntu_deps()

[INFO ] Salt installed on lbaas-client-install-tester1
[INFO ] Created Cloud VM lbaas-client-install-tester1 with the following values:
[INFO ] private_ips: [u'10.2.154.133']
[INFO ] extra: {'updated': u'2013-04-11T16:02:30Z', 'hostId': u'', 'created': u'2013-04-11T16:02:29Z', 'key_name': u'lbaas-saltmaster', 'uri': u'https://az-3.********.compute.hpcloudsvc.com/v1.1/42064206420642/servers/891293', 'imageId': u'48335', 'metadata': {}, 'password': u'thismachinewontlivelongenoughforyoutouseit', 'flavorId': u'103', 'tenantId': u'42064206420642'}
[INFO ] image: None
[INFO ] _uuid: None
[INFO ] driver: [INFO ] state: 4
[INFO ] public_ips: [u'15.185.228.34']
[INFO ] size: None
[INFO ] id: 891293
[INFO ] name: lbaas-client-install-tester1


Once the vm has been created and salt has been installed, we can call state.highstate to configure the machine to install the lbaas client and test suite:


+ sudo salt lbaas-client-install-tester1 state.highstate
lbaas-client-install-tester1:
----------
State: - pkg
Name: required_packages
Function: installed
Result: True
Comment: The following package(s) were installed/updated: python-pip, python-novaclient, git, python-requests, python-prettytable.
Changes: python-novaclient: {'new': '2012.1-0ubuntu1', 'old': ''}
python-setuptools: {'new': '0.6.24-1ubuntu1', 'old': ''}
git: {'new': '1:1.7.9.5-1', 'old': ''}
liberror-perl: {'new': '0.17-1', 'old': ''}
python-pip: {'new': '1.0-1build1', 'old': ''}
python-distribute: {'new': '1', 'old': ''}
python-requests: {'new': '0.8.2-1', 'old': ''}
git-man: {'new': '1:1.7.9.5-1', 'old': ''}
python-greenlet: {'new': '0.3.1-1ubuntu5.1', 'old': ''}
python-gevent: {'new': '0.13.6-1ubuntu1', 'old': ''}
git-completion: {'new': '1', 'old': ''}
python-prettytable: {'new': '0.5-1ubuntu2', 'old': ''}
----------
State: - git
Name: https://github.com/pcrews/libra-integration-tests.git
Function: latest
Result: True
Comment: Repository https://github.com/pcrews/libra-integration-tests.git cloned to /root/libra-integration-tests
Changes: new: https://github.com/pcrews/libra-integration-tests.git
revision: f6290d551188c9239248f0cd0ddcf22470c444d3
...


From there, we can use salt to execute the test suite through the client on the vm and get the results back:

sudo salt \lbaas-client-install-tester1 cmd.run_all cwd='/root/libra-integration-tests' 'python loadbalancer_integration.py --os_username=******** --os_password=******** --os_tenant_name=********-tenant --os_auth_url=https://********.identity.hpcloudsvc.com:35357/v2.0/ --os_region_name=******** --driver=python-client --max_backend_nodes=50 '
0
lbaas-client-install-tester1:
----------
pid:
6567
retcode:
0
stderr:
test_createLoadBalancer (tests.create_loadbalancer.testCreateLoadBalancer)
test creation of loadbalancers for libra ... 20130411-160445 PM Setting up for testcase:
20130411-160445 PM - test_description: basic_positive_name
20130411-160445 PM - lb_name: the quick, brown fox jumps over the lazy dog.
20130411-160445 PM - nodes: [{'port': '80', 'address': '15.185.42.06'}, {'port': '80', 'address': '15.185.42.07'}]
20130411-160445 PM - expected_status: 200
20130411-160445 PM load balancer id: 132651
20130411-160445 PM load balancer ip addr: 15.185.226.182
20130411-160445 PM Validating load balancer detail...
20130411-160446 PM Validating load balancer list...
20130411-160447 PM Validating load balancer nodes url...
20130411-160447 PM testing loadbalancer function...
20130411-160447 PM gathering backend node etags...
20130411-160447 PM testing lb for function...
20130411-160447 PM Deleting loadbalancer: 132651
ok
...


After the tests are finished, we simply blow the vm away via the magic of salt-cloud again:

+ sudo salt-cloud -y -d lbaas-client-install-tester1
[INFO ] Loaded configuration file: /etc/salt/cloud
[INFO ] salt-cloud starting
[WARNING ] 'AWS.id' not found in options. Not loading module.
[WARNING ] 'EC2.id' not found in options. Not loading module.
[INFO ] Destroying VM: lbaas-client-install-tester1
[INFO ] Destroyed VM: lbaas-client-install-tester1
- True
Finished: SUCCESS


</code>

From there, it is easy to create new variations of this work – one can create multiple client minions for stress testing, clients running different sets of tests, etc.  In addition to this, we are doing some neat tricks with Jenkins + salt for monitoring and management.  If you guys are interested in hearing more and learning some nifty cloud / salt voodoo, let me know in the comments section 😉

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 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

My presentation slides are now up here.

The testing tool is still very early days, but it is based on some very cool research from Microsoft’s SQL Server team.

Say what you will about MS (and I have plenty to say), their QA is quite amazing and they are one of the only resources for information related to database QA.  In case you didn’t know, the amazing randgen is based on research from their team as well.

I’ll be posting more about how to play with the tool later and it requires a bit of explanation, so I won’t go too deeply into it.  If someone is just dying to try the new hawtness in dbms testing, you can contact me and I’ll see about getting you sorted.

I’ll just close with an explanation of the source of the name:  kewpie = query probulator.  Futurama for the win!  That and it is every QA Engineer’s dream to have tools that leave the system under test feeling like this:

taken from http://slurmed.com/wall/143/143_fry-probulator_0800.png

Many thanks to Javier for the use of the picture.

So, the amazing Mr. Shrewsbury, master of all things Drizzle-replication, has created a beta tree with multi-master replication!

As his blog explains, it is still very early days, but as it currently stands, it can provide some long-hoped-for functionality for people with particular replication needs.  This feature will definitely be made more robust in the future, but it is still quite useful in its present form.

As I think this would be cool to see in trunk, I have started testing the tree.  No progress has been made on the multi-master side of things…yet, but I am happy to report that all of our existing replication tests (including crash and restore) are passing just fine.

What this means is that the code could make it into trunk without breaking anything for anyone and also providing access to a very cool new feature.  As time permits, we will be expanding the test cases for this feature, but in the interim, I hope that you will enjoy testing Dave’s awesome work ; )

A little while ago, we added EXECUTE functionality to the server.  This lets us either:

• EXECUTE “SELECT * FROM TEST_TABLE”;
• SET @var = “SELECT * FROM TEST_TABLE” ; EXECUTE @var;

We have added a new suite to our test tree and we’ve also started testing this with the randgen.  The random query generator allows for code known as a Validator.  This code executes on a per-query basis and can do whatever you need it to in order to verify a query.  Some examples:

• ResultSetComparator – which compares result sets between two different servers for the same query.  Useful for checking result set correctness against a trusted validation server.  This tool, combined with the optimizer* grammars quickly found over 30 optimizer bugs in MySQL >:-)
• ExecutionTimeComparator  – compares execution times between two servers.  This is useful when checking a patch for a regression, especially in the optimizer.

There is a special type of Validator known as a Transformer.  There are various Transforms that can be used on a query.  The randgen will alter the query in some way (a Transform).  Each Transform states how the result set should relate to the result set of the original query, for example a TRANSFORM_OUTCOME_SUBSET is expected when tacking on a LIMIT clause.  Some Transforms:

• ExecuteAsSPTwice – This takes the original query, creates a MySQL stored procedure from it, then executes it twice in a row.  This was developed due to a MySQL bug
• InlineSubqueries – Converts SELECT…WHERE col_int IN (SELECT col_int…) -> SELECT …WHERE IN (1,3,5) i.e. the actual values returned from the subquery

For Drizzle, we have created two new Transforms.  For each SELECT query generated by a given grammar, the randgen EXECUTE’s it as a string and as a variable.  I’m happy to report that the tests are passing with flying colors and will be added to our automated tests.

It was incredibly easy to create these new Transforms for the randgen.  Now, we get to try the functionality out against every SELECT we can generate via the randgen – we get to cover a lot more ground this way versus trying to craft these tests by hand (though we have added several such tests as previously noted).

Anyway, please feel free to kick the tires on this feature.  I leave it to you to check out EXECUTE…CONCURRENT ; )

It’s been a while since I’ve blogged about the work we are doing on the transaction log.  Basically, our priority has been to ensure that the log and supporting code is rock-solid before we move further along with replication.  The intent is to allow for a wide variety of replication solutions, all of which will be built on the log’s contents.  We’re very concerned with giving developers and users a solid foundation for whatever solution they may use.

In my last post on this topic, we had just created tests for the test-suite and had starting beating on the log with the randgen in single-user scenarios.  This was important as it helped us catch basic bugs before we moved on to more complicated testing.  We have since moved on to high-concurrency testing.  We use the randgen to generate a wide variety of queries, using 5+ connections.  Once all of the queries have been executed, we use the transaction_reader utility to generate SQL from the log file’s contents.  We use this log file to populate a validation server.  From there, we do a comparison of drizzledump output and report an error if any difference is found.

Our randgen grammars use varying levels of ‘noise’.  We issue some pretty awful SQL at times, but when we consulted with the DBA’s at Rackspace, they said they see such things regularly so our log had better be able to handle it : )  We found a number of bugs by throwing fuzzy queries at the server.  Most of these were issues where one query out of several within a transaction would fail and this would cause problems for the entire transaction.  Fortunately, David Shrewsbury and Joe Daly are very devoted to killing any such bugs I may find : )

We have now automated our randgen tests for the transaction log.  That means that these tests will be run against every patch before it can be pushed to trunk; we’ll have early feedback if something breaks.  We also have a param-build job that runs these tests.  If a developer has been working on this code, they can run the tests against their branch to find out if they have broken anything.

At the time of this writing, I would say that the log is pretty solid.  We do have a couple of troublesome outstanding bugs that show up in concurrent testing:

• Differences between slave and master in concurrent testing scenarios – randgen tests using many threads to operate on the same set of tables are producing differences between the master server and a validation server populated from the transaction log’s contents.  Still tracking down the exact interaction that is causing this to fail.
• Transaction ID not unique – we are seeing cases where different transactions in a concurrent environment are using the same transaction id’s

We are still in the process of testing things, but David Shrewsbury and Marcus Ericsson have been making progress with the Tungsten Replicator.  We’ll be working on testing scenarios using that solution once it is ready.  Any developers interested in replication are encouraged to give the transaction log a spin with their favorite solution.  The basics definitely work well, and now would be the time to chime in with your thoughts / needs for the log.  We realize that the concurrency problems are an issue and we’re actively working on resolving these, but things are in a state where one could start testing basic functionality as they saw fit.

As always anyone with any questions, recommendations, or whatever are welcome to contact us via IRC or the mailing list.

Just a quick update on the state of Drizzle’s transaction log as it’s been a while since I’ve mentioned it.

As I wrote earlier, we have already spent some time on basic tests of the transaction log structure – i.e. can we capture INSERT/UPDATE/DELETE/etc correctly?

Well, the next phase of testing is underway and we’re now beating up on things with the randgen!

At present, we are working with a single thread and throwing a large number of randomly generated queries at the log. We have the randgen set up so that it runs 20-30 queries per cycle and we run several hundred cycles. Once these queries have been executed, we make use of drizzled/message/transaction_reader to create SQL from the transaction log contents.

The example below assumes you’ve started the server via ./test-run –start-and-exit –mysqld=–transaction-log.enable and have created some tables to play with.  We call the transaction_reader like this:

drizzled/message/transaction_reader var/master-data/transaction.log

As an example, a query like:

UPDATE dd SET col_bigint=5 LIMIT 5;

Will show up as:

START TRANSACTION;
UPDATE `test`.`dd` SET `col_bigint`=5 WHERE `pk`=3389;
UPDATE `test`.`dd` SET `col_bigint`=5 WHERE `pk`=2329;
UPDATE `test`.`dd` SET `col_bigint`=5 WHERE `pk`=3634;
UPDATE `test`.`dd` SET `col_bigint`=5 WHERE `pk`=2369;
UPDATE `test`.`dd` SET `col_bigint`=5 WHERE `pk`=3674;
COMMIT;

We then send this SQL to a validation server (just another instance of Drizzle) and then compare drizzledump files between the master and the slave.

So far, we’ve found a couple of new crashes, some minor issues with the transaction_reader program, and a couple of issues where the log actually fails to capture data (only UPDATEs have failed in this way so far). I’d like to give a special mention to David Shrewsbury and Joe Daly for their awesomely fast responses to the bugs I’ve found so far : ) We maintain a list of all  transaction log bugs we have found with the testing blueprint.  Most of these bugs are already closed (thanks Dave and Joe!).

Our next steps will be to tweak our single-threaded grammars a bit further, then we will move on to concurrent testing. We’ll be repeating the testing process I laid out above, except that we will let the multiple threads run to completion, say five to ten thousand queries apiece, and then replicate and validate. At the moment, we’re shooting for testing to be complete in time for next week’s milestone release.

We have now upgraded our lcov testing to take advantage of lcov 1.9‘s branch coverage.

While code coverage isn’t the be-all-end-all of testing, it is a very useful tool in helping us target areas that could use more tender, loving care (by which I mean beating them mercilessly with our test tools).  It doesn’t prove completeness of testing – it merely helps us move in that direction : )  The addition of branch-level coverage gives us another dimension to help us expand our testing.  We’re also making use of the –demangle-cpp option to produce neater function names for the function-level coverage.

You can check out the updated reports here.  We gather the code coverage of our test suite with every push to trunk and store the data for general analysis.

In other news, Brian has added two new functions for testing.  These changes were a part of another blueprint to remove print_stack_trace and stack dump. While I haven’t had a chance to do anything with them yet, I think these will be very useful and look forward to seeing what kinds of tests our community will be able to cook up : )

• Crash the server:

% ./drizzled/drizzled –plugin-add=crash_function

select crash();

• Shutdown the server:

% ./drizzled/drizzled –plugin-add=shutdown_function

select shutdown()

Finally, I have been playing with the syslog plugin and it is awesome!  It is easy to setup and is very easy to use.  The data produced will be very useful in testing as well.  An example produced via a randgen run for your enjoyment:

I know I write a lot about testing with the randgen. In case anyone is interested, I wanted to share some links to randgen documentation and information. It really is an amazing and powerful tool and has found many bugs in MySQL, MariaDB, and Drizzle.

• It can generate queries faster than anyone could ever think of them.  This provides breadth of input testing and allows for stress testing (multi-threaded scenarios are supported)
• A number of Validators are available to determine query correctness.  These include server-server comparison validators for testing result sets, execution time comparison validators for comparing query execution time, and others.  Custom validators are implemented fairly easily
• Grammar-based testing allows for faster generation of test queries.  Rather than thinking of individual queries, one can focus on ‘patterns’ of queries and have the randgen generate hundreds of queries for the same amount of time and effort.

With that little sales pitch out of the way, here are the links to documentation.

• MySQL Forge – general documentation
• Drizzle wiki – information on how to run randgen tests and what to look for
• DrizzleQA – links to information about all Drizzle testing

I will be writing more about how to use this tool in the future, but I’d definitely welcome feedback on what people are interested in.  The tool has applications for testing patches for regressions, validating upgrades based on current workloads, stress testing, etc. – feel free to let me know what you’d like to learn about.  Discussion is always welcome – stop by IRC (#drizzle) or drop us a line on our mailing list.