Posts

Rackspace vs Amazon impressions

I’ve been a customer of Amazon Web Services since they have been in beta. I’ve also worked with Rackspace now and then. Lately I’ve been working extensively with Rackspace infrastructure so I have a better understanding of their products. Bellow there are a few things that bother me at Rackspace:

Instances

The thing you are going to use 99% of the time with any of the providers. Amazon offers normal instances, high cpu instances, high memory instances, huge memory and cpu instances. On Rackspace you will have a very limited offer of instances. No high cpu or high memory. They do have plans to add them in nearby future. Until then, if you have a CPU bottleneck then it’s tough luck: scale up the instance and pay double.

And since we are talking about pricing for instances, Amazon is cheaper on the long run due to Reserved Instances (you pay an upfront fee and lower price/hour for the instance after). Amazon has been cutting down prices several times. Rackspace? Never.

Block storage

Called EBS on Amazon or CBS on Rackspace, it’s the preferred way of adding extra space to running instances without upgrading them. Beware that on Rackspace it takes an horrible amount of time to do a snapshot. I had to contact RS support several times because of this.

MySQL instances

Called RDS on Amazon or Cloud Database on Rackspace, they are basically optimized instances that run MySQL, but you are not given SSH access to them. Here Rackspace has the worst offer:
no scheduled backups (RS recommends mysqldump … lol try to do mysqldump for 10GB+ of data)
no replication (really? I couldn’t replicate my database)
no hotspare (Amazon MultiA-Z)

The good thing about Cloud Database is that they are fast. Really fast. Also RS promised to address all the above things in the nearby future (yeah).

Load Balancing

Rackspace does not support LB inside private network. Come on RackSpace, do your homework. This thing has been working for AWS since ancient history.

Conclusion:

There are many other things that need to be discovered, probably not pleasant ones. So far I am disappointed with Rackspace and what it has to offer and I would recommend to any customer to use Amazon instead.

How to setup Galera 3 node cluster on Ubuntu 12.04

Galera is a multi-master replication solution for MySQL, which provides an interesting alternative to the standard master-master MySQL replication we are all so used with. One main advantage of Galera is the ability of doing sync replication, thus reducing the risk of data inconsistency between masters.

Setup on RackSpace Cloud

3x 512MB RAM instances, with 20GB storage space
1x Load Balancer for MySQL, RoundRobin algorithm, Health check enabled
1x 512MB RAM instance for testing
OS: Ubuntu 12.04 LTS 64bit

Goal:

Quickly setup a Galera cluster and run some benchmarks using sysbench.

Note: For the sake of simplicity I will refer to the Galera instances as node01, node02 and node03. The test instance will be referred as test01.

Common settings on all nodes

On every node execute:

  1. An apt-get update and upgrade to bring the instances up to date.
  2. Install required packages
    apt-get install libaio1 libssl0.9.8 mysql-client libdbd-mysql-perl libdbi-perl
  3. Download Galera wsrep provider
    wget https://launchpad.net/galera/2.x/23.2.4/+download/galera-23.2.4-amd64.deb
    dpkg -i galera-23.2.4-amd64.deb
  4. Download MySQL server with wsrep patch
    wget https://launchpad.net/codership-mysql/5.5/5.5.28-23.7/+download/mysql-server-wsrep-5.5.28-23.7-amd64.deb
    dpkg -i mysql-server-wsrep-5.5.28-23.7-amd64.deb
  5. I had some issues and I had to create /var/log/mysql
    mkdir -pv /var/log/mysql
    chown mysql:mysql -R /var/log/mysql
  6. Secure the mysql installation and assign a good password to root user:
    service mysql restart
    mysql_secure_installation
  7. Create an user for galera nodes to use for connect/replication
    mysql -p
    mysql> grant all privileges on *.* to galera@'%' identified by 'password';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> set global max_connect_errors = 10000;
    Query OK, 0 rows affected (0.01 sec)
  8. Edit /etc/hosts and make sure you add all the nodes and their corresponding IPs

Galera setup for each node

Edit the /etc/mysql/conf.d/wsrep.cnf and change the values for the following variables:

Configuration for node01:

wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="galera"
wsrep_cluster_address="gcomm://"
wsrep_sst_method=mysqldump
wsrep_sst_auth=galera:password

Configuration for node02:

wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="galera"
wsrep_cluster_address="gcomm://node01:4567"
wsrep_sst_method=mysqldump
wsrep_sst_auth=galera:password

Configuration for node03:

wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="galera"
wsrep_cluster_address="gcomm://node02:4567"
wsrep_sst_method=mysqldump
wsrep_sst_auth=galera:password

