In one of my previous posts, I started a series on data compression options with MySQL. The first post focused on the more traditional compression options like InnoDB Barracuda page compression and MyISAM packing. With this second part, I’ll discuss a newer compression option, InnoDB transparent page compression with punch holes available since 5.7. First, I’ll describe the transparent page compression method and how it works. Then I’ll present similar results as in the first post.

InnoDB transparent page compression

Before we can discuss transparent page compression, we must understand how InnoDB accesses its data pages. To access an InnoDB page, you need to know the tablespace (the file) and the offset of the page within the …

Recently I received several questions about rotating log files for ProxySQL, so I decided to draft it up as a blog post. Let me go by this using an example.

In my testing lab, I have set up a fairly default ProxySQL (version 1.4.13) service. The default location for the proxysql.log is in /var/lib/proxysql.

[root@proxysql ~]# ls -hal /var/lib/proxysql/proxysql.log*
-rw-------. 1 root root 4.9K Jan 30 18:47 /var/lib/proxysql/proxysql.log

I created a pretty basic default logrotate configuration to ensure my logfile rotates on a daily basis and five rotations are kept before expiring.

[root@proxysql ~]# cat /etc/logrotate.d/proxysql
/var/lib/proxysql/proxysql.log {
missingok
daily
notifempty
compress
create 0600 root root
rotate 5
}

First attempt

Let’s check whether this is actually the correct file that is used that we will be rotating. As it turned out it is!

[root@proxysql ~]# lsof | …

Tweet

When things go horrible wrong and a process crashes, one of the most powerful things to investigate the cause of the crash is a core dump. As the amount of memory allocated to processes such as MySQL has increased – in some cases approaching 1TiB of memory – enabling core dumps can cause problems of their own. MySQL Server 8.0.14 and later supports an option to reduce the size of the core dump which will be discussed in this blog.

Typically the largest single user of memory for MySQL is the InnoDB buffer pool. This is used to cache the data and indexes for tables using the InnoDB storage engine (the default). It is rarely important to know what is stored in the InnoDB buffer pool when investigating a crash, so a good way to …

The latest release of the MySQL Shell 8.0.14 (GA) improved the JSON import utility to support the conversion of more BSON data types from the strict mode representation of MongoDB Extended JSON. This removes a previous limitation regarding the import of more complex MongoDB data types to MySQL, making it more reliable.…

Facebook Twitter Google+ LinkedIn

In my recent post about the Left-Prefix Index Rule, I explained how queries can use all or part of a compound (multi-column) index. Knowing what makes an index fully usable by a query is important, but it's also important to know how to figure out how much of an index a query is able to use. In this article I'll show you how to do this by reading the query's explain plan. This article covers MySQL, Postgres, and MongoDB.

As a quick review, a query can use an index if it has filtering values that constrain a contiguous leading portion of the index, up to and including the first inequality condition in index-column order. Now let's see where the database server exposes how much of the index is used.

MySQL

In MySQL's …

ProxySQL 1.4.14, released by ProxySQL, is now available for download in the Percona Repository along with an updated version of Percona’s proxysql-admin tool.

ProxySQL is a high-performance proxy, currently for MySQL,  and database servers in the MySQL ecosystem (like Percona Server for MySQL and MariaDB). It acts as an intermediary for client requests seeking resources from the database. René Cannaò created ProxySQL for DBAs as a means of solving complex replication topology issues.

The ProxySQL 1.4.14 source and binary packages available from the  …

This event was just awesome. We got 110 participants ! Thank you everybody and also a big thank to the speakers.

Here are the slides of all the sessions:

• MySQL Shell – A DevOps-engineer day with MySQL’s development and administration tool, Miguel Araújo
• MySQL Shell : the best DBA tool ? – How to use the MySQL Shell as a framework for DBAs, Frédéric Descamps
• Tuning MySQL 8.0 InnoDB for High Load, Dimitri Kravtchuk
• …

In my webinar How To Rock with MyRocks I briefly mentioned the column families feature in MyRocks, that allows a fine tuning for indexes and primary keys.

Let’s review it in more detail.

To recap, MyRocks is based on the RocksDB library, which stores all data in [key => value] pairs, so when it translates to MySQL, all Primary Keys (data) and secondary keys (indexes) are stored in [ key => value ] pairs, which by default are assigned to “default” Column Family.

Each column family has individual set of

• SST files, and their parameters
• Memtable and its parameters
• Bloom filters, and their parameters
• Compression settings

There is a N:1 relation between tables and indexes to column family, so schematically it looks like this:

How do you assign …

Last week I presented the following session at the pre-FOSDEM MySQL Day:

MySQL Shell : the best DBA tool ? from Frederic Descamps

The audience seemed very interested on how the MySQL Shell can be extended.

During the presentation I showed how I extended the MySQL Shell with two new modules in Python:

• mydba
• innotop

Both projects are on github and are waiting for ideas, feature requests, pull requests, …

Here is …

The next two weeks are a big change for me compared to the last two weeks.  The last two weeks I was in Belgium and now I am in Miami before heading to the Dominican Republic.  The one thing in common is that I am speaking on the MySQL Document Store.

SunshinePHP has become a favorite conference for many PHP developers for many reasons and this morning I will be running a three hour tutorial on using the PECL MySQL X DevAPI and the MySQL Document Store with PHP and then have a one hour talk later in the show. Plus I am joined by the amazing Kathy Forte who will speak on Driving Innovation With MySQL 8.0 and Docker.

Pycaribbean is a new show hoping to foster a growing developer community into the area.  And i will be showing how to use the MySQL Document Store with Python.All opinions …