DBA Dojo

How to use EC2 mountpoints

2008-05-26T04:13:00.000-07:00

It has been quiet around here. My workload has been high and I have been spending my down time reading, rather than experimenting and hence publishing. Frederic commented in a recent post about Sysbench fileio benchmark. If you want to see whole set of posts use EC2 Benchmark There were three questions and my reply was going to be long. So now it is a post. Questions: /dev/sda/ : do you mean

Sysbench fileio vs XLarge EC2 Part 3

2008-04-16T04:00:00.000-07:00

Background: If you have been following the series. I have gone back to checking the raw IO performance of all the Amazon EC2 instances. Sysbench fileio vs EC2 Part 1 Sysbench fileio vs Large EC2 Part 2 seeker io benchmark vs EC2 In the meantime Amazon has finally announced that they are providing persistent storage for all EC2 instances. This has removed a bunch of complexity from running a

Is EC2 useful as a database server

2008-04-05T18:28:00.001-07:00

Plenty of people have been excited by the prospect of Amazon EC2 and the ability to scale out your databases as load increases from your original configuration. I noticed Morgan Tocker and Carl Mercier are going to be presenting on this topic at the upcoming MySQL Conference However almost immediately people are worried about the lack of persistent of data across instance terminations. In a

Sysbench fileio vs Large EC2 Part 2

2008-03-25T06:35:00.000-07:00

Overview: I reran the sysbench fileio benchmarking tool on a large EC2 instance. This is basically the middle range offering from Amazon. The large instance runs a 64 bit OS of your choice and safely sits in the commodity PC/Server range (with a little extra memory). If you missed the first article, I ran the same sysbench fileio benchmark on the small EC2 instance Install: Follow the

Seeker io benchmark on small EC2

2008-03-25T03:39:00.000-07:00

Frederic posted an interesting link to the seeker io script in the sysbench fileio vs small EC2 article. He was concerned that the seeker was showing difference numbers to sysbench fileio, especially on the /mnt mountpoint. I read the thread and there was another comment made concerning how Xen could use a sparse file image to improve the io performance. I tested that theory out and found as

Sysbench fileio vs EC2 Part 1

2008-03-12T03:22:00.000-07:00

Overview: Peter Zaitsev's recent article about Evaluating IO subsystem performance for MySQL spurred my interest in doing something similar on EC2. I have covered running sysbench against MySQL on EC2 however not specifically used sysbench to test IO. Rather I had used bonnie++ and iozone to do that. I don't have a lot of respect for the EC2 small instance. Whilst it was reasonable in the

MySQL Master-Master replication table sync

2008-03-04T02:19:00.000-08:00

I saw a post by Baron mentioning that his tool maatkit is best for handling situations where a master-master replication setup has got out of sync. If you think Baron was blowing his own trumpet he has good reason to. I have used his mk-archiver tool as part of the Maatkit to make the problem of archiving and purging data much easier. This was much easier than rolling my own solution. Anyhow. I

MySQL Multi Master-Master on EC2

2008-02-26T03:41:00.000-08:00

Background: As I mentioned in the previous post on running multiple mysql instances on EC2 using mysqld_multi. This was the first step in running 2 or more EC2 Amazon Machine Image (AMI) as a Multi-instance Master-Master (MIMM) replication cluster. The idea cames from a blog article from Apokalyptik.com It is about improving the availability of your databases on EC2 and allowing easy backups

MySQL SolidDB vs MySQLSlap EC2

2008-02-13T01:56:00.001-08:00

Overview: I am always keen to try and test out new technologies. The idea of having a choice in storage engines is a great feature of MySQL. In MySQL 5.1, the MySQL engine API is available to write your own storage engine. In the meantime, there are a couple of MySQL storage engines produced by 3rd party companies. SolidDB is one of them. I wanted to install and then test the SolidDB engine

MySQL vs MySQLSlap Round 3

2008-02-02T16:03:00.000-08:00

In this continuing series on using mysqlslap to pound mysql databases. http://blog.dbadojo.com/2007/08/mysql-vs-mysqlslap.html http://blog.dbadojo.com/2008/01/mysql-vs-mysqlslap-round-2.html I was moving towards running mysqlslap to test any concurrency limits inherent in mysql-proxy. However I was unable to get the small instances on EC2 to handle even small concurrent levels (500 concurrent

MySQL vs MySQLslap round 2

2008-01-23T02:07:00.000-08:00

I was going to post some more on the clash of the titans i.e. mysql-proxy vs mysqlslap, however after doing some retesting, I never got past ramping the number of concurrent sessions in mysqlslap. I wanted to make sure the Mysql database could handle the raw load, before adding another layer in the form of mysql-proxy. Back in August I did some preliminary work with MySQL and mysqlslap. So

MySQL-Proxy vs MySQLSlap Round 1

2008-01-14T03:22:00.000-08:00

Outline: Given the mysql-proxy is moving toward a production ready version (at some point). I thought it would be useful to put it up against mysqlslap and see how it fared. In this round, I wanted to see if mysql-proxy could handle various workloads and concurrent connections in the plain vanilla load-balancing state. Essentially we are building on past experience. http://blog.dbadojo.com/

Multiple MySQL instances on EC2

