Transmissions resumed

Sorry for the recent loss of the site. Blogger just didn’t play nice with the new hosting provider, no matter what I did. Out with the old and in with the new.

So what is on the agenda after such as long period without posts.

Some ideas for future posts:

  1. Hosted Database solutions, the good, the bad and the ugly.
  2. Running databases on virtual machines/clouds.
  3. DBA toolsets, do custom scripts have a place in the world containing percona-tools
  4. DBAs, Is this the beginning of the end, the end of the beginning? or am I running out of clichés
  5. SQL is dead long live SQL.
  6. Interviews with DBAs. 10 questions answered by the finest DBAs
  7. Remote DBA work, is it the promised land?
  8. Databases and Machine learning, is the self-tuning database in sight?

Re-constructing directory structure on Linux

If ever you need to re-construct the directory structure on Linux/Unix on a different machine you can just run this command.

# Generates a list of mkdir commands to re-construct the directory structure from current location

find . -type d| while read -r line; do echo “mkdir -p $line”; done

If you are wanting to copy files as well, just use scp or rsync

The use case for these kind of commands nowadays is greatly reduced, if you are using DevOps tools such as puppet or chef, they will do this kind of thing automagically out-of-the-box. If you are running your databases on VMs (datafiles within the VM), most of the time you could clone the image and everything is the same.
The aim of all those tools is to make the job of Sysadmins and DBAs easier whilst producing a environment where the state is consistent/known.

Have Fun

Oracle Fun with Predicate pushdown

I had some fun recently with a Oracle database choosing a poor execution plan.

The problem was with a view which had a column which was explicitly cast to a value.

For example:

create table vw_temp
cast(ID) as NUMBER(19,0) as ID,
Name varchar2(50)
from very_large_table a
join large_table b on a.ID = b.ID
where Name = ‘whatever’ ;

Oracle in this case was unable to use the ability to push predicates down and make the joins more optimized.

So the moral of the story is be careful if you are doing casts/converts or any function which will change the column in a view.

Have Fun

For more info about predicate push down have a read of this blog entry
Or this short entry in the documenation

Oracle RAC on EC2 redux

I was reading some RSS feeds the other day and noticed that Jeremy Schneider over at Ardent Performance Computing was working on getting Oracle RAC working on Amazon EC2.
He looks to have solved the whole Virtual IP issue by using another instance. Nice solution!

When I get a spare moment (don’t believe for a minute that the lack of posts means I am not busy) it would be good to take the scripts and get the whole Oracle RAC working in Amazon EC2 finally!

Other News:

I have had the chance to play with some columnar databases, Vertica and Ingres VectorWise and the performance is good. I used the TPC-H benchmark to test to a scale 20 (small only due to a lack of disk space). So the results are nothing like the recent Scale 100 benchmarks that Ingres VectorWise did but useful nonetheless. Sadly I can’t publish any scripts or results as the IP is owned by my current employer.

Currently I am focused on improving my skills in predictive modeling and analytics. This is using the data rather than just supporting/recovering and hand-holding the data a.k.a. being a DBA.

Listening to trance, in the zone and most definitely Having Fun!


mysqlslap howto

I noticed that people were hitting the site for information on how to run mysqlslap.

To help out those searchers, here is a quick mysqlslap howto

  1. Make sure you have mysql 5.1.4 or higher. Download MySQL from the MySQL website
  2. Make sure your MySQL database is running.
  3. Run mysqlslap, using progressively more concurrent threads:
    mysqlslap  --concurrency=1,25,50,100 --iterations=10 --number-int-cols=2 \
    --number-char-cols=3 --auto-generate-sql --csv=/tmp/mysqlslap.csv \
    --engine=blackhole,myisam,innodb --auto-generate-sql-add-autoincrement \
    --auto-generate-sql-load-type=mixed --number-of-queries=100 --user=root \

For detailed descriptions of each parameter see the MySQL documentation:

