PlanetMysql.ru – информация о СУБД MySQL

You know already that InnoDB in MySQL 5.5 has great improvements in performance and scalability. You will have to wait a few months for that, though, because MySQL 5.5 is not GA yet. But if you need some extra performance in MySQL 5.1, you may want to use the Innodb Plugin instead of the built-in one. As of version 5.1.47, the Innodb plugin is of GA quality, and it comes with a good out-of-the-box improvement compared to the built-in engine.

innodb_buffer_pool_size=5G

sysbench \
    --test=oltp \
    --oltp-table-size=1000000 \
    --mysql-db=test \
    --mysql-user=$PASSWD \
    --mysql-password=$USER \
    --mysql-host=$HOST \
    --mysql-port=$PORT \
    --max-time=60 \
    --oltp-read-only=$ON_OFF \
    --max-requests=0 \
    --num-threads=8 run

Generally I avoid going down rabbit holes but today I decided to see how deep a particular testing rabbit hole went. This post is a third in what seems be a continuing series of programming anecdotes. It’s not particularly MySQL-related so you can stop reading here unless you grok code stuff.

Before beginning work on issue 720 I ran the mk-table-checksum test suite to make sure it was in working order. No sense writing new tests and code when the old tests and code aren’t reliable. I actually made one seemingly innocuous change to the test suite in preparation for the issue: I changed the –replicate checksum table from MyISAM to InnoDB.

Surprisingly, the test suite proved unstable. Random tests would fail at random times. Some instability was due to new tests for other issues that I wrote poorly and hadn’t been run a zillion times yet. But other instability was due to switching the checksum table to InnoDB. I knew this because I could switch it back to InnoDB and the tests were ok. Thus began my descent into the rabbit hole.

In particular the test for issue 982 was not finishing or, if it did, it took forever. I discovered that the problem had to do with –lock even though it shouldn’t since the docu says that –replicate and –lock are useless together because the former eliminates locking concerns. Obviously not. So for this I created issue 1027.

Then I turned my attention to test 207_issue_51.t for issue 51 which was opened 2 years ago and closed 1 year ago as WontFix because the problem could not be reproduced. Some might have thought it forgotten amongst the nearly 1,000 issues since its time, but it was found again at the end of this rabbit hole and revivified.

It turns out that issue 51 is, as the original reporter stated (partially), caused by –wait when (this is the part the original reporter didn’t state) the –replicate checksum table is InnoDB because –wait enables –lock which disables AutoCommit so writes to the checksum table may not be committed.

So the rabbit hole lead from issue 720 to issue 982 to issue 1027 to issue 51. Then end results will be a needed bug fix and faster, more stable tests. Sometimes it pays to follow the rabbit.

I’ve recently had the pleasure of reading “Python Testing: An easy and convenient approach to testing your python projects” from Packt Publishing. It’s been a quick read but a solid set of instructions on the different methods for the subject.

The book starts out very quickly with details about the various methods that are available, the means of automation for testing, and of course the environment you’d want to be in for working on the subjects that the book covers. It then, in the second chapter, moves into the guts of testing by describing the basics of doctest via syntax and some simple examples, and then moves on to a real world example via the AVL tree. It’s all very basic testing until chapter three where the author gets into unit testing, which is probably the most useful method in my opinion, and he goes to prove it’s usefulness with examples of it’s use in different parts and stages of the development process. Later in the book the python mocker is used to separate unit sections, and then the actual unittest framework is discussed with more examples and a enough details that if you don’t understand it by then, you may never. By chapter six we are into the Nose app that drives the unittest, which is very useful of course.

The most useful part of the book comes toward the end where the author discusses and the walks through the method used to create a test-driven application and then even shows examples via the whole chapter dedicated towards making a testable web application frontend. Very impressive for such a quick read. Integration testing and System testing is also covered, thankfully. The final chapter covers some useful tools and techniques of which I particularly enjoyed the section on version control hooks. If you are not using version control in your development process you need to start now, as such the hooks for integration with the test framework are rather useful to know.

