Read the original article at MySQL needs single master to check data integrity

MySQL slaves can drift out of sync. Many of our clients are surprised to find some data differences in their replication topology, once we do some checking and sniffing around. Such checks require a single reliable or authoritative master to compare against. Click through to the end for multi-master solutions that work with MySQL. Reason [...]

For more articles like these go to Sean Hull's Scalable Startups

Related posts:

1. MySQL requires an authoritative master to build slaves
2. …

While most of the talk recently has mostly been around the new changes in MySQL 5.6 (and that is understandable), I have had lately some very interesting cases to deal with, with respect to the Metadata Locking related changes that were introduced in MySQL 5.5.3. It appears that the implications of Metadata Locking have not been covered well, and since there are still a large number of MySQL 5.0 and 5.1 installations that would upgrade or are in the process of upgrading to MySQL 5.5, I thought it necessary to discuss what these implications exactly are.

The post Implications of Metadata Locking Changes in MySQL 5.5 appeared first on ovais.tariq.

MySQL 5.6 seems to be ready for GA. I have no inside information about it, but from some clues collected in various places I feel that the release should not be far away. Thus, it's time for some serious testing, and for that purpose I have worked at updating MySQL Sandbox with some urgent features.

I have just released MySQL Sandbox 3.0.28, with more support for MySQL 5.6. Notably in this release, there is suppression of MySQL 5.6 annoying verbosity, additional suppression of more annoying warnings ( actually a bug) when using empty passwords on …

The recently released TokuDB brings many features. One of those features is support for XA Transactions. InnoDB already has support for XA Transactions.

XA Transactions are transactions which span multiple databases and or applications. XA Transactions use 2-phase commit, which is also the same method which MySQL Cluster uses.

Internal XA Transactions are used to keep the binary log and InnoDB in sync.

Demo 1: XA Transaction on 1 node:

mysql55-tokudb6> XA START 'demo01';
Query OK, 0 rows affected (0.00 sec)

mysql55-tokudb6> INSERT INTO xatest(name) VALUES('demo01');
Query OK, 1 row affected (0.01 sec)

mysql55-tokudb6> SELECT * FROM xatest;
+----+--------+
| id | name   |
+----+--------+
|  3 | demo01 |
+----+--------+
1 row in set (0.00 sec)

mysql55-tokudb6> XA END 'demo01';
Query OK, 0 rows affected (0.00 sec) …

You may not realize it, but in MariaDB 5.2 and earlier and in MySQL up to version 5.5, START TRANSACTION WITH CONSISTENT SNAPSHOT does not give any guarantees of consistency between different storage engines.

For example, suppose you have two transactions which run in parallel:

Transaction T1:

BEGIN;
    SET @t = NOW();
    UPDATE xtradb_table SET a= @t WHERE id = 5;
    UPDATE pbxt_table SET b= @t WHERE id = 5;
    COMMIT;

Transaction T2:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    START TRANSACTION WITH CONSISTENT SNAPSHOT;
    SELECT t1.a, t2.b
      FROM xtradb_table t1 INNER JOIN pbxt_table t2 ON t1.id=t2.id
    WHERE t1.id = 5;

In the above case, it is possible, even with a "consistent" snapshot, to see the changes in a transaction only in InnoDB/XtraDB tables, and not in PBXT tables.

Naturally, it would be much better if the changes were visible no matter the …

The problem and its cause

There have been several complaints over the years about InnoDB’s inability to scale beyond 256 connections. One of the main issues behind this scalability bottleneck was the read view creation that is required for MVCC (Multi Version Concurrency Control) to work. When the user starts a transaction this is what InnoDB does under the hood:

• Create or reuse a transaction instance – usually it is reused, the transactions are reused from a pool (trx_sys_t::mysql_trx_list).
• Initialize the transaction start time and assign a rollback segment
• Append the transaction to an active  transaction list ordered on trx_t::id in descending order

The append to  the trx_sys_t::trx_list and corresponding remove during commit is covered by trx_sys_t::mutex. After the transaction is “started” and if the transaction has an isolation greater …

This recent session at OSDC 2011 Canberra is based on part of an Open Query training day, and (due to time constraints) without much of the usual interactivity, exercises and further MySQL specific detail. People liked it anyway, which is nice! The info as presented is not MySQL specific, it provides general insight in how databases implement concurrency and what trade-offs they make.

See http://2011.osdc.com.au/SQLL for the talk abstract.

We did recently a review of one of our customers systems and we found that the Query Cache was disabled even thought it had significant more read than write queries.
When we asked the customer why he has not enabled the Query Cache he mentioned a review that was done a few years ago and which stated that the Query Cache hit ratio was non optimal.
This was verified on a testing system which had the Query Cache enabled by accident.

But we all thought that the Query Cache would make sense in this situation so we investigated a bit more.

They have a Java application where they do pretty complex queries (10 to 30-way Joins) and they Connect with Connector/J to the database. We tried it out in the application on a dedicated system and verified that the Query Cache was not serving our queries but the query did a full dive to the data.

So first we were looking in the MySQL documentation if there is anything stated why …

So, this is a small blog that I felt necessity to do cause I am seeing many tweets on twitter (obviously) talking about that “a rollback query on innodb does not reset the auto_increment counter”. I quickly went test it and really didn’t understand what happened with this user environment, due to it I decided [...]

Isolation is an important part of ACID properties that guarantee that transactions are processed in a reliable manner. But there are four different levels of isolation available and you have to understand each one of them to be able to select the correct one for your needs. This post intends on explaining the four levels together with their effects on locking and performance.