Peter Zaitsev posted Percona's answer to the question, "why use the slow query log instead of the PERFORMANCE_SCHEMA tables to gather information about query execution and timing?" At VividCortex, we don't currently use the PERFORMANCE_SCHEMA for this either. Instead, we capture and time queries by sniffing the TCP traffic to the server, and decoding the MySQL protocol. We extract rich information from the protocol, including the query text, timings, errors, warnings, query flags, and more.

In this post I'll explain why we do this and how it works relative to other options.

Query Capture Methods

What options exist for measuring queries the server executes? Here's a short list.

• query logs
• PERFORMANCE_SCHEMA
• packet inspection
• proxies
• tools that …

Databases store information, right? Well, what if they could store information about your information so you could query it? Good news! Most database engines such as MySQL implement Information Schema, or a set of views that describe your tables and columns. If you’ve ever used a language like Java or C#, you might be familiar with reflection: the ability to read an object’s metadata. This enables you to do meta-programming, or writing logic about the program itself.

The idea of Information Schema is like reflection: they are views in your database that you can use in your programs or scripts when you need to know what the schema looks like or check the health of the instance. Unlike Show Processlist or Desc Table commands, the results are in tabular format.

I’m going to tour through six important views, and I’ll point you to some others at the end. Let’s get started.

Information_Schema.Tables

Let’s …

If you have followed this series of blogs (Introduction in part 1, Setting up the cluster in part 2 and Installing and configuring MaxScale in part 3), you should now have MaxScale up and running on a Cluster using MariaDB Replication. But as I said when I left off in Part 3, there is more to it.

To begin with, let's look at how the replication system works. Let's insert some more data through MaxScale, this should end up with the master and the replication system will handle making this available on the slaves. So on the Client machine (192.168.0.167), logged in a root, do:
# mysql -h …

In my previous post I pointed out that the existing ARCHIVE storage engine in MySQL may not be the one that will satisfy your needs when it comes to effectively storing large and/or old data. But are there any good alternatives? As the primary purpose of this engine is to store rarely accessed data in disk space efficient way, I will focus here on data compression abilities rather then on performance.

The InnoDB engine provides compressed row format, but is it’s efficiency even close to the one from that available in archive engine? You can also compress MyISAM tables by using myisampack tool, but that also means a table will be read only after such operation.

Moreover, I don’t trust MyISAM nor Archive when it comes to data durability. Fortunately along came a quite new (open source since April …

PodcastsLearning

This week, we talk about the awesome ways to use MariaDB's CONNECT storage engine that go above and beyond merely connecting to remote tables.

MariaDB CONNECT Engine
Episode 174, where we talked about setting up the CONNECT storage engine and some simple examples.

Pivot Tables
MariaDB documentation page about pivot tables with CONNECT

Wikipedia entry about pivot tables

Creating the pivot table example:

If you are starting out on the MySQL command line client, you might be interested in the following information.

When you use the MySQL command line client, it is often useful to log the commands you enter and the response you receive back from the MySQL server. The MySQL 'tee' command allows you to do this very simply and conveniently. Just enter 'tee' followed by the path to the file you want to log the session to (normally referred to as the 'tee' file). From that point on, all your session activity is logged to that file. To cancel tee file logging, just execute 'notee'.

To learn more about getting started on the MySQL Database, take the MySQL for Beginners training course. You can take this 4-day course through the following delivery …

Today I looked at the MariaDB Release Candidate wondering how my earlier predictions came out.

I predicted, for "roles":

For all of the bugs, and for some of the flaws, there's no worry -- they'll probably disappear.

In fact all the bugs are gone, and I belatedly realized (after some gentle nudges from a MariaDB employee) that some of the flaws weren't flaws.
The inability to grant to PUBLIC still troubles me, but it looks like roles are ready to roll.

I predicted for "mroonga":

At the time I'm writing this, MariaDB 10.0.8 doesn't have mroonga yet.

In other words, I thought it would be in 10.0.8. It was not to be, as another …

Over the last few days different people asked me for comments about PHP dropping MySQL support. These questions confused me, but meanwhile I figured out where these rumors come from and what they mean.

The simple facts are: No, PHP is not dropping MySQL support and we, Oracle's MySQL team, continue working with the PHP community.

For the long story we first have to remember what "PHP's MySQL support" includes. There key part are four extensions which are part of the main PHP tree:

• ext/mysql
• ext/mysqli
• ext/pdo_mysql
• ext/mysqlnd

The first one, ext/mysql provides the mysql_* functions. This is the classic interface taught in many (old) books and used by lots of (old) software. mysqli is "mysql improved", this is a younger extension providing access to all MySQL features. pdo_mysql contains the driver …

The LinkedIn MySQL DB Development group posed a questions on how to handle images. Naturally, the argument always goes: Should images be deployed in the database or the file system? I believe they should be stored in the database because the cost and time associated is too high with regard to managing files, a file naming schema, and backing up the file system discretely from the database.

Since there’s a significant difference between the backup of transactional data and image data, they should be placed in different databases. The imagedb database is where you would place the images and large text descriptions, as shown in the MySQL Workbench ERD:

The imagedb ERD splits the foreign key references back to the system_user table, which contains the individual user credentials. The …

Covering indexes can speed up your queries a lot, how much will depend on if  your are CPU or disk bound. For those who are not familiar with covering indexes,  a covering index is a special type of index where the index itself contains all required data fields in statement. This is especially important for InnoDB which have a clustered primary key, and all secondary keys will contain the primary key at leaf node, using covering indexes means one less look-up fetching data which normally leads to less random I/O.

So, lets see how much performance improvements we can get by using covering indexes. Firstly we need to fire up a MySQL instance, I opted for a installed MySQL 5.6.12 with default settings and imported a copy of the world database 

…