Overall this is a very nice book that discusses python application testing from the ground up. It’s perfect for a beginner or an intermediate python programmer that has little to no experience in automated testing methods. More advanced programmers that have already used these methods will probably not find the book too useful except for the last chapter that covers extra tools and techniques that they might not have seen before. If I didn’t have this book and needed to learn about python testing, it would be my first choice and my only recommendation so far. Well written and very useful.

If there is one thing I do not like about the book, it would be the reliance on the python CLI for running commands. I am a CLI kind of person and I keep lots of terminals open at the same time, so I prefer to write my code in an editor or IDE in one term tab, then switch to another and execute the script; I do not use the python command line to do much of anything. So following some of the steps in the book require that you follow the CLI method and that gets old for me. It’s a personal preference but one worth noting as there is a lot of it in the book. That’s the only thing I did not enjoy in a book that was otherwise basically perfect for the subject.

I have now published the Buildbot configuration file that we use for our continuous integration tests in our Buildbot setup. Every push into main and development branches of MariaDB is built and tested on a range of platforms to catch and fix any problems early (and we also test MySQL releases before merging to easily see whether any new problems already existed in MySQL or were introduced by something specific to MariaDB).

The configuration is included in the Tools for MariaDB Launchpad project.

Now, the Buildbot configuration file is not something that most MariaDB users will need or want to care about, of course. But I think it is still very important to have it publicly available, not sitting on some private server of the company Monty Program AB.

The reason is that the whole idea with MariaDB is to make a community branch of MySQL, developed by the community and for the community. We want MariaDB the project to be bigger than Monty Program AB the company. And since the Buildbot testing is so central to the whole MariaDB development process, the Buildbot setup also needs to be available for the community. Want to improve the setup, just see what it is doing, or even set up your own master to show you can do a better job (and yes our Windows setup currently really suck)? Just go ahead! Wondering how the Buildbot setup can be continued if Monty Program AB disappears or turns fascist? Now there is an answer.

Hopefully the configuration can also be useful as an example for people doing fancy things with Buildbot. There is some cool stuff in there. Like creating a source tarball on a linux host, and uploading it to be built on a Windows host (this is how releases are done, so important to check that no files are missing from the source tarball). Another cool thing is the builders that boots op KVM virtual machines on demand to build and test binary packages (.deb, .rpm, and .tar.gz) on all of the 18 Linux platforms we currently release for.

BTW, you do not get the miscellaneous passwords in the published configuration file, sorry! :-)

[The license for the configuration file (which is in fact a sizable Python script, as this is the way Buildbot is configured) is GPL.]

I have now published the Buildbot configuration file that we use for our continuous integration tests in our Buildbot setup. Every push into main and development branches of MariaDB is built and tested on a range of platforms to catch and fix any problems early (and we also test MySQL releases before merging to easily see whether any new problems already existed in MySQL or were introduced by something specific to MariaDB).

The configuration is included in the Tools for MariaDB Launchpad project.

Now, the Buildbot configuration file is not something that most MariaDB users will need or want to care about, of course. But I think it is still very important to have it publicly available, not sitting on some private server of the company Monty Program AB.

The reason is that the whole idea with MariaDB is to make a community branch of MySQL, developed by the community and for the community. We want MariaDB the project to be bigger than Monty Program AB the company. And since the Buildbot testing is so central to the whole MariaDB development process, the Buildbot setup also needs to be available for the community. Want to improve the setup, just see what it is doing, or even set up your own master to show you can do a better job (and yes our Windows setup currently really suck)? Just go ahead! Wondering how the Buildbot setup can be continued if Monty Program AB disappears or turns fascist? Now there is an answer.

Hopefully the configuration can also be useful as an example for people doing fancy things with Buildbot. There is some cool stuff in there. Like creating a source tarball on a linux host, and uploading it to be built on a Windows host (this is how releases are done, so important to check that no files are missing from the source tarball). Another cool thing is the builders that boots op KVM virtual machines on demand to build and test binary packages (.deb, .rpm, and .tar.gz) on all of the 18 Linux platforms we currently release for.

