Yves Trudeau and Francisco Bordenave, MySQL experts at Percona, recently published a three-part post: (1) Give Love to Your SSDs – Reduce innodb_io_capacity_max!; (2) InnoDB Flushing in Action for Percona Server for MySQL; (3) Tuning MySQL/InnoDB Flushing for a Write-Intensive Workload. It’s a fantastic read from start to finish, and it made me realize: dirty pages sound bad, but they are good. I suspect the opposite (“dirty pages are not good”) is a misconception due to an incomplete picture. Let’s complete the picture.

Back to part 1

Now that we had seen how to setup our environment with PXC8 is time to see how our setup will behave and what can be done in case of issues.

We will now analyse the following common situations:
- Put a node in maintenance or remove a node and insert it back
- node crash
- network failure
- proxysql node crash

The following tests are done using a java application connecting using straight JDBC connection and no connection pooling. I choose to use that more than a …

In real life, there are frequent cases where getting a running application to work correctly is strongly dependent on consistent write/read operations. This is no issue when using a single data node as a provider, but it becomes more concerning and challenging when adding additional nodes for high availability and/or read scaling.

In the MySQL dimension, I have already described it here in my blog Dirty Reads in High Availability Solution.

We go from the most loosely-coupled database clusters with primary-replica async replication, to the fully tightly-coupled database clusters with NDB Cluster (MySQL/Oracle).

Adding components like ProxySQL to the architecture can, from one side, help in improving high availability, and from the other, it can amplify and randomize the negative effect of a …

As proven again very recently by my colleague and friend Dimitri K, with the TPCC “Mystery”, sysbench made by a former colleague and friend Alexey K, is really the recommended framework to benchmark your system.

I use sysbench of course to benchmark MySQL but also my OS and hardware capabilities.

For those willing to benchmark MySQL 8.0 with sysbench using all supported authentication methods including of course the much more secure default one, caching_sha2_password, you need to compile sysbench using the MySQL libs.

And …

The Percona Kubernetes Operator for Percona XtraDB Cluster(PXC) comes with ProxySQL as part of the deal. And to be honest, the behavior of ProxySQL is pretty much the same as in a regular non-k8s deployment of it. So why bother to write a blog about it? Because what happens around ProxySQL in the context of the operator is actually interesting.

ProxySQL is deployed on its own POD (that can be scaled as well as the PXC Pods can). Each ProxySQL Pod has its own ProxySQL Container and a sidecar container. If you are curious, you can find out which node holds the pod by running

kubectl describe pod cluster1-proxysql-0 | grep Node:
Node: ip-192-168-37-111.ec2.internal/192.168.37.111

Login into and ask for the running containers. You will see something like this:

[root@ip-192-168-37-111 ~]# docker ps | grep -i proxysql …

MySQL Day Virtual Event

Join us on July 29th, 2020 (8AM - 1PM PST) for a virtual event about why and how to upgrade to MySQL 8.0.  Learn the key reasons you should upgrade to 8.0. Discover the best practices developed by our support team based on their experience working directly with customers.  Get tips and techniques from our community evangelists.  Find out why the University of California at Irvine chose to upgrade to 8.0, learn about their process, their experience, and the improvements to their application performance.

Register to the event and attend the sessions of your choice.  Sessions are running on the hour so you can easily plan your agenda around your session interest.  Each session will last approximately 30-40 minutes with a 15 minute Q&A, followed by 5-15 minute break in …

MySQL Day Virtual Event Join us on July 29th, 2020 (8AM - 1PM PST) for a virtual event about why and how to upgrade to MySQL 8.0. Learn the key reasons you should upgrade to 8.0. Discover the best practices developed by our support team based on their experience working directly with customers. Get ...

“Running MySQL on selected NUMA node(s)” looks pretty straightforward but unfortunately it isn’t. Recently, I was faced with a situation that demanded running MySQL on 2 (out of 4) NUMA nodes.

Naturally, the first thing I tried was to restrict CPU/Core set using numactl --physcpubind selecting only the said CPUs/cores from the said NUMA nodes. MySQL was configured to use innodb_numa_interleave=1 so I was expecting it to allocate memory from the said NUMA nodes only (as I restricted usage of CPU/core).

Suprise-1:

MySQL uses numa_all_nodes_ptr->maskp that means all the nodes are opted even though the CPU task-set is limited to 2 NUMA nodes.

Some lookout pointed me to these 2 issues from Daniel Black

• https://github.com/mysql/mysql-server/pull/104 (5.7)
  
• https://github.com/mysql/mysql-server/pull/138 (8.0)

Issue …

It’s been 20 years since MySQL Replication was introduced in MySQL 3.23.15 (Released in May 2000). Since then, virtually every MySQL Database deployment in production has been using Replication in order to achieve high availability, disaster recovery, read scale out and various other purposes.…

Tweet Share

One of the most common errors encountered in the MySQL world at large is the infamous Error 1040:

ERROR 1040 (00000): Too many connections

What this means in practical terms is that a MySQL instance has reached its maximum allowable limit for client connections.  Until connections are closed, no new connection will be accepted by the server.

I’d like to discuss some practical advice for preventing this situation, or if you find yourself in it, how to recover.

Accurately Tune the max_connections Parameter

This setting defines the maximum number of connections that a MySQL instance will accept.  Considerations on “why” you would want to even have a max number of connections are based on resources available to the server and application usage patterns.  Allowing uncontrolled connections can crash a server, which may be considered “worse” than preventing further …