Now that MySQL 8.0.3 RC1 is installed and that we saw how to verify the workload, it’s time to see if we can optimize some of the queries. As explained before, rewriting queries when using a product like WordPress is complicated but maybe we can do something for the indexes ?

So, do you remember how to check the query that was consuming most resources ? Let’s find it out again:

mysql> select t1.*, QUERY_SAMPLE_TEXT from statement_analysis as t1 
                join performance_schema.events_statements_summary_by_digest as t2 
                on t2.digest=t1.digest and t2.SCHEMA_NAME = t1.db where db = 'wp_lefred' …

Now that MySQL is upgraded to 8.0 RC1, let’s have a look on how we could check the workload and see if we can optimize something by adding indexes for example. The same technique can be used to find inefficient queries requiring rewriting.

The first thing we will check is some information regarding the usage of the user used for lefred.be’s website:

mysql> select * from sys.user_summary where user= 'wp_lefred'\G
*************************** 1. row ***************************
user: wp_lefred
statements: 65060
statement_latency: 2.71 m
statement_avg_latency: 2.50 ms
table_scans: 234
file_ios: 17151
file_io_latency: 54.83 s
current_connections: 0
total_connections: 2541
unique_hosts: 1
current_memory: 52.27 MiB
total_memory_allocated: 12.50 GiB

This was after the upgrade, so not much info yet but if I compare with the other sites hosted on the …

Yesterday I decided to upgrade the database server of lefred.be running the latest WordPress to MySQL 8.0.

The previous version was 5.7.19.

My dedicated server is still running on CentOS 6.x, and I had just to enable the MySQL Community 8.0 yum repository and upgrade the rpms:

# yum update mysql-community-server --enablerepo=mysql80-community

After the upgrade, I had to modify my.cnf to remove the lines related to the non regretted Query Cache:

#query_cache_type=0
 …

It was expected for a long time…. here is the fix for bug #199 !!

The bug #199 submitted by PeterZ has been fixed in 8.0. Thank you to Zhang Simon for his contribution that inspired us to implement the fix.

Let’s have a look at the test case described in the bug report, I will reproduce it on MySQL 5.7.19 & MySQL 8.0.3.

The initial steps are exactly the same on both versions:

mysql> create table a(id int unsigned not null primary key auto_increment);
Query OK, 0 rows affected (0.17 sec)

mysql> show create table a\G
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `id` int(10) unsigned …

In my previous article about roles I said that one of the problems with role usage is that roles need to be activated before they kick in. Let's recap briefly what the problem is:

## new session, as user `root`

mysql [localhost] {root} ((none)) > create role viewer;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {root} ((none)) > grant select on *.* to viewer;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {root} ((none)) > create user see_it_all identified by 'msandbox';
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {root} ((none)) > grant viewer to see_it_all;
Query OK, 0 rows affected (0.01 sec)

## NEW session, as user `see_it_all`

mysql [localhost] {see_it_all} ((none)) > use test
ERROR 1044 (42000): Access denied for user 'see_it_all'@'%' to database 'test'

mysql [localhost] {see_it_all} ((none)) > show grants\G …

OOW17 is just around the corner. And again this year the MySQL team is very proud to present you their work mostly on MySQL 8.0 but also on NDB and all Enterprise tools we have. This year, we will also focus on the place MySQL has in Oracle’s Cloud !

Of course MySQL’s Community is also present in the schedule with very interesting talks. The full schedule related to the MySQL sessions can be found here.

Let’s focus on the agenda for the event:

Sunday, October 1st Moscone South Room 160

• …

From September 25th to 27th 2017, the MySQL engineers and community team will be speaking at Percona Live Europe in Dublin.

MySQL is also part of the contributing sponsors.

For the conference, we tried to bring new faces to the Community. Usually, it’s always the same famous team leaders speaking at the conferences, this time, it’s not. A lot of key developers will present their own work. We have so much talented people in MySQL we want to present to our community.

Of course, once again we will focus our talks on MySQL 8.0. This is the list of sessions the MySQL Team will deliver:

Monday, Sep 25th

On Monday, I will deliver a tutorial with my friend …

Today is my last day in Asia (China and Taiwan), and between the sessions I had the time to code a small proof-of-concept for people that are asking how they could read their writes on all nodes and ensure the read consistency with their last write ?

The usual (and easier) answer to this particular question is to read on the same host you just wrote. But currently MySQL provides you all the elements to force a consistent read across all the nodes of a group.

Since MySQL 5.7.5, we introduced session_track_gtidsand in 5.7.6 we also introduced  GTIDs context to the OK packet (session tracker) (WL#6128 and WL#6972). So we can use this implementation in …

Recently I wrote a blog post on how to use ProxySQL with 8.0.2 dmr

This blog post was related to the change we implemented in the Group Replication’s monitoring (performance_schema tables) and how sys schema should be changed to modify the view used by ProxySQL according.

However, I used my labs machines that I was using for different purposes too and some changes I made hid a problem that avoid ProxySQL to work with the new defaults in MySQL 8.0.2. Thank you Andy for discovering this.

In fact the new collation default (utf8mb4_0900_ai_ci) is not yet supported by ProxySQL …

Some time ago I wrote this article explaining how to find which node was the Primary Master in a MySQL Group Replication cluster running in Single-Primary Mode.

In the latest release of Group Replication, MySQL 8.0.2 dmr, Jaideep improved the visibility of Group Replication extending the performance_schema tables (see his article).

Thanks to these improvements, it’s now very easy to find which host is acting a Primary-Master. This is the query you can use:

mysql-sql> SELECT MEMBER_HOST as `PRIMARY` 
           FROM performance_schema.replication_group_members 
           WHERE MEMBER_ROLE='PRIMARY';
+---------+
| PRIMARY |
+---------+
| mysql3  |
+---------+
1 row in set (0.00 sec)