In this blog post, we will explore how network partitions impact group replication and the way it detects and responds to failures. In case you haven’t checked out my previous blog post about group replication recovery strategies, please have a look at them for some insight. Topology: [crayon-670436de44bdb443765296/] Scenario 1: One of the GR nodes […]
I have been scratching my head about MySQL startup for some time. There is much to say about this, and many other posts will probably follow. For now, it is enough to know that with many tables (millions) the startup of MySQL 8.0+ (including 8.0, 8.4 and 9.0) is suboptimal (to say the least). With very little changes, I was able to speed it up, from 2:39 to 1:09 (one minute and
Mysterious warning Recently, I was involved in an investigation whose goal was to find out the reason for a warning message like this: [crayon-670436de477b9458138817/] The message looks clear, isn’t it? Well, the problem was that this particular table had not been changed for years, and so no DDL (ALTER) query was involved here. Moreover, there […]
I thought I’d share some quick intro steps into how we can monitor the MySQL Router.
This can be useful if we’re observing intermittent outages, network packet drops or you’re just not sure if everythings fine in your MySQL InnoDB Cluster.
My scenario: The drupal servers are connecting and sometimes the users are getting connection errors. I don’0t see anything at MySQL server level of any instance nor cluster problem. Let’s review the Routers.
On all MySQL Router servers, double check the config file for the log location and also the log level. At /etc/mysqlrouter/mysqlrouter.conf (default rpm install location):
[DEFAULT]
name=myrouter
user=mysqlrouter
..
..
logging_folder=/routerlog/log
..
[logger]
level=DEBUG
#level=INFO
I’ve changed my logger level to DEBUG which will give you a lot more info about connections and counters so you can see what’s happening …
[Read more]In MySQL 8.0 we introduced a totally new design for InnoDB REDO Log management. The main difference was about implementing a lock-free solution for user threads, and use dedicated REDO threads for all background IO write work.
for more details, see an excellent and very detailed article by Pawel : https://dev.mysql.com/blog-archive/mysql-8-0-new-lock-free-scalable-wal-design/
However, over a time we also added an option to let users to switch REDO threads=OFF to enforce REDO log processing efficiency in some particular cases. Unfortunately this feature created a lot of confusions for MySQL users, and many ones interpreted this in different ways, providing different and sometimes opposite advices, etc..
My main advice will be always : test each feature yourself and within your …
[Read more]TL;DR: Make sure to run “SET persist_only disabled_storage_engines=’MyISAM’, persist sql_generate_invisible_primary_key=ON;” on all instances and restart each one in your MySQL InnoDB Cluster.
Ok, what does “safe from naughtiness” mean?:
– Anyone creating tables that aren’t InnoDB, as this doesn’t make
sense, after all, it is an “InnoDB” cluster.
– Making sure all tables have a Primary Key (invisible or
not).
– Making sure that my (invisible) primary keys are visible to the
cluster as it will rightfully complain if they aren’t!
This basically means that once you’ve got it all up and running you won’t run into those horrible situations whereby someone, somewhere, creates a MyISAM table that didn’t have a Primary Key and thus leave you with a broken cluster.
Eg.
MySQL rtnode-01:3306 ssl JS > vlc.status()
{
"clusterName": "VLC",
"clusterRole": "PRIMARY", …
[Read more]
Now this is far from being any observability manual for your InnoDB Cluster and let alone go into everything MySQL Shell API Admin, or the collectDiagnostics utility. You can also use the default javascript commands that we all know and love via dba.getCluster() and so on, but here’s a different take.
I just want to share something I’ve been playing with to pull out some key info from mycluster. Hope it helps someone else out there.
General setup:
select cluster_id, cluster_name, description, cluster_type, primary_mode, clusterset_id
from mysql_innodb_cluster_metadata.clusters;
Members of our cluster:
select * from …
[Read more]
Yesterday, MySQL 8.4, the very first LTS version of MySQL was released.
A lot of deprecations have finally been removed, and several InnoDB variable default values have been modified to match current workloads and hardware specifications.
The default value of 20 InnoDB variables has been modified!
Let’s have a look at those variables and explain the reason for such modification:
innodb_buffer_pool_in_core_file
Previous Value: | ON |
New Value (8.4 LTS): |
OFF if MADV_DONTDUMP is supported else ON |
MADV_DONTDUMP is a macro supported in Linux 3.4 and later, (“sys/mman.h” header file is present and contains the symbol MADV_DONTDUMP, a …
[Read more]In MySQL 8.0.12, we introduced a new algorithm for DDLs that won’t block the table when changing its definition. The first instant operation was adding a column at the end of a table, this was a contribution from Tencent Games.
Then in MySQL 8.0.29 we added the possibility to add (or remove) a column anywhere in the table.
For more information, please check these articles from Mayank Prasad : [1], [2]
In this article, I want to focus on some dangers …
[Read more]Percona XtraBackup (PXB) version 8.0.28 supports taking backups for the encrypted tables in your MySQL database using the AWS Key Management Service. For setting up data-at-rest encryption using AWS key management service, please see Configuring Keyring for Encryption Using AWS Key Management Service in Percona Server for MySQL.In this blog post, we will discuss how […]