-
MySQL Cluster 7.2.6 is available for download
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.
-
Getting rid of huge ibdata file, no dump required
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:
STOP SLAVE
One ALTER TABLE ... ENGINE=MyISAM
START SLAVE again
Wait for slave catch up
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:
Shut MySQL down
Delete ibdata1 file, ib_logfile[01] (i.e. delete all InnoDB files)
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:
STOP SLAVE
Do one ALTER TABLE ... ENGINE=InnoDB [ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 ...]
START SLAVE again
Wait for slave catch up
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.
-
Intel 520 SSD in MySQL sysbench oltp benchmark
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
-
Galera Cluster 2.1 Release Rolling Out
Galera Cluster release 2.1 has been published. This is a maintenance release and includes the new Galera Replication library version 2.1 and corresponding MySQL server releases for both MySQL 5.5.23 and MySQL 5.1.62 versions. The replication API version is still at version #23. The 2.1 release has a number of fixes in Galera replication and MySQL provider layers, including:fixes for foreign key support (with 5.5.23 release only)fixes for incremental state transfersupport for wsrep_sst_method=skip to avoid performing any state snapshot transfer during node joiningwsrep_urls configuration variable to specify a list of known cluster nodes where node should try to connect during startupsupporting MySQL replication filters ...to name but a few, a comprehensive list of bug fixes can be browsed in launchpad bug trackers (Galera 2.1, MySQL 5.5.23-23.6, MySQL 5.1.62-23.4). The 2.1 release is backwards compatible with earlier releases, and rolling online upgrade for the Cluster is possible. Downloads can be reached at downloads page as usual.
-
MySQL Connector/Net 6.4.5 has been released
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!
|
