This morning I was planning to check the integrity of a snapshot of a MySQL database that runs on EC2 using EBS, and I accidentally stumbled upon a fast way to quickly find certain types of problems with a backup.

My plan was to use CHECK TABLE on all of the tables to verify the integrity of the backup. The simplest way to run CHECK TABLES on an entire database is like this:

mysqlcheck -c --all-databases

Rather than using that command, I decided to generate a bunch of individual CHECK TABLE statements dynamically by running a query on the information_schema.tables table. Why do it that way? It gives me the flexibility to check a specific schema first, or check the smallest or most frequently updated tables first, etc.

So I ran this command to generate my CHECK TABLE script:

select concat('check table ', table_schema, …

How To Configure Tungsten Connector For Load Balancing, Read/Write Splitting, Automatic Failover And Online Maintenance

As the part of analyzing surprising MySQL 5.5 vs 5.6 performance results I’ve been looking at changes to default variable values. To do that I’ve loaded the values from MySQL 5.5.30 and 5.6.10 to the different tables and ran the query:

mysql [localhost] {msandbox} (test) > select var55.variable_name,left(var55.variable_value,40) value55, left(var56.variable_value,40) var56  from var55 left join var56 on var55.variable_name=var56.variable_name where  var55.variable_value!=var56.variable_value;
+---------------------------------------------------+------------------------------------------+------------------------------------------+
| variable_name                                     | value55                                  | var56                                    | …

This describes the tables in the Facebook patch for MySQL for table and user monitoring. I frequently refer to them as table_stats and user_stats. These tables are in the INFORMATION_SCHEMA and started in the Google patch many years ago (2007 or 2008). They have been significantly enhanced since then with more columns and more efficient implementations. The feature started before the INFORMATION_SCHEMA, so SHOW TABLE_STATISTICS and SHOW USER_STATISTICS can be used to get their data in addition to SELECT statements. It is important for frequently used monitoring commands to be easy to type and the SHOW commands are easy to type when you need all of the data.Some of the metrics below are only implemented for InnoDB but should be implemented for all storage engines in a general purpose product. Other metrics are specific to InnoDB, like the ones for compressed tables. Some of the disk IO metrics are implemented for InnoDB as per-tablespace metrics. This …

This describes the tables in the Facebook patch for MySQL for table and user monitoring. I frequently refer to them as table_stats and user_stats. These tables are in the INFORMATION_SCHEMA and started in the Google patch many years ago (2007 or 2008). They have been significantly enhanced since then with more columns and more efficient implementations. The feature started before the INFORMATION_SCHEMA, so SHOW TABLE_STATISTICS and SHOW USER_STATISTICS can be used to get their data in addition to SELECT statements. It is important for frequently used monitoring commands to be easy to type and the SHOW commands are easy to type when you need all of the data.Some of the metrics below are only implemented for InnoDB but should be implemented for all storage engines in a general purpose product. Other metrics are specific to InnoDB, like the ones for compressed tables. Some of the disk IO metrics are implemented for InnoDB as per-tablespace metrics. This …

Lately we’ve had many reports of the RPM packages for CentOS 5 (mostly) and CentOS 6 having issues when installing different combinations of our products, particularly with Percona Toolkit. Examples of bugs related to these issues are lp:1031427 and lp:1051874.

These problems arise when trying to install a package from the distribution that is linked against the version of libmysqlclient.so shipped by the distribution (libmysqlclient.so.15 for CentOS 5/libmysqlclient.so.16 for CentOS 6) and a version of Percona Server that depends on another version of libmysqlclient.so, usually more recent. Bug lp:1031427 is an example of this, and shows how the packages would conflict when trying to …

Note: I was recently asked by a large financial firm to write some thoughts about write scaling strategies around Postgres. While it’s not code, I thought I would “open source” that work by posting it here. What follows is the first in a 5 part series of posts.

Introduction

Horizontal partitioning, often referred to as sharding, has become a common pattern for scaling large database loads, especially within the webops community. This is not something particular to Postgres, if you look on the web, you’ll see many articles about sharding with other database, most prominently MySQL. The main drivers behind these techniques (IMHO) are a combination of the inability of OSS databases to scale vertically on high-end hardware (though they are making improvements in this area), and the desire to spend hardware dollars on easily replaceable and interchangeable, smaller servers running on commodity hardware, rather than investing in …

Latest training events calendar & class schedule now available

read more

In my last few posts, I wrote about “How to install MySQL replication using GTID’s” (Part One, Part Two). In this post, I will show you how to install MySQL 5.6 and set up replication between two MySQL servers the “old fashioned way” using the binary log and binary log position.

I am going to create some virtual machines instead of using individual servers. But, you can also use these instructions to create a MySQL replication (master/slave) setup with real servers.

Here is …

MySQL 5.6 has introduced a new unit test framework beside the existing ones. The googletest test framework is now part of the MySQL test framework for 5.6. GoogleTest is a C++ Testing Framework that helps  write better C++ tests.

I realized that it was used when i tried to buid a MySQL release from sources [...]