Testing the setup

Now restart mysql on all the nodes and check out if cluster is working:

service mysql restart
mysql -p
mysql> show status like 'wsrep%';
+----------------------------+-------------------------------------------------------------+
| Variable_name | Value |
+----------------------------+-------------------------------------------------------------+
| wsrep_cluster_size | 3 |
| wsrep_ready | ON |
+----------------------------+-------------------------------------------------------------+

One more thing before you are done:
Edit node01 wsrep_cluster_address=”gcomm://node3:4567″ and restart mysql server.

Benchmarks were performed from test01 instance using sysbench 0.5 OLTP read-only complex test:

sysbench OLTP (ro) Galera cluster transactions vs threads
ThreadsTransactions/s
115
225
449
8103
16205
32390
64506
128653

galera-transactions-threads

sysbench OLTP (ro) Galera cluster avg response time
ThreadsAvg response timeMin response timeAprox 95%
16642131
27953135
48042153
87742136
167743143
328142142
6412548322
12819445427

galera-response-times

Benchmark Galera cluster vs MySQL master-master on RackSpace

Setup:

Before starting this I would like to point out that I have compared 2 instances(master-master) vs 3 instances(galera cluster) so the test is not correct/accurate. It’s more of a “what if I switch from master-master replication to 3 nodes galera”.

MySQL Master-Master replication:

2x 512 MB instances with 20GB of storage, Ubuntu 12.04 64bit, mysql-server 5.5 was used with no optimization changes to my.cnf, except the required changes for master-master replication.
1x LoadBalancer, RoundRobin algorithm

Galera 3 nodes cluster:

3x 512 MB instances with 20GB of storage, Ubuntu 12.04 64bit, mysql-server 5.5 from galera was used, with no changes to my.cnf, only required node changes were made wsrep.cnf.
1x LoadBalancer, RoundRobin algorithm

Test instance:

1x 512MB instance with 20GB of storage, Ubuntu 12.04 64bit running sysbench

sysbench --test=oltp --mysql-host=loadbalancer_ip --mysql-user=root --mysql-password=password--oltp-table-size=1000000 prepare

The tests were performed on a database of about 256MB size, InnoDB table(s). No optimization changes were made to default my.cnf files, except the required to setup replication.

sysbench OLTP transactions per second
TestMaster-MasterSingle nodeGalera cluster
1 thread,3m10.9717.1112
16 threads,1m, rw1541400
16 threads,1m, r only217158.7206
32 threads,1m, r only325160.79375

galera-cluster-vs-master-master

As you can see from the table and graph I had some issues performing sysbench for Galera cluster in rw mode for 16 threads. From what I have found on Internet it’s an issue with sysbench 0.4.12 so I will attempt to rerun the tests with a newer version.

MySQL errors after update , error 1018 (HY000), errno: 24

Today I’ve updated MySQL server on one of my Ubuntu 12.04 servers. After the upgrade finished, shock and horror: all websites were down, WordPress sites were presenting install page. Quickly connected to mysql via CLI and tried to see if the databases are still there:

mysql> show databases;
ERROR 1018 (HY000): Can't read dir of '.' (errno: 24)

I’ve tried setting limits as in this article, but that didn’t seem to help. Only after adding “open-files-limit = 2048” to my.cnf and restarting the MySQL server things went back to normal. So a big thank you to the author of the article, but I would like to know if there is any need setting those limits or not(obviously I was stressed out over the web sites being down and didn’t test all things).

Anyone else has any experience with this problem?

Recover MySQL InnoDB database from ibdata1 and frm

In this post I will deal with recovery from a corrupted InnoDB database. Remember that sometimes data cannot be recovered. That’s it. Deal with it and move on. As a matter this article is based on my findings when trying to recover several databases after a crash. In the end I couldn’t recover them, but I thought maybe my article will give you some ideas to try. Maybe it will work for you 🙂

If you already tried innodb_force_recovery with no success, prepare for the worst. I am assuming that your MySQL server is not started because of this.

First of all make a backup copy of you ibdata1 file, you will use this to work on it.

cd /var/lib/mysql
dd if=ibdata1 of=ibdata1.recovery conv=noerror

Most of the following things are documented very well here http://www.percona.com/docs/wiki/innodb-data-recovery-tool:mysql-data-recovery:start but I think there are some issues with their approach. First of all they demonstrate how to recover a single table. That’s perfectly fine, but I had several databases crashed with a dozen of tables each, so I couldn’t afford the luxury to recover each table.

Now download the database recovery tools from percona, in your home directory:

wget https://launchpad.net/percona-data-recovery-tool-for-innodb/trunk/release-0.5/+download/percona-data-recovery-tool-for-innodb-0.5.tar.gz
tar zxvf percona-data-recovery-tool-for-innodb-0.5.tar.gz

Next make MySQL server start

cd /var/lib/mysql
mv ibdata1 ibdata1.bak
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak
service mysqld start

The tricky part comes now. Create recovery database and within it create the table structure (this can be done from an old backup, or maybe you can use the frm files from the database you try to recover). Make sure that the tables are using InnoDB as engine.

The following script is modified a bit after the script provided as example here http://www.percona.com/docs/wiki/innodb-data-recovery-tool:mysql-data-recovery:advanced_techniques. Put it in the same directory where you extracted the percona recovery tools.

#!/bin/sh

db=$1

tables=`mysql -ss -u root -p -e "SHOW TABLES" $db`
for i in $tables
do
        #Check how many rows has a table
        rows=`mysql -u root -p -e "SELECT COUNT(*) FROM $i" -s $db`
                # Prepare environment
                echo "Restoring table $i"
                table=$i
                perl create_defs.pl --host=localhost --user=root --password=YOUR_PASSWORD --db=$1 --table=$table > include/table_defs.h.$table
                cd include && rm -f table_defs.h && ln -s table_defs.h.$table table_defs.h
                cd ..
                make clean all
                # Restoring rows
                found=0
                while [ $found -lt 1 ]
                do
                        echo ""
                        ./constraints_parser -5 -f /var/lib/mysql/ibdata1.recovery >> out.$i
                        found=1
                done
done

Now execute the script like:

sh recover-tables.sh recovery_database_you_created

If you are lucky you will get some output in out.TABLE_NAME. Clean the file and load the data into database.

I know the script looks like POS. Sorry for that. You can take a look at percona’s script and modify it to your needs. Please feel free to correct me if I am wrong in this post (probably I am 🙂 ).

Amazon RDS SUPER privileges

#1419 – You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable

This error occurs sometimes on RDS instances when you try to use procedures. You will soon find out that grant super privilege for a user won’t work. So the only way to make things work is to set log_bin_trust_function_creators to 1.

RDS console available at https://console.aws.amazon.com/rds/ allows you to create a new group and modify its parameters. Log in to RDS console, go to “DB Parameters Groups” and click the “Create DB Parameter Group”. Set the following

  • DB Parameter Group Family: mysql5.1
  • DB Parameter Group Name: mygroup
  • Description: mygroup

Confirm by clicking “Yes, create” button.

Here comes the ugly part, since you cannot edit from the console the parameters (for the moment, I hope they are going to change that). You will need to log to your instance using SSH and download RDS cli from here: http://aws.amazon.com/developertools/2928?_encoding=UTF8&jiveRedirect=1

To do so right click on “Download” button and copy link location. In the SSH window use wget to download and unzip it:

wget "http://s3.amazonaws.com/rds-downloads/RDSCli.zip"
unzip RDSCli.zip

If you don’t have unzip you can quickly get it using “apt-get install unzip”(for ubuntu) or “yum install unzip”(for centos). Of course you will need root privileges.

After successfully unpacking the RDSCli cd to that directory and set a few variables. Following is an example on Ubuntu 10.04:

cd RDSCli-1.4.006
export AWS_RDS_HOME="/home/ubuntu/RDSCli-1.4.006"
export JAVA_HOME="/usr/lib/jvm/java-6-sun"
cd bin
./rds --help

If rds –help outputs no errors then you have set it correctly. Congrats. One more command:

./rds-modify-db-parameter-group mygroup --parameters="name=log_bin_trust_function_creators, value=on, method=immediate" --I="YOUR_AWS_ACCESS_KEY_ID" --S="YOUR_AWS_SECRET_ACCESS_KEY"

The AWS keys can be obtain from your AWS account Security Credentials->Access Credentials->Access Keys.

Go to AWS RDS console, “DB Instances”, select your instance and right click “Modify”. Set “DB Parameter group” to “mygroup” and check “Apply Immediately”. Confirm with “Yes, modify”.

You are done 🙂

Mysql benchmark: RDS vs EC2 performance

the setup: 1 m1.small ec2 instance vs 1 db.m1.small rds instance, tests are being run from the m1.small instance. The goal is to determine how the site will perform when moving the database from localhost to a remote instance.

I used sysbench for mysql benchmarks. On a linux server running ubuntu 10.04 you can simply install it with the following command(it’s obvious but just in case):

sudo apt-get install sysbench

The first tests performed were m1.small EC2 instance running mysql-server 5.1.41-3ubuntu12.8 VS RDS instance type db.m1.small running mysql server 5.1.50. The test database had been set to 10 000 records, number of threads = 1, test oltp.