If you want to see how I used mysqlslap to test mysql performance on Amazon EC2, here are the list of posts

MySQL Error: error reconnecting to master

Error message:

Slave I/O thread: error reconnecting to master
Last_IO_Error: error connecting to master


Check that the slave can connect to the master instance, using the following steps:

  1. Use ping to check the master is reachable. eg ping
  2. Use ping with ip address to check that DNS isn’t broken. eg. ping
  3. Use mysql client to connect from slave to master. eg mysql -u repluser -pREPLPASS – –port=3306 (substitute whatever port you are connecting to the master on)
  4. If all steps work, then check that the repluser (the SLAVE replication user has the REPLICATION SLAVE privilege). eg. show grants for ‘repl’@’’;


  • If step 1 and 2 fail, you have a network or firewall issue. Check with a network/firewall administrator or check the logs if you wear those hats.
  • If Step 1 fails but Step 2 works, you have a DNS or names resolution issue. Check that the slave can connect and resolves names using mysql client or ssh/telnet/remote desktop.
  • If Step 3 fails, you need to check the error reported, it will either be a authentication issue (login failed/denied) or an issue with the TCP port the master is listening on. A good way to verify that port is open is to use: telnet 3306 (or the port the master is listening on) if that fails then there is a firewall(s) in the network which are blocking that port.
  • If you get to step 4 and everything looks fine and the slave does reconnect fine on retrying. Then you have probably had either temporary, network failure, names resolution failure, firewall failure or any of the prior together.

Continuing Sporadic issues:

Get hold of the network and firewall logs.
If this is not possible, setup a script to periodically ping, connect, mysql connect and log that over
time to prove to your friendly network admin that there is an problem with the network.

How MySQL deals with it:

MySQL will try and reconnect by itself after a network failure or query timeout.

The process is governed by a few variables:


In a nutshell, a MySQL slave will try to reconnect after getting a timeout (slave-net-timeout) after waiting the number of seconds in master-connect-retry but only for the number of times
specified in master-retry-count.
By default, a MySQL slave waits one hour before retry, and will then retry every 60 seconds for 86,400 times. That is every minute for 60 days.

If the one hour slave-net-timeout is too long for your DR/Slave read strategy you will need to adjust it accordingly.

Edit: 2011/02/02

Thanks to leBolide. He discovered that there is a 32 character limit on the password for replication.

Have Fun


P.S. If you liked this post you might be good enough to try these challenges

Upcoming articles for 2009

I was reviewing what I had written over the last 2 years, and how people had reacted via comments and page views, even what keywords were most popular.

Here is the first draft (pending input from interested readers via adding a comment)

  1. More dbt2 benchmark articles.
  2. Amazon EC2 LVM snapshots vs EBS snapshots.
  3. Backup software for MySQL and specifically on EC2.
  4. Benchmarking Amazon EBS using iozone.
  5. Installing, testing and benchmarking the non-standard MySQL engines such as OurDelta and XtraDB and plugins.
  6. Using EC2 as a test bed for new versions of MySQL and Oracle and other dbs.
  7. New: Using Microsoft SQL server on EC2.
  8. New: Using Postgresql on EC2.
  9. New: Using DB2 on EC2.

New stuff over the next 12 months:

The newest theme for the next 12 months will be revisiting the original idea behind the blog. That theme was the publication of useful methods and recipes for all DBAs and DBA/Developers and people wearing DBA/Developer/SysAdmin/NetworkAdmin or more hats.

A Dojo is a commonly associated with a training place for training in Martial Arts. This Dojo is a training place for Database Administrators (DBAs).

A common method in Martial Arts is a Kata. This is the idea of honing your skills (as a DBA in this case) through training and practice. So there will be articles published under the kata category covering exercises to help people become more polished and skilful with the common tasks and responsibilities of being a DBA.

Have Fun


Top 9 Posts for the last 12 months

