Category: Troubleshooting

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 …

Miscellaneous, Troubleshooting

Handy Stored Procedure for Regular DBA Tasks

As a stored procedures fan, I use MySQL stored procedures to get some of my DBA tasks accomplished. To make it simple, I have a template stored procedure that can be customized for several purposes. The template syntax contains cursor, continue handler, loop, if condition and prepared statement. Thought it may be useful for others – …

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. …

Performance, Troubleshooting

Improved JOIN Order by Taking Condition Filter Effect into Account

One of the major challenges of query optimizers is to correctly estimate how many rows qualify from each table of a join. If the estimates are wrong, the optimizer may choose a non-optimal join order. Before MySQL 5.7, the estimated number of rows from a table only took into account the conditions from the WHERE …