The latest from the MySQL community

This is a three-part blog series that focuses on dealing with an unexpected high traffic event as it is happening. Part one can be found here, and part two can be found here.   13. Configure MySQL Server Properly

Complexity:Medium
Potential Impact: High 

A poorly configured MySQL Server can cause severe issues, especially under high load during a traffic spike, yet getting the basics right is not that hard. While MySQL Server has more than 400 variables you can tune, you rarely need to change more than 10-20 of them to get 95% of the possible performance for your workload.

…

By and large this would be a topic of interest for most of us including me when I started to explore this space. Before we dwell into the numbers let’s first understand some basic differences between 2 architectures. Beyond being CISC and RISC let’s look at the important differences from MySQL perspective.

• Strong vs Weak memory model (weak memory model needs proper memory barrier while writing lock-free code).
• Underlying hardware specific specialized instructions. For example: both now support crc32c hardware instructions but being low-level they are different ways to invoke them. For more differences checkout for x86-SSE/ARM-ACLE.
• Cache Line differences. Most of the ARM processors tend to use bigger cache lines (128 bytes for all caches or a mix of 64/128 bytes).
• Other sys-call level differences like: absence of PAUSE instructions with ARM and substitute instruction with very low latency …

While testing in an orchestrator lab I saw that none of my Orchestrator on-raft nodes were coming online after a reboot.

This is the status report from SystemD.

$ sudo systemctl status orchestrator
* orchestrator.service - orchestrator: MySQL replication management and visualization
   Loaded: loaded (/etc/systemd/system/orchestrator.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Fri 2020-04-03 09:30:05 UTC; 30s ago
     Docs: https://github.com/github/orchestrator
 Main PID: 957 (code=exited, status=1/FAILURE)

Apr 03 09:30:05 orchestrator-1 systemd[1]: Started orchestrator: MySQL replication management and visualization.
Apr 03 09:30:05 orchestrator-1 orchestrator[957]: 2020-04-03 09:30:05 ERROR dial tcp 127.0.0.1:3306: connect: connection refused
Apr 03 09:30:05 orchestrator-1 orchestrator[957]: 2020-04-03 09:30:05 FATAL dial tcp 127.0.0.1:3306: connect: connection refused
Apr 03 09:30:05 orchestrator-1 systemd[1]: …

MySQL has the inbuilt query rewritten plugin for rewrite the SQL statements which received by the server before execute them .

• Before MySQL 8.0.12 , the query rewritten plugin only supports for SELECT’s
• From MySQL 8.0.12 , the query rewritten plugin will supports for INSERT , REPLACE, UPDATE, DELETE , SELECT .

One of our customer had the performance issue with the DELETE statements for particular table . The issue was happened after the new code deployment . In this blog, I am going to explain the implementation of the MySQL query rewrite plugin and how we overcome the issue using the MySQL query rewritten plugin .

MySQL Query rewritten plugin Implementation :

MySQL provides two SQL files for the installation / Uninstallation purpose . The files will be located under the directory “lc_messages_dir”

• The …

In this blog post, we talk about the basic function and features of the Tungsten Connector.

The Tungsten Connector is an intelligent MySQL proxy that provides key high-availability and read-scaling features. This includes the ability to route MySQL queries by inspecting them in-flight.

Connector Basics Understanding the Two Key Features

The most important function of the Connector is failover handling. When the cluster detects a failed master because the MySQL server port is no longer reachable, the Connectors are signaled and traffic is re-routed to the newly-elected Master node.

Next is the ability to route MySQL read-only queries to a slave (or the master, if no slave is …

In this blog, we will test a new security feature added in ProxySQL 2.0.9. Since a time ago, we have had the ability to block queries using mysql_query_rules table matching a group of queries using reg exp like a blacklist. Check out a previous blog for how to config “ProxySQL Firewalling” using the mysql_query_rules table.

You can improve a whitelist using the mysql_query_rules table, but it is difficult if you have hundreds of queries.

ProxySQL 2.0.9 introduces two new tables for the firewall whitelist algorithm:

Admin> SELECT name AS tables FROM main.sqlite_master WHERE type='table' AND name IN ('mysql_firewall_whitelist_rules','mysql_firewall_whitelist_users') ORDER BY name;
+--------------------------------+
| tables                         | …

In my Webinar on Using Percona Monitoring and Management (PMM) for MySQL Troubleshooting, I showed how to use direct queries to ClickHouse for advanced query analysis tasks. In the followup Webinar Q&A, I promised to describe it in more detail and share some queries, so here it goes.

PMM uses ClickHouse to store query performance data which gives us great performance and a very high compression ratio. ClickHouse stores data in column-store format so it handles denormalized data very well. As a result, all query performance data is stored in one simple “metrics” table:

Update 2020-05-05: I stopped providing consulting hours.  They were not very popular, but it was worth trying.

We are all impacted by COVID-19.  On my side, this virus disrupted my travel, professional and personal plans for the upcoming months.  To overcome these disruptions, and following the idea of someone on MySQL Community Slack, I am experimenting with giving MySQL advice

MySQL Audit Logging — How to Avoid Data Overload

In the last few months, our solution engineers have had many discussions with security and operation teams about how to implement MySQL auditing. In some cases, these teams have been asked to implement new database auditing in response to a security breach or to meet a regulatory requirement (PCI, HIPPA, GDPR or CCPA).

More often, however, these companies are switching audit strategies because their existing method has led to data overload. They typically start by monitoring everything, sometimes even using the general query log. They quickly learn that even with moderate database activity, the amount of generated data soon becomes overwhelming.

Below are a couple considerations for selecting your audit strategy.

1. Review your schema and identify data that *must* be audited. It’s likely your company’s personal identifiable information (PII) …

Disclaimer: The following blog post does not try to provide a solution for split-brain situations, and the example provided is for demonstrative purposes only. Inconsistencies resulting from a split-brain scenario might possibly be more complex than the one shown, so do not use the following example as a complete guide.

What is Split-Brain?

A split-brain scenario is the result of two data sets (which were originally synced) losing the ability to sync while potentially continuing to receive DMLs over the same rows and ids on both sides. This could have consequences such as data corruption or inconsistencies where each side has data that does not exist on the other side.

For example before split-brain:

After split-brain:

Node1:

Node2:

It can be seen that after the split-brain scenario there are many differences between the nodes:

• Customer row id=2 was deleted …