Some of us Perconians are at OpenStack summit this week in Atlanta. Matt Griffin, our director of product management, tweeted about the turbo-hipster CI talk about their experience of ALTER TABLEs running faster on Percona Server. Oracle’s Morgan Tocker then tweeted in response, asking why this was the case. I decided that the simplest way to answer that was here in this post.

The reason for this is the …

So I figured it was about time I looked at MySQL 5.7. This is a high level overview, but I was looking over the MySQL 5.7 in a nutshell document:

• http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html

So I am starting with a fresh Fedora 20 (Xfce) install.
Overall, I will review a few items that I found curious and interesting with MySQL 5.7. The nutshell has a lot of information so well worth a review.

I downloaded the MySQL-5.7.4-m14-1.linux_glibc2.5.x86_64.rpm-bundle.tar

The install was planned on doing the following
# tar -vxf …

In InnoDB bugs found during research on InnoDB data storage I mentioned MySQL Bug #67963 which was then titled “InnoDB wastes 62 out of every 16384 pages”. I said:

InnoDB needs to occasionally allocate some internal bookkeeping pages; two for every 256 MiB of data. In order to do so, it allocates an extent (64 pages), allocates the two pages it needed, and then adds the remainder of the extent (62 free pages) to a list of extents to be used for single page allocations called FREE_FRAG. Almost nothing allocates pages from that list, so these pages go to waste.

This is fairly subtle, wasting only 0.37% of disk space in any large InnoDB table, but nonetheless interesting and quite fixable.

Wasting 0.37% of disk space was …

The post Percona Live MySQL Conference and Expo appeared first on Pythian.

In years past, MySQL was a bit of a black box when it came to understanding what was happening and why. In MySQL 5.6 and 5.7, we’ve added many new features that provide much needed transparency and insight into the inner workings of MySQL. The single biggest feature was the new Performance Schema, but some other examples are:

1. The ability to see what query generated a row based binary log event.
2. The ability to see a tremendous amount of data points for InnoDB.
3. The ability to see an extended query …

We just released, as an open source release, our new percona-agent (https://github.com/percona/percona-agent), the agent to work with Percona Cloud Tools. This agent is written in Go.

I will give a webinar titled “Monitoring All MySQL Metrics with Percona Cloud Tools” on June 25 that will cover the new features in percona-agent and Percona Cloud Tools, where I will also explain how it works. You are welcome to register now and join me. …

Improving security has been a high priority for MySQL 5.7. To give you an idea of some of the changes to date, I recommend reading Todd Farmer's blog: Redefining --ssl option, Password expiration policy, AES256 encryption.

In MySQL 5.7, we are also proposing to remove the 'old' format for saving passwords. To provide some historical context:

• The old password format is a 16-byte hash and considered to be cryptographically weak. Code appears online (I won't link to it) which allows the hash to be brute-forced in …

Recently, I was asked about an interesting case in ORDER BY … LIMIT optimization. Consider a table

create table tbl (
  …
  KEY key1(col1, col2),
  PRIMARY KEY (pk)
) engine=InnoDB;

Consider queries like:

  select * from tbl where col1=’foo’ and col2=123 order by pk limit 1;
  select * from tbl where col1=’bar’ and col2=123 order by pk limit 1;

These run nearly instantly. But, if one combines these two queries with col1='foo' and col1='bar' into one query with col1 IN ('foo','bar'):

  select * from tbl where col1 IN (’foo’,'bar’) and col2=123 order by pk limit 1;

then the query is be orders of magnitude slower than both of the queries with col1=const.

The first thing to note when doing investigation is to note that the table uses InnoDB engine, which has …

Kudos to the SkySQL Engineering team, who released the 3rd alpha version of MaxScale, a database proxy for MySQL, MariaDB and Percona servers, labeled MaxScale 0.6.
This version comes with two important additions:

• A feature-complete read/write splitting module, i.e. read and write operations are now balanced in a smarter way to master or slave servers.

• New client-based features, such as a version string that provides compatibility with the major MariaDB and MySQL connectors, the ability to connect through the root user and the use of the unix socket when MaxScale is co-located with a client application on the same server.

Binaries and source code are “hidden” here: http://downloads.skysql.com/files/SkySQL/MaxScale.
The project is on GitHub: https://github.com/skysql/MaxScale/
Other helpful links are:

• Bugs …

The combination of max_allowed_packet variable and replication in MySQL is a common source of headaches. In a nutshell, max_allowed_packet is the maximum size of a MySQL network protocol packet that the server can create or read. It has a default value of 1MB (<= 5.6.5) or 4MB (>= 5.6.6) and a maximum size of 1GB. This adds some constraints in our replication environment:

• The master server shouldn’t write events to the binary log larger than max_allowed_packet

• All the slaves in the replication chain should have the same max_allowed_packet as the master server

Sometimes, even following those two basic rules we can have problems.

For example, there are situations (also called bugs) where the master writes more data than the max_allowed_packet …