At MySQL, replication usability is of utmost importance to us. Replication information has long been part of SHOW commands, SHOW SLAVE STATUS occupying a major chunk of it. The other sources of replication information being:

SHOW MASTER STATUS,
SHOW BINLOG EVENTS,
SHOW RELAYLOG EVENTS,
SHOW VARIABLES,
SHOW STATUS,
…

Multi-source replication for MySQL has been released as a part of 5.7.5-labs-preview
downloadable from labs.mysql.com. It is one among the several features that are
cooking in the replication technologies at MySQL.  (For a birds eye view of all
replication features introduced in 5.7 and labs, look  at the blog posts here and here.

Previously, we have introduced a preliminary multi-source feature labs preview. Based on the feed back from that labs release, we …

We have been asked several times by MySQL users about how to install multiple MySQL instances on a single host.
Typically, this is required when testing different MySQL versions or MySQL servers (MySQL server, Percona server and MariaDB server) while no available resources are available.
Sometimes, it is even required to install multiple MySQL instances on a single production server.

In this article, I'll go through the steps needed to install multiple MySQL instances on a single host (using the tar balls binaries) and how our popular tool MyEnv can make such process so easy.

Prepare MySQL environment

[root@centos-temp ~]# groupadd mysql
[root@centos-temp ~]# useradd -g mysql mysql
[root@centos-temp ~]# su - mysql
[mysql@centos-temp:~]$ mkdir ~/product
[mysql@centos-temp:~]$ mkdir ~/data
[mysql@centos-temp ~]$ cd /downloads
[mysql@centos-temp …

I recently had a client ask me how to use Percona Toolkit tools with an SSL connection to MySQL (MySQL-SSL). SSL connections aren’t widely used in MySQL due to most installations being within an internal network. Still, there are cases where you could be accessing MySQL over public internet or even over a public “private” network (ex: WAN between two colo datacenters). In order to keep packet sniffers at bay, the connection to MySQL should be encrypted.

If you are connecting to Amazon RDS from home or office (ie: not within the AWS network) you better be encrypted!

As there is already a MySQL Performance Blog post on how to setup MySQL SSL connections, we can skip that and dive right in.

As you probably know, the mysql client …

In a few weeks I’m presenting “Performance Benchmarking: Tips, Tricks, and Lessons Learned” at Percona Live London 2014 (November 3-4). I continue to learn lessons and improve my benchmarking capabilities, so the content is a full upgrade from my presentation at Percona Live Santa Clara in April 2013. Anyone interested in achieving and sustaining the best performance out of their software/hardware/application should attend.

Also, Tokutek is sponsoring so we’ll be available in the expo hall throughout the show.

If you are attending or in the area and want to learn more about …

In the new InnoDB lab release we support page sizes of 32k and 64k. This gives users even more choices on the page size, allowing you to further customize InnoDB for your particular workload.

There are some things worthy of note related to this new feature:

1. The extent size changes when the innodb_page_size is set 32k or 64k.
   The extent size is 2M for 32k page sizes, and 4M for 64k page sizes (the extent size is 1M for 4k, 8k, and 16k page sizes). If we do not enlarge the extent size then we will have too many extent headers on the allocation bitmap page, and the bitmap page will overflow.
2. The …

Below is the excerpt from MySQL manuals on slow query log :

“By default, administrative statements are not logged, nor are queries that do not use indexes for lookups. This behavior can be changed using log_slow_admin_statements and log_queries_not_using_indexes, as described later. “

This manual entry is kind of misleading . It means that queries not using indexes are not logged by default. Let us see what the reality is :

1) Let us see if queries not using indexes are being logged.

Below table does not have index:

mysql> describe employee ;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| ID    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> show indexes from employee ;
Empty set (0.00 sec) …

One of our agents, vc-mysql-query, works by sniffing TCP traffic with libpcap and decoding the MySQL protocol. As you can imagine, it’s one of the most complicated portions of our codebase. It’s also difficult to test. We have a set of tests using tcpdump files of production MySQL traffic to deterministically test the sniffer code. We run the tcpdump files through the sniffer and check the generated output. The issue with this is that every time we add a new dump file, it’s a lot of manual work to know for sure what we should be seeing in the output. If the agent says that 100 SELECTs were run, how do we make sure it’s true?

In order to brute-force test and try to smoke out bugs, we wrote a small Go tool that we call the sanity-check. It runs a production version of vc-mysql-query and tests it with a black-box approach.

Functionality

The first thing that the sanity-check tool does is create a test database to hold …

I still get asked about the lost+found directory enough, and so I wanted to provide a current update.

The lost+found directory is a filesystem directory created at the root level of a mapped drive. Thus this is common to see if you create your mysql datadir at the root level of a mapped drive.

In the past, you could ignore it, if it wasn’t too problematic for you, or you could move your datadir down a level, and then it wouldn’t be created in the datadir anymore.

However, there is now the –ignore-db-dir option. It is actually not too new (it’s been in MariaDB since 5.3.9 and 5.5.28, and in MySQL as of 5.6.3), but I don’t think many are too familiar with it.

But when you do run into this problem, some/many would prefer to add a single line to the config file rather than move the datadir.

To do this, just add the following option to your my.cnf file, under the [mysqld] section (it cannot be set …

I have recently posted up benchmarks of libAttachSQL vs. libmysqlclient using sysbench. Whilst these are great and shows the results I hoped for, this isn't what we designed libAttachSQL for. It was designed for non-blocking many connections per thread.

With this in mind I spent today knocking up a quick benchmark tool which replicates the Sysbench "Select" test but using libAttachSQL's connection groups on a single thread. The code for this can be seen in the new AttachBench GitHub tree. Of course the secondary reason for this is to try and hammer the connection groups feature, which of course did find a bug when I scaled to around 32 connections. This has been fixed in libAttachSQL's master ready for …