Jun
03
2022
--

MyDumper’s Stream Implementation

MyDumper Stream Implementation

MyDumper Stream ImplementationAs you might know, mysqldump is single-threaded and STDOUT is its default output. As MyDumper is multithreaded, it has to write on different files. Since version 0.11.3 was released in Nov 2021, we have the possibility to stream our backup in MyDumper. We thought for several months until we decided what was the simplest way to implement it and we also had to add support for compression. So, after fixing several bugs, and we now consider it is stable enough, we can explain how it works.

How Can You Stream if MyDumper is Multithreaded?

Receiving a stream is not a problem for myloader, it receives a file at a time and sends it to a thread to process it. However, each worker thread in mydumper is connected to the database, and as soon as it reads data, it should be sent to the stream, which might cause collisions with other worker threads that are reading data from the database. In order to avoid this issue, we ended up with the simplest solution: mydumper is going to take a backup and store it in the local file system that you configured, and the filename will be enqueued to be processed by the Stream Thread which pops one file at a time and pipes to stdout. We study the alternative to send chunks of the file while it is being dumped, but the way that we implemented is simpler and improves the overall performance.

Implementation Details

Here is a high-level diagram of how we implemented it:

MyDumper


When a mydumper Worker Thread processes a job, it connects to the database and stores the output into a file. That didn’t change, but with stream, we are pushing the filename into the mydumper stream_queue.

The mydumper Stream Thread is popping filenames from the mydumper stream_queue, it is going to send the header of the file to stdout and then open the file and send its content.

Then, myloader Stream Thread is going to receive and detect the header, it will create the new file with the filename from the header and store the content in it.

After closing the file, it will enqueue the filename in the myloader stream_queue. A myloader Worker Thread is going to take that file and process it according to the kind of file it is.

By default, the files are deleted, but if you want to keep them, you can use the –no-delete option. 

The header is simply adding — to the filename so you can use myloader or mysql client to import your database. Here is an example:

-- sbtest-schema-create.sql
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `sbtest` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

-- sbtest.sbtest1-schema.sql
/*!40101 SET NAMES binary*/;
/*!40014 SET FOREIGN_KEY_CHECKS=0*/;

/*!40103 SET TIME_ZONE='+00:00' */;
CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  `pad2` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100010 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- sbtest.sbtest1.00000.sql
/*!40101 SET NAMES binary*/;
/*!40014 SET FOREIGN_KEY_CHECKS=0*/;
/*!40103 SET TIME_ZONE='+00:00' */;
INSERT INTO `sbtest1` VALUES(1,49929,"83868641912-28773972837-60736120486-75162659906-27563526494-20381887404-41576422241-93426793964-56405065102-33518432330","67847967377-48000963322-62604785301-91415491898-96926520291","")
…

Simple Use Cases

A thread writes to a single file to avoid a collision, which improves the performance. However, having thousands of files for a backup of a couple of tables is not manageable. So, the simplest use case is to send everything to a single file:

mydumper -B <SCHEMA_NAME> -h <FROM> > filename.sql

Then you can just simply import it using:

myloader --stream -o -h <TO_SERVER> < filename.sql

Now that you can pipe from a mydumper process to myloader, this execution is possible:

mydumper -B <SCHEMA_NAME> -h <FROM> | myloader --stream -o -h <TO>

pipe from a mydumper process to myloader

Or you can send the stream through the network using nc: 

mydumper -B <SCHEMA_NAME> -h <FROM_SERVER> | nc <MYDUMPER_SERVER> <ANY_PORT>
nc -l <MYDUMPER_SERVER> <ANY_PORT> | myloader --stream -o -h <TO_SERVER>

 

stream through the network using nc

This implementation is using the backup directory on mydumper and myloader as Buffers, you must take this into account, as by default it is going to create a directory where you run it.

Another thing that you need to take into account is that mydumper and myloader will be writing on disk, the whole backup will be written on both File Systems while it is being processed, and use a file system with enough disk space.

Finally, you can keep myloader running and send several mydumper backups. First, you need to run:

nc -k -l <MYDUMPER_SERVER> <ANY_PORT> | myloader --stream -o -h <TO_SERVER>

And then execute:

mydumper -B <SCHEMA_NAME_1> -h <FROM_SERVER> | nc <MYDUMPER_SERVER> <ANY_PORT>
mydumper -B <SCHEMA_NAME_2> -h <FROM_SERVER> | nc <MYDUMPER_SERVER> <ANY_PORT>
mydumper -B <SCHEMA_NAME_3> -h <FROM_SERVER> | nc <MYDUMPER_SERVER> <ANY_PORT>
mydumper -B <SCHEMA_NAME_4> -h <FROM_SERVER> | nc -N <MYDUMPER_SERVER> <ANY_PORT>

Some versions of nc have these two options:

      -k      When a connection is completed, listen for another one.  Requires -l.

     -N      shutdown(2) the network socket after EOF on the input.  Some servers require this to finish their work.

