MySQL Bi-weekly News
Apr 26, 2012 / By Marco Tusa
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:
- 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”
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.
Some possible uses of MySQL with Galera:
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.
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.
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 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.
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.
The main difference between Galera solution and Continuent Replicator is that Replicator use Asyncronous replication.
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.
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:
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:
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.
Good hint to prevent replication issues when usign temp table in MySQL without get rid of them
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.
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
“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
MySQL Cluster loading data howto a useful giude
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
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?
2 comments on “MySQL Bi-weekly News”
Pingback: Log Buffer #269, A Carnival of the Vanities for DBAs | The Pythian Blog
Pingback: Rutweb Technology : Portable Tablespace in InnoDB I test it!