If you were ever wondering what other people check out on this site, here are the most popular articles by pageviews for the last 12 months.

Seems most people like the LVM snapshots article, articles about running multiple MySQL instances and the various benchmark articles.

  1. mysql backups using lvm snapshots
  2. oracle 11g on ec2 using silent install
  3. mysql multi master master replication on ec2
  4. mysql master master replication table sync
  5. multiple mysql instances on ec2
  6. mysql dbt2 benchmark on ec2
  7. mysql 51 ndb cluster replication on ec2
  8. sysbench vs mysql on ec2
  9. bonnie io benchmark vs ec2

Have Fun


OurDelta MySQL on EC2 – updating binaries

Given the amount of time since my last post on installing OurDelta MySQL on EC2. It allowed me to show quickly how to get your OurDelta MySQL install up-to-date.


You have already installed the OurDelta Repository as per this documentation

To update:

Now just yum update to get the latest version:

yum update MySQL-OurDelta*

It is as simple as that.


Thanks for the feedback from the last post.

Some people requested the Amazon Machine Image (AMI). The main issue with this is, once you bundle an AMI image it is going to start with those binaries (including mysql) and require you to constantly run yum update each time you launch an instance. So if I had bundled an AMI back in February, anyone using that AMI now would be way behind on the latest updates for OurDelta and also any other CentOS packages.

It is better to go down the path of learning how to bundle an AMI yourself, then getting an base CentOS 4 or CentOS 5 AMI up-to-date once month and using yum update afterwards, when you launch the instance.
You can even pass a script which runs after the instance has launched, or use a configuration tool like Puppet.
Believe me when I say that whilst bundling AMIs is straight-forward you do not want large numbers of old/obsolete AMIs floating around to manage later.

Upcoming stuff:

I am going to use this AMI with MySQL Sandbox to show how easy it is to have a test environment if you want to take your existing MySQL 5.0.x versions to OurDelta MySQL or any other version on MySQL.

Have Fun


Screen log:

[root@domU-12-31-39-04-71-D3 ~]# yum update MySQL-OurDelta*
Setting up Update Process
Setting up repositories
Reading repository metadata in from local files
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Package MySQL-OurDelta-client.i386 0:5.0.77.d8-54.el4 set to be updated
---> Package MySQL-OurDelta-shared.i386 0:5.0.77.d8-54.el4 set to be updated
---> Package MySQL-OurDelta-test.i386 0:5.0.77.d8-54.el4 set to be updated
---> Package MySQL-OurDelta-server.i386 0:5.0.77.d8-54.el4 set to be updated
---> Package MySQL-OurDelta-devel.i386 0:5.0.77.d8-54.el4 set to be updated
--> Running transaction check

Dependencies Resolved

Package Arch Version Repository Size
MySQL-OurDelta-client i386 5.0.77.d8-54.el4 ourdelta 6.0 M
MySQL-OurDelta-devel i386 5.0.77.d8-54.el4 ourdelta 7.5 M
MySQL-OurDelta-server i386 5.0.77.d8-54.el4 ourdelta 17 M
MySQL-OurDelta-shared i386 5.0.77.d8-54.el4 ourdelta 1.7 M
MySQL-OurDelta-test i386 5.0.77.d8-54.el4 ourdelta 6.7 M

