This topic describes how to use on-premises DBGateway to collect full SQL statements and the difference in CPU utilization.

Precautions

  • If you use only the monitoring and alerting features but do not enable full SQL collection or slow SQL collection, DBGateway utilizes only 2% of CPU resources.
  • If you enable full SQL collection on a self-managed database hosted on a 4 vCPUs, 8 GiB memory ECS instance that runs Linux kernel 3.10, DBGateway utilizes only 10.7% of CPU resources when the transactions per second (TPS) of the database reaches 8000.

Full SQL collection solution

  • On Linux systems, after you enable full SQL collection, DBGateway creates the AF_PACKET socket and starts to capture packets. This socket does not affect data transmission.
  • DBGateway parses the captured packets based on the TCP/IP protocol. DBGateway analyzes only the inbound packets on MySQL ports and filters out packets on other ports.
  • The inbound packets on MySQL ports are assembled into complete SQL statements based on the MySQL protocol.
  • After DBGateway packages and compresses multiple SQL statements, it encrypts and uploads the SQL statements to a storage cluster of DAS.

CPU utilization

  • If you use only the monitoring and alerting features but do not enable full SQL collection or slow SQL collection, DBGateway utilizes only 2% of CPU resources.
  • If you enable full SQL collection, the CPU utilization of DBGateway varies with the operating system, hardware configuration, and MySQL workload. The following table lists sample data of online stress testing.
    • Test environment:
      • Hardware: The tested servers are I/O optimized ECS instances of the ecs.c5.xlarge type (4 vCPUs, 8 GiB memory).
      • Operating system:
        • The operating system of the ECS instance is 64-bit CentOS 6.8, and the kernel version is 2.6.
        • The operating system of the ECS instance is 64-bit CentOS 7.4, and the kernel version is 3.10.
        • The operating system of the ECS instance is 64-bit Ubuntu 16.04, and the kernel version is 4.4.
    • Test method:
      • Deploy a MySQL application on another ECS instance to create SQL statements and access a MySQL database. Initiate 40 session threads to select, insert, update, and delete data in the MySQL database.
      • Each SQL statement is 50 to 281 bytes in length.
      • Run the top command to obtain the CPU utilization and memory usage of the ECS instance. Then, record the results of the command.
    • Test results:

      For Linux systems that run kernel 3.2 or later, DBGateway captures data packets by using PACKET_MMAP. PACKET_MMAP allocates a kernel buffer in the kernel space. Then, PACKET_MMAP uses a user space program to call the mmap() function to map the kernel buffer to the user space.

      DBGateway supports the full SQL collection feature. For Linux systems that run kernel 3.2 or later, CPU overhead is significantly reduced.

      If you enable full SQL collection on a self-managed database hosted on a 4 vCPU, 8 GiB memory ECS instance that runs Linux kernel 3.10, DBGateway utilizes only 10.7% of CPU resources when the TPS of the database reaches 8000. The following table describes the percentage of CPU utilization for different kernels and TPS values.

      Note If you enable only basic monitoring, such as database performance monitoring and host performance monitoring, DBGateway utilizes only 2% of CPU resources.
      Kernel version 1000 TPS 2000 TPS 4000 TPS 8000 TPS 16000 TPS
      2.6 6.3% 8.6% 12.3% 21% 33.9%
      3.10 3.7% 4.7% 7% 10.7% 18%
      4.4 4% 5.3% 7% 11% 18.7%

Mappings between Linux distributions and Linux kernel versions

Distribution Version Kernel version
Redhat / CentOS RHEL 7.5 3.10.0-862
Redhat / CentOS RHEL 7.4 3.10.0-693
Redhat / CentOS RHEL 7.3 3.10.0-514
Redhat / CentOS RHEL 7.2 3.10.0-327
Redhat / CentOS RHEL 7.1 3.10.0-229
Redhat / CentOS RHEL 7.0 GA 3.10.0-123
Redhat / CentOS RHEL 7.0 Beta 3.10.0-54.0.1
Redhat / CentOS RHEL 6.10 2.6.32-754
Redhat / CentOS RHEL 6.9 2.6.32-696
Redhat / CentOS RHEL 6.8 2.6.32-642
Redhat / CentOS RHEL 6.7 2.6.32-573
Redhat / CentOS RHEL 6.6 2.6.32-504
Redhat / CentOS RHEL 6.5 2.6.32-431
Redhat / CentOS RHEL 6.4 2.6.32-358
Redhat / CentOS RHEL 6.3 2.6.32-279
Redhat / CentOS RHEL 6.2 2.6.32-220
Redhat / CentOS RHEL 6.1 2.6.32-131.0.15
Redhat / CentOS RHEL 6.0 2.6.32-71
Ubuntu 14.04 LTS 3.13
Ubuntu 16.04 LTS 4.4
Ubuntu 18.04 LTS 4.15
Aliyun Linux 17.0-123 4.4
Debian 8 3.16
Debian 9 4.9
SUSE Linux Enterprise 11 2.6.27
SUSE Linux Enterprise 11.1 2.6.32
SUSE Linux Enterprise 11.2 3.0.13
SUSE Linux Enterprise 11.3 3.0.76
SUSE Linux Enterprise 11.4 3.0.101
SUSE Linux Enterprise 12 3.12
SUSE Linux Enterprise 12.1 3.12
SUSE Linux Enterprise 12.2 4.4
SUSE Linux Enterprise 12.3 4.4
SUSE Linux Enterprise 15 4.12
OpenSUSE 42.1 4.1.12
OpenSUSE 42.2 4.4
OpenSUSE 42.3 4.4
CoreOS 1745.7.0 4.14.48
CoreOS 1465.8.0 4.12.14