The latest from the MySQL community

Today, yet another blog post about improvements in MySQL 8.0 related to Performance_Schema. Before MySQL 8.0 it was not always easy to get an example of the queries you could find in Performance_Schema when looking for statements summaries. You had to link several tables (even from sys) to achieve this goal as I explained it in this post.

Now in MySQL 8.0, we have changed the table events_statements_summary_by_digest. This table now contains 6 extra columns:

• QUANTILE_95 : stores the 95th percentile of the statement latency, in …

With MySQL 8.0, the error logging subsystem has been redesigned to use the new component architecture.

Thanks to this new redesign, now the log events can be filtered, the output can be sent to multiple destinations (different formats like JSON). All that is controlled by system variables.

This work gives the possibility for a log event to become the raw material for log processing by more modern and automated systems like filebeat for beats, …

In MySQL 8.0, the engineers have added useful meta-data to the table SETUP_INSTRUMENT. This table lists the classes of instrumented objects for which events can be collected.

To the NAME, ENABLES and TIMED columns, were added PROPERTIES, VOLATILITY and DOCUMENTATION.

Let’s have a quick look at these new columns:

PROPERTIES can have the following values

• global_statistics: only global summaries are available for this instrument. Example: memory/performance_schema/metadata_locks that return the memory used for table performance_schema.metadata_locks
• …

MySQL 8.0’s new default authentication plugin means more secure connections for users connections but also for replication… but you need to be aware of it !

Yesterday Giuseppe – the datacharmer – Maxia, submitted a bug related to a strange behavior of MySQL’s replication.

He observed that after creating a new user for replication (using the new authentication method, caching_sha2_password), when he created the slave as usual, replication was not starting… Slave_IO_Running was constantly in “Connecting” and the Last_IO_Error was:

Last_IO_Error: error connecting to master 'repl2@127.0.0.1:24011' - …

With MySQL 8.0, engineers improved the visibility of data locking. Usually it was not that easy to deal with the output of SHOW ENGINE INNODB STATUS and mixing Information_Schema tables like INNODB_LOCKS and INNODB_LOCK_WAITS.

So in MySQL 8.0 we instrument data locks in Performance_Schema and we changed the SYS Schema view related to the InnoDB Locks Wait too.

This means that when a transaction A is locking row R, and transaction B is waiting on this very same row, B is effectively blocked by A. What we added is extra instrumentation to expose which data is locked (R), who owns the lock (A), and who is waiting for the data (B).

Testing

Let’s have a look at this simple scenario in MySQL 5.7.21 and 8.0.4 to compare:

2 clients starting a …

As you may already know, MySQL 8.0 is coming with SQL Roles . I’ve already quoted them before in some posts (here and here). Giuseppe Maxia is doing a great job testing and promoting them (thank you for that!). Of course he also made some remarks on …

Prior to MySQL 8.0, MySQL was missing a very well know feature in other RDBMS: optimizer histograms.

The Optimizer Team implemented this feature as more and more MySQL DBAs were requesting it.

Definition

But what are histograms ? For wikipedia, a histogram is an accurate representation of the distribution of numerical data. For RDBMS, a histogram is an approximation of the data distribution within a specific column.

So in MySQL, histograms help the optimizer to find the most efficient Query Plan.

Example

To illustrate how histograms  influence the optimizer, I will use data from dbt3.

My query is simple: …

Today the call for participation for Oracle Open World 2018 has started.

The 2018’s edition will take place in San Francisco as usual, from October 22nd to 25th.

As every year, MySQL will be present and if you want to participate, we encourage you to submit a session. We encourage the submission on the following topics:

• case studies / user stories of your use of MySQL
• lessons learned in running web scale MySQL
• Production DBA/DevOps perspectives into Architecture, Performance, Replication, InnoDB, Security, etc

The call for paper is open until March 22nd, don’t miss the chance to be part …

As for each previous version, to upgrade to MySQL 8.0 you must first read the upgrade section in the manual. It’s important to know that you should upgrade without skipping a major version, so upgrade from 5.6 to 8.0 is not supported neither recommended.

Please also note that upgrades from any MySQL 8.0 RC won’t work. We support upgrade process only from GA versions.

Then one of the most important section to be aware of, is the incompatibility changes brought with the new version.

Once you have read the upgrade section in the manual and the release note of the version you want to install, with MySQL 8.0 and its new MySQL Shell, we developed …

Join Percona Chief Evangelist Colin Charles as he covers happenings, gives pointers and provides musings on the open source database community.

I think the biggest news from Percona-land is that besides the tutorial grid, the schedule for day 1 and day 2 are live! Also notice the many “sub-themes”: a 101 track, using MySQL, MongoDB, cloud, MySQL database software, PostgreSQL, containers & automation, monitoring & ops, and misc. database stuff. Learn from 107 different experts (this number is likely to increase). So register now.

This coming week, Peter Zaitsev, Tom Basil, and I will be in New …