Sweden MySQL User Group SMUG are inviting you to join our next meeting on 28th of November!

Next SMUG Event in Stockholm, 28th of November!
We plan to start at around 16:00 CET

Agenda for meeting:
- MySQL Fabric, by Mats Kindahl, Senior Principal Software Developer MySQL
- Q&A MySQL fabric
Food & mingle

Register on LinkedIn here or on Facebook here

See you there!

MySQL 5.6 - a picture is worth a thousand words!

I feel obligated to use the classic quote from  Arthur Brisbane "One look is worth a thousand words". I recently received the picture below and the question; "guess when we upgraded to MySQL 5.6"?

The graph is from MySQL Enterprise Monitor (more information about MySQL Enterprise Monitor can be found here) and  is showing the amount of rows accessed via using indexes verses full table scan. So all blue stuff is where MySQL is not using indexes for statements, this is of course always a bad thing.

At the 4th of June you see that almost all "Rows Read via Full Scan" went away, this was the point in time when customer upgraded to MySQL 5.6, the only spike seen now is when they run mysqldump for backups. Main reason behind this improvement is from all our optimizer improvements in MySQL 5.6, we improved the optimizer in several areas in MySQL 5.6 but key for this customer was our optimization of subqueries.

If you want to read more about what our wonderful engineering team are doing our VP of engineering Tomas Ulin summarized it in a great blogpost here. If you want to keep up with all the great stuff our optimizer team is doing start following their blog.

mysqldump now safe for InnoDB!

It's now safe to backup InnoDB with mysqldump

Before MySQL 5.6, running mysqldump to make backup of InnoDB tables could cause your backup to 'loose' some data. The problem is described in our manual  here.

In latest MySQL 5.6 this is no longer a problem, this means you no longer risk 'loosing' data when using mysqlbackup together with DDL statements on your InnoDB tables. If you are interested in metadata-locking (MDL) you can read more about MDL  here.

To test this we need to create a few tables and also look at which order mysqldump processes tables.

mysql> CREATE DATABASE ted;
mysql> USE ted;
mysql> CREATE TABLE `a` (`i` int(11) DEFAULT NULL) ENGINE=InnoDB
mysql> CREATE TABLE `b` (`i` int(11) DEFAULT NULL) ENGINE=InnoDB
mysql> CREATE TABLE `c` (`i` int(11) DEFAULT NULL) ENGINE=InnoDB

Run a backup using mysqldump:

mysqldump -S /tmp/mysql.sock -uuser -ppass --single-transaction --databases ted > ted.sql

Flow of backup will be:
-- Table structure for table `a`
-- Dumping data for table `a`
-- Table structure for table `b`
-- Dumping data for table `b`
-- Table structure for table `c`
-- Dumping data for table `c`
So tables are accessed in the following order by mysqldump: a,b,c.

So lets add some data to table b so we have some time to run our DDL statements.
I have created a file with 10 mio rows to give us some time to run our test cases.
(I recomend relaxing InnoDB (innodb_flush_log_at_trx_commit=2) to speed up load of data)

mysql> INSERT INTO a VALUES (1);
mysql> LOAD DATA INFILE '/tmp/data-10000000' INTO TABLE b;
mysql> INSERT INTO c VALUES (1);

We want to test two cases:

1. Run DDL statement after table have been accessed by mysqldump, this will be done by running ALTER TABLE statement at table `a` during mysqldump.
2. Run DDL statement before table have been accessed by mysqldump, this will be done by running ALTER TABLE statement at table `c` during mysqdump.

Test case 1)

Run mysqldump:
mysqldump -S /tmp/mysql.sock -uuser -ppass --single-transaction --databases ted > ted.sql
Start client and run DDL statement against table a whilst mysqldump is still runing:
mysql> ALTER TABLE a ADD COLUMN (j int);

Result:
mysqldump will continue as normal and successfully finish its run with all data in place.
ALTER TABLE will 'wait' until mysqldump is done and then successfully return, everything works as expected.

mysql> SELECT * FROM a;
+------+---------+
| i    | j       |
+------+---------+
| 1    | NULL    |
+------+---------+
1 row in set (0.00 sec)

Test case 2)