BTW, you do not get the miscellaneous passwords in the published configuration file, sorry! :-)

[The license for the configuration file (which is in fact a sizable Python script, as this is the way Buildbot is configured) is GPL.]

Testing and automation. These two are key to ensuring high quality of software releases.

Ever since I worked briefly in the team at MySQL AB that is responsible for creating the binary (and source) packages of MySQL releases, I have had the vision of a fully automated release procedure. Whenever someone pushes a new commit to the release branch revision control tree, the continuous integration test framework should kick in and do all the steps needed for producing release packages:

• Checkout the new revision.
• Build a source tarball, and save it.
• For each platform, build a binary package from the source tarball. The build should be done in a freshly installed machine without any revision control checkouts, previous build trees, or extra installed software, to ensure that no unwanted dependencies or stray references to other files or packages are introduced.
• For each platform, install the binary packages, this time on freshly installed machines with also no build tools (compilers, development packages, etc.) installed, to check that they install correctly without unexpected dependencies. Run tests of the installed server, including starting the server and running basic queries and test suites.
• Upgrade testing, installing the new packages on machines prepared with earlier installations, and testing that the upgrade procedure works and preserves old data.

To do this efficiently, clearly the use of virtual machines is needed. This weekend I played with KVM and Buildbot, and managed to set up a proof-of-concept of this that I am really pleased with.

KVM

