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

Overview

  • If you only use the monitoring and alerting features but do not enable full SQL collection or slow SQL collection, DBGateway consumes only 2% of CPU resources.
  • If you enable full SQL collection on a self-managed database hosted on a 4 vCPU, 8 GiB memory ECS instance that runs kernel 3.10, DBGateway consumes only 10.7% of CPU resources at 8,000 TPS.

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 and captures 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 only use the monitoring and alerting features but do not enable full SQL collection or slow SQL collection, DBGateway consumes 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.

If you only use the monitoring and alerting features but do not enable full SQL collection or slow SQL collection, DBGateway consumes 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 must be I/O optimized ECS instances of the ecs.c5.xlarge type (4 vCPUs, 8 GiB memory).
    • Operating system:
      • The ECS instance that runs kernel 2.6 must use CentOS 6.8 64-bit operating system.
      • The ECS instance that runs kernel 3.10 must use CentOS 7.4 64-bit operating system.
      • The ECS instance that runs kernel 4.4 must use Ubuntu 16.04 64-bit operating system.
  • 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.
    • Create an SQL statement that is 50 to 281 bytes in length.
    • Run the top command to record the CPU utilization and memory usage of the ECS instance.
  • 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, DBGateway eliminates the overhead of CPU utilization.

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

    Note If you enable only basic monitoring, such as database performance monitoring and host performance monitoring, DBGateway consumes only 2% of CPU resources.
    Kernel version 1,000 TPS 2,000 TPS 4,000 TPS 8,000 TPS 16,000 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 release versions and Linux kernel versions

Release version Version number 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