Home

Automatic update of InnoDB Persistent Statistics Never Triggers

Table Of Contents BackgroundExampleNo RestartWith RestartWorkaroundMonitoringSince MySQL 5.6 InnoDB has supported persistent index statistics. This means that when you restart MySQL, InnoDB does not have to recalculate the statistics for the tables, rather it can read the statistics from its persistent storage. This has several advantages over the transient statistics, but as it turns out, there is also a catch: MySQL may under some circumstances never get around to update the index statistics. This particularly affects instances that are restarted frequently and tables with a large number of rows. Background Before I dive into why there are scenarios where persistent statistics are never updated, it is necessary to recap how persistent statistics work. Every time the statistics are updated, the

Read More »

Locking SELECT with CREATE TABLE, INSERT INTO, and User Variables

Table Of Contents BackgroundExampleNo RestartWith RestartWorkaroundMonitoringCREATE TABLE .. SELECT / INSERT INTO … SELECTUser VariablesLocking is an important concept in databases. They help regulate access to the data, so your SELECT queries return consistent results and DML and DDL statements leave the data and schema in a consistent state. For the data, there are four different transaction isolation levels that influence which locks are taken. The most two commonly used isolation levels are REPEATABLE READ (the default in InnoDB) and READ COMMITTED (the default in some other databases). Both of those are said to provide non-locking reads, but there is a little more to it than that. One case where reads are always locking is when you explicitly requests locks

Read More »

Updates to Code for MySQL Concurrency – v1.1 and v1.2

Table Of Contents BackgroundExampleNo RestartWith RestartWorkaroundMonitoringCREATE TABLE .. SELECT / INSERT INTO … SELECTUser VariablesWhen I wrote the book MySQL Concurrency I included a Python module for MySQL Shell that would help reproducing the examples in the book. Since things change, it has been necessary to update the code. In this blog I will explain what the changes are which also give me a chance to say thanks to those that have submitted pull requests. Version v1.1 was mostly about correcting the directory structure of the repository which was not as it was meant to be – and different from the instructions in the book. Additionally some files with code listings and images were missing. Finally, I added a check

Read More »

MySQL Query Attributes

Table Of Contents BackgroundExampleNo RestartWith RestartWorkaroundMonitoringCREATE TABLE .. SELECT / INSERT INTO … SELECTUser VariablesWhy Query Attributes?Adding Attributes in a CommentThe Query Attributes ComponentInstallationUsing the ComponentLimitationsPersisting AttributesConclusionQuery attributes have in a long time been used to add metadata to queries to provide additional context such as where in the application the query was generated. The traditional way to do this is to add a comment at the start of the query with the attributes. Since the MySQL parser ignores the comment, it does not change the semantics of the query, but various tools can extract the comments. This post looks into the reasons for using query attributes, how it is done using a comment, and the new component for MySQL

Read More »

New Book: MySQL Concurrency

Table Of Contents BackgroundExampleNo RestartWith RestartWorkaroundMonitoringCREATE TABLE .. SELECT / INSERT INTO … SELECTUser VariablesWhy Query Attributes?Adding Attributes in a CommentThe Query Attributes ComponentInstallationUsing the ComponentLimitationsPersisting AttributesConclusionAcknowledgementsBook StructureMySQL Shell Module for Reproducing Test CasesIn March 2020, my book MySQL 8 Query Performance Tuning (Apress) was released. That book with its more than 900 pages in 27 chapters covered a wide range of topics from executing benchmarks with sysbench over indexes and the optimizer to query rewriting, replication, and caching. Some time later my editor Jonathan Gennick suggested to take a limited set of the topics and go into more detail. We settled on locks and transactions which together have become MySQL Concurrency. If you have already read MySQL 8 Query

Read More »

The MySQL X DevApi: Working with NULL Values

