Prewarm your EBS backed EC2 MySQL slaves

This is the story of cold blocks and mismatched instances and how they will cause you pain and cost you money until you understand why.

Most of the clients that we support run on the Amazon cloud using either RDS or running MySQL on plain EC2 instances using (Provisioned IOPS) PIOPS EBS for data storage.

As expected the common architecture is running a master with one or more slaves handling the read traffic.

A common problem is that after the slaves are provisioned (normally created from an EBS snapshot) they lag badly due to slow IO performance.

Unfortunately what tends to be lost in the “speed of provisioning new resources” fetish is some limitations in terms of data persistence layer (EBS).

If you are using EBS and you have created the EBS volume from snapshot or created a new volume you have to pre-warm the EBS volume otherwise you will suffer a bad (I mean seriously bad) first usage penalty.  Bad? I am talking up to 50% performance drop[1]. So that expensive PIOPS EBS volume you created is going to perform like rubbish every time it reads/writes a cold block.

The other thing which also tends to happen is mixing up the wrong instance (network performance) with the PIOPS EBS. This the classic networked storage, the network is the bottleneck. If your instance type has limited network performance, having a higher PIOPS than the network can handle means you are wasting money (on PIOPS) you can’t use. A bit like in the old days (of dedicated servers and SAN storage) where the SAN could deliver 200-300Mbytes per sec, but the 1 Gigabit network could only do 40-50Mbytes per sec.

Here is the real downside, using the cloud you can provision new resources to handle peak load (in the case more MySQL slaves to handle read load) as fast as you can click, or faster using API calls, or even automagically, if you have some algo forecast the need for additional resources. But… the EBS is all cold blocks, so these new instances will be up and available in minutes but the IO performance will be poor until you either pre-warm or the slave gets around to writing/reading all blocks.

So the common solution is to pre-warm the blocks using dd to read the EBS device (and warm the block) to /dev/null

eg: sudo dd if=/dev/xvdf of=/dev/null bs=1M

Consider how long this will take for any reasonable sized DB (200GBytes) using an instance with 1 Gigabit network.

200Gigabytes read at 50Mbytes/sec  = 200,000 Mbytes/50 = 4000 secs = 3600 (1hr) + 400 (6 mins 40 secs) =~ more than 1 hr.

So you or your algo provisioned a new EC2 instance for the database in minutes but either your IO will be rubbish for an extended period, or you wait more than 1 hr per 200GB to have the EBS pre-warmed.

What are the solutions?

  1. Forecast further in advance depending on the size of your db (or any other persistent storage layer eg NoSQL etc)
  2. Use ephemeral storage and manage the increased risk of data loss in the event of instance termination.
  3. Break your DB or your application into smaller pieces aka micro services.[2]
  4. Pay more $ and have your databases stay around longer so waiting for a instance to be ready in the beginning is not a problem.

As you can expect, most businesses are happy with option 4. Pay more, leave instances around like they were dedicated servers (base load). Amazon is happy too.

Option 3 whilst requiring some thought (argh) and additional complexity is where the real speed of provisioning, dare I say it, agile nature of the cloud will bear the most fruit.

[1] http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-prewarm.html

[2] http://martinfowler.com/articles/microservices.html

 

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
as
select
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
https://blogs.oracle.com/optimizer/entry/basics_of_join_predicate_pushdown_in_oracle
Or this short entry in the documenation
http://www.oracle.com/pls/db102/to_URL?remark=ranked&urlname=http:%2F%2Fdownload.oracle.com%2Fdocs%2Fcd%2FB19306_01%2Fserver.102%2Fb14211%2Foptimops.htm%23i55050

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.
http://www.ardentperf.com/2011/03/04/byo-oracle-rac-on-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!

Paul

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 \
    --password YOURPASSWORDHERE

For detailed descriptions of each parameter see the MySQL documentation:

http://dev.mysql.com/doc/refman/5.1/en/mysqlslap.html
http://dev.mysql.com/doc/refman/5.5/en/mysqlslap.html

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

http://blog.dbadojo.com/2007/08/mysql-vs-mysqlslap.html
http://blog.dbadojo.com/2008/01/mysql-vs-mysqlslap-round-2.html
http://blog.dbadojo.com/2008/02/mysql-vs-mysqlslap-round-3.html

MySQL Error: error reconnecting to master

Error message:

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

Diagnosis:

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 master.yourdomain.com
  2. Use ping with ip address to check that DNS isn’t broken. eg. ping 192.168.1.2
  3. Use mysql client to connect from slave to master. eg mysql -u repluser -pREPLPASS –host=master.yourdomain.com –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’@’slave.yourdomain.com’;

Resolution:

  • 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 master.yourdomain.com 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:

master-connect-retry
slave-net-timeout
master-retry-count

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

Paul