Transaction Summary
Install 0 Package(s)
Update 5 Package(s)
Remove 0 Package(s)
Total download size: 39 M
Downloading Packages:
(1/5): MySQL-OurDelta-cli 100% |=========================| 6.0 MB 00:03
(2/5): MySQL-OurDelta-sha 100% |=========================| 1.7 MB 00:01
(3/5): MySQL-OurDelta-tes 100% |=========================| 6.7 MB 00:03
(4/5): MySQL-OurDelta-ser 100% |=========================| 17 MB 00:09
(5/5): MySQL-OurDelta-dev 100% |=========================| 7.5 MB 00:03
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Updating : MySQL-OurDelta-client ####################### [ 1/10]
Updating : MySQL-OurDelta-shared ####################### [ 2/10]
Updating : MySQL-OurDelta-test ####################### [ 3/10]
Giving mysqld 5 seconds to exit nicely
Updating : MySQL-OurDelta-server ####################### [ 4/10]
090608 20:19:24 [Warning] option 'sync-mirror-binlog': unsigned value 18446744073709551615 adjusted to 4294967295
090608 20:19:24 [Warning] option 'sync-mirror-binlog': unsigned value 18446744073709551615 adjusted to 4294967295
To do so, start the server, then run:

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

MySQL bug reports should be submitted through
Issues related to the OurDelta patches or packaging should be
submitted in the OurDelta project on Launchpad, simply follow the
links via

The latest information about MySQL is available on the web at
Other information sources are
- the MySQL Mailing List archives (;
- the MySQL Forums (

Notes regarding SELinux on this platform:

The default policy might cause server startup to fail because it is
not allowed to access critical files. In this case, please update
your installation.

The default policy might also cause inavailability of SSL related
features because the server is not allowed to access /dev/random
and /dev/urandom. If this is a problem, please do the following:

1) install selinux-policy-targeted-sources from your OS vendor
2) add the following two lines to /etc/selinux/targeted/src/policy/domains/program/mysqld.te:
allow mysqld_t random_device_t:chr_file read;
allow mysqld_t urandom_device_t:chr_file read;
3) cd to /etc/selinux/targeted/src/policy and issue the following command:
make load

Starting MySQL.[ OK ]
Giving mysqld 2 seconds to start
Updating : MySQL-OurDelta-devel ####################### [ 5/10]
Cleanup : MySQL-OurDelta-client ####################### [ 6/10]
Cleanup : MySQL-OurDelta-shared ####################### [ 7/10]
Cleanup : MySQL-OurDelta-test ####################### [ 8/10]
Cleanup : MySQL-OurDelta-server ####################### [ 9/10]
Cleanup : MySQL-OurDelta-devel ####################### [10/10]

Updated: MySQL-OurDelta-client.i386 0:5.0.77.d8-54.el4 MySQL-OurDelta-devel.i386 0:5.0.77.d8-54.el4 MySQL-OurDelta-server.i386 0:5.0.77.d8-54.el4 MySQL-OurDelta-shared.i386 0:5.0.77.d8-54.el4 MySQL-OurDelta-test.i386 0:5.0.77.d8-54.el4

Just make sure the mysql instance is secure

[root@domU-12-31-39-04-71-D3 ~]# /usr/bin/mysql_secure_installation


In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] n
... skipping.

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n]
... Success!

Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]
... Success!

By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n]
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n]
... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

OurDelta MySQL on EC2 – install


Arjen would give me an earful if I got this wrong or poorly worded.

“OurDelta produces enhanced builds for MySQL, with OurDelta and third-party patches, for common production platforms” from

Over the next series of articles I am going to put the many additions to the MySQL 5.0 baseline through their paces on Amazon EC2.

Using a base CentOS 4.4 I had lying around on Amazon S3 I had Ourdelta installed and secure in no time flat.

This is outline for the installing Ourdelta onto CentOS 4


yum install yum-plugin-protectbase
rpm --import
rpm -Uvh
mkdir downloads
cd downloads/
yum localinstall MySQL-OurDelta-*
ps -ef|grep mysql
mysql -u root -p


Thats it. Straightforward install.
I am getting lazy in my old age I used a pipe combo to generate the install commands using this:

history |grep -v history|awk ‘{ print $2″ “$3” “$4 }’

The next article will cover the upgrade of any existing MySQL 5.0 release to OurDelta.
I have saved this Ourdelta MySQL install as an AMI.

If people are interested in making this a public AMI please post a comment.

Have Fun