On Wednesday May 16^th, we ran a webinar to provide an overview of all of the new replication features and enhancements that are previewed in the MySQL 5.6 Development Release – including Global Transaction IDs, auto-failover and self-healing, multi-threaded, crash-safe slaves and more.

Collectively, these new capabilities enable MySQL users to scale for next generation web and cloud applications.

Attendees posted a number of great questions to the MySQL developers, serving to provide additional insights into how these new features are implemented. So I thought it would be useful to post those below, for the benefit of those unable to attend the live webinar (note, you can listen to the On-Demand replay which is available now).

Before getting to the Q&A, there are a couple of other resources that maybe useful to those wanting to learn more about Replication in MySQL 5.6

- On-Demand webinar

- Slides used during the webinar

For more detail on any of the features discussed below, be sure to check out the Developer Zone article: Replication developments in MySQL 5.6

So here is the Q&A from the event 

Multi-Threaded Slaves

The results from recent benchmarking of the Multi-Threaded Slave enhancement were discussed, prompting the following questions

Q. Going from 0 - 10 threads, did you notice any increase in IOwait on CPU?

A. In this case, yes. The actual amount depends on a number of factors: your hardware, query/transaction distribution across databases, server general and InnoDB specific configuration parameters.

Q. Will the multiple threads work on different transactions on the same database, or each thread works on a separate database?

A. Each worker thread works on separate databases (schemas).

Q. If I set the slave-parallel-workers to less than the number of databases, can I configure which databases use which worker threads?

A. There is no such configuration option to assign a certain Worker thread to a database. Configuring slave-parallel-workers to less than the number of databases is a good setup. A Worker can handle multiple databases.

Q. If I create 4 threads and I have 100 databases, can I configure which databases use which threads?

A. There won't be 1 worker to a mapping of exactly 25 databases, but it will be very close to that type of distribution.

Q. Thank You. I ask as we have about 8 databases that have a lot of transactions and about 30 that are used less frequently. It would be nice to have the ability to create 9 workers, 1 for each if the heavy databases and 1 for all the others

A. The current design enables relatively equal distribution of transactions across your databases, but it could be that 9 workers will fit anyway.

Q. Does multi-thread slave still work if there is foreign key across database?

A. MTS preserves slave consistency *within* a database, but not necessarily *between* databases. With MTS enabled and replication ongoing, updates to one database can be executed before updates to another causing them to be temporarily out of sync with each other.

Q. How is auto-increment managed with the multi-thread slave?

A. MTS makes sure Worker threads do not execute concurrently on the same table. Auto-increment is guaranteed to be handled in the same way as the single threaded "standard" slave handles auto-increment

Q. Can you use semi-synchronous replication on one of the slaves when using MTS?

A. Semi-sync is friendly to MTS. MTS is about parallel execution - so they cooperate well.

Optimized Row Based Replication

Q. If you only store the PK column in the Before Image for updates, does this mean you don't care about the slave's data potentially being out-of-sync? Will we be able to control how much data is stored in the binary logs?

A. The rule is that we ship a *PK equivalent* so that the slave is always able to find a row. This means:
  1. if master table has PK, then we ship the PK only
  2. if master table does not have PK, then we ship the entire row

Global Transaction IDs and HA Utilities

Q. Would the failover utility need to sit on a 3rd party host to allow arbitration?

A. The utility would typically run on a client, not on the hosts it is monitoring

Q. Can you explain the upgrade process to move to MySQL 5.6? I am assuming that the slave(s) are upgraded first and that replication would be backwards compatible. And after the slave(s) are upgraded, the master would be next. But then how do you turn on the GTID?

A. Right: the slave(s) are upgraded first. After upgrading Slaves they would be started with --gtid-mode=on (technically a couple of other options are needed as well). And then the same process would be followed for the upgraded Master.

Q. For failover functionality, if I had a setup like this: Server1 replicates to Server2, Server2 replicates to Server3, Server4, and Server5. If Server2 were to fail, can I have it configured so that Server1 can become the new master for Server3/4/5?

A. Yes - you can either configure the failover to the most recent slave based on GTID, or list specific candidates. What will happen is that Slave 1 will temporarily become a slave of 3, 4 and 5 to ensure it replicates any more recent transactions those slaves may have, and then it will become the master

Replication Event Checksums

Q. What is the overhead of replication checksums?

A. It is minimal - we plan to publish benchmarks over the summer to better characterize any overhead

Q. Are you exposing the checksum to the plugin api so we can add our own checksum types?

A. We prepared some of interfaces, i.e. checksum methods are identified by a specific code byte (1-127) values. But it's not really a plugin at this point.

Q. Do checksums verify both replicated data and the data on slave?

A. Yes - checksums are implemented across the entire path - so you can check for issues in replication itself, or in the hardware or network

