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 …

Today, let’s have a look at the TOP 10 new features in MySQL 8.0 that will improve DBA’s life.

To shrink the list to 10 only items wasn’t an easy task, but here is the top 10:

1. Temporary Tables Improvements
2. Persistent global variables
3. No more MyISAM System Tables
4. Reclaim UNDO space from large transactions
5. UTF8 performance
6. Removing Query Cache
7. Atomic DDLs
8. Faster & More Complete Performance Schema (Histograms, Indexes, …) and Information Schema
9. ROLES
10. REDO & UNDO logs encrypted if tablespace is encrypted

Temporary Tables Improvements

Since 5.7, all internal temporary tables are created in a unique shared tablespace called “ …

MySQL 8.0 RC2 has just been released with a lot of new features compared to MySQL 5.7. In this blog post, I will list the top 10 new features that should excite developers.

These functionalities are presented in descending order of preference of our MySQL Community.

TOP 10

1. MySQL Document Store
2. Default to utf8mb4
3. JSON enhancements
4. CTEs
5. Window Functions
6. Descending Indexes
7. Better Optimizer Cost Model
8. MySQL Server Components
9. Improvement in GIS
10. InnoDB NO WAIT & SKIP LOCKED

MySQL Document Store

This is the most expected and liked feature in MySQL 8.0 … and it’s …