There are lots of options for virtualisation these days, including KVM, Xen, VirtualBox, and Vmware. I use KVM, and I really like it. The integration into the distributions is excellent (sudo apt-get install kvm and you're up and running). The interface is powerful and flexible, and at the same time really simple to learn and use. Just a couple of commands with man pages, like it should be in a Unix system. Basically, it just works!

I started by installing a basic ubuntu Jaunty server in a virtual machine:

  qemu-img create -f qcow2 vm-jaunty-i386-base.qcow2 8G
  kvm -m 2047 -hda vm-jaunty-i386-base.qcow2 -cdrom ubuntu-9.04-server-i386.iso \
    -boot d -smp 2 -cpu qemu32,-nx -net nic,model=virtio -net user -redir tcp:2222::22

Using the virtio network driver greatly improved throughput for me when copying things into and out of the virtual machine. The -cpu qemu32,-nx (disable "No eXecute" support) is needed in this case due to some bug or incompatibility, or the installation hangs upon reboot. As usual Google is your friend in cases like this:

• https://bugs.launchpad.net/ubuntu/+source/kvm/+bug/396219
• http://episteme.arstechnica.com/eve/forums/a/tpc/f/96509133/m/303006642931

Incidentally, I did this using remote X over an SSH connection. This works fine, no need for physical access to the host server. After installation we will run the virtual machine without a graphic console, but it was just easier to use the stock Ubuntu installer than trying to find a way to install over the emulated serial port.

Initial setup

Next I did some basic preparation to make the installed virtual machine work well for command line and script usage. However, the amount of extra packages installed is kept to a minimum to get proper testing against unwanted dependencies.

I Installed ssh server for remote access. I then set it up to use the serial console (as we will be running kvm in -nographic mode). To get a login prompt on serial port 0, create /etc/event.d/ttyS0:

    start on stopped rc2
    start on stopped rc3
    start on stopped rc4
    start on stopped rc5
    stop on runlevel 0
    stop on runlevel 1
    stop on runlevel 6
    respawn
    exec /sbin/getty 115200 ttyS0

    serial --unit=0 --speed=115200 --word=8 --parity=no --stop=1
    terminal --timeout=3 serial console

    sudo adduser --disabled-password buildbot
    sudo adduser buildbot sudo
    sudo visudo
    # uncomment `%sudo ALL=NOPASSWD: ALL'

    ssh-keygen -t dsa

    kvm_pid_2222' ; exec kvm -m 2047 -hda /kvm/vms/vm-jaunty-i386-makedist.qcow2 \
        -redir 'tcp:2222::22' -boot c -smp 2 -cpu qemu32,-nx -nographic \
        -net nic,model=virtio -net user
    # We should get a login prompt in the terminal window
    ssh -p 2222 buildbot@127.0.0.1 'sudo id'
    # We should get root access without login or sudo asking for password.

    qemu-img create -b vm-jaunty-i386-base.qcow2 -f qcow2 vm-jaunty-i386-makedist.qcow2

    sudo apt-get install bzr
    sudo apt-get build-dep mysql-5.1-server

    scp -rp -P 2222 .bzr buildbot@127.0.0.1:

    qemu-img create -b vm-jaunty-i386-base.qcow2 -f qcow2 vm-jaunty-i386-build.qcow2

    qemu-img create -b vm-jaunty-i386-base.qcow2 -f qcow2 vm-jaunty-i386-install.qcow2

     sudo adduser --system --group mysql

    sudo apt-get install buildbot
    sudo addgroup buildbot kvm  # To allow buildbot to run kvm
    sudo -u buildbot buildbot create-slave --usepty=0 /var/lib/buildbot/maria-slave \
        hasky.askmonty.org:9989 knielsen-kvm-x86 <password>

f_kvm_jaunty_x86.addStep(Compile(
        logfiles={"kernel": "kernel_2222.log"},
        command=["sh", "-c", """
kill -9 "$(cat kvm_pid_2222)"
(exec sh -c "echo \$\$ > 'kvm_pid_2222' ; exec kvm -m 2047 \
    -hda /kvm/vms/vm-jaunty-i386-makedist.qcow2 -redir 'tcp:2222::22' -boot c \
    -smp 2 -cpu qemu32,-nx -nographic -net nic,model=virtio -net user" \
    </dev/null >kernel_2222.log 2>&1) &
sleep 15
while : ; do ssh -o ConnectTimeout=4 -p 2222 buildbot@127.0.0.1 true && break; sleep 2; done
ssh -p 2222 buildbot@127.0.0.1 'mkdir -p buildbot && cd buildbot && \
    rm -Rf build && bzr co "lp:~maria-captains/maria/mariadb-5.1-knielsen" build && \
    cd build && BUILD/compile-dist && make dist && \\
    mv "$(make show-dist-name).tar.gz" ..'
"""]))

f_kvm_jaunty_x86.addStep(SetProperty(
        property="distname",
	command=["ssh", "-p", "2222", "buildbot@127.0.0.1", "cd buildbot/build && make show-dist-name"],
        ))

f_kvm_jaunty_x86.addStep(ShellCommand(
	command=["sh", "-c", WithProperties("""
scp -P 2222 buildbot@127.0.0.1:buildbot/%(distname)s.tar.gz .
ssh -p 2222 buildbot@127.0.0.1 'sudo shutdown -h now'
while : ; do sleep 5; kill -0 "$(cat kvm_pid_2222)" || break; done
rm -f kvm_pid_2222
""")],))

f_kvm_jaunty_x86.addStep(Compile(
        command=["sh", "-c", WithProperties("""
qemu-img create -b /kvm/vms/vm-jaunty-i386-build.qcow2 -f qcow2 vm-tmp-2222.qcow2
kill -9 "$(cat kvm_pid_2222)"
(exec sh -c "echo \$\$ > 'kvm_pid_2222' ; exec kvm -m 2047 -hda vm-tmp-2222.qcow2 \
    -redir 'tcp:2222::22' -boot c -smp 2 -cpu qemu32,-nx -nographic -net nic,model=virtio \
    -net user" </dev/null >>kernel_2222.log 2>&1) &
# ...
ssh -p 2222 buildbot@127.0.0.1 'rm -Rf buildbot && mkdir buildbot'
scp -P 2222 %(distname)s.tar.gz buildbot@127.0.0.1:buildbot/
ssh -p 2222 buildbot@127.0.0.1 'cd buildbot && tar zxf %(distname)s.tar.gz && \
    cd %(distname)s && ./configure ...'
# ...
""")],))

f_kvm_jaunty_x86= factory.BuildFactory()
f_kvm_jaunty_x86.addStep(Compile(
        description=["making", "dist"],
        descriptionDone=["make", "dist"],
        logfiles={"kernel": "kernel_2222.log"},
        command=["sh", "-c", """
kill -9 "$(cat kvm_pid_2222)"
(exec sh -c "echo \$\$ > 'kvm_pid_2222' ; exec kvm -m 2047 \
    -hda /kvm/vms/vm-jaunty-i386-makedist.qcow2 -redir 'tcp:2222::22' -boot c -smp 2 \
    -cpu qemu32,-nx -nographic -net nic,model=virtio -net user" \
    </dev/null >kernel_2222.log 2>&1) &
sleep 15
while : ; do ssh -o ConnectTimeout=4 -p 2222 buildbot@127.0.0.1 true && break; sleep 2; done
ssh -p 2222 buildbot@127.0.0.1 'mkdir -p buildbot && cd buildbot && \
    rm -Rf build && bzr co "lp:~maria-captains/maria/mariadb-5.1-knielsen" build && \
    cd build && BUILD/compile-dist && make dist && \
    mv "$(make show-dist-name).tar.gz" ..'
"""
                 ],
        ))
f_kvm_jaunty_x86.addStep(SetProperty(
        property="distname",
	command=["ssh", "-p", "2222", "buildbot@127.0.0.1",
                 "cd buildbot/build && make show-dist-name"],
        ))
f_kvm_jaunty_x86.addStep(ShellCommand(
        description=["copying", "tarball"],
        descriptionDone=["copying", "tarball"],
        logfiles={"kernel": "kernel_2222.log"},
	command=["sh", "-c", WithProperties("""
scp -P 2222 buildbot@127.0.0.1:buildbot/%(distname)s.tar.gz .
ssh -p 2222 buildbot@127.0.0.1 'sudo shutdown -h now'
while : ; do sleep 5; kill -0 "$(cat kvm_pid_2222)" || break; done
rm -f kvm_pid_2222
""")],
        ))
f_kvm_jaunty_x86.addStep(Compile(
        description=["making", "bintar"],
        descriptionDone=["make", "bintar"],
        logfiles={"kernel": "kernel_2222.log"},
        command=["sh", "-c", WithProperties("""
qemu-img create -b /kvm/vms/vm-jaunty-i386-build.qcow2 -f qcow2 vm-tmp-2222.qcow2
kill -9 "$(cat kvm_pid_2222)"
(exec sh -c "echo \$\$ > 'kvm_pid_2222' ; exec kvm -m 2047 -hda vm-tmp-2222.qcow2 \
    -redir 'tcp:2222::22' -boot c -smp 2 -cpu qemu32,-nx -nographic -net nic,model=virtio \
    -net user" </dev/null >>kernel_2222.log 2>&1) &
sleep 15
while : ; do ssh -o ConnectTimeout=4 -p 2222 buildbot@127.0.0.1 true && break; sleep 2; done
ssh -p 2222 buildbot@127.0.0.1 'rm -Rf buildbot && mkdir buildbot'
scp -P 2222 %(distname)s.tar.gz buildbot@127.0.0.1:buildbot/
ssh -p 2222 buildbot@127.0.0.1 'cd buildbot && tar zxf %(distname)s.tar.gz && \
    cd %(distname)s && CC="gcc -static-libgcc" CXX="gcc -static-libgcc" \
    CFLAGS="-O2 -fno-omit-frame-pointer -g" CXXFLAGS="-O2 -fno-omit-frame-pointer -g" \
    ./configure --prefix=/usr/local/mysql --exec-prefix=/usr/local/mysql \
    --libexecdir=/usr/local/mysql/bin --localstatedir=/usr/local/mysql/data \
    --with-server-suffix=1 --with-comment="(MariaDB - http://askmonty.org/)" \
    --with-system-type=linux-gnu --enable-shared --enable-static --enable-thread-safe-client \
    --enable-local-infile --with-big-tables --with-libwrap --with-ssl --without-docs \
    --with-readline --with-extra-charsets=all --with-embedded-server --with-libevent \
    --with-partition --with-zlib-dir=bundled --with-plugins=max-no-ndb && make -j3 && \
     sudo rm -Rf /usr/local/mysql && sudo make install && \
     sudo mv /usr/local/mysql /usr/local/%(distname)s-Linux-x386 && \
    tar zcf ../%(distname)s-Linux-x386.tar.gz -C /usr/local %(distname)s-Linux-x386/'
scp -P 2222 buildbot@127.0.0.1:buildbot/%(distname)s-Linux-x386.tar.gz .
ssh -p 2222 buildbot@127.0.0.1 'sudo shutdown -h now'
while : ; do sleep 5; kill -0 "$(cat kvm_pid_2222)" || break; done
rm -f kvm_pid_2222
""")],
        ))
f_kvm_jaunty_x86.addStep(Test(
        description=["testing", "bintar"],
        descriptionDone=["test", "bintar"],
        logfiles={"kernel": "kernel_2222.log"},
        command=["sh", "-c", WithProperties("""
qemu-img create -b /kvm/vms/vm-jaunty-i386-install.qcow2 -f qcow2 vm-tmp-2222.qcow2
kill -9 "$(cat kvm_pid_2222)"
(exec sh -c "echo \$\$ > 'kvm_pid_2222' ; exec kvm -m 2047 -hda vm-tmp-2222.qcow2 \
    -redir 'tcp:2222::22' -boot c -smp 2 -cpu qemu32,-nx -nographic -net nic,model=virtio \
    -net user" </dev/null >>kernel_2222.log 2>&1) &
sleep 15
while : ; do ssh -o ConnectTimeout=4 -p 2222 buildbot@127.0.0.1 true && break; sleep 2; done
ssh -p 2222 buildbot@127.0.0.1 'rm -Rf buildbot && mkdir buildbot'
scp -P 2222 %(distname)s-Linux-x386.tar.gz buildbot@127.0.0.1:buildbot/
ssh -p 2222 buildbot@127.0.0.1 'cd buildbot && \
    sudo rm -Rf /usr/local/mysql /usr/local/%(distname)s-Linux-x386 && \
    sudo tar zxf %(distname)s-Linux-x386.tar.gz -C /usr/local/ && \
    sudo ln -s %(distname)s-Linux-x386 /usr/local/mysql && cd /usr/local/mysql && \
    sudo sudo chown -R mysql . && sudo chgrp -R mysql . && \
    sudo bin/mysql_install_db --user=mysql && sudo chown -R root . && \
    sudo chown -R mysql data mysql-test && \
    cd mysql-test && sudo su -s /bin/sh -c "perl mysql-test-run.pl alias" mysql'
ssh -p 2222 buildbot@127.0.0.1 'sudo shutdown -h now'
while : ; do sleep 5; kill -0 "$(cat kvm_pid_2222)" || break; done
rm -f kvm_pid_2222
""")],
        ))

bld_kvm_jaunty_x86 = {'name': 'kvm-jaunty-x86',
                      'slavename': 'knielsen-kvm-x86',
                      'builddir': 'kvm-jaunty-x86',
                      'factory': f_kvm_jaunty_x86,
                      }

c['builders'].append(bld_kvm_jaunty_x86)

MySQL opens its labs to the community. Users who want to test the early builds, before they are released for general availability can get them from MySQL Labs.

There is a detailed announcement that warns against using these binaries in production, but encourages everyone to test them. A companion tutorial explains how to use the snapshots to test the InnoDB plugin, which was released recently, and it is included in the latest MySQL 5.1 binaries.