Table Of Contents BackgroundExampleNo RestartWith RestartWorkaroundMonitoringCREATE TABLE .. SELECT / INSERT INTO … SELECTUser VariablesWhy Query Attributes?Adding Attributes in a CommentThe Query Attributes ComponentInstallationUsing the ComponentLimitationsPersisting AttributesConclusionAcknowledgementsBook StructureMySQL Shell Module for Reproducing Test CasesThe Short StoryThe Example ProgramInserting DataFind Documents by Binding NoneFind Documents Using IS NULLThe John Doe DocumentFind Documents Using a JSON Document with nullFull Program OutputThere was an excellent question from Francis on my MySQL X DevAPI tutorial on how to work with NULL values: Is it possible to store an attribute with the null value, and how to find it (xxx is null ? It may sound like a trivial question, but the answer is not all that simple, so instead of just replying directly to

Read More »

I Am Speaking at Oracle Developer Live – MySQL 2020

It is a year of online conferences and next week, the time has come to Oracle Developer Live for MySQL. It consists of a keynote at the start of the day and two tracks at the rest of the day. The event takes place in two rounds: On 13 October the event takes place during American business hours, and on 15 October it happens during European, African, Middle Eastern, and Asian-Pacific business hours/evening. I am honoured to have been given the opportunity to speak about MySQL performance tuning. On 13 October, I will speak at 12:05pm Pacific Time and on 15 October at 10:05 Central European Time/1:35pm Indian time/7:05pm Australian Eastern Daylight Time. A teaser: you will learn about the

Read More »

Happy Birthday MySQL

Table Of Contents BackgroundExampleNo RestartWith RestartWorkaroundMonitoringCREATE TABLE .. SELECT / INSERT INTO … SELECTUser VariablesWhy Query Attributes?Adding Attributes in a CommentThe Query Attributes ComponentInstallationUsing the ComponentLimitationsPersisting AttributesConclusionAcknowledgementsBook StructureMySQL Shell Module for Reproducing Test CasesThe Short StoryThe Example ProgramInserting DataFind Documents by Binding NoneFind Documents Using IS NULLThe John Doe DocumentFind Documents Using a JSON Document with nullFull Program OutputToday 23 May 2020, it is 25 years since the first release of MySQL. So, I would like to take the opportunity to wish MySQL – and Sakila – a happy birthday. My own MySQL journey started in 2006 when I at a job interview was told that if I got the job, I would need to learn MySQL before starting. Since

Read More »

MySQL Compressed Binary Logs

Table Of Contents BackgroundExampleNo RestartWith RestartWorkaroundMonitoringCREATE TABLE .. SELECT / INSERT INTO … SELECTUser VariablesWhy Query Attributes?Adding Attributes in a CommentThe Query Attributes ComponentInstallationUsing the ComponentLimitationsPersisting AttributesConclusionAcknowledgementsBook StructureMySQL Shell Module for Reproducing Test CasesThe Short StoryThe Example ProgramInserting DataFind Documents by Binding NoneFind Documents Using IS NULLThe John Doe DocumentFind Documents Using a JSON Document with nullFull Program OutputConfigurationLimitationsMonitoringExamples – WorkloadsExamples – Compression LevelConclusionOn a busy server, the binary logs can end up being one of the largest contributors to amount of disk space used. That means higher I/O, larger backups (you are backing up your binary logs, right?), potentially more network traffic when replicas fetch the logs, and so on. In general, binary logs compress well, so it has

Read More »

Apress Blog: MySQL Performance Tuning Best Practices

Table Of Contents BackgroundExampleNo RestartWith RestartWorkaroundMonitoringCREATE TABLE .. SELECT / INSERT INTO … SELECTUser VariablesWhy Query Attributes?Adding Attributes in a CommentThe Query Attributes ComponentInstallationUsing the ComponentLimitationsPersisting AttributesConclusionAcknowledgementsBook StructureMySQL Shell Module for Reproducing Test CasesThe Short StoryThe Example ProgramInserting DataFind Documents by Binding NoneFind Documents Using IS NULLThe John Doe DocumentFind Documents Using a JSON Document with nullFull Program OutputConfigurationLimitationsMonitoringExamples – WorkloadsExamples – Compression LevelConclusionTo celebrate the publishing of my new book MySQL 8 Query Performance Tuning, the Apress team invited me (thanks Jonathan and Liz) to write a post for the Apress blog. I decided to write about my top six best practices: Be wary of best practices Monitor Work methodically Consider the full stack Make small, incremental changes Understand

Read More »