Distributed | DBLE's general log implementation

Author: Wen Yunhan

A development member of the Aikesheng DBLE team, mainly responsible for the development of DBLE requirements, troubleshooting and community problem solving.

Source of this article: original submission

* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization, please contact the editor and indicate the source for reprinting.

---

Problem background

When using some GUI tools to connect to DBLE operation, some SQL is not compatible in DBLE, causing GUI tools to be abnormal and cannot be used normally.

The usual troubleshooting plan:

• Step 1: You need to know which SQL is sent to DBLE during GUI tool operation; generally use tcpdump, Wireshark and other packet capture tools to obtain SQLs
• Step 2: Execute SQLs one by one in Mysql Client to locate the problem SQL

Case investigation 1

Log in to the first interface of phpMyAdmin, the database list is not displayed

GUI tool: phpMyAdmin 7.4.20 (docker is used here)

DBLE version: 3.21.02.x

##docker方式搭建phpMyAdmin
## 拉取phpmyadmin镜像
$ docker pull phpmyadmin/phpmyadmin
  
##初始化phpmyadmin容器 且关联dble服务
$ docker run -d --name myadmin_aliyun -e PMA_HOST=xxx.mysql.rds.aliyuncs.com -e PMA_PORT=3xx6 -p 8081:80 phpmyadmin/phpmyadmin
  
##详解：
-d：以后台模式运行
--name myadmin： 容器命名为 myadmin, 容器管理时用(启动/停止/重启/查看日志等)
-e PMA_HOST=xx.xxx.xx.xx： Dble服务器域名或IP地址
-e PMA_PORT=8066： Dble的8066端口
-p 8080:80： 端口映射, 本地端口:容器端口, 访问: http://ip:8080
phpmyadmin/phpmyadmin： 要初始化的镜像名

Visit http://ip:8080 and log in with DBLE's 8066 user password; after logging in, it is found that the database list is not displayed, as shown in the figure below:

Why is there no database list displayed?

troubleshooting step one:

First download the tcpdump (for packet capture), Wireshark (view package) tools; then execute the tcpdump command to collect the tcp protocol transmission during the operation (the interface after logging in to phpmyadmin) to generate the cap file, and then place the file in Wireshark to convert to the Mysql protocol Then filter (as shown in the figure below) to facilitate reading, troubleshooting, and extraction of all issued SQLs; (in fact, you need to open Request Query one by one and then manually extract SQL)

troubleshooting step two:

Execute the collected SQLs one by one in Mysql Client to locate the problem SQL (reason: DBLE returns an empty result set when phpMyAdmin sends out the query library list)

Specific questions SQL:

1. SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA . SCHEMATA , (SELECT DB_first_level FROM (SELECT DISTINCT SUBSTRING_INDEX(SCHEMA_NAME,'_', 1) DB_first_level FROM INFORMATION_SCHEMA.SCHEMATA WHERE TRUE) t ORDER BY DB_first_level ASC 1 = WHERE TRATE ASC 1 = WHERE TRUE 0, 100 CONCAT(DB_first_level,'_'), CONCAT(SCHEMA_NAME,'_')) ORDER BY SCHEMA_NAME ASC; – query all libraries
2. 2021-08-06T15:33:28.350 9 Query SELECT COUNT(*) FROM (SELECT DISTINCT SUBSTRING_INDEX(SCHEMA_NAME,'_', 1) DB_first_level FROM INFORMATION_SCHEMA.SCHEMATA WHERE TRUE) t; – the number of query libraries

In step 1, you need to use additional tools to assist in troubleshooting, and you may need to reserve some knowledge (such as: TCP protocol, packet capture commands); therefore, it is more distressing for non-developers to troubleshoot this problem.

general log

Turn on the general log and record all SQL statements that reach DBLE. In this way, you no longer need to use the packet capture tool to get all the SQLs (directly go to step 2)

See the general log for details:

https://actiontech.github.io/dble-docs-cn/2.Function/2.27_general_log.html

Related parameters

• enableGeneralLog: switch identification of general log, 1-on, 0-off
• generalLogFile: The storage path of the general log file
• generalLogFileSize: The size of the trigger file flip. When it exceeds 16MB, the general.log will be flipped to a yyy-MM/general-MM-dd-%d.log format file
• generalLogQueueSize: the size used by the internal implementation mechanism queue, the default is 4096

The above parameters are configured in bootstrap.cnf. The changes in this configuration need to be inflated to take effect.

Related commands

1. show @@general_log; – Query the switch status and file path of the general log
2. enable @@general_log; – enable general log
3. disable @@general_log; – Turn off general log
4. reload @@general_log_file='/tmp/dble-general/general/general.log'; - modify the file path of the general log

The above commands are only supported by the management terminal and take effect immediately without restarting DBLE. In addition, the current settings will be retained when the DBLE is restarted next time and will not be lost.

Version

• The general log has been introduced from DBLE3.21.02 version; (previous version does not support)

performance

• After the general log is turned on, the performance loss of DBLE is between 3 and 5%; it is recommended to turn it on temporarily after troubleshooting some errors, and turn it off after debugging.

Output type

• Compared with Mysql, the output type of DBLE's general log is only File mode

Print format

• After opening, the record format (consistent with the general log format of Mysql)

/FAKE_PATH/mysqld, Version: FAKE_VERSION. started with:
Tcp port: 3320  Unix socket: FAKE_SOCK
Time                 Id Command    Argument
2021-08-05T16:24:53.558     1 Query   select * from no_sharding_t1
2021-08-05T16:25:00.210     1 Query   desc tb_grandson1
2021-08-05T16:26:32.774     1 Query   desc sharding_2_t1
2021-08-05T16:26:37.990     1 Query   select * from sharding_2_t1
2021-08-05T16:26:54.862     1 Query   insert into sharding_2_t1 values(1,1,1,1,1)

Implementation Mechanism

Refer to log4j for the specific implementation of the asynchronous placement mechanism of the general log in DBLE

Implementation

• Synchronization

  • Wrap sql as Log; Log contains Time, Id, Command, Argument and other information
  • Put Log in the generalLog queue; (If the queue is full, it will be blocked here until there is a free place in the queue)
  • Subsequent process processing of sql

• Asynchronous processing

  • Listen to the queue and process the queue
  • The byte size of the current Log <= Buffer buffer remaining space (default 4096), then enter the Buffer buffer first; subsequent buffer buffer fullness will trigger a disk placement
  • The byte size of the current Log> the remaining space of the Buffer cache, first place the Log in the Buffer cache + the current Log directly into the disk
  • Because the Disruptor is used (generalLog is actually a circular queue), the enqueue calculation will determine whether it is batch processing. If it is batch processing, it will also trigger the placement of the Log/current Log in the Buffer cache.
  • Rollover condition: Date changes (every day) || When the size of general.log>generalLogFileSize