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 …

“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 …

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 …

The TPCC workload "mystery" exposed in the following post was already clarified the last year, and I've presented explanations about the observed problem during PerconaLIVE-2019. But slides are slides, while article is article ;-)) So, I decided to take a time to write a few lines more about, to keep this post as a reference for further TPCC investigations..

The "mystery" is related to observed scalability issues on MySQL 8.0 under the given TPCC workload -- just that on the old aged DBT-2 workload (TPCC variation) I was getting much higher TPS when running on 2 CPU Sockets, comparing to1 CPU Socket, which is was not at all the case for Sysbench-TPCC.

Read more... (8 min remaining to read)

In this blog post, we will briefly explore the OS tool strace. It is not widely used due to its performance impacts, and we don’t recommend using it in production. Still, it is amazing at helping you understand some things that happen in MySQL, where the OS is involved, and as a last case resource for troubleshooting.

The strace tool intercepts and records any system calls (a.k.a.  syscalls) performed and any signals received by a traced process. It is excellent for complex troubleshooting, but beware, as it has a high-performance impact for the traced process.

We start our exploration with a simple question: what are the files opened in the OS when you issue FLUSH LOGS in MySQL? We could look at the documentation, but we decided to find out using strace.

For that, we started a MySQL lab instance and …

While there is no magic bullet for MySQL tuning, there are a few areas that can be focused on upfront that can dramatically improve the performance of your MySQL installation. While much information has been published on this topic over the years, I wanted to break down some of the more critical settings that anyone can implement with no guesswork required.

Depending on the version of MySQL you are running, some of the default values used in this post may differ from your install, but the premise is still largely the same.

Initial MySQL performance tuning can be broken down to the following categories:

• Tuning for your hardware
• Tuning for best performance / best practices
• Tuning for your workload

Tuning MySQL for Your Hardware

Depending on the hardware you have installed MySQL on, some …

So you running MySQL with the validate_password plugin. What about existing users and their passwords? How do you check that those passwords are ok and aren’t bad like ‘123456’ or easily guessable like ‘Tuesday2020!’? and how do you fix them. … Continue reading →

This is tutorial to setup 2 innodb clusters and create Replication using REPLICATION FILTER to define replication channel across 2 InnoDB Cluster.

Background
InnoDB Cluster setup is commonly found within a Data Center (DC1) where the network is good and reliable.   Having another Datacenter for DR purpose, set up of another InnoDB Cluster with Replication is an example.   To allow MySQL Replication to be successfully created between 2 InnoDB Clusters, the following items must be considered carefully :

1. InnoDB Cluster when it is created, there is mysql_innodb_cluster_metadata schema which stores the state and information about the 'cluster'.  The 2 InnoDB Clusters in 2 DCs are different.  The state information within the 'mysql_innodb_cluster_metadata' is different per each of the InnoDB Cluster.   MySQL Replication may bring the data update to metadata from DC1 …