Overview

SUMMARY

apiary is a load-testing framework for MySQL, written in Python, that replays captured queries, simulating production load patterns.

A QueenBee process feeds sequences of one or more messages into an AMQP queue, and one or more WorkerBee processes retrieve sequences from the queue, send the associated messages to the specified target host, and report their results to the BeeKeeper to be tallied. The QueenBee prints a summary of the progress every 15 seconds and when all the WorkerBees are done. The QueenBee and WorkerBees work together to attempt to mimic production load patterns as closely as possible, including timing.

Query timing can be slowed down or sped up by an arbitrary factor using the --speedup option. In testing, Apiary has reproduced 30,000 queries per second when run on a powerful test system. An ideal system will have at least 4 CPU cores that are as fast as possible. This is preferable to more cores that are slower. The gating factor tends to be RabbitMQ.

REQUIREMENTS

  • RabbitMQ >= 1.6
  • lsprof (if you want to use --profile)

http://codespeak.net/svn/user/arigo/hack/misc/lsprof/

  • py-amqplib (only tested with an implementation of the 0-8 spec)

http://hg.barryp.org/py-amqplib/file/

  • maatkit or percona-toolkit (either may be available in your Linux distribution)

http://www.percona.com/software/percona-toolkit

RABBITMQ SETUP

To configure a local, running instance of RabbitMQ, execute the following:

sudo bin/apiary_setup_rabbitmq.sh

This will delete the apiary vhost and user, re-add them, and then set up the appropriate permissions. This must be run as root.

TUTORIAL

This tutorial assumes you have mysql running on localhost with no password for the root user. Adapt the commands below appropriately if that is not the case.

  1. Some sample data is included. This will create an "apiary_demo" database:

    mysql -u root < doc/examples/demo.sql

  2. Use tcpdump to capture mysql traffic for consumption by pt-query-digest:

    sudo tcpdump -i lo port 3306 -s 65535 -x -n -q -tttt> /tmp/tcpdump.out

  3. In a separate terminal, generate some queries against the test data:

    while read query; do

    mysql -u root -h localhost --protocol=TCP -e "$query" apiary_demo > /dev/null; sleep 0.1;

    done < doc/examples/demo_queries.sql

  4. Stop the tcpdump process that you started in step 2.

  5. Turn the tcpdump data into a query digest using percona-toolkit:

    pt-query-digest --type=tcpdump --no-report --print /tmp/tcpdump.out > /tmp/apiary_query_digest.txt

Use mk-query-digest if you have maatkit instead of percona-toolkit. They both work identically.

  1. Convert the query digest into a sequence file:

    bin/genjobs /tmp/apiary_query_digest.txt > /tmp/apiary_test.jobs

  2. Run apiary:

    bin/apiary --workers 10 --mysql-user root --mysql-db apiary_demo --mysql-host localhost /tmp/apiary_test.jobs

  1. Apiary will fork Queenbee, WorkerBee, and Beekeeper processes internally.

They will work together to read and execute your traffic. Apiary will very quickly print this message:

Waiting for workers to complete jobs and terminate (may take up to 300 seconds)...

This is because the QueenBee prefills the job queue with up to 300 seconds of query traffic to make sure that the WorkerBees never run out of jobs to run.

  1. You should see a summary of the results as queries are being executed and
after all queries have been replayed.

NOTES

I found this command line useful to reset between tests:

sudo pkill -f rabbitmq; pkill -f bin/apiary; sudo /etc/init.d/rabbitmq-server start; sudo bin/apiary_setup_rabbitmq.sh ; watch -n 1 sudo rabbitmqctl list_queues -p /apiary

I found these networking settings were useful with large query volumes:

sudo bash -c 'echo 1 > /proc/sys/net/ipv4/tcp_tw_reuse' sudo bash -c 'echo 1024 65535 > /proc/sys/net/ipv4/ip_local_port_range'

Otherwise, you may start to see errors like "Could not connect to MySQL host", or "resource not available", because the kernel will quickly run out of local ports it's willing to use.