How to add ProxySQL to our MySQL/MariaDB environment, and have it monitored by PMM. Docker allows to do this in minutes, for testing purposes.
The slow query log is the trusted old method of recording slow query, so the database administrator can determine which queries are in the most need for optimization. Since MySQL 5.6, it has to some extend been overshadowed by the Performance Schema which has lower overhead and thus allows collecting statistics about all queries. The slow query log has one major advantage though: the data is persisted. In MySQL 8.0.14 which was recently released, there is an improvement for the slow query log: additional statistics about the recorded queries.
The slow query log with log_slow_extra enabled.
Thanks for Facebook for …[Read more]
In a MySQL hosting replication setup, the parameter Seconds_Behind_Master (SBM), as displayed by the SHOW SLAVE STATUS command, is commonly used as an indication of the current replication lag of the slave. In this blog post, we examine how to understand and interpret this value in various situations.
Possible Values of Seconds Behind Master
The value of SBM, as explained in the MySQL documentation, depends on the state of the MySQL slave in general, and the states of MySQL slave SQL_THREAD and IO_THREAD in particular. While IO_THREAD connects with the master and reads the updates, SQL_THREAD applies these updates on the slave. Let’s examine the possible values of SBM during different states of the MySQL Slave.
When SBM Value is Null
- SBM is …
I am a Junior DBA at Mydbops. This is my first blog professionally, I would like to brief my encounter with Log-rotate in first few weeks of my work, Hope it will help other beginners as well. This Blog will cover the following sections.
Introduction to Log-rotate
Solutions (Fix for the above issues)
How to configure the Log-rotate
Operation of Log-rotate
Files responsible for the Log_rotate utility.
1.0. Introduction to Log-rotate:
Log-rotate is a utility and …
Have you been experiencing slow MySQL startup times in GTID mode? We recently ran into this issue on one of our MySQL hosting deployments and set out to solve the problem. In this blog, we break down the issue that could be slowing down your MySQL restart times, how to debug for your deployment, and what you can do to decrease your start time and improve your understanding of GTID-based replication.
How We Found The Problem
We were investigating slow MySQL startup times on a low-end, disk-based MySQL 5.7.21 deployment which had GTID mode enabled. The system was part of a master-slave pair and was under a moderate write load. When restarting during a scheduled maintenance, we …[Read more]
MySQL 8.0.13 improves replication lag monitoring by extending the instrumentation for transaction transient errors. These temporary errors, which include lock timeouts caused by client transactions executing concurrently as the slave is replicating, do not stop the applier thread: instead, they cause a transaction to retry.…
A standard cluster deployment uses three nodes, which allows for no-downtime upgrades along with the ability to have a fully available cluster during maintenance.
Please note that with only two database cluster nodes, there is a window of vulnerability created by leaving zero failover candidates available when the lone slave is taken down for service.
The Best Practices: Staging Performing a No-Downtime Upgrade for a Staging Deployment
When upgrading a Staging-style deployment, all nodes are upgraded
at once in parallel via the
tools/tpm update command
run from inside the staging directory on the staging host.
No Master switch happens, and all layers are restarted to use the new code. …[Read more]
Tungsten Clustering is an extraordinarily flexible tool, with options at every layer of operation.
In this blog post, we will describe and discuss the two different methods for installing, updating and upgrading Tungsten Clustering software.
When first designing a deployment, the question of installation methodology is answered by inspecting the environment and reviewing the customer’s specific needs.
Staging Deployment Methodology
All for One and One for All
Staging deployments were the original method of installing
Tungsten Clustering, and relied upon command-line tools to
configure and install all cluster nodes at once from a central
location called the
This staging server (which could be one of the cluster nodes) requires SSH access to all …[Read more]
Your database cluster contains your most business-critical data and therefore proper performance under load is critical to business health. If response time is slow, customers (and staff) get frustrated and the business suffers a slow-down.
If the database layer is unable to keep up with demand, all applications can and will suffer slow performance as a result.
To prevent this situation, use load tests to determine the throughput as objectively as possible.
In the sample
load.pl script below, increase load by
increasing the thread quantity.
You could also run this on a database with data in it without polluting the existing data since new test databases are created to match each node’s hostname for uniqueness.
Note: The examples in this blog post assume that a Connector is …[Read more]