Mon, 2015-03-16 11:20jean-françoisgagné

Parallel replication is a much-expected feature of MySQL. It’s available in MariaDB 10.0 and in MySQL 5.7. Yet, both lose efficiency when replicating through intermediate masters. In this post, we’ll explain how parallel replication works and why it does not play well with intermediate masters. We’ll also offer a solution (hint: it involves Binlog Servers).

In MySQL 5.5 and MariaDB 5.5, replication is single threaded: on a slave, the previous transaction must complete (commit) before the next transaction can start. This is not the case on a master where many transactions can make progress at the same time. The consequence is that a slave has less transaction processing capabilities than its master (for writes). This means that care must be taken to avoid loading a master with …

Over on the codecentric blog I just published a new post on True KVM Live Migration with OpenStack Icehouse and Ceph based VM storage, outlining the process it took me to get this actually working. There were several issues to work around, some of them bugs in OpenStack, some of them home-grown issues.

I provide a few patches to OpenStack classes as well as a description of what is actually going wrong and how to circumvent the problems. So head right over and have fun reading :)

One of our Percona Support customers recently reported that Percona XtraBackup failed with a page corruption error on an InnoDB table. The customer thought it was a problem or bug in the Percona XtraBackup tool. After investigation we found that an InnoDB page was actually corrupted and a Percona XtraBackup tool caught the error as expected and hence the backup job failed.

I thought this would be an interesting topic and worthy of a blog post. In this article I will describe the innochecksum tool, when and how to use it and what are the possible fixes if an InnoDB table suffers from …

PostgreSQL’s approach to automatic numbering is as simple as Oracle but different than MySQL, and Microsoft SQL Server. For example, you have a two-step process with Oracle, PostgreSQL, MySQL, and Microsoft SQL Server. First, you create an Oracle table with the GENERATED AS IDENTITY clause, a PostgreSQL table with the SERIAL data type, a MySQL table with the AUTO_INCREMENT clause, and a Microsoft SQL Server table with the IDENTITY(1,1) clause. Then, you need to write an INSERT statement for Oracle, MySQL, or Microsoft SQL Server like:

1. Oracle’s INSERT statement excludes the auto-incrementing column from the list of columns or provides a NULL value in the VALUES-list. You can then assign the RETURNING INTO result from an INSERT statement to a session-level (bind) variable.
2. MySQL’s …

On March 10, 2015, we released MySQL-5.7.6 and among many other things it includes multi-source replication which …

One of my customers wants to search for names in a table. But sometimes the search is case insensitive, next time search should be done case sensitive. The index on that column always is created with the collation of the column. And if you search with a different collation in mind, you end up with a full table scan. Here is an example:

The problem

 mysql> SHOW CREATE TABLE City\G  
 *************************** 1. row ***************************  
 Table: City  
 Create Table: CREATE TABLE `City` (  
 `ID` int(11) NOT NULL AUTO_INCREMENT,  
 `Name` char(35) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,  
 `CountryCode` char(3) NOT NULL DEFAULT '',  
 `District` char(20) NOT NULL DEFAULT '',  
 `Population` int(11) NOT NULL DEFAULT '0',  
 PRIMARY KEY (`ID`),  
 KEY `CountryCode` (`CountryCode`),  
 KEY `Name` (`Name`),  
 ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1  
 1 row in set (0,00 sec)  

…

http://blog.booking.com/better_parallel_replication_for_mysql.html

Follow the link above to read my latest article on the Booking.com developer blog.  It is about MySQL Parallel Replication and Binlog Servers.

This fulfills my promise made at Percona Live London 2014 during my talk High Availability, Disaster Recovery and Extreme Read Scaling using Binlog Servers: I finally took the time to

My webinar “Multi-threaded Replication in MySQL 5.6 and 5.7″ on February 25 generated several excellent questions following the presentation (available here for playback along with the slides). I didn’t have time to answer many of the questions during the session and so in this post I answer all of them. Thanks to everyone who attended!

Q: What do you expect from MTS with logical clock? Do you think performance would be good as with per database?
A: MTS with 5.6 is not usable if you have a single database. I do not have numbers, but this is quite frequent. With 5.7 everyone should be able to benefit from multi-threaded replication.

Q: When MySQL 5.6 was released, performance of MTS was lower, than in 5.5, for example. Is this addressed now?
A: I am not sure which …

The MySQL developer tools team is pleased to announce 6.3.2 RC - the first release candidate for MySQL Workbench 6.3

For the full list of changes in this revision, visit

http://dev.mysql.com/doc/relnotes/workbench/en/changes-6-3.html

For detailed information about the new features, see What’s
New in MySQL Workbench 6.3
http://dev.mysql.com/doc/workbench/en/wb-what-is-new-63.html

For discussion, join the MySQL Workbench Forums:
http://forums.mysql.com/index.php?151

Download MySQL Workbench 6.3.2 RC now, for Windows, Mac OS X 10.7+,
Oracle Linux 6 and 7, Fedora 20 and Fedora 21, Ubuntu 14.04 and …

Today I read a nice article , which give a new design for mysql database . In this article , Author (Mr.lou) uses OneSQL (a branch of MySQL) for the whole test.

you can read from : http://www.onexsoft.com/?page_id=378

This article explain how to use mysql in seckilling scenario (especially for E-Commerce commany )

If you want to use transaction in RDBMS database, you must manual start transaction and commit in the end of your operation.
So,commit will be cost of the total process, round-trip cost will be the most important cost as author said

(For simple update operations, without regard to conflict of IO and locks, a statement execution time is about 0.1ms, network delay under normal conditions is 0.4-0.8ms, that the waiting time is longer than the transaction commit time to really notice the SQL execution several times, showing that the …