Q. I think, it is better to turn on checksums at the relaylog to avoid overhead on the master, but if we do that and checksum fails (i.e. not matching the master's data) then what happens – will the slave throw an error

A. I agree, it's better to relax the Master, which verifies the checksum only optionally when the Dump Thread reads from the binlog prior to the replication event being sent out to the Slave. The slave mandatorily checks the checksummed events when they are sent across the network, and optionally when they are read from Relay-log. In either case, an error is thrown.

Q. Are checksums optional? In some cases we don't care for huge data loads

A. Yes, checksums are optional.

Time Delayed Replication

Q. Is Time delayed replication applied at the database level only and not for the entire slave?

A. Applied for the slave as execution is global.

Informational Log Events

Q. When we configure information log event, does it show meaningful query log for any binlog format? (Row based especially)

A. When using row based replication, you get the original query in human readable format... obviously you don’t want to see all the rows modified in a table of size, which can be huge

Q. Will the binlog include user id?

A. User id is replicated in some cases for Query-log-event - namely user id of the invoker when a stored routine is called.

Remote Binlog Backup

Q. What level of access does the remote binlog backup need?

A. Replication slave

Summary

As you can see, our Engineering team was kept busy with questions over the course of the webinar. Be sure to check out the MySQL 5.6 Replication webinar replay and if you have further questions, please don’t hesitate to use the comments below!

PlanetMySQL Voting: Vote UP / Vote DOWN

Every time I have a conversation on SSD, someone mentions btrfs filesystem. And usually it is colored as a solution that will solve all our problems, improve overall performance and SSD in particular, and it is a saviour. Of course it caught my curiosity and I decided to perform a benchmark similar to what I did on ext4 filesystem over Intel 520 SSD.
I was prepared for surprises, as even on formatting stage, mkfs.btrfs says that filesystem is EXPERIMENTAL. In case with filesystems I kind of agree with Stewart, so question #1, what you should ask deciding on what filesystem to use, is “Was this filesystem used in a production more than 5 years?”, so from this point, btrfs has a long way ahead.

How you can get btrfs? Actually it is quite easy if you are on CentOS/RedHat/Oracle Linux 6.2.
Oracle provides Unbreakable Enterprise Kernel, which includes btrfs, so you can get it with this kernel. And installation is quite easy and straightforward, just follow instructions.

So, to numbers. Workload and benchmark are exactly the same as in my previous benchmark, and I perform runs only for 10 and 20GB buffer pool, as it is enough to understand picture. The previous run was done on ext4, so if we repeat the same on btrfs, it will allow us to compare the results.

I format btrfs with default options, and mount it with -o ssd,nobarrier options.

Throughput results:

We can see that btrfs not only provides worse throughput (5x!), but it is also less stable.

Response time:

The same happens with response time. Actually 95% response time is about 10x worse with btrfs.

And response time, timeline:

We can see that btrfs is very far from providing a stable response time.

I guess the conclusion is obvious, and I think it is fine for a filesystem that is in the EXPERIMENTAL state.
Most likely it is some bug or misconfiguration that does not allow btrfs to show all its potential.
I just will consider all talks of btrfs characteristic as premature and will wait until it is more stable
before running any more experiments with it.

Benchmarks specification, hardware, scripts and raw results are available in the full report for Intel 520 SSD.

Follow @VadimTk

PlanetMySQL Voting: Vote UP / Vote DOWN

The best MySQL high availability solution on the market gets even better! We are happy to announce immediate availability of Continuent Tungsten 1.5.New Continuent Tungsten 1.5 offers significant improvements that help you to deploy cost effective HA clusters fast include: Easy Installation - One-step command to deploy an entire Tungsten cluster in minutes, either in your data center or in
PlanetMySQL Voting: Vote UP / Vote DOWN

The binary version for MySQL Cluster 7.2.6 has now been made available at http://www.mysql.com/downloads/cluster/ (GPL version) or https://support.oracle.com/ (commercial version).

A description of all of the changes (fixes) that have gone into MySQL Cluster 7.2.6 (compared to 7.2.5) are available from the 7.2.6 Change log.

PlanetMySQL Voting: Vote UP / Vote DOWN

You have been told (guilty as charged), that the only way to get rid of the huge InnoDB tablespace file (commonly named ibdata1), when moving to innodb_file_per_table, is to do a logical dump of your data, completely erase everything, then import the dump.

To quickly reiterate, you can only delete the ibdata1 file when no InnoDB tables exist. Delete this file with an existing InnoDB table, even a table in its own tablespace, and nothing ever works anymore.

The problem with the dump-based solution

The impact of doing a logical dump is often overwhelming. Well, the dump may be tolerable, but the restore is much longer. The real pain is that you can't do this one table at a time: you have to destroy everything before dropping the ibdata1 file; you then have to import everything.

Perhaps the most common scenario is that we do the changes on a slave, so as not to completely shut down our database. This is nice; no one is aware of the shutdown process. However, Huston, we have a problem: we need to make sure we can keep up the binary logs on the master for the duration of the entire process.

A semi-solution for binary logs

You may get by by keeping the SQL_IO_THREAD running on the slave while dump is taken (SQL thread is better turned off). If you're careful, you could do the same after restarting the database: you should still be able to acquire relay logs. With row based replication becoming more common, the problem of binary logs disk space returns: the logs (rather, log entries) are just so much larger!

Either way, the process can takes long days, at the end of which your slave is up, but lags for long days behind.

Wishful thought: do it one table at a time

If we could do it one table at a time, and assuming our dataset is fairly split among several tables (i.e. not all of our 500GB of data is in one huge table), life would be easier: we could work on a single table, resume replication, let the slave catch up, then do the same for the next table.

How? Didn't we just say one can only drop the ibdata1 file when no InnoDB tables exist?

Solution: do it one table at a time

I'm going to illustrate what seems like a longer procedure. I will later show why it is not, in fact, longer.

The idea is to first convert all your tables to MyISAM (Yay! A use for MyISAM!). That is, convert your tables one table at a time, using normal ALTER TABLE t ENGINE=MyISAM.

Please let go of the foreign keys issue right now. I will address it later, there's a lot to be addressed.

So, on a slave:

1. STOP SLAVE
2. One ALTER TABLE ... ENGINE=MyISAM
   
3. START SLAVE again
4. Wait for slave catch up
5. GOTO 1

What do we end up with? A MyISAM only database. What do we do with it? Why, convert it back to InnoDB, of course!

But, before that, we:

1. Shut MySQL down
2. Delete ibdata1 file, ib_logfile[01] (i.e. delete all InnoDB files)
3. Start MySQL

A new ibdata1 file, and new transaction log files will be created. Note: the new ibdata1 file is small. Mission almost accomplished.

We then:

1. STOP SLAVE
2. Do one ALTER TABLE ... ENGINE=InnoDB [ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 ...]
   
3. START SLAVE again
4. Wait for slave catch up
5. GOTO 1

What do we end up with? An InnoDB only database, with true file per table, and a small ibdata1 file. Space recovered!

The advantage of this method

The thing is, we resume replication after each table alteration. This means breaking the lag period into many smaller periods. While the total runtime does not reduce, we do reduce the maximum lag time. And this makes for easier recovery: no need to store multitudes of binary logs!

So what about the foreign keys?

Phew. Continued next post.

PlanetMySQL Voting: Vote UP / Vote DOWN

In my raw IO benchmark of Intel 520 SSD we saw that the drive does not provide uniform throughput and response time, but it is interesting how does it affect workload if it comes from MySQL.
I prepared benchmarks results for Sysbench OLTP workload with MySQL running on Intel 520.
You can download it there.

There I want to publish graphs to compare Intel 520 vs regular RAID10.

Throughput:

Response time:

So despite big variation in raw IO, it seems it does not affect MySQL workload significantly, and single Intel 520 SSD gives much better throughput and response time comparing with traditional SAS RAID, and what is interesting it also much cheaper.
What’s bad with Intel 520 is that this card does not have capacitor to protect write cache, so if you worry about data protection in case of power outage it is better to disable write cache on this card and use write cache from RAID controller (i.e. LSI-9260).

Benchmarks specification, hardware, scripts and raw results are available in the full report.

Follow @VadimTk

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL Connector/Net 6.4.5 has been released!  This is an update to our 6.4 driver and brings several bug fixes.  It is appropriate for production use with MySQL server versions 5.0-5.5

It is now available in source and binary form from http://dev.mysql.com/downloads/connector/net/#downloads and mirror sites (note that not all mirror sites may be up to date at this point-if you can't find this version on some mirror, please try again later or choose another download site.)

You can read about the changes in this version at http://dev.mysql.com/doc/refman/5.5/en/connector-net-news-6-4-5.html

You can find our team blog at http://blogs.oracle.com/MySQLOnWindows.  You can also post questions on our forums at http://forums.mysql.com/. 

Enjoy and thanks for the support!      
PlanetMySQL Voting: Vote UP / Vote DOWN

WordPress on S3: no more backups

How much trouble will it be if your webserver failed?  No trouble at all, if your website keeps its content on reliable Amazon S3 storage.

There are a lot of nuances in ensuring proper backups and restores of websites. When was the last backup taken? How much data might have been lost? How long will it take to recover it? When was the last time you tested restore? Do you even have an offsite backup?

Now that you can run dynamic websites off Amazon S3 storage, we’ll demonstrate why you no longer need to worry about backing up and restoring your website data. Losing the webserver is no longer a disaster. Cloud storage offers almost unsurpassable reliability a lot of website owners (small & large) would benefit from. In a way you get an "instantaneous backup" to the cloud. Your data is always safe with the cloud storage. The webserver can simply be replaced in minutes.

Contents

OblakSoft has released the 1^st ever dynamic WordPress site running on top of Amazon S3: Yapixx.  Yapixx is ready-to-run WordPress on S3, it stores all its data (content and media) in Amazon S3 storage.  Yapixx stands for Yet Another Picture Sharing Site.

Yapixx taps into power of Amazon S3.  Amazon S3 is inexpensive, highly reliable, available and scalable storage service.  Using Amazon S3 to store Yapixx data has the following benefits:

• No backup and recovery of the site data is needed
• Storage is extremelyreliable and durable by Amazon S3 design
• Pictures are served by Amazon S3 directly, which makes Yapixx highly scalable
• Storage cost scales with usage, no upfront reservation is needed
• Storage consumption scales up and down with the amount of data stored

The crash test is going to illustrate the first point: no backup and recovery is needed.  The data for the web site is safely stored in Amazon S3, so the machine that Yapixx runs on can be replaced in minutes.

Crash test. Start the Webserver.

To get started with the crash test, follow the five steps to deploy Yapixx:

1. Sign up for an AWS account.
2. Create an S3 bucket.
3. Start EC2 instance using read-only Yapixx AMI.
4. Connect to the web application from a web browser.
5. Enter the S3 data location and authentication information.

Refer to the complete step-by-step guide for extra pointers in setting up Yapixx.

Now Yapixx is up and running and you can upload pictures.  Upload some pictures to Yapixx.  Make sure the pictures are uploaded successfully.

Crash the Webserver – it is a crash test after all.

Then terminate the EC2 instance that is running Yapixx.  As a result the server should get completely destroyed.

Is all lost?  Not at all! 

Launch the replacement Webserver.

To get a new server running, repeat the steps 3-5 of the deployment instructions:

3. Start EC2 instance using read-only Yapixx AMI.
4. Connect to the web application from a web browser.
5. Enter the S3 data location and authentication information.

The full step-by-step guide for Yapixx crash test is available at here.

Make sure that you enter the same S3 data location information!   Yapixx is going to ask for a confirmation that this is the only instance that accesses the S3 data location: running multiple instances accessing the same S3 data location may lead to data corruption and data loss.

Feeling adventurous? Change the Webserver machine.

To add extra thrill, try using a different instance size: if you originally deployed a micro instance, try using a small instance, or vice versa.  This will get you a feeling of how easy it is to scale servers up and down.  Now, that’s truly elastic!

It’ll be even more fun if it was your WordPress site, will not it be? You can rely on Yapixx as the starting point to take your site to Amazon S3. Launch your own WordPress site to the cloud today!  It’s easy and FREE, no writing code is required.  Start at http://www.oblaksoft.com/downloads.

We hope you enjoyed the crash test!  Are you interested in making your site highly available with the help of cloud storage?

See also

WordPress on S3: run a beautiful website on Amazon cloud storage.

WordPress on S3: how it works.

WordPress on S3: the beauty of simplicity (blog).

PlanetMySQL Voting: Vote UP / Vote DOWN

Hello,

We are delighted to announce the release of SQLyog 10.0. The version number makes this release very special. We must say the journey from SQLyog 1.0 to 10.0 was very exciting and rewarding. SQLyog was first released in 2002 as a MySQL GUI. Now we are a decade away from the first release. During the course of time, we have introduced tons of features taking SQLyog beyond the definition of a GUI. We couldn’t have done it without your encouragement and patronage. A big thank you for being with us in this endeavour.

Listed below are the features and enhancements introduced in this release:

• Refreshing modern looks. We redesigned the user interface with a soothing color palette. We call it Twilight. You can also design your own themes and there is an option to switch to your default desktop theme as well.
• The data tab is now moved to the upper pane. With this, all important tabs are in the upper pane and you can see more of what you want to see, and more than one table data can be opened under the same connection.
• Schema synchronization logic is optimized and now it is up to 100 times faster for large schemas.
• Introduced many keyboard shortcuts for power users.
• Added an option for multi-column sorting and filtering of table data.
• Many more usability enhancements and bug fixes.

Please refer to the version history for detailed release notes.

SQLyog customers can download SQLyog 10.0 GA from the Customer Area. Please make sure to get the new registration keys for SQLyog 10.0 from the Customer Area.

To evaluate SQLyog 10.0 GA please download a 30-day Trial.

We are very excited about this release, and hope that you will like it. We would love to hear from you!

Cheers,
Team SQLyog

PlanetMySQL Voting: Vote UP / Vote DOWN

Below you will find all code to create the table and the stored procedures to manage hierarchical trees in MySQL. All code is documented and can be downloaded in a zip file.
PlanetMySQL Voting: Vote UP / Vote DOWN