There are probably thousands of articles on the Internet about disk statistics in Linux, what various columns mean, how accurate the information is, and so on. I decided to attack the problem from a little bit more practical side. Hopefully this will be just the first of many future posts on identifying various I/O related performance problems on a MySQL server.

Linux exposes disk statistics through /proc/diskstats. However the contents of this file isn’t something anyone can understand quickly. It needs a tool to transform the information into something human readable. A tool that is available for any Linux distribution is called iostat and comes with sysstat package.

How to access and read I/O statistics

Usually you want to call iostat one way:

iostat -xkd <interval> <block device>

The interval should typically be one second as it is the …

This example uses a backup to rename a database in the MySQL instance.

You can activate Google Cloud SQL from the Google APIs Console at https://code.google.com/apis/console. NOTE: At the time of this publication this was in limited beta.

From the Google APIs console, you can create a new instance. You can then use the Web interface to run SQL statements, import and export data. There is also a Google SQL command line client that can be configured with:

# Java 6 is a dependency of the Google SQL client
$ [ -z `which java 2>/dev/null` ] && sudo apt-get install -y openjdk-6-jre-headless
$ [ -z `which unzip 2>/dev/null` ] && sudo apt-get install -y unzip
$ cd $HOME
$ mkdir cloud-sql
$ cd cloud-sql/
$ wget http://dl.google.com/cloudsql/tools/google_sql_tool.zip
$ unzip ../google_sql_tool.zip
$ ./google_sql.sh 
Please authorize Google SQL Service for your Google Account at:

http://goo.gl/XXXX

Enter Authorization Code: …

While working with RDS and Google Cloud SQL I have come to realize that excluding the mysql schema from a mysqldump is important. However with many databases, the –all-databases option enables you only to select all or none. There is however an easy solution to exclude one or more databases in mysqldump with this little gem I created.

$ time mysqldump --databases `mysql --skip-column-names -e "SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') FROM information_schema.schemata WHERE schema_name NOT IN ('mysql','performance_schema','information_schema');" >` >/mysql/backup/rds2.sql

An you can exclude as many schemas as you want.

I checked the mysqldump –help, there was no option in MySQL 5.1, asked a colleague just to be sure I wasn’t wasting my time, and it took all of 2 minutes to create and test a working solution.

mysql> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| innodb             |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

mysql> drop schema innodb;
ERROR 1010 (HY000): Error dropping database (can't rmdir './innodb/', errno: 17)

This is an additional schema that is included in an AWS RDS installation. You should not put directories in the MySQL data directory.

Thanks to all of those who came by our booth and to see Leif’s presentation on Read Optimization, and to my Lightning Talk on OLTP and OLAP at the Percona MySQL Conference and Expo. It was an incredible week and a great place to launch TokuDB v6.0 from! A big thanks to Percona for a great event, to …

Schema-free NoSQL Data

Update – the webinar replay is now available from here.

On Thursday, I’ll be presenting a webinar on NoSQL (of course with a MySQL twist!) – as always it’s free to attend but you need to register here in advance. Even if you can’t attend, it’s worth registering as you’ll be sent a link to the replay and the charts. The session will introduce the concepts and motivations behind the NoSQL movement and then go on to explain how you can get most of the same benefits with MySQL (including MySQL Cluster) while still getting the RDBMS benefits such as ACID transactions.

The …

Amazon Web Services (AWS) provides a managed MySQL solution via Relational Database Service (RDS). The following instructions will enable you to configure and run RDS. Refer to Using Amazon Web Services for initial information about AWS requirements.

The following instructions are for Ubuntu.

Pre-requisites

$ [ -z `which java 2>/dev/null` ] && sudo apt-get install -y openjdk-6-jre-headless
$ [ -z `which unzip 2>/dev/null` ] && sudo apt-get install -y unzip

Installation

# See http://docs.amazonwebservices.com/AmazonRDS/latest/CommandLineReference/Welcome.html?r=8890
$ cd $HOME
$ mkdir -p aws
$ cd aws
$ wget http://s3.amazonaws.com/rds-downloads/RDSCli.zip
$ unzip RDSCli.zip
$ ln -s RDSCli-*/ rds

Configuration

You need to create the $HOME/aws/credentials file with your specific account …

Last summer my colleague Marko Mäkelä committed this seemingly innocent performance fix for InnoDB in MySQL 5.6:

3581 Marko Makela    2011-08-10
Bug#12835650 VARCHAR maximum length performance impact

row_sel_field_store_in_mysql_format(): Do not pad the unused part of
the buffer reserved for a True VARCHAR column (introduced in 5.0.3).
Add Valgrind instrumentation ensuring that the unused part will be
flagged uninitialized.
Before this, buffers which were used to send VARCHARs from InnoDB to the MySQL server were padded with 0s if the string was shorter than specified by the column. If, e.g., the string "foo" was stored in a VARCHAR(8), InnoDB used to write "3foo00000" to the buffer (the first character - 3 - determines the actual length of the string). However, even though these trailing bytes are not used anywhere, writing 0s to the buffer certainly has a cost. Hence …

In my heart, I'm a DBA, always was and always will be. People say I'm a database guy by the way I think, keep my car, and file my music and also bank statements... However I did great deal of development, design, architecture on the apps side. I (hope to) have some perspective.

Applications come and go. The second programming language I've ever learned and worked on was COBOL, some still say most of the world's lines of code are written in this language, maybe so, but anyway I since then have known and written in dozens of programming languages, from Assembly to Force.com, from Pascal to Delphi, from functional C to Object Oriented SmallTalk, C++, Java and , from compiled C/CGI to interpreted Perl, ASP and Ruby back to compiled node.js... My first applications ran on Main-Frame with green screen, later I created beautiful graphic client-server applications, later I had to create hideous white web applications …