This is very useful if you are refreshing some testing environment and you only need a couple of tables on different databases or if you are using a where clause that only applies to some tables.

Considerations

Usually, when you send data to STDOUT, you are not going to have trouble with disk space usage on the dumper server. That is NOT true if you are using MyDumper. Files will be stored on the mydumper server until they are transferred to the receiving server. For instance, if you have a 10TB database, with a very low network bandwidth compared to the disk bandwidth, you might end up filling up the disk where you keep the files temporarily.

Conclusion

We focus the implementation to speed up export and import processes. Opposite to other software or implementations, we use the file system as a buffer causing a higher disk utilization.

Jun
01
2022
--

Session Temporary Tablespaces and Disk Space Usage in MySQL

Session Temporary Tablespaces and Disk Space Usage in MySQL

Session Temporary Tablespaces and Disk Space Usage in MySQLTemporary Tables handling in MySQL has a somewhat checkered past. Some time ago, my colleague Przemek wrote a post about differences in the implementations between versions of MySQL. If you are looking for the differences in implementation, that’s a perfect place to check.

In this post, I’d like to focus on Session Temporary Tablespaces, InnoDB on-disk internal temporary tables that come to play when a query requires storing more data than tmp_table_size or TempTable engine allocated more than temptable_max_mmap of memory-mapped temporary files.

If that condition happens, a session executing the query needs to be allocated with a tablespace from a pool of temporary tablespaces. The pool initially contains 10 temporary tablespaces that are created when an instance is started. The size of the pool never shrinks, and tablespaces are added to the pool automatically if needed. The default size of the tablespace (IBT file) is five pages or 80 KB.

When a session disconnects, its temporary tablespaces are truncated and released to the pool with their initial size. The truncate operation happens ONLY when the session disconnects; this also means that as long as the session is connected, the tablespaces consume disk space. MySQL can reuse the area for future queries, but if one query requires a lot of temporary space, the IBT will remain huge for the whole lifetime of a session.

Let’s demonstrate that behavior. Firstly, let’s create a table and populate it with one million rows. As a side note, storing SHA1 checksums in a char(40) field isn’t perfect, but it makes the test cleaner.

mysql> CREATE TABLE table01 (id int unsigned primary key auto_increment, s char(40));
mysql> INSERT INTO table01 (s) SELECT sha1(FLOOR(RAND() * 100))  from sequence_table(1000000) t;

Then, the below query that’s using temporary tables is going to be executed. As the temporary table size is larger than a value of tmp_table_size, which is 16 MB by default, that query will have to use the Session Temporary Tablespace.

mysql> pager pt-visual-explain
mysql> explain SELECT * FROM ( SELECT * FROM table01 UNION SELECT * FROM table01 ORDER BY s DESC) t LIMIT 1;
Table scan
rows           1991860
+- DERIVED
   table          derived(temporary(union(table01,table01)))
   +- Table scan
      +- TEMPORARY
         table          temporary(union(table01,table01))
         +- Filesort
            +- Table scan
               +- UNION
                  table          union(table01,table01)
                  +- Table scan
                  |  rows           995930
                  |  +- Table
                  |     table          table01
                  +- Table scan
                     rows           995930
                     +- Table
                        table          table01

mysql> SELECT * FROM ( SELECT * FROM table01 UNION SELECT * FROM table01 ORDER BY s DESC) t LIMIT 1;
+--------+------------------------------------------+
| id     | s                                        |
+--------+------------------------------------------+
| 999145 | fe5dbbcea5ce7e2988b8c69bcfdfde8904aabc1f |
+--------+------------------------------------------+

Let’s check the temporary tablespaces attached to that session after executing the query:

mysql> SELECT PATH, format_bytes(SIZE), STATE, PURPOSE FROM INFORMATION_SCHEMA.INNODB_SESSION_TEMP_TABLESPACES WHERE id = CONNECTION_ID();
+----------------------------+--------------------+--------+-----------+
| PATH                       | format_bytes(SIZE) | STATE  | PURPOSE   |
+----------------------------+--------------------+--------+-----------+
| ./#innodb_temp/temp_10.ibt | 392.00 MiB         | ACTIVE | INTRINSIC |
+----------------------------+--------------------+--------+-----------+

# ls -la './#innodb_temp/temp_10.ibt'
-rw-r----- 1 mysql mysql 392M 05-29 14:10 temp_10.ibt

After closing the session the file size is back to its default value:

# ls -la './#innodb_temp/temp_10.ibt'
-rw-r----- 1 mysql mysql 80K 05-29 14:10 temp_10.ibt

The solution works as described in the MySQL manual, however, it also means that it can be problematic. Long-living sessions aren’t uncommon in the databases world, primarily if applications use connection pooling. Moreover, connection pooling was designed precisely for that purpose, to mitigate the overhead of creating a new connection each time an application needs it, as reusing already existing connections to a database is more efficient than opening a new connection.

