If you are using Galera replication, you know that schema changes may be a serious problem. With its current implementation, there is no way even a simple ALTER will be unobtrusive for live production traffic. It is a fact that with the default TOI alter method, Percona XtraDB Cluster (PXC) cluster suspends writes in order to execute the ALTER in the same order on all nodes.

For factual data structure changes, we have to adapt to the limitations, and either plan for a maintenance window, or use pt-online-schema-change, where interruptions should be very short. I suggest you be extra careful here, as normally you …

You say you want a Replication?

One of the best features of MySQL is the ability to use MySQL‘s built-in database replication feature to automatically replicate data from one server (source/master) to another (slave/replica). Group Replication was added in MySQL 5.7 as a way to provide a high-availability solution using a new variation of MySQL replication.

(In some earlier posts, I explained how to setup Group Replication using three MySQL database servers and how to …

We already have seen how to gather overall MySQL server diagnostics information and how to trace a specific thread activity. Let’s go to more granular level this time ! What about tracing a specific statement. Has MySQL a built-in way to do that ?

The “ps_trace_statement_digest” introduced in MySQL 5.7 allow tracing a specific statement digest (As explained in the doc : The digesting process converts each SQL statement to normalized form (the statement digest) and computes a SHA-256 hash value (the digest hash value) from the normalized result.Normalization permits statements that are …

We’re in the process of upgrading our MySQL databases from v5.7 to v8.0; since one of the differences in v8.0 is that the default encoding changed from utf8 to utf8mb4, and we had the conversion in plan anyway, we anticipated it and performed it as preliminary step for the upgrade.

This post describes in depth the overall experience, including tooling and pitfalls, and related subjects.

Contents:

• Introduction
• Migration plan: overview and considerations
  • …

Real-time web/mobile application is becoming popular day by day. Services like Firebase and Pusher provides API’s and Services to develop effective real-time notification system for your mobile and web apps.

We are not going to use these Services in this post; instead, we will develop an application that pops up notification on a particular event – Say a new comment added on Post. For notification, we will use Chrome desktop notification and for real-time communication – Socket.io.

I have already covered desktop notification here and Socket.io tutorial here.

DOWNLOAD Prerequisites :

Level of the …

In reply to Dhanunjay.

I can actually run the query on the same MySQL version with no issues and it takes less than 1 sec to return the data. Have you checked if some long-running queries were being executed at the times you ran the query? Or maybe the server was at a high load?

LikeLike

In reply to Sani.

Thanks!
I’m fine if you share the links but not copying and pasting the contents.

LikeLike

In my last blog post we have seen how we can easily collect general MySQL status information using the sys.diagnostics() procedure. What about a single session/thread ? Is there built-in way to easily trace a target thread/session activity (ex: transactions/ statements/stage/wait events) like using an oracle SQL trace ?

The procedure “ps_trace_thread()” introduced in MySQL 5.7 allow monitoring a specific thread. This procedure is based on Mark Leith work in his blog post A Big Bag of Epic Awesomeness. (I love the title :))

It will  captures as much information on the thread activity as possible depending on the actual …

Has MySQL a built-in way to easily take a look at the overall server status without the need to execute many different custom query or program ? Maybe something similar to an oracle AWR report ?

Starting with MySQL 5.7.9 and later we can use the SYS procedure “diagnostics()” for that which rely the information available on the PERFORMANCE_SCHEMA !

Let’s give it a try  by generating a HTML report for the currently running instance (starts an iteration every 30 seconds and runs for at most 60 seconds using the current Performance Schema settings ) :

mysql -u root -p -H -e"CALL sys.diagnostics(60, 30, 'current');" > ./current_instance_report.html

The report contain information such as :

Wait event :

…

ProxySQL is an intelligent and high-performance SQL proxy which supports MySQL, MariaDB and ClickHouse. Recently, ProxySQL 2.0 has become GA and it comes with new exciting features such as GTID consistent reads, frontend SSL, Galera and MySQL Group Replication native support.

It is relatively easy to run ProxySQL as Docker container. We have previously written about how to run ProxySQL on Kubernetes as a helper container or as a Kubernetes service, which is based on ProxySQL 1.x. In this blog post, we are going to use the new version ProxySQL 2.x which uses a different approach for Galera Cluster configuration. …