MySQL Bi-Weekly News (April 26, 2012)

The following is a brief list of what I have found interesting during the last two weeks. Up to now, April has been 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 allows MySQL to become the most adopted open source database.

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

• Security
• Solution design
• Oracle – MySQL migration
• Tutorial on “How to evaluate which MySQL High Availability solution best suits you”

Replication solutions

Galera

Many presentations were focused on Replication solutions or Synchronous and Asynchronous and based on Percona/Galera synchronous solution or on Continuent Replicator. The Galera solution allows architecture based on multi MySQL nodes. All allow writes, connected to each other by synchronous replication.

https://www.codership.com

Some possible uses of MySQL with Galera:

• Read Master: It’s traditional MySQL master-slave topology, but with Galera all “slave” nodes are capable masters at all times. Only the application 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. 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 failover delay.

• Latency Eraser: With WAN replication topology, cluster nodes can be located close to clients. 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 the 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. (https://www.continuent.com)

But replicator can be used 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

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

MariaDB

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

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

Oracle

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

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

New flushing algorithm in InnoDB

MySQL labs release April 2012 includes 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 the number of dirty pages, the amount of reusable redo space, the rate at which redo is generated, and the IO capacity for which the server is configured, Based on these factors, is decides how many pages we need to flush.

A description of the function can be found at the following links:

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

https://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. The execution time can now be improved by 25%.

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

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

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

Good hint to prevent replication issues when using temp table in MySQL without getting rid of them

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

It could happen that applications need to create temporary tables to manage datasets while preparing reports. Such action is normally reported in the binary log and then replicated to the other servers. Given that those tables are supposed to be there only for the report writing then in the binary log, it’s expensive and useless. Robert provides a simple indication on how to prevent it from happening.

Security issue in MySQL allows DoS attack

April 2012 Oracle has announced new security upgrades for MySQL in their Critical Patch Update Advisory. See demo of the bug here.

MySQL users only need CREATE, INSERT, DELETE, and SELECT privileges on his Schema to be able to perform the DoS. MySQL databases that are older than 5.1.62 and 5.5.22 must be upgraded. This is also valid for Percona Server and MariaDB.

Finally, InnoDB tablespace will be transportable

“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 delivered with the new MySQL 5.6 GA

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

MySQL Cluster loading data how-to, a useful giude

https://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 on how to do it properly.

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

If you get an error message and you suspect a bug, what can you do to file a good bug report with inside/code information?