2008-01-03T03:57:00.000-08:00

Overview: I am keen to see what other people's thoughts of MySQL and using MySQL on EC2 are in general. So I read any blogs which discuss them with interest. In the latter part of last year I read an interesting article on proposing to run multiple MySQL instances on a single EC2. Running multiple MySQL instances on one box is reasonably straightforward. Most of the exposure I have seen is

MySQL Proxy on EC2

2007-12-08T20:54:00.000-08:00

MySQL Proxy is a tool to sit between the client and database. It can load balance, redirect queries, track slow queries amongst other possible uses. http://dev.mysql.com/doc/refman/5.0/en/mysql-proxy.html http://forge.mysql.com/wiki/MySQL_Proxy There is a ramp up within MySQL to get MySQL Proxy into a production ready product. So it is time to check out the product and see if the stated simple

Making Logical Volumes on EC2

2007-11-30T20:49:00.000-08:00

The MySQL backups using LVM Snapshots post is now by far the most popular post. Here is shell script which helps get the Logical Volumes (LV) setup in the first place. ### START OF SCRIPT ### #!/bin/sh # Name: make_mnt_LV.sh # Script to make EC2 /mnt into a LVM volume modprobe dm-snapshot umount /mnt pvcreate /dev/sda2 vgcreate vg /dev/sda2 lvcreate -L30720M -n myvmdisk1 vg mkfs -t ext3 /

Sysbench vs MySQL on EC2

2007-11-19T01:27:00.000-08:00

I was reading how Morgan was slightly disappointed at the results of his sysbench test. He ran sysbench on his laptop and then on EC2 and got a large difference in the results. Thorsten from RightScale also ran some sysbench tests. I was keen to either replicate or disprove their results. Given their parameters I replicated their results. Rather than stop there, I decided to see what was main

IOZone benchmark vs EC2 heat maps

2007-10-29T04:56:00.000-07:00

I have been using the IOZone benchmarking tool to test the IO ability of EC2 running CentOS 4. In the last post I showed the 3D surface area chart showing how as the file size grows, the io performance degrades, quite sharply as the file migrates from CPU cache to memory cache to disk. I redid the charts as what Excel calls contour charts, but remind me of heat maps. The change was

IOzone benchmark vs EC2

2007-10-18T04:54:00.000-07:00

Here are some pretty surface area graphs from the EC2 benchmark, the stepping down indicates from CPU cache to Memory cache, the last cliff is down to disk once the file was larger the available memory. As I mentioned yesterday I was running off a IOzone benchmark on EC2 to see how the disk performs, after reading about it in this online benchmark article. There are a couple of nice features

Bonnie IO Benchmark vs EC2

2007-10-04T04:45:00.000-07:00

Andy, a reader of the blog left a comment asking if I could run some benchmarking of EC2. If someone takes the time to comment, making the effort to respond is always worthwhile. Feedback drives most conversation, business and innovation. So I went off and google'ed the most appropriate and easiest benchmarking tool. http://www.tux.org/pub/benchmarks/ http://oss.sgi.com/LDP/HOWTO/

MySQLtoolkit: mysql-table-checksum

2007-09-21T19:38:00.000-07:00

Mysqltoolkit is a bunch of MySQL utilities based on perl written by Baron Schwartz. Essentially he found no tool or software available to help him, so decided to write his own. Not only that, he turned around and released them for any DBA to use. As soon as I reviewed the list of tools available I immediately wanted to give mysql-table-checksum a test. Like many DBAs who support MySQL,

Future Posting Roadmap: 2007

2007-09-16T01:17:00.000-07:00

Posting has been lighter than normal, as I have had the flu and it is footy finals time in Australia. However I sat down the other day and thought about which areas I hope to cover over the next couple of months. Given this is a blog and comments are on, if you want something covered which is not on the list feel free to add a comment with a request. Topics: Replication: Determine the IO/sec

MySQL Backups using LVM snapshots

2007-09-08T04:24:00.000-07:00

There are a couple of options available to get consistent backups from MySQL. Use mysqldump with FLUSH TABLES WITH READ LOCKUse a slave with STOP SLAVE and your favourite backup tool. For innodb, use the commerical backup tool ibbackupUse LVM (Logical Volume Manager) snapshots with FLUSH TABLES WITH READ LOCKShutdown the database.We are going to demostrate how to use LVM snapshots to reduce time

mysqlslap proves Innodb auto increment limits scalability

2007-09-05T05:38:00.000-07:00

I was testing some mysqlslap runs which Brian 'Krow' Aker was running testing the effect of changing the commit interval. Whilst I was doing that I noticed that he had added the option to add an auto incrementing column to the generated table. Having read elsewhere here and here that the way innodb handles auto incrementing columns can cause issues, I thought I would test that out. And as the

MySQL DBT2 Benchmark on EC2 part 1

2007-08-31T19:16:00.001-07:00

In the last couple of articles I have been using the load simulator/generator tool provided with the MySQL 5.1 install called mysqlslap. I read around on some other blogs and thought it might be also useful to use a benchmarking tool. DBT2 is a TPC-C like benchmark tool provided by OSDL. You can download the software from the DBT sourceforge site. The TPC-C is a online transaction test