In this blog post, I’ll review how a hung MySQL transaction can cause the InnoDB history length to grow and negatively affect MySQL performance.

Recently I was helping a customer discover why SELECT queries were running slower and slower until the server restarts (which got things back to normal). It took some time to get from that symptom to a final diagnosis. Please follow me on the journey of chasing this strange MySQL behavior!

Symptoms

Changes in the query response time can mean tons of things. We can check everything from the query plan to the disk performance. However, fixing it with a restart is less common. After looking at “show engine innodb status”, I noticed some strange lines:

Trx read view will not see trx with id >= 41271309593, sees < 41268384363
---TRANSACTION 41271309586, ACTIVE 766132 sec
2 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1 …

The MySQL Development team announced that the 8.0.1 development milestone release is available for download at dev.mysql.com. (8.0.1 adds features to 8.0.0). The source code is available on GitHub. This release makes several important changes in Unicode character set support. In particular, the default character set has changed from latin1 to utf8mb4. Check out all the changes by reading the

InnoDB Cluster was released as GA a few weeks ago. I remember the initial announcement of the product at OOW 2016, promising a seamless solution for replication and high availability with great ease of use. I was a bit disappointed to see that, at GA release time, the InnoDB Cluster is a patchwork of three separate products (Group Replication, MySQL Router, MySQL Shell) which the users have to collect and install separately.

Given this situation, I was very pleased when Matthew Lord published Docker-InnoDB-Cluster, an image for Docker that contains everything you need to get the system up and running. The …

In conversations about SSL/TLS people often say that they either don't need TLS because they trust their network or they say it is too slow to be used in production.

With TLS the client and server has to do additional work, so some overhead is expected. But the price of this overhead also gives you something in return: more secure communication and more authentication options (client certificates).

SSL and TLS have existed for quite a long time. First they were only used for online banking and during authentication on web sites. But slowly many websites went to full-on SSL/TLS. And with the introduction of Let's encrypt many small websites are now using SSL/TLS. And many non-HTTP protocols either add encryption or move to a HTTP based protocol.

So TLS performance is very important for day-to-day usage. Many people and companies have put a lot of effort …

The MySQL Community Awards initiative is an effort to acknowledge and thank individuals and corporations for their contributions to the MySQL ecosystem. It is a from-the-community, by-the-community, and for-the-community effort. The committee is composed of an independent group of community members of different orientation and opinion, themselves past winners or known contributors to the community.

The 2017 community awards were presented on April 27, 2017, during the keynotes at the Percona Live conference. The winners are:

MySQL Community Awards: Community Contributor of the year 2017

• René Cannaò
  René – for creating and maintaining the ProxySQL project actively.
• Shlomi Noach
  Shlomi has been a been a conference chairperson for years, ran these mysqlawards for a long time, has been …

Isolation levels are a rare subject in MySQL literature. The documentation provides a terse description and focuses mainly on locking issues, but does not discuss the semantics of each isolation level. This is not only a problem that affects MySQL documentation but also the SQL standard itself.

Both the lack of documentation and the absence of a deeper description of the expected behavior in the SQL standard make isolation levels a topic that is more assumed than known by database administrators and developers. In this blog post, I aim to help you understand how the default isolation level in MySQL works and show you some surprising facts about it.

But first let’s see how isolation levels are described in the standard: “The transaction isolation level of a SQL-transaction defines the degree to which the operations on SQL-data, or schemas in that SQL-transaction are affected by the effects of and can affect operations on …

With tons of new No-SQL database offerings everyday, developers & architects have a lot of options. Cassandra, Mongodb, Couchdb, Dynamodb & Firebase to name a few. Join 33,000 others and follow Sean Hull on twitter @hullsean. What’s more in the data warehouse space, you have Hadoop, which can churn through terabytes of data and get … Continue reading Will SQL just die already? →

OK, Y’all.  If you have been a DBA long enough you have run into situations where you’ve had to import DDL from another database instance and forgotten to remove AUTO_INCREMENT on a table definition.  Sometimes this means your auto_increment is way up in the millions and you have 10 rows in the table.  People worried about ID depletion on an integer column will do a “facepalm”.

Making matters worse, having perhaps done this on more than one table, sometimes it’s a real pain to detect and fix the issue of auto_increments being much higher than you want them to be across a big number of tables.  Sometimes the pain is so great that you might want to simply repeat your data importation.

This quick and dirty “southern fried” script will help you report and rectify that.  It detects …

Taking a logical backup of a member of a Group Replication Cluster is not something very easy.

Currently (5.7.17, 5.7.18 or 8.0.0) if you want to use mysqldump to take a logical backup of your dataset, you need to lock all the tables on the member you are taking the dump. Indeed, a single transaction can’t be used as savepoints are not compatible with Group Replication.

[root@mysql3 ~]# mysqldump -p  --single-transaction --all-databases --triggers \
                      --routines --events >dump.sql
Enter password:
mysqldump: Couldn't execute 'SAVEPOINT sp': The MySQL server is running with the 
--transaction-write-set-extraction!=OFF option so it cannot execute this 
statement (1290)

So we need to use:

[root@mysql3 ~]# mysqldump -p  --lock-all-tables --all-databases --triggers \
                      --routines --events >dump.sql
Enter password:

This can have a negative effect on the full Group’s …

It was a big April if you’re in the MySQL ecosystem, so am looking forward to other events that have different focus and a different base, so to speak. See you at:

• rootconf – May 11-12 2017 – Bangalore, India. My first Rootconf was last year, and it was a great event; I look forward to going there again this year, to talk about capacity planning for your databases. If you register with this link you get a 10% discount.
• Open Source Data Center Conference – May 16-18 2017 – Berlin, Germany. I’ve enjoyed my trips to OSDC in the last few years, and they’re on their last tickets now …