Showing entries 1 to 10 of 117
10 Older Entries »
Displaying posts with tag: mysql-and-variants (reset)
MySQL Monitoring and Reporting Using the MySQL Shell

MySQL Shell is the advanced MySQL client, which has many excellent features. In this blog, I am going to explain the MySQL shell commands “\show” and “\watch”. Both commands are very useful to monitor the MySQL process. It provides more insights into the foreground and background threads as well. 

Overview

“\show” and “\watch” are the MySQL shell commands, which can be executed using the Javascript (JS), Python (Py), and SQL interfaces. Both commands are providing the same information, but the difference is you can refresh the results when using the command “\watch”. The refresh interval is two seconds. 

  • \show: Run the specified report using the provided options and arguments.
  • \watch: Run the specified report using the provided options and arguments, and refresh the results at regular intervals.

Below are the available options you can use with the “\show” …

[Read more]
Point-In-Time Recovery in Kubernetes Operator for Percona XtraDB Cluster – Architecture Decisions

Point-In-Time Recovery (PITR) for MySQL databases is an important feature that is essential and covers common use cases, like a recovery to the latest possible transaction or roll-back the database to a specific date before some bad query was executed. Percona Kubernetes Operator for Percona XtraDB Cluster (PXC) added support for PITR in version 1.7, and in this blog post we are going to look into the technical details and decisions we made to implement this feature.

Architecture Decisions Store Binary Logs on Object Storage

MySQL uses …

[Read more]
MySQL 5.6 and Percona Server for MySQL 5.6 are End of Life

MySQL 5.6.51 is the last release of the MySQL 5.6 series. Oracle will no longer provide updates or security fixes for this version.

Following Percona’s Release Lifecycle policies, the Percona Server for MySQL 5.6 series has reached End of Life (EOL) as well, and we will no longer provide public builds for bugs and security fixes.

We recommend that you upgrade to MySQL 5.7 or Percona Server for MySQL 5.7, or for the latest features, MySQL 8.0 or Percona Server for MySQL 8.0.

Suppose you have not upgraded because you have off-the-shelf applications that also must be upgraded and require 5.6, or you have other …

[Read more]
Various Types of InnoDB Transaction Isolation Levels Explained Using Terminal

The goal of this blog post is to explain the various types of transaction isolation levels available in MySQL. After reading the blog, you will be able to explain dirty reads, non-repeatable reads, and the concept of phantom rows as well.

What is the Isolation Level in MySQL?

Isolation (I) is one of the properties from ACID. It defines how each transaction is isolated from other transactions and is a critical component of application design. As per the SQL:1992 standard, InnoDB has four types of Isolation levels. Below, I have listed the types in order, and each transaction isolation level provides better consistency compared to the previous one.

  • READ-UNCOMMITTED
  • READ-COMMITTED
  • REPEATABLE-READ – ( MySQL’s DEFAULT )
  • SERIALIZABLE

You can change the isolation level using the variable “transaction_isolation” at runtime. As transaction isolation changes can …

[Read more]
Webinar February 24: What’s Old Is New, What’s Coming Is Here – Percona Offerings for MySQL 5.6 and DBaaS in PMM

Join Brian Walters and Brandon Fleisher for an interactive discussion around new service and software offerings coming from Percona.

Oracle has decided that MySQL 5.6 will reach its end of life in February 2021. This means there will be no more updates and, more importantly, no more security fixes for discovered vulnerabilities. Yikes! Did you know you have options? Did you know Percona can solve this problem? Tune in to find out how.

Choosing a database-as-a-service (DBaaS) provider can be a daunting challenge. Not ready to move to DBaaS in the public cloud? Or, are you constrained to on-prem deployment due to regulations or other market conditions? Tune-in to learn how Percona is bringing the benefits of simplified DBaaS deployment to the on-prem, private cloud, and hybrid datacenter.

This interactive webinar will be conducted in a roundtable forum style. Bring your interests, bring …

[Read more]
Q&A on Webinar “Using PMM to Identify and Troubleshoot Problematic MySQL Queries”