Run mysqldump:
mysqldump -S /tmp/mysql.sock -uuser -ppass --single-transaction --databases ted > ted.sql
Start client and run DDL statement against table c whilst mysqldump is still runing:
mysql> ALTER TABLE c ADD COLUMN (j int);

Result:
The "ALTER TABLE" statement immediately return successfully.
mysqldump stops with error: mysqldump: Error 1412: Table definition has changed, please retry transaction when dumping table `c` at row: 0

Conclusion

It's is now safe to run mysqldump and at the same time run DDL statements as long as you look at return value from mysqldump!

These tests where done with MySQL 5.6.11

MySQL configuration:
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=1G
innodb_log_file_size=512M

Next MySQL User Group Event in Stockholm, 10th of April!!

Next MySQL User Group Event in Stockholm, 10th of April!!

Agenda is:

• Replication news in MySQL 5.6 by Mats Kindahl, Senior Principal Software Developer MySQL
• Q&A MySQL Replication
• Topics for upcoming SMUG meetings
• Food & mingle

Register on LinkedIn here or on Facebook here

MySQL User Group Event in Stockholm 10th of April!

Tid: 16:00

Datum: 10/4 2013

Adressen är:

Emineo

Lilla Västerbron 20

11219 Stockholm

Det är dags igen! Denna gången kommer Mats Kindahl, som vi känner igen från MySQL High availability i bokhyllan, tala om replikering i 5.6. Det är också dags att prata lite i gruppen om vad vi vill se på kommande träffar!

Agendan ser ut så här:

• Replikeringsnyheter i MySQL 5.6 av Mats Kindahl, Senior Principal Software Developer MySQL
• Q&A MySQL Replikering
• Ämnen för kommande SMUG träffar
• Mat & mingel

Denna gången sponsras vi av Emineo!

Bolaget har funnits sedan 1997 och arbetar uteslutande med Oracles databas på de flesta plattformar (Windows, Solaris, HPUX, AIX, Linux). Sen 2011 har Emineo satsat på att bygga kompetens kring MySQL. Idag erbjudes förutom rena konsulttjänster även remote Drift- och Administration av MySQL databaser och DBA-on-demand för MySQL. 

Vill du veta mer, gå in på hemsidan: www.emineo.se 

Anmäl dig på facebook här eller på LinkedIn här

Vi syns den 10:e!

Virtual developer day for MySQL

Virtual Developer Day: MySQL is a one-stop shop for you to learn all the essential MySQL skills. With a combination of presentations and hands-on lab experience,  you’ll have the opportunity to practice in your own environment and sharpen your skills to:

    •   Develop your new applications cost-effectively using MySQL

    •   Improve performance of your existing MySQL databases

    •   Manage your MySQL environment more efficiently

We offer two tracks of content - one targeting the beginners and the other for the advanced users, and both include presentations and hands-on lab experience.

Don’t miss this exclusive opportunity to learn tips and tricks from the MySQL experts at Oracle who will be moderating the event during the dates and times listed below:

Americas and Canada - March 12th, 2013

9:00 a.m – 1:00 p.m PST  /  12:00 p.m. – 4:00 p,m EST  /  1:00 p.m – 5:00 p.m BRT

Europe, Middle East, Africa and Some Asian Countries (English)  - March 19th, 2013

9:00 a.m. – 13:00 p.m UTC/GMT / 10:00 a.m. – 14:00 p.m. CET / 12:00 p.m. – 16:00 p.m. AST / 13:00 p.m. – 17:00 p,m. MSK / 14:30 p.m. – 18:30 p.m. IST

Register here

The agenda and abstract can be found here

Upcoming MySQL event in Oslo 21st of February

MySQL Tech Tour: From the Web to the Cloud
Thursday, 21 February 2013, Oslo Norway

Are you looking to deploy MySQL-based applications either on-premise or in the cloud?
Join us to learn how you can reduce costs and improve business agility while achieving the highest levels of MySQL scalability, security and uptime.

We will help you better understand:

• Why MySQL has become the leading database in the cloud, and how it addresses the critical attributes of cloud-based deployments
• How ISVs can power their SaaS offerings with MySQL
• What are the best practices to deploy the world’s most popular open source database in public and private clouds

You will also find out:

• How you can leverage MySQL together with Hadoop and other technologies to unlock the value of Big Data, either on-premise or in the cloud
• How to boost the performance of InnoDB and benefit from enhanced instrumentation with the Performance Schema in MySQL 5.6
• How to take advantage of the new MySQL 5.6 Replication features

This event will close with a real world customer example.
Don’t miss this free Oracle MySQL Seminar! We look forward to seeing you there.
All presentations will be delivered in local language.

Join us to learn how you can reduce costs and improve business agility while achieving the highest levels of MySQL scalability, security and uptime.

Learn more and register for event »

Upcoming MySQL event in Stockholm

MySQL Tech Tour: From the Web to the Cloud
March 20, 2013, Stockholm, Sweden

Are you looking to deploy MySQL-based applications either on-premise or in the cloud?
Join us to learn how you can reduce costs and improve business agility while achieving the highest levels of MySQL scalability, security and uptime.

We will help you better understand:

• Why MySQL has become the leading database in the cloud, and how it addresses the critical attributes of cloud-based deployments
• How ISVs can power their SaaS offerings with MySQL
• What are the best practices to deploy the world’s most popular open source database in public and private clouds

You will also find out:

• How you can leverage MySQL together with Hadoop and other technologies to unlock the value of Big Data, either on-premise or in the cloud
• How to boost the performance of InnoDB and benefit from enhanced instrumentation with the Performance Schema in MySQL 5.6
• How to take advantage of the new MySQL 5.6 Replication features

This event will close with a real world customer example.
Don’t miss this free Oracle MySQL Seminar! We look forward to seeing you there.
All presentations will be delivered in local language.

Join us to learn how you can reduce costs and improve business agility while achieving the highest levels of MySQL scalability, security and uptime.

Learn more and register for event »

Using replication to make the move from MySQL to MySQL Cluster with minimal downtime!

Last couple of months I've got several questions regarding migrating from normal MySQL (InnoDB or MyISAM) to MySQL Cluster. Migration from normal MySQL to MySQL Cluster can not be done the usual way by "just" changing ENGINE from MyISAM or InnoDB to NDBCluster, the reason for this is because MySQL Cluster is not a traditional Storage engine in MySQL.

If you are new to MySQL Cluster and want to know more, please follow links bellow to get started with MySQL Cluster:

• Compare InnoDB and MySQL Cluster, go here.
• MySQL Cluster Evaluation Guide, go here.

Now to the interesting part - how to actually migrate from normal MySQL to MySQL Cluster?

The normal way to migrate from normal MySQL to MySQL Cluster would be:

1. Use mysqldump to make logical dump of your database.
2. Install MySQL Cluster, follow our manual here or follow Andrew Morgans blog to bootstrap a cluster using MCM here.
3. Import data to cluster using mysql client, there are two options here, either you import data into InnoDB tables and later change table handler to ndbcluster, second option is to first substitute engine in dump-file before loading data. I prefer first option, first import data into InnoDB tables and then change engine to ndbcluster using "alter table" command,  this way you can check constraints and also get arount tuning cluster for bulk loading.
4. Configure application (change IP) to start use MySQL Cluster.

However this procedure is time consuming and will cause downtime for your application.

Also you might want to run some tests before moving production into your new cluster platform.

So how to solve this - MySQL replication!

Before moving over our production to MySQL Cluster we want to:

• Run production load tests on our MySQL Cluster.
• Test our applications against a MySQL Cluster with live data.

And when we are done testing, we want to make the move to our new MySQL Cluster environment with minimal disruption for our clients.

Below you can see the new database architecture after we setup replication between production and MySQL Cluster.

The steps that needs to be done will not be described in detail since most of it is normal MySQL replication, which is straight forward and explained in detail in our manual here.

Procedure to set up replication from production to MySQL Cluster:

1. Enable binary logging for MySQL server in production.
2. Same as step 1-3 above but since we want to set-up replication, when doing step 1) remember to add flag "master-data=2" so mysqldump outputs binary log position into backup file.
3. Start replication from production to new cluster site.

So, by now we have a MySQL Cluster running as a slave to the production environment and getting all updates from production. We are now ready to move over production our load to cluster site at any given time. Run all your test and when you are ready, just move production to your MySQL Cluster installation and you are done.

Happy Clustering!