For instance, if between an application and MySQL, ProxySQL is used as middleware, the application lacks most of the control of the backend connection. It means that balanced connections to the backend are going to be used, but also, the connections will most likely live forever. Because of that fact, MySQL will rarely reclaim space from Session Temporary Tablespace, and this, as a result, will increase the overall disk space utilization.

It’s not that hard to hit. One heavy OLAP query from time to time it’s enough to start the snowball.

ProxySQL has an option that forces backend connection to be re-initialized – mysql-connection_max_age_ms (disabled by default). However, this works only for ProxySQL, and there are plenty more Connection Poolers available, and some of them are implemented directly on the applications’ side, which in general increases the complexity of the whole problem.

In an ideal world, MySQL should handle this issue by triggering the truncate process more often. I’ve submitted a Feature Request with the goal of having the “surprise” factor removed, and ultimately making it more user-friendly.

May
26
2022
--

Is MySQL Statement-Based / Mixed Replication Really Safe?

MySQL Statement-Based

MySQL Statement-BasedThe binary logging format in MySQL has been ROW by default since MySQL 5.7, yet there are still many users sticking with STATEMENT or MIXED formats for various reasons. In some cases, there is just simple hesitation from changing something that has worked for years on legacy applications. But in others, there may be serious blockers, most typically missing primary keys in badly designed schemas, which would lead to serious performance issues on the replicas.

As a Support Engineer, I can still see quite a few customers using STATEMENT or MIXED formats, even if they are already on MySQL 8.0. In many cases this is OK, but recently I had to deal with a pretty nasty case, where not using ROW format was found to cause the replicas to silently lose data updates, without raising any replication errors! Was it some really rare edge use case? Not at all! Let me demonstrate a very simple test case below to illustrate how easy it is to end up in such a bad situation.

— source 