Hi and thanks to all who attended my webinar on Tuesday, January 26th titled Using PMM to Identify & Troubleshoot Problematic MySQL Queries!

Like we do after all our webinars, we compile the list of questions that were answered verbally and also those that were posed yet remained unanswered since we ran out of time during the broadcast.  Before we get to the questions, I wanted to make sure to include a link to the RED Method for MySQL Queries by Peter Zaitsev, Percona’s CEO:

https://grafana.com/grafana/dashboards/12470

Hi Michael, you suggested that table create and update times should be ignored. Surely these values come from information_schema.tables? Does that not reflect what I would see …

[Read more]
Percona XtraBackup 8 Enables –lock-ddl by Default

Percona XtraBackup 8.0.23 enables the “lock-ddl” option by default to ensure any DDL events do not corrupt the backups. Any DML events continue to occur, and only DDL events are blocked.

A DDL lock protects the definition of tables and views. With the “–lock-ddl” option disabled, Percona XtraBackup allows backups while concurrent DDL events continue to happen. These backups are invalid and fail at the Prepare stage. If DDL events have occurred without the backup user’s knowledge, they may be unaware the backup is corrupt.

MySQL 8.0 introduced “Lock Instance for Backup”. This backup lock is lightweight, and the performance for DML operations is almost the same with or without backup locks. This lock is not available in 5.7, because …

[Read more]
Low Value in range_optimizer_max_mem_size Might Cause Full Table Scan

Although how range_optimizer_max_mem_size operates is explained in the official doc, it’s not a well-known variable and surely not a variable you need to modify under most circumstances. But from time to time we get some performance tickets related to this.

What problem does this variable cause if it is not properly sized? Let’s find out with an example!

Given the following table definition:

CREATE TABLE `joinit` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  `s` varchar(64) DEFAULT NULL,
  `t` time NOT NULL,
  `g` int(11) NOT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB AUTO_INCREMENT=2490316 DEFAULT CHARSET=latin1

With ~2M rows

select count(*) from joinit ;
+----------+
| count(*) |
+----------+
|  2097152 |
+----------+

With the default value of  range_optimizer_max_mem_size = 8388608, the …

[Read more]
The MySQL Clone Wars: Plugin vs. Percona XtraBackup

Large replication topologies are quite common nowadays, and this kind of architecture often requires a quick method to rebuild a replica from another server.

The Clone Plugin, available since MySQL 8.0.17, is a great feature that allows cloning databases out of the box. It is easy to rebuild a replica or to add new nodes to a cluster using the plugin. Before the release of the plugin, the best open-source alternative was Percona XtraBackup for MySQL Databases.

In this blog post, we compare both alternatives for cloning purposes. If you need to perform backups, Percona XtraBackup is a better tool as it supports compression and incremental backups, among other features not provided by the plugin. The plugin supports compression only for network transmission, not for storage.

But one of the plugin’s strong points is simplicity. …

[Read more]
MySQL 8.0.22: SHOW PROCESSLIST Version 2 – Now Available From PERFORMANCE_SCHEMA

The “SHOW PROCESSLIST” command is very famous and very useful for MySQL DBAs. It will help you to understand the ongoing thread activities and their current states. By default, the “show processlist” output details will be collected from the thread manager, and it needs the global mutex. From MySQL 8.0.22, we have an alternative way to get the process details from the PERFORMANCE_SCHEMA. It doesn’t need the global mutex. 🙂

Note: We also have the non-blocking SYS schema views “processlist” and “x$processlist”, which provide more complete information than the SHOW PROCESSLIST statement and the INFORMATION_SCHEMA.PROCESSLIST and PERFORMANCE_SCHEMA.PROCESSLIST. But, we can’t integrate this with the “SHOW PROCESSLIST” command.

In this blog, I am going to explain the complete details about the new processlist implementation using PERFORMANCE_SCHEMA.

“SHOW PROCESSLIST” Using Thread …

[Read more]
Showing entries 1 to 10 of 117
10 Older Entries »