MySQL Bi-weekly News

Following a brief list of what I have found more interesting during the last two weeks.

Up to now, April has being a great month for MySQL.

MySQL Conference  – Percona conference 2012

The Percona MySQL 2012 conference, has seen the MySQL community, interact as it was doing many years ago,

re-creating the dynamic and creative environment that allow MySQL to become the most adopted open source database.

Pythian was present at the conference with 3 speech and one tutorial, covering different aspects:

- Security http://www.percona.com/live/mysql-conference-2012/sessions/security-around-mysql

- Solution design http://www.pythian.com/news/32055/percona-live-mysql-conference-presentation/#comments

- Oracle – MySQL migration http://www.pythian.com/news/32075/my-presentation-slides-on-oracle-mysql/

- Tutorial on “How to evaluate which MySQL High Availability solution best suits you”

http://www.percona.com/live/mysql-conference-2012/sessions/how-evaluate-which-mysql-high-availability-solution-best-suits-you

Replication solutions

Galera

A lot of presentation were focus on Replication solutions, Synchronous and Asyncronous, base on Percona/Galera syncronous solution, or on Continuent Replicator.

Galera solution allow architecture based on multi MySQL nodes, all allow writes, connected each other by syncronous replication.

http://www.codership.com

Some possible uses of MySQL with Galera:

Read Master

Traditional MySQL master-slave topology, but with Galera all “slave” nodes are capable masters at all times, it is just the application who treats them as slaves. Galera replication can guarantee 0 slave lag for such installations and due to parallel slave applying, much better throughput for the cluster.

Write Scalability

Distributing writes across the cluster will harness the CPU power in slave nodes for better use to process client write transactions. Due to the row based replication method, only changes made during a client transaction will be replicated and applying such a transaction in slave applier is much faster than the processing of the original transaction. Therefore the cluster can distribute the heavy client transaction processing across many master nodes and this yields in better write transaction throughput overall.

WAN Clustering

Synchronous replication works fine over the WAN network. There will be a delay, which is proportional to the network round trip time (RTT), but it only affects the commit operation.

Disaster Recover

Disaster recovery is a sub-class of WAN replication. Here one data center is passive and only receives replication events, but does not process any client transactions. Such a remote data center will be up to date at all times and no data loss can happen. During recovery, the spare site is just nominated as primary and application can continue as normal with a minimal fail over delay.

Latency Eraser

With WAN replication topology, cluster nodes can be located close to cilents. Therefore all read & write operations will be super fast with the local node connection. The RTT related delay will be experienced only at commit time, and even then it can be generally accepted by end user, usually the kill-joy for end user experiences is the slow browsing response time, and read operations are as fast as they possibly can be.

Continuent Replicator

The main difference between Galera solution and Continuent Replicator is that Replicator use Asyncronous replication.

http://www.continuent.com

But replicator can have be use to implement many different replication models like:

- Replicate data in real time between Oracle and MySQL.

Tungsten Replicator allows you to replicate data to and from other DBMS types in real time. Tungsten Replicator moves between MySQL and Oracle in real time and without application changes.

- Implement multi-master replication between a head office and branches using star topologies.

Tungsten Replicator allows star patterns required to implement head office/branch office data sharing. Tungsten Replicator supports complex multi-master topologies such as star replication where multiple masters share data via a single share master DBMS.

- Aggregate data from multiple masters into a single slave.

Tungsten Replicator permits slaves to have multiple masters in a simple, easy-to-manage topology. Tungsten Replicator moves data required for reporting and data aggregation from multiple masters into a slave.

- Eliminate slave lag.

Tungsten Replicator eliminates slave lag. Tungsten Enterprise parallel replication boosts MySQL replication performance up to 5x.

Optimizer

Other area of improvement was the unbelivable work done by the two development team at Oracle and MariaDB. Both have implemented a lot of optimization specially for the OPTIMIZER. Documentation can be found at:

MariaDB

- http://kb.askmonty.org/en/what-is-mariadb-53

- http://kb.askmonty.org/en/optimizer-feature-comparison-matrix

Oracle

https://blogs.oracle.com/MySQL/entry/new_replication_optimizer_and_high

http://mikaelronstrom.blogspot.ca/2012/04/mysql-team-increases-scalability-by-50.html

New flushing algorithm in InnoDB

MySQL labs release April 2012 include the flushing heuristics in InnoDB.

Flushing means writing dirty pages to disk. The page_cleaner thread checks the state of the system every second and takes into account number of dirty pages, amount of reusable redo space, the rate at which redo is generated and the IO capacity for which the server is configured and based on these factors decide how many pages we need to flush.

Description of the function can be found at the following links:

http://dimitrik.free.fr/blog/archives/04-01-2012_04-30-2012.html#143

http://dimitrik.free.fr/blog/archives/04-01-2012_04-30-2012.html#142

Another interisting article is related to subqueries and Materialization

Nowadays MySQL can, and does, use subquery materialization for query; thanks to it, the execution time can have 25% improvement.

http://guilhembichot.blogspot.ca/2012/04/faster-subqueries-with-materialization.html

http://dev.mysql.com/doc/refman/5.6/en/subquery-materialization.html

http://monty-says.blogspot.ca/2012/04/lots-of-mariadb-releases.html

Good hint to prevent replication issues when usign temp table in MySQL without get rid of them

http://scale-out-blog.blogspot.co.uk/2012/04/replication-is-bad-for-mysql-temp.html

It could happen that applications, needs to create temporary table to manage datasest, while preparing reports.

This actions are normally reported in the binary log and then replicated to the other servers.

Given those tables are suppose to be there only for the report writing them in the binary log, is expensive and usless.

Robert provide simple indication on how to prevent it to happen.

Security issue in MySQL allow DoS attack

April 2012 Oracle has announced in their Critical Patch Update Advisory, new security upgrades for MySQL.

http://www.oracle.com/technetwork/topics/security/cpuapr2012-366314.html

See demo of the bug at http://eromang.zataz.com/2012/04/10/oracle-mysql-innodb-bugs-13510739-and-63775-dos-demo/

MySQL user only needs CREATE, INSERT, DELETE and SELECT privileges on his Schema to be able to perform the DoS.

MySQL databases which are older than 5.1.62 and 5.5.22 must be upgrade.

This is also valid for Percona Server and MariaDB.

Finally InnoDB tablespace will be transportable

http://blogs.innodb.com/wp/2012/04/innodb-transportable-tablespaces/

“In the past, users were unable to take full advantage of the FLUSH TABLES WITH READ LOCK statement.

InnoDB simply ignored the flush to disk part.

If the table did not have any dirty pages in the InnoDB buffer that weren’t synced to disk (due to sheer luck) then it was safe to copy the .ibd file to another location.

Also, the restore was not without its limitations and complications.

The .ibd file could not be copied over to another server because InnoDB during import did not fix up metadata required for a trouble-free import.”

This is a huge improvment that only people working daily with MySQL/InnoDB can understand, so far it is still in the lab version but we all really hope to have it deliver with the new MySQL 5.6 GA

http://labs.mysql.com/ (mysql-5.6-labs-april-2012)

MySQL Cluster loading data howto a useful giude

http://johanandersson.blogspot.ca/2012/04/mysql-cluster-how-to-load-it-with-data.html

Moving your data from InnoDB or MyISAM is not just changing the engine with the ALTER command.

It requires some review and optimizations, here Johan gives good hints how to do it properly.

Learn how to track down a bug in MySQL without being a developer

http://www.skysql.com/blogs/adam-donnison/using-mysql-stack-trace-isolate-bugs

If you get an error message and you suspect a bug, what you can do to file a good bug report,

with inside/code information?