Use ClickHouse like MySQL with ProxySQL Introduction

We have several customers on ClickHouse now for both columnar database analytics and archiving MySQL data, You can access data from ClickHouse with clickhouse-client but this involves some learning  and also limitations technically. Our customers are very comfortable using MySQL so they always preferred a MySQL client for ClickHouse query analysis and reporting, Thankfully ProxySQL works as a optimal bridge between ClickHouse and MySQL client, This indeed was a great news for us and our customers worldwide. This blog post is about how we can use MySQL client with ClickHouse.

Installation

• https://github.com/sysown/proxysql/releases/  (**Download the package starting with clickhouse )
• Dependencies installation:
  • yum -y install …

Debezium is providing out of the box CDC solution from various databases. In my last blog post, I have published how to configure the Debezium MySQL connector. This is the next part of that post. Once we deployed the debezium, to we need some kind of monitoring to keep track of whats happening in the debezium connector. Luckily Debezium has its own metrics that are already integrated with the connectors. We just need to capture them using the JMX exporter agent. Here I have written how to monitor Debezium MySQL connector with Prometheus and Grafana. But the dashboard is having the basic metrics only. You can build your own dashboard for more detailed monitoring.

Reference: List of Debezium monitoring metrics

Install JMX exporter in …

While we’ve had MySQL Group Replication support in ProxySQL since version 1.3 (native as of v1.4), development has continued in subsequent versions. I’d like to describe a scenario of how latency can affect ProxySQL in a MySQL Group Replication environment, and outline a few new features that might help mitigate those issues. Before we dive into the specifics of the discussion, however, let’s take a quick overview of ProxySQL and Group Replication for those who may not be familiar.

MySQL Group Replication

Similar in functionality to Percona XtraDB Cluster or Galera, MySQL Group Replication is the only synchronous native HA solution for MySQL*. With built-in automatic distributed recovery, conflict detection, and group membership, MySQL GR provides a completely native HA solution for MySQL environments.

In an earlier post, I discussed the Shared Responsibility Model in the cloud and how it relates to databases.  With either IaaS or DBaaS deployments, much of the operational and security burden is shifted away from the DBA to the cloud provider.  I also noted that regardless of the deployment method, there is always a need for a DBA. In both cloud deployment models, notice the top user responsibility: customer data.

Let’s review the major tasks of a DBA in the cloud and how that role differs between and IaaS and DBaaS deployment.

DBA Responsibility in the Cloud Application/Database

With the burden of hardware, OS, and physical security in the cloud, the focus is shifted to the application data and performance.  From the application perspective, here are the top areas of focus: …

As you could read in this previous post, PHP 7.4 is now completely supporting MySQL 8.0 and the new default authentication plugin.

I wanted to make a summary table providing and overview of all PHP versions and how they supported MySQL 8.0 and the different authentication plugins.

As I am a RPM based distribution user, I’m using the famous repository of remi since a lot of years, and I use it then also to install PHP 7.4.0 and 7.4.1…

I created a new user to test to …

Credit: AWS

CDC is becoming more popular nowadays. Many organizations that want to build a realtime/near realtime data pipe and reports are using the CDC as a backbone to powering their real-time reports. Debezium is an opensource product from RedHat and it supports multiple databases (both SQL and NoSQL). Apache Kafka is the core of this.

Managing and scaling Kafka clusters is not easy for everyone. If you are using AWS for your infra then let AWS manage the cluster. AWS has MSK is a managed Kafka service. We are going to configure Debezium with AWS MSK.

Configuration File:

If you are already worked with AWS MSK, then you might be familiar with this configuration file. This is similar to the RDS Parameter group but here you need to upload a with your parameter name and its value. If you are using MSK for the first time, then it’ll make you a bit confused. No worries, I’ll give you the steps to do …

In a high concurrency world, where more and more users->connections->threads are used, contention is a given. But how do we identify the contention point easily?

Different approaches had been discussed previously, like the one using Perf and Flame graphs to track down the function taking way more time than expected. That method is great but how can we do it with what one normally has, like the MySQL Client? Enter: the SEMAPHORES section from the SHOW ENGINE INNODB STATUS command output.

SEMAPHORES

The SEMAPHORES section displays all the metrics related to InnoDB mechanics on waits. This section is your best friend if you have a high concurrency workload. In short, it contains 2 kinds of data: …

This is the second part of a two-part series blog for Maximizing Database Query Efficiency In MySQL. You can read part one here.

Using Single-Column, Composite, Prefix, and Covering Index

Tables that are frequently receiving high traffic must be properly indexed. It's not only important to index your table, but you also need to determine and analyze what are the types of queries or types of retrieval that you need for the specific table. It is strongly recommended that you analyze what type of queries or retrieval of data you need on a specific table before you decide what indexes are required for the table. Let's go over these types of indexes and how you can use them to maximize your query performance.

Single-Column Index

InnoD table can contain a maximum of 64 secondary indexes. A …

In the previous blog post of this series, MySQL Encryption: Talking About Keyrings, I described how keyrings work. In this post, I will talk about how master key encryption works and what the pros and cons are of using envelope encryption such as a master key.

The idea behind envelope encryption is that you use one key to encrypt multiple other keys. In InnoDB, this “one key” is the master encryption key and the “multiple other keys” are the tablespace keys. Those tablespace keys are the ones that are actually used to encrypt tablespaces. Graphically it can be presented like this:

The master key resides in the keyring, while encrypted tablespace keys reside in tablespace headers (written on page 0 of a tablespace). In the picture above:

Table A is encrypted with key 1. Key 1 is encrypted …

Percona Monitoring and Management (PMM) is built upon the shoulder of giants like Prometheus and Grafana. And speaking of Grafana, one of the coolest features that come with it is the ability to customize the experience through 3rd party plugins.

Plugins are an easy way to enhance the ability to have specialized graphs. One case that we saw in Managed Services is the ability to have a throughput graph, that shows QPS vs Threads running. This is different in essence of the “default” graphs that show a metric against time (time being the X-axis) since what we wanted is to show queries per second not during a time (that graph already exists) but for specific values of threads.

One way to achieve that is by using a plugin called Plotly …