Percona Toolkit’s pt-table-checksum is a great tool to find data inconsistencies between a MySQL master and its replicas. However it is sometimes not enough to know that there are inconsistencies and let pt-table-sync fix the issue: you may want to know which exact rows are different to identify the statements that created the inconsistency. This post shows one way to achieve that goal.

The issue

Let’s assume you have 2 servers running MySQL 5.5: db1 the master and db2 the replica. You want to upgrade to MySQL 5.6 using an in-place upgrade and to play safe, you will upgrade db2 (the slave) first. If all goes well you will promote it and upgrade db1.

A good thing to do after upgrading db2 is to check for potential data …

Stop increasing max_connections every time there’s a 1040: Too Many Connections error. Every additional connection is another share to further divide the available memory.

Instead, while it would be best to manage the workload, it is also reasonable to properly utilize the available hardware with good server configuration.

There are three relevant server configuration options for managing connection counts as they relate to satisfying web requests.

1. max_connections – the queue depth
2. innodb_thread_concurrency – the count of queue consumers
3. innodb_concurrency_tickets – the amount of work a consumer can do on a query before switching to the next query request

Correctly configuring these three variables, and controlling your workload of course, can prevent 1040 Too many connections errors, assuming, …

Mon, 2014-11-03 13:09anatoliydimitrov

Yii is a fast and secure framework for developing PHP applications. It has excellent support for various databases, including MariaDB, as we'll illustrate here.

Yii installation is simple and straightforward. Just download the framework archive and extract it to a directory outside your website's document root. For example, if your site resides in /var/www/html/, extract the archive to /var/www/ so that you can access the framework at /var/www/framework. Then run the shell command /var/www/framework/yiic webapp /var/www/html/ to create the skeleton of a new web application in /var/www/html.

To make sure the PHP applications you create with Yii can work with MariaDB, make sure your PHP installation has MySQL PDO support, which means it will also have MariaDB support. In CentOS, the package that provides this is …

Mon, 2014-11-03 13:09anatoliydimitrov

Yii is a fast and secure framework for developing PHP applications. It has excellent support for various databases, including MariaDB, as we'll illustrate here.

Yii installation is simple and straightforward. Just download the framework archive and extract it to a directory outside your website's document root. For example, if your site resides in /var/www/html/, extract the archive to /var/www/ so that you can access the framework at /var/www/framework. Then run the shell command /var/www/framework/yiic webapp /var/www/html/ to create the skeleton of a new web application in /var/www/html.

To make sure the PHP applications you create with Yii can work with MariaDB, make sure your PHP installation has MySQL PDO support, which means it will also have MariaDB support. In CentOS, the package that provides this is …

We recently announced our 5.7.5 Milestone Release, yet another milestone on our road to 5.7 GA. The purpose of our milestone releases is to get community feedback and to ensure high quality on 5.7 from day one. This blog post gives the reader a high level view on 5.7 so far, while also attempting to demonstrate where we are heading as many of the individual pieces make much more sense when you can see the bigger picture. You might further drill into the series of milestone blog posts (5.7.1, 5.7.2, …

Facebook recently made opensource, osquery. It gives you operating system data via SQL queries! Its very neat, and you can test this even on MacOSX (it works on that platform & Linux). It is by far the project with the most advanced functionality, linked here in this post.

I noticed that rather quickly, there was a PostgreSQL project, called pgosquery, based on Foreign Data Wrappers with a similar idea. (apparently it was written in less than 15 minutes; so a much lower learning curve than the regular MySQL storage engine interface)

I immediately thought about an older MySQL project, by Chip Turner (then at Google, now at Facebook), called …

I am writing this blog to provide a basic introduction on MySQL Security Settings where it is useful and easy to kick start a secured environment within MySQL.

This blog page has no intent to cover "all" security concerns but it is a very common step in securing the MySQL server.

The article (1) provides general steps in the following topics to secure MySQL database
- Database Hardening using mysql_secure_installation
- Secure Channel with ssl certificate(s) between mysqld and mysql

There are more and more .... (Audit Plugin, MySQL 5.6 Enterprise Encryption, Password Encryptionm Password Policy, etc...)  which they are not covered in this blog!

Database Hardening using "mysql_secure_installation"
By default, when a MySQL database is installed (for example using …

We’ve recently added the ability to monitor MySQL query performance statistics from MySQL’s PERFORMANCE_SCHEMA, and there were a number of lessons learned. There are definitely right and wrong ways to do it. If you are looking to the P_S tables for monitoring MySQL query performance, this blog post might save you some time and mistakes.

What Is The Performance Schema?

First, a quick introduction. The Performance Schema includes a set of tables that give information on how statements are performing. Most of the P_S tables follow a set of predictable conventions: there’s a set of tables with a limited set of full-granularity current and/or historical data, which is aggregated into tables that accumulate over time. In the case of statements, there’s a table of current statements, which feeds into a statement history, that accumulates into statement summary statistics. The tables are named as follows:

The PostgreSQL manual says:

"The standard's definition of the behavior of temporary tables is widely ignored. PostgreSQL's behavior on this point is similar to that of several other SQL databases."

The first sentence is false. The second sentence could be rephrased as "MySQL copied us", although nobody else did, as far as I know.

The standard's definition is widely followed

I base this claim on the documentation of DB2, Oracle Rdb, Oracle 12c, and …

“HBase and Hadoop are the only technologies proven to scale to dozens of petabytes on commodity servers, currently being used by companies such as Facebook, Twitter, Adobe and Salesforce.com.”–Monte Zweben.

Is it possible to turn Hadoop into a RDBMS? On this topic, I have interviewed Monte Zweben, Co-Founder and Chief Executive Officer of Splice Machine.

RVZ

Q1. What are the main challenges of applications and operational analytics that support real-time, interactive queries on data updated in real-time for Big Data?

Monte Zweben: Let’s break down “real-time, interactive queries on data updated in real-time for Big Data”. “Real-time, interactive queries” means that results need to be returned in milliseconds to a few seconds.
For “Data updated in real-time” to happen, …