sysbench --test=oltp --mysql-host=smalltest.us-east-1.rds.amazonaws.com --mysql-user=root --mysql-password=password --max-time=180 --max-requests=0 prepare
sysbench --test=oltp --mysql-host=smalltest.us-east-1.rds.amazonaws.com --mysql-user=root --mysql-password=password --max-time=180 --max-requests=0 run

The results

m1.small EC2 instancedb.m1.small RDS instance
OLTP test statistics:
queries performed:
read: 263354
write: 94055
other: 37622
total: 395031
transactions: 18811 (104.50 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 357409 (1985.56 per sec.)
other operations: 37622 (209.01 per sec.)
Test execution summary:
total time: 180.0044s
total number of events: 18811
total time taken by event execution: 179.7827
per-request statistics:
min: 4.04ms
avg: 9.56ms
max: 616.04ms
approx. 95 percentile: 38.42ms
OLTP test statistics:
queries performed:
read: 188230
write: 67225
other: 26890
total: 282345
transactions: 13445 (74.67 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 255455 (1418.74 per sec.)
other operations: 26890 (149.34 per sec.)
Test execution summary:
total time: 180.0573s
total number of events: 13445
total time taken by event execution: 179.9174
per-request statistics:
min: 9.08ms
avg: 13.38ms
max: 904.58ms
approx. 95 percentile: 20.99ms

As you can see the EC2 can perform 40% more transactions than the RDS instance. Nothing unexpected so far.

Time to move on and increase the number of threads to 10

m1.small EC2 instancedb.m1.small RDS instance
OLTP test statistics:
queries performed:
read: 264866
write: 94545
other: 37818
total: 397229
transactions: 18899 (104.97 per sec.)
deadlocks: 20 (0.11 per sec.)
read/write requests: 359411 (1996.22 per sec.)
other operations: 37818 (210.05 per sec.)

Test execution summary:
total time: 180.0462s
total number of events: 18899
total time taken by event execution: 1799.9289
per-request statistics:
min: 4.08ms
avg: 95.24ms
max: 2620.70ms
approx. 95 percentile: 445.91ms

OLTP test statistics:
queries performed:
read: 343812
write: 122772
other: 49109
total: 515693
transactions: 24551 (136.18 per sec.)
deadlocks: 7 (0.04 per sec.)
read/write requests: 466584 (2588.13 per sec.)
other operations: 49109 (272.41 per sec.)

Test execution summary:
total time: 180.2788s
total number of events: 24551
total time taken by event execution: 1801.8298
per-request statistics:
min: 13.41ms
avg: 73.39ms
max: 1126.02ms
approx. 95 percentile: 143.83ms

In this test the small RDS instance is faster than the EC2, 136 vs 105 transactions per second. I’ve also benchmarked a large RDS instance (the next one available after db.m1.small) and it got 185 transactions per second. Quite good, but the price is 4x higher.

The next test was performed vs a 10 million records, 16 threads. This time I only benchmarked a small and a large RDS instance. The large instance managed to do 228 transactions per second while the small one got a decent score of 127 transactions. One thing I noticed during this test is that the small instance started to use it’s swap, while the large one did not have this issue. This is probably due to the fact that 10M records db is aprox 2.5GB and the small RDS only has 1.7GB of RAM.

So if you are planing to grow and want an easy way to do it, switching your database to its own RDS is one of the first things you should consider. One of the immediate effects you will notice is that the CPU usage on the EC2 instance will be greatly reduced, leaving more power for the web server. You can easily increase the size and capacity of the RDS instance with just a few clicks. The backups are done automatically, which is great considering how many times I had to recover databases.

Mysql max_allowed_packet error

You are probably here because you tried to import a big database (several GB) and got the following error:

ERROR 1153 (08S01) at line 2533: Got a packet bigger than 'max_allowed_packet' bytes

If you have access to your mysql server and SUPER privileges things are easy, you just need to log in as superuser to mysql and type this:

mysql>set global max_allowed_packet=64*1024*1024;

and then import the database normally, just adding “–max_allowed_packet=64M” to the parameter list. Example:

$mysql --max_allowed_packet=64M database < database.sql

Everything is so easy. But if you are using Amazon RDS you are out of luck. You setup a user when you create the instance but of course it doesn’t have the SUPER privilege so if you try to execute the above command it will fail. Not even “grant super on *.* to myuberuser” will help you, no no. So after some googling and reading a lot of crap I found this blog which had the same error as mine. Yuppy! Thanks Henry!

The solution is to use DB Parameter Groups. Grab your mouse and start copy pasting fast.

Download Amazon RDS Command Line Toolkit
The latest version can be found here

wget http://s3.amazonaws.com/rds-downloads/RDSCli.zip
unzip RDSCli.zip
cd RDSCli-1.3.003 (this will surely change so make sure you cd to the right directory)
export AWS_RDS_HOME=`pwd`
export JAVA_HOME=/usr/lib/jvm/java-1.6.0-openjdk (this may vary depending on your java location and may not have to set it)
cp credential-file-path.template credential-file
vi credential-file (set your aws credentials there, use whatever text editor you like)
export AWS_CREDENTIAL_FILE=${AWS_RDS_HOME}/credential-file
cd bin
./rds --help

If everything went well you should get some output. On Henry blog he says he suggests that you create a parameter group. Well the reality is you have to create it since Amazon won’t let you modify parameters inside the default group.

./rds-create-db-parameter-group mygroup -f MySQL5.1 -d "My group"
./rds-modify-db-parameter-group mygroup --parameters "name=max_allowed_packet,value=67108864,method=immediate"
./rds-modify-db-instance YOURINSTANCENAMEHERE --db-parameter-group-name mygroup

Go to Amazon management console and check that the new parameter group is created and applied to your instance. You can begin now the import as you would do normally just add “–max_allowed_packet=64M” to the list of your options.

Hope it helps!

MySQL benchmarks using Amazon EC2 instances

Here are some tests I’ve run on Amazon using AMIs provided by scalr for the mysql role. I’ve used the benchmark scripts supplied by MySQL located in /usr/share/mysql/sql-bench. I had to install a package before running the tests:

apt-get install libdbd-pg-perl

After that everything was simple:

root@ec2# mysql
mysql> create database test;
mysql> quit;
root@ec2# cd /usr/share/mysql/sql-bench
root@ec2# perl run-all-tests --dir='/root/'

For EBS tests I’ve done the following:
-created 1GB EBS volume in scalr
-attached it to the instance I was testing
-notice the device name (/dev/sdb for example)

root@ec2# apt-get install xfsprogs
root@ec2# mkfs.xfs /dev/sdb
root@ec2# mkdir /mnt/storage
root@ec2# cp -R /var/lib/mysql /mnt/storage/
root@ec2# chown mysql:mysql -R /mnt/storage/mysql

-edit /etc/mysql/my.cnf and change datadir from “/var/lib/mysql” to “/mnt/storage/mysql”
-restart mysql server and start the tests:


root@ec2# /etc/init.d/mysql restart
root@ec2# mysql
mysql> drop database test;
mysql> create database test;
mysql> quit;
root@ec2# cd /usr/share/mysql/sql-bench
root@ec2# perl run-all-tests --dir='/root/'

Instances types used and their codes:

m1.small(0.10$/hour) – Small Instance (Default) 1.7 GB of memory, 1 EC2 Compute Unit (1 virtual core with 1 EC2 Compute Unit), 160 GB of instance storage, 32-bit platform

m1.large(0.40$/hour) – Large Instance 7.5 GB of memory, 4 EC2 Compute Units (2 virtual cores with 2 EC2 Compute Units each), 850 GB of instance storage, 64-bit platform

c1.medium(0.20$/hour) – High-CPU Medium Instance 1.7 GB of memory, 5 EC2 Compute Units (2 virtual cores with 2.5 EC2 Compute Units each), 350 GB of instance storage, 32-bit platform

c1.xlarge(0.80$/hour) – High-CPU Extra Large Instance 7 GB of memory, 20 EC2 Compute Units (8 virtual cores with 2.5 EC2 Compute Units each), 1690 GB of instance storage, 64-bit platform

EC2 Compute Unit (ECU) – One EC2 Compute Unit (ECU) provides the equivalent CPU capacity of a 1.0-1.2 GHz 2007 Opteron or 2007 Xeon processor.


seconds usr sys cpu tests
m1.small 1823 196.54 28.66 225.2 3425950
m1.small+ebs 1646 197.18 29.61 226.79 3425950
m1.large 1072 157.06 26.97 184.03 3425950
m1.large+ebs 1088 154.23 25.23 179.46 3425950
c1.medium 902 131.18 25.63 156.81 3425950
c1.medium+ebs 901 130.76 28.84 159.6 3425950
c1.xlarge 704 123.31 32.8 156.11 3425950
c1.xlarge+ebs 781 121.02 29.52 150.54 3425950

Bellow you can see a nice chart with how much time it took for each instance to finish the benchmark tests. Either I did something terribly wrong or EBS doesn’t improve MySQL performance.