Category: Troubleshooting

Troubleshooting

Bitten by MariaDB 10.2 Incompatibile Change

Since MariaDB first claimed to be a “drop-in replacement for MySQL”, I knew that claim wouldn’t age well.  It hasn’t – the list of incompatibilities between various MySQL and MariaDB verions grows larger and larger over time, now requiring a dedicated page to compare two specific recent releases.  Despite that, I largely operated under the …

Troubleshooting

MariaDB version upgrade to 10.1.31 breaks Galera cluster

Recently we faced an issue where the config management software had automatically upgraded the mariadb-server-10.1 package to the latest 10.1.31 version. This upgrade broke the galera cluster setup for this installation. I’ve started to recreate this issue in my local lab setup and I managed to reproduce this problem. I have created a 3 node …

Miscellaneous, Troubleshooting

Beware of large MySQL max_sort_length parameter

Today we had a very interesting phenomena at a customer. He complained that MySQL always get some errors of the following type: [ERROR] mysqld: Sort aborted: Error writing file ‘/tmp/MYGbBrpA’ (Errcode: 28 – No space left on device) After a first investigation we found that df -h /tmp shows from time to time a full …

Miscellaneous, Troubleshooting

Why is varchar(255) not varchar(255)?

Recently I was working on a clients question and stumbled over an issue with replication and mixed character sets. The client asked, wether it is possible to replicate data to a table on a MySQL slave, where one column had a different character set, than the column in the same table on the master. I …

Software, Troubleshooting

Compare Current and Past Time Series Graphs in Percona Monitoring and Management (PMM)

In this short blog post, I will show you how you can compare current and past time series in Percona Monitoring and Management (PMM). Recently, a support customer shared a graph with us that compared query throughput between today and yesterday as a confirmation that optimizing their server variables improved performance. Do you want to …

Troubleshooting

MySQL 8 and Common Table Expressions – An Introduction

MySQL 8 will have Common Table Expressions also known as CTEs.  I have had people asking me when  MySQL would have CTEs for years and I am glad to report they are ready for testing. What Does a CTE look like? The keyword WITH is the big hint that the statement is a CTE.  For now …

Troubleshooting

MysQL 8.0 Places Where MySQL Configuration Variables Can be Initialized

As you may already know, there are many different places where a MySQL configuration variables can be initialized. In MySQL 8.0, we added in performance_schema a table allowing you to easily find where a variable was defined. Let’s check this in action with max_connections for example. I started mysqld and now I check the value …

Troubleshooting

MySQL 8.0, One Key Feature Is The New Data Dictionary

With MySQL 8.0, one key feature is the new Data Dictionary. The system tables that were previously in MyISAM are now replaced by new protected ones in the DD. My friend Giuseppe already explained how you could see those tables using sandbox  and he also warned you that you should not mess up with them …

Performance, Troubleshooting

Improving the Stability of MySQL Single-Threaded Benchmarks

I have for some years been running the queries of the DBT-3 benchmark, both to verify the effect of new query optimization features, and to detect any performance regressions that may have been introduced. However, I have had issues with getting stable results. While repeated runs of a query is very stable, I can get …

Performance, Troubleshooting

Improved Query Performance by Using Derived Table instead of IN-subquery

MySQL 5.6 introduced semi-join transformation for IN-subqueries. This opened up for several new ways to execute such subqueries; one was no longer tied to executing the subquery once for every row of the outer query. This dramatically improved the performance of many such queries. However, semi-join transformation does not apply to all types of queries. …