One of the features I missed in my blog post on problematic MySQL features back in July is XA transactions. Probably I was too much in a hurry, as this feature is known to be somewhat buggy, limited and not widely used outside of Java applications. My first related feature request, Bug #40445 - "Provide C functions library implementing X/OPEN XA interface for Bea Tuxedo", was created almost 10 years ago, based on the issue from one of MySQL/Sun customers of that time. I remember some …[Read more]
10 Older Entries »
But if you want to setup a very quick Master-Slave environment from scratch for a quick test (you can always use dbdeployer), here are some commands to make it right the first time
You need to have MySQL 8.0 installed and running on both servers
and with the same initial data (a fresh install for example).
Here we use
mysql2. We will
also use GTID as it’s much more convenient.
mysql1> SET PERSIST server_id=1; mysql1> SET …[Read more]
We saw in the previous post that MySQL likes memory. We also saw how to perform operating system checks and some configuration changes for Swap and NUMA.
Today, we will check what MySQL server can tell us about its memory usage.
Introduced in MySQL 5.7 and enabled by default in MySQL 8.0, the
Performance_Schema‘s Memory instrumentation allows
us to have a better overview of what MySQL is allocating and why.
Let’s check on our MySQL server using
Pay attention that there is a bug
related to how InnoDB Buffer Pool statistics are accounted in
Performance_Schema. This is fixed in 8.0.13. …
While writing about problematic Oracle MySQL features previously I
concentrated mostly on InnoDB problems that I have to fight with
really often and deliberately skipped replication from even the
preliminary list of features to study in details for that blog
post. First of all, I mostly work with MariaDB users now, and
implementation of many replication features in MariaDB is notably
different already (and has its own list of known problems). But this
happened also because (asynchronous) replication plays a key role
in most MySQL environments and deserves a detailed study in a
As you may know, sometimes MySQL can be memory-hungry. Of course having data in memory is always better than disk… RAM is still much faster than any SSD disk.
This is the reason why we recommended to have the working set as much as possible in memory (I assume you are using InnoDB of course).
Also this why you don’t want to use Swap for MySQL, but don’t
forget that a slow MySQL is always better than no MySQL at all,
so don’t forget to setup a Swap partition but try to avoid using
it. In fact, I saw many people just removing the Swap partition…
and then OOM Killer did its job… and
mysqld is often
its first victim.
MySQL allocates buffers and caches to improve performance of database operations. That process is explained in details in the manual.
In this article series, I will provide you some …[Read more]
made this amazing tool that the majority of MySQL DBAs are using,
but if you use sysbench provided with your GNU/Linux
distribution or its repository on packagecloud.io you won’t be able to use it
with the new default authentication plugin in MySQL 8.0
This is because most of the sysbench binaries are compiled with the MySQL 5.7 client library or MariaDB ones. There is an issue on github where Alexey explains this.
So if you want to use sysbench with MySQL 8.0 …[Read more]
Many of you already know how to retrieve the size of your dataset, schemas and tables in MySQL.
To summarize, below are the different queries you can run:
I the past I was using something like this :
But now with
sys schema being installed by default,
I encourage you to use some of the formatting functions provided
with it. The query to calculate the dataset is now:
SELECT sys.format_bytes(sum(data_length)) DATA, sys.format_bytes(sum(index_length)) INDEXES, sys.format_bytes(sum(data_length + index_length)) 'TOTAL SIZE' FROM information_schema.TABLES ORDER BY data_length + index_length;[Read more]
So today, I will show you how you can see a list of users that didn’t connect to MySQL since we restarted it (since last reboot).
SELECT DISTINCT mu.user FROM mysql.user mu
LEFT JOIN performance_schema.users psu
ON mu.user = psu.user
WHERE psu.user IS NULL
AND mu.user NOT IN ('mysql.infoschema', 'mysql.session', 'mysql.sys')
ORDER BY mu.user;
mysql> SELECT DISTINCT mu.user FROM mysql.user mu -> LEFT JOIN performance_schema.users psu -> ON mu.user = psu.user -> AND mu.user NOT IN ('mysql.infoschema', 'mysql.session', 'mysql.sys') …[Read more]
In April, I already posted an article on how to upgrade safely your MySQL InnoDB Cluster, let’s review this procedure now that MySQL 8.0.12 is out.
To upgrade all the members of a MySQL InnoDB Cluster (Group), you need to keep in mind the following points:
- upgrade all the nodes one by one
- always end by the Primary Master in case of Single Primary Mode
- after upgrading the binaries don’t forget to start MySQL
without starting Group Replication
- to run
Let’s see this in action on the video below:
As you could see, this is quick and easy.
We received feedback about how a member should act when leaving the group. And the majority of users wanted that when a node drops out of the group, it should kill all connections and shutdown. I totally agree with that behavior and it’s now the default in MySQL 8.0.12.
This new feature is explained in WL#11568.
Before this change, the server goes into super read only mode when dropping out of the group and allows users connected to this server or new connections (if you don’t use the router) to read old data.
Let’s check this out in the following video:
So now in MySQL 8.0.12, there is a mnew option called …[Read more]
10 Older Entries »