mysql> select @@binlog_format,@@system_time_zone;
+-----------------+--------------------+
| @@binlog_format | @@system_time_zone |
+-----------------+--------------------+
| STATEMENT       | BST                |
+-----------------+--------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `test1` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `d` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   `a` varchar(30) NOT NULL,
    ->   `name` varchar(25) DEFAULT NULL,
    ->   PRIMARY KEY (`a`),
    ->   UNIQUE KEY `id` (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> insert into test1 values (null,now(),"test1",0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test1 values (null,now(),"test2",0);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test1 values (null,now(),"test3",0);
Query OK, 1 row affected (0.01 sec)

mysql> select * from test1;
+----+---------------------+-------+------+
| id | d                   | a     | name |
+----+---------------------+-------+------+
|  1 | 2022-05-22 10:13:37 | test1 | 0    |
|  2 | 2022-05-22 10:13:37 | test2 | 0    |
|  3 | 2022-05-22 10:13:38 | test3 | 0    |
+----+---------------------+-------+------+
3 rows in set (0.00 sec)

— replica

mysql> select @@system_time_zone;
+--------------------+
| @@system_time_zone |
+--------------------+
| UTC                |
+--------------------+
1 row in set (0.00 sec)

mysql> select * from db1.test1;
+----+---------------------+-------+------+
| id | d                   | a     | name |
+----+---------------------+-------+------+
|  1 | 2022-05-22 09:13:37 | test1 | 0    |
|  2 | 2022-05-22 09:13:37 | test2 | 0    |
|  3 | 2022-05-22 09:13:38 | test3 | 0    |
+----+---------------------+-------+------+
3 rows in set (0.00 sec)

— source 

mysql> UPDATE test1 SET name = 'foobar', d = CURRENT_TIMESTAMP WHERE a = 'test1' AND d = '2022-05-22 10:13:37';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test1;
+----+---------------------+-------+--------+
| id | d                   | a     | name   |
+----+---------------------+-------+--------+
|  1 | 2022-05-22 10:16:15 | test1 | foobar |
|  2 | 2022-05-22 10:13:37 | test2 | 0      |
|  3 | 2022-05-22 10:13:38 | test3 | 0      |
+----+---------------------+-------+--------+
3 rows in set (0.00 sec)

— replica

mysql> select * from db1.test1;
+----+---------------------+-------+------+
| id | d                   | a     | name |
+----+---------------------+-------+------+
|  1 | 2022-05-22 09:13:37 | test1 | 0    |
|  2 | 2022-05-22 09:13:37 | test2 | 0    |
|  3 | 2022-05-22 09:13:38 | test3 | 0    |
+----+---------------------+-------+------+
3 rows in set (0.00 sec)

mysql> pager egrep "Running|SQL_Error"
PAGER set to 'egrep "Running|SQL_Error"'

mysql > show replica status\G
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
               Last_SQL_Error: 
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
     Last_SQL_Error_Timestamp: 
1 row in set (0.00 sec)

 

Another test, using UTC_TIME() compared against a column that was populated using the CURRENT_TIMESTAMP function:

— source 

mysql> select * from test1 WHERE TIME(d) > DATE_SUB(UTC_TIME(), INTERVAL 11 HOUR) AND id=3;
+----+---------------------+-------+------+
| id | d                   | a     | name |
+----+---------------------+-------+------+
|  3 | 2022-05-22 10:13:38 | test3 | 0    |
+----+---------------------+-------+------+
1 row in set (0.00 sec)

— replica

mysql> select * from test1 WHERE TIME(d) > DATE_SUB(UTC_TIME(), INTERVAL 11 HOUR) AND id=3;
Empty set (0.00 sec)

Therefore, when a similar condition is used for update:

— source

mysql> update test1 set name="bar" WHERE TIME(d) > DATE_SUB(UTC_TIME(), INTERVAL 11 HOUR) AND id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test1 where id=3;
+----+---------------------+-------+------+
| id | d                   | a     | name |
+----+---------------------+-------+------+
|  3 | 2022-05-22 22:12:15 | test3 | bar  |
+----+---------------------+-------+------+
1 row in set (0.01 sec)

— replica

mysql> select * from test1 where id=3;
+----+---------------------+-------+------+
| id | d                   | a     | name |
+----+---------------------+-------+------+
|  3 | 2022-05-22 09:13:38 | test3 | 0    |
+----+---------------------+-------+------+
1 row in set (0.01 sec)

mysql > show replica status\G
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
               Last_SQL_Error: 
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
     Last_SQL_Error_Timestamp: 
1 row in set (0.00 sec)

Again replica ignored the update while there is no replication error reported. This particular scenario can be expected to happen really often in geographically-distributed database environments.

As the functions used here are not considered unsafe for replication, two usual safety actions are not performed:

  • no warning is printed in the error log when the STATEMENT format used
  • replication event is not logged in RBR format when the MIXED format is used but rather left the original query as it is

I find this potentially dangerous and hence reported it: https://bugs.mysql.com/bug.php?id=107293

Summary

ROW-based replication has become the standard in MySQL and is the most reliable one. It is also the only one permitted for virtually synchronous replication solutions like Percona XtraDB Cluster/Galera and MySQL Group Replication.

At the same time, STATEMENT or even MIXED format, may lead to data consistency issues, which can be undetected for a long time, making it very difficult to investigate when finally replication error happens as a result.

If there is anything that has prevented you from switching to ROW format yet, the sooner you deal with it the better.

May
26
2022
--

Looking for an Excellent MySQL Book for Beginners? The MySQL Workshop is a Great Choice

MySQL Book for Beginners

MySQL Book for Beginners

Last week at Percona Live, I was asked what book I recommend for novices seeking to learn MySQL.  For a long time, there has not been a good choice for modern versions of MySQL. Luckily I had just stumbled upon such a book.  Now I am happy to recommend The MySQL Workshop – A practical guide to working with data and managing databases with MySQL by Petit and Cosentino.

The first chapter introduces database architectures, data types, storage engines (including MyRocks), and data normalization. The following chapter cover in great detail how to create a database, using MySQL Workbench, backups & restoring data, and creating indexes. Chapter four has a very good section on working with SQL, functions, and case statements. Then JOINs and stored procedures are covered.

In another book, that would probably be enough content, but later chapters plunge into using Node.JS, Access, and Excel with MySQL.

The last few chapters cover loading data from many file formats, JSON data usage, user permissions, and logical backups. While knot a comprehensive MySQL DBA guide this material is well done and complete.

That is a lot of content in seven hundred pages.  The writing style is clear and numerous examples are well done.  Bravo to the authors for their achievements in this book.

This may not be the reference text for a grizzled MySQL veteran but for real novices and intermediates, this book is a must-have.  This would be a great classroom text for an introduction to MySQL class or a handy hardcopy reference for someone on the initial climb of the learning curve.

May
25
2022
--

Securing Dynamic Log File Locations in MySQL

Securing Dynamic Log File Locations in MySQL

Securing Dynamic Log File Locations in MySQLMySQL allows changing the location of the general log and the slow query log while the server is running by anybody having the SYSTEM_VARIABLES_ADMIN privilege to any location, including appending to existing files. In Percona Server for MySQL 8.0.28-19 we introduced a new system variable, secure-log-path, that can be used to restrict the location of these log files to avoid accidents or possible data corruption attacks.

When somebody with the system variables admin privilege changes these variables, the server runs a few sanity checks. Unfortunately, these checks are quite minimal, and only verify that the specified file is writable by mysqld.

Compared to this, other variables specifying write-related file and directory names are either read-only during the runtime of the server (such as datadir, tmpdir, or log_error), or have additional security checks, for example, the FILE privilege restricted by secure-file-priv.

Without a mechanism like that, these log files can be redirected to any file writable by MySQL, such as ibdata0 or any other data file in the data directory. The server only appends to these files, and no data will be deleted from them, but this still makes it possible to corrupt these files by writing unrelated log messages into them.

To fix this issue, starting with Percona Server for MySQL 8.0.28-19 we introduced a new variable, secure-log-path. This works similarly to secure-file-priv: it can be either empty (which is the default) or the name of a directory. If it is specified, the general log file and the slow query log can only be set to a file within that directory.

Similar to secure-file-priv, the server will display a warning during startup if this new variable is empty, and also if the data directory is within it.

While these two variables are similar, it is also important that they are not the same: in most setups, users with the FILE privilege shouldn’t have read access to the log files written by mysqld using SQL commands, as that could leak potentially sensitive data. To avoid this, it is important to set them to different directories.

May
24
2022
--

Store and Manage Logs of Percona Operator Pods with PMM and Grafana Loki

Store and Manage Logs of Percona Operator Pods with PMM and Grafana Loki

While it is convenient to view the log of MySQL or MongoDB pods with

kubectl logs

, sometimes the log is purged when the pod is deleted, which makes searching historical logs a bit difficult. Grafana Loki, an aggregation logging tool from Grafana, can be installed in the existing Kubernetes environment to help store historical logs and build a comprehensive logging stack.

What Is Grafana Loki

Grafana Loki is a set of tools that can be integrated to provide a comprehensive logging stack solution. Grafana-Loki-Promtail is the major component of this solution. The Promtail agent collects and ships the log to the Loki datastore and then visualizes logs with Grafana. While collecting the logs, Promtail can label, convert and filter the log before sending. Next step, Loki receives the logs and indexes the metadata of the log. At this step, the logs are ready to be visualized in the Grafana and the administrator can use Grafana and Loki’s query language, LogQL, to explore the logs. 

 

     Grafana Loki

Installing Grafana Loki in Kubernetes Environment

We will use the official helm chart to install Loki. The Loki stack helm chart supports the installation of various components like

promtail

,

fluentd

,

Prometheus

and

Grafana

$ helm repo add grafana https://grafana.github.io/helm-charts
$ helm repo update
$ helm search repo grafana

NAME                                CHART VERSION APP VERSION DESCRIPTION
grafana/grafana                     6.29.2       8.5.0      The leading tool for querying and visualizing t...
grafana/grafana-agent-operator      0.1.11       0.24.1     A Helm chart for Grafana Agent Operator
grafana/fluent-bit                  2.3.1        v2.1.0     Uses fluent-bit Loki go plugin for gathering lo...
grafana/loki                        2.11.1       v2.5.0     Loki: like Prometheus, but for logs.
grafana/loki-canary                 0.8.0        2.5.0      Helm chart for Grafana Loki Canary
grafana/loki-distributed            0.48.3       2.5.0      Helm chart for Grafana Loki in microservices mode
grafana/loki-simple-scalable        1.0.0        2.5.0      Helm chart for Grafana Loki in simple, scalable...
grafana/loki-stack                  2.6.4        v2.4.2     Loki: like Prometheus, but for logs.

For a quick introduction, we will install only

loki-stack

and

promtail

. We will use the Grafana pod, that is deployed by Percona Monitoring and Management to visualize the log. 

$ helm install loki-stack grafana/loki-stack --create-namespace --namespace loki-stack --set promtail.enabled=true,loki.persistence.enabled=true,loki.persistence.size=5Gi

Let’s see what has been installed:

$ kubectl get all -n loki-stack
NAME                            READY   STATUS    RESTARTS   AGE
pod/loki-stack-promtail-xqsnl   1/1     Running   0          85s
pod/loki-stack-promtail-lt7pd   1/1     Running   0          85s
pod/loki-stack-promtail-fch2x   1/1     Running   0          85s
pod/loki-stack-promtail-94rcp   1/1     Running   0          85s
pod/loki-stack-0                1/1     Running   0          85s

NAME                          TYPE        CLUSTER-IP     EXTERNAL-IP   PORT(S)    AGE
service/loki-stack-headless   ClusterIP   None           <none>        3100/TCP   85s
service/loki-stack            ClusterIP   10.43.24.113   <none>        3100/TCP   85s

NAME                                 DESIRED   CURRENT   READY   UP-TO-DATE   AVAILABLE   NODE SELECTOR   AGE
daemonset.apps/loki-stack-promtail   4         4         4       4            4           <none>          85s

NAME                          READY   AGE
statefulset.apps/loki-stack   1/1     85s

Promtail

and

loki-stack

pods have been created, together with a service that

loki-stack

will use to publish the logs to Grafana for visualization.

You can see the promtail pods are deployed by a

daemonset

 which spawns a promtail pod in every node. This is to make sure the logs from every pod in all the nodes are collected and shipped to the

loki-stack

  pod for centralizing storing and management.

$ kubectl get pods -n loki-stack -o wide
NAME                        READY   STATUS    RESTARTS   AGE    IP           NODE                 NOMINATED NODE   READINESS GATES
loki-stack-promtail-xqsnl   1/1     Running   0          2m7s   10.42.0.17   phong-dinh-default   <none>           <none>
loki-stack-promtail-lt7pd   1/1     Running   0          2m7s   10.42.1.12   phong-dinh-node1     <none>           <none>
loki-stack-promtail-fch2x   1/1     Running   0          2m7s   10.42.2.13   phong-dinh-node2     <none>           <none>
loki-stack-promtail-94rcp   1/1     Running   0          2m7s   10.42.3.11   phong-dinh-node3     <none>           <none>
loki-stack-0                1/1     Running   0          2m7s   10.42.0.19   phong-dinh-default   <none>           <none>

Integrating Loki With PMM Grafana

Next, we will add Loki as a data source of PMM Grafana, so we can use PMM Grafana to visualize the logs. You can do it from the GUI or with kubectl CLI. 

Below is the step to add data source from PMM GUI:

Navigate to

https://<PMM-IP-addres>:9443/graph/datasources

  then select

Add data source

 

Integrating Loki With PMM Grafana

Then Select Loki

Next, in the Settings, in the HTTP URL box, enter the DNS records of the

loki-stack

  service 

Loki settings

You can also use the below command to add the data source. Make sure to specify the name of PMM pod, in this command,

monitoring-0

  is the PMM pod. 

$ kubectl -n default exec -it monitoring-0 -- bash -c "curl 'http://admin:verysecretpassword@127.0.0.1:3000/api/datasources' -X POST -H 'Content-Type: application/json;charset=UTF-8' --data-binary '{ \"orgId\": 1, \"name\": \"Loki\", \"type\": \"loki\", \"typeLogoUrl\": \"\", \"access\": \"proxy\", \"url\": \"http://loki-stack.loki-stack.svc.cluster.local:3100\", \"password\": \"\", \"user\": \"\", \"database\": \"\", \"basicAuth\": false, \"basicAuthUser\": \"\", \"basicAuthPassword\": \"\", \"withCredentials\": false, \"isDefault\": false, \"jsonData\": {}, \"secureJsonFields\": {}, \"version\": 1, \"readOnly\": false }'"

Exploring the Logs in PMM Grafana

Now, you can explore the pod logs in Grafana UI, navigate to Explore, and select Loki in the dropdown list as the source of metrics:

Exploring the Logs in PMM Grafana

In the Log Browser, you can select the appropriate labels to form your first LogQL query, for example, I select the following attributes

 

{app="percona-xtradb-cluster", component="pxc", container="logs",pod="cluster1-pxc-1"} 

 

Click Show logs  and you can see all the logs of

cluster-pxc-1

pod

You can perform a simple filter with |= “message-content”. For example, filtering all the messages related to State transfer  by

{app="percona-xtradb-cluster", component="pxc", container="logs",pod="cluster1-pxc-1"} |= "State transfer"

Conclusion

Deploying Grafana Loki in the Kubernetes environment is feasible and straightforward. Grafana Loki can be integrated easily with Percona Monitoring and Management to provide both centralized logging and comprehensive monitoring when running Percona XtraDB Cluster and Percona Server for MongoDB in Kubernetes.

It would be interesting to know if you have any thoughts while reading this, please share your comments and thoughts.

May
10
2022
--

Spring Cleaning: Discontinuing RHEL 6/CentOS 6 (glibc 2.12) and 32-bit Binary Builds of Percona Software

Discontinuing RHEL 6/CentOS 6

Discontinuing RHEL 6/CentOS 6As you are probably aware, Red Hat Enterprise Linux 6 (RHEL 6 or EL 6 in short) officially reached “End of Life” (EOL) on 2020-11-30 and is now in the so-called Extended Life Phase, which basically means that Red Hat will no longer provide bug fixes or security fixes.

Even though EL 6 and its compatible derivatives like CentOS 6 had reached EOL some time ago already, we continued providing binary builds for selected MySQL-related products for this platform.

However, this became increasingly difficult, as the MySQL code base continued to evolve and now depends on tools and functionality that are no longer provided by the operating system out of the box. This meant we already had to perform several modifications in order to prepare binary builds for this platform, e.g. installing custom compiler versions or newer versions of various system libraries.

As of MySQL 8.0.26, Oracle announced that they deprecated the TLSv1 and TLSv1.1 connection protocols and plan to remove these in a future MySQL version in favor of the more secure TLSv1.2 and TLSv1.3 protocols. TLSv1.3 requires that both the MySQL server and the client application be compiled with OpenSSL 1.1.1 or higher. This version of OpenSSL is not available in binary package format on EL 6 anymore, and manually rebuilding it turned out to be a “yak shaving exercise” due to the countless dependencies.

Our build & release team was able to update the build environments on all of our supported platforms (EL 7, EL 8, supported Debian and Ubuntu versions) for this new requirement. However, we have not been successful in getting all the required components and their dependencies to build on EL 6, as it would have required rebuilding quite a significant amount of core OS packages and libraries to achieve this.

Moreover, switching to this new OpenSSL version would have also required us to include some additional shared libraries in our packages to satisfy the runtime dependencies, adding more complexity and potential security issues.

In general, we believe that running a production system on an OS that is no longer actively supported by a vendor is not a recommended best practice from a security perspective, and we do not want to encourage such practices.

Because of these reasons and to simplify our build/release and QA processes, we decided to drop support for EL 6 for all products now. Percona Server for MySQL 8.0.27 was the last version for which we built binaries for EL 6 against the previous version of OpenSSL.

Going forward, the following products will no longer be built and released on this platform:

  • Percona Server for MySQL 5.7 and 8.0
  • Percona XtraDB Cluster 5.7
  • Percona XtraBackup 2.4 and 8.0
  • Percona Toolkit 3.2

This includes stopping both building RPM packages for EL 6 and providing binary tarballs that are linked against glibc 2.12.

Note that this OS platform was also the last one on which we still provided 32-bit binaries.

Most of the Enterprise Linux distributions have stopped providing 32-bit versions of their operating systems quite some time ago already. As an example, Red Hat Enterprise Linux 7 (released in June 2014) was the first release to no longer support installing directly on 32-bit Intel/AMD hardware (i686/x86). Already back in 2018, we had taken the decision that we will no longer be offering 32-bit binaries on new platforms or new major releases of our software.

Given today’s database workloads, we also think that 32-bit systems are simply not adequate anymore, and we already stopped building newer versions of our software for this architecture.

The demand for 32-bit downloads has also been declining steadily. A recent analysis of our download statistics revealed that only 2.3% of our total binary downloads are referring to i386 binaries. Looking at IP addresses, these downloads originated from 0.4% of the total range of addresses.

This change affects the following products:

  • Percona Server for MySQL 5.7
  • Percona XtraDB Cluster 5.7
  • Percona XtraBackup 2.4
  • Percona Toolkit

We’ve updated the Percona Release Lifecycle Overview web page accordingly to reflect this change. Previously released binaries for these platforms and architectures will of course remain accessible from our repositories.

If you’re still running EL 6 or a 32-bit database or OS, we strongly recommend upgrading to a more modern platform. Our Percona Services team would be happy to help you with that!

May
09
2022
--

MySQL 8.0.29 and Percona XtraBackup Incompatibilities

MySQL 8.0.29 and Percona XtraBackup Incompatibilities

MySQL 8.0.29 and Percona XtraBackup IncompatibilitiesEarlier last week, Oracle released their Q2 releases series. Unlike previous releases, backward compatibility has now been broken with previous versions of MySQL.

MySQL 8.0.29 extended the support for the online DDL algorithm INSTANT. Prior to 8.0.29 only adding columns to the end of the table was supported.

In 8.0.29, this functionality was extended to allow the INSTANT algorithm the ability to add columns in any position of the table as well to drop columns. This new functionality required the redo log version to increase and new redo log types to be added, thus making it incompatible with older versions of the MySQL server and also older versions of Percona Xtrabackup. Please note that an in-place minor version downgrade of the server is also not supported.

We are currently working on making Percona Xtrabackup compatible with those changes. Until then users relying on Percona Xtrabackup for backups are advised to not upgrade the MySQL server to 8.0.29.

May
09
2022
--

MyDumper 0.12.3-1 is Now Available

MyDumper 0.12.3-1

MyDumper 0.12.3-1The new MyDumper 0.12.3-1 version, which includes many new features and bug fixes, is now available.  You can download the code from here. MyDumper is Open Source and maintained by the community, it is not a Percona, MariaDB, or MySQL product.

In this new version we focused on:

  • Refactoring tasks: Splitting mydumper.c and myloader.c into multiple files will allow us to find bugs and implement features easily.
  • Adding compression capabilities on stream: One of the more interesting features added to MyDumper was being able to stream to another server and now, compression and transmission will be available and much faster.
  • Support for the Percona Monitoring and Management (PMM) tool: Being able to monitor scheduled processes is always desired, even more, if you know what the process is doing internally and that is why we used the textfile extended metrics to show how many jobs have been done and queue status.

Enhancement:

  • Adding PMM support for MyDumper #660 #587
  • Adding compression capabilities on stream #605
  • Dockerfile: use multi-stage builds #603
  • restore defaults-file override behavior #601

Fix:

  • Fixing when DEFINER is absent and stream/intermediate sync when finish #662 #659
  • Adding better message when schema-create files are not provided #655 #617
  • re adding –csv option #653
  • Fixing trigger absent when –no-schema is used #647 #541
  • Allow compilation on Clang #641 #563
  • Fixing version number #639 #637
  • Adding critical message when DDL fails #636 #634
  • Fix print time #633 #632
  • print sigint confirmation to stdout instead of to a log file #616 #610
  • Dockerfile: add missing libglib2.0 package #608
  • [BUG] mydumper zstd doesn’t run on CentOS due to missing libzstd package dependency #602

Refactoring:

Help Wanted:

  • [BUG] myloader always uses socket to connect to db #650
  • [BUG] real_db_name isn’t found #613

Documentation:

  • Update README.md #614
  • Use 0.11.5-2 in installation examples #606
  • Question: What exactly is inconsistent about inconsistent backups? #644

Download MyDumper 0.12.3 Today!

MyDumper has its own merch now, too. By shopping for the merch you contribute to the project. Check it out! 

Apr
27
2022
--

A Quick Peek At MySQL 8.0.29

review of MySQL 8.0.29

MySQL 8.0.29Oracle released MySQL Server 8.0.29 on April 26th and this is a quick review of the release notes.  I have put my own comments in italics.

So what is in the ’29 release of MySQL Server?  Does it come festooned with new, neat features or is it a big bug-fix bonanza?

The TL;DR

While this server release has some interesting stuff, there is no compelling feature that will necessitate an immediate upgrade.  Read through the release notes to see if anything in there that is a must for you but for most of us, MySQL 8.0.29 does not require an immediate update.  If this was a birthday or holiday present, ’29 is the equivalent of getting a fresh box of dental floss – useful but not thrilling.

The shell is evolving too and the new version for VS Code looks promising.

MySQL Server 8.0.29

UTF8MB3?

The server now makes extensive use of UTF8MB3 (yes 3, not 4)  for the output of SHOW commands, reporting on invalid strings, when populating data dictionary tables from built-in character sets

Goodbye Arbitrary Delimiters in DATETIME/TIMESTAMP

The use of any nonstandard or excess delimiter or whitespace characters now triggers a warning.  MySQL used to allow you to use arbitrary delimiters and use an arbitrary number of them in TIME, DATE, DATETIME, and TIMESTAMP literals plus an arbitrary number of whitespaces before, after, and between the date and time values in DATETIME and TIMESTAMP literals. Those halcyon days are now over.  Expect this ability to be removed in a future release.

Deprecated Variables & Options

The replica_parallel_type, query_prealloc_size, and transaction_prealloc_size variables are deprecated with the mysqld options of  –abort-slave-event-count and –disconnect-slave-event-count.

PRIMARY and SECONDARY Engine

The Performance Schema now tracks the PRIMARY and SECONDARY engine used to process a query. Usually, this will be InnoDB and HeatWave. There is now an EXECUTION_ENGINE column to record which engine did the work with a COUNT_SECONDARY column to know how many times that engine was used.

SSL Libraries

Version bumped from1.1.1l o 1.1.1.n.

IF NOT EXITS

We get IF NOT EXISTS checks on function, procedure, and triggers. We needed this for a l-o-n-g time.

Detached XA Transactions

Group replication had a problem where XA transactions prepared on another connection could not be committed.  A prepared XA transaction can now be committed or rolled back by another connection. The current session can also then initiate another XA or local transaction without waiting for the prepared XA transaction to complete.

Classic and X Protocol Lock Files

It was possible that the lock file from either the MySQL Classic or X Protocol, which have different formats, could keep a server from starting. But now they use the same format.

Instant Drop Table

ALTER TABLE … DROP COLUMN  ALGORITHM=INSTANT is now available.

Secure Persisted Variables

Rather than use a keyring, we can now have secure persisted variables. You still need a keyring component instead of the keyring plugin to hold the encrypted information. So the data stays with the operating system in a file, not the keyring.

Clone Delayed

Cloning data too after dropping the old data is problematic for some operating systems that free things up asynchronously. So the new clone_delay_after_data_drop variable lets you wait up to an hour before your clone arrives.  Too bad we don’t get an Uber-like ‘your clone arriving in x minutes’ status update.

MyISAM

The myisam_repair_threads variable and myisamchk –parallel-check option is deprecated.

Isolated Variables

Better code for parsing, resolution, and execution of SQL statements now better enforces the isolation of access to system variables.

Bug Fixes

There are many bug fixes that take care of things from anonymous users with the PROCESS privilege could not select rows from the processlist table to histograms now storing buckets in dynamic arrays instead to reduce space overhead and will speed up selectivity estimation due to reduced indirection when performing a binary search on the buckets. I recommend reading through the bug fixes with each new release because they can trigger an ‘ah-ha’ moment when you find something that is impacting your instances that were on the edge of your awareness or provide a flood of endorphins from the schadenfreude of others’ problems.

MySQL Shell

MySQL Shell has a  new diagnostics utility, util.debug.collectDiagnostics, that gathers diagnostic information from the connected MySQL server. You can get reports in TSV and YAML formats before them in a zip archive in the location of your choice. This utility enables you to retrieve diagnostic information from standalone servers, members of replication topologies, MySQL Database Service DB Systems, and so on.

And there is a shell, prompt() function to provide a way for scripts to interact with a user.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com