Best Practices for Using HAProxy in TiDB

This document describes best practices for configuration and usage of HAProxy in TiDB. HAProxy provides load balancing for TCP-based applications. From TiDB clients, you can manipulate data just by connecting to the floating virtual IP address provided by HAProxy, which helps to achieve load balance in the TiDB server layer.

HAProxy Best Practices in TiDB

Use HAProxy - 图2

Note

The minimum version of HAProxy that works with all versions of TiDB is v1.5. Between v1.5 and v2.1, you need to set the post-41 option in mysql-check. It is recommended to use HAProxy v2.2 or newer.

HAProxy overview

HAProxy is free, open-source software written in C language that provides a high availability load balancer and proxy server for TCP and HTTP-based applications. Because of its fast and efficient use of CPU and memory, HAProxy is now widely used by many well-known websites such as GitHub, Bitbucket, Stack Overflow, Reddit, Tumblr, Twitter, Tuenti, and AWS (Amazon Web Services).

HAProxy is written in the year 2000 by Willy Tarreau, the core contributor to the Linux kernel, who is still responsible for the maintenance of the project and provides free software updates in the open-source community. In this guide, HAProxy 2.6 is used. It is recommended to use the latest stable version. See the released version of HAProxy for details.

Basic features

  • High Availability: HAProxy provides high availability with support for a graceful shutdown and a seamless switchover;
  • Load Balancing: Two major proxy modes are supported: TCP, also known as layer 4, and HTTP, also known as layer 7. No less than 9 load balancing algorithms are supported, such as roundrobin, leastconn and random;
  • Health Check: HAProxy periodically checks the status of HTTP or TCP mode of the server;
  • Sticky Session: HAProxy can stick a client to a specific server for the duration when the application does not support sticky sessions;
  • SSL: HTTPS communication and resolution are supported;
  • Monitoring and Statistics: Through the web page, you can monitor the service state and traffic flow in real time.

Before you begin

Before you deploy HAProxy, make sure that you meet the hardware and software requirements.

Hardware requirements

According to the HAProxy documentation, the minimum hardware configuration for HAProxy is shown in the following table. Under the Sysbench oltp_read_write workload, the maximum QPS for this configuration is about 50K. You can increase the server configuration according to your load balancing environment.

Hardware resourceMinimum specification
CPU2 cores, 3.5 GHz
Memory4 GB
Storage50 GB (SATA)
Network Interface Card10G Network Card

Software requirements

You can use the following operating systems and make sure the required dependencies are installed. If you use yum to install HAProxy, the dependencies are installed along with it and you do not need to separately install them again.

Operating systems

Linux distributionVersion
Red Hat Enterprise Linux7 or 8
CentOS7 or 8
Oracle Enterprise Linux7 or 8
Ubuntu LTS18.04 or later versions

Use HAProxy - 图3

Note

Dependencies

  • epel-release
  • gcc
  • systemd-devel

To install the dependencies above, run the following command:

  1. yum -y install epel-release gcc systemd-devel

Deploy HAProxy

You can easily use HAProxy to configure and set up a load-balanced database environment. This section shows general deployment operations. You can customize the configuration file based on your actual scenario.

Install HAProxy

  1. Download the package of the HAProxy 2.6.2 source code:

    1. wget https://www.haproxy.org/download/2.6/src/haproxy-2.6.2.tar.gz

  2. Extract the package:

    1. tar zxf haproxy-2.6.2.tar.gz

  3. Compile the application from the source code:

    1. cd haproxy-2.6.2
    2. make clean
    3. make -j 8 TARGET=linux-glibc USE_THREAD=1
    4. make PREFIX=${/app/haproxy} SBINDIR=${/app/haproxy/bin} install  # Replace `${/app/haproxy}` and `${/app/haproxy/bin}` with your custom directories.

  4. Reconfigure the profile:

    1. echo 'export PATH=/app/haproxy/bin:$PATH' >> /etc/profile
    2. . /etc/profile

  5. Check whether the installation is successful:

    1. which haproxy

HAProxy commands

Execute the following command to print a list of keywords and their basic usage:

  1. haproxy --help
OptionDescription
-vReports the version and build date.
-vvDisplays the version, build options, libraries versions and usable pollers.
-dEnables debug mode.
-dbDisables background mode and multi-process mode.
-dM [<byte>]Forces memory poisoning, which means that each and every memory region allocated with malloc() or pool_alloc2() will be filled with <byte> before being passed to the caller.
-VEnables verbose mode (disables quiet mode).
-DStarts as a daemon.
-C <dir>Changes to directory <dir> before loading configuration files.
-WMaster-worker mode.
-qSets “quiet” mode: This disables some messages during the configuration parsing and during startup.
-cOnly performs a check of the configuration files and exits before trying to bind.
-n <limit>Limits the per-process connection limit to <limit>.
-m <limit>Limits the total allocatable memory to <limit> megabytes across all processes.
-N <limit>Sets the default per-proxy maxconn to <limit> instead of the builtin default value (usually 2000).
-L <name>Changes the local peer name to <name>, which defaults to the local hostname.
-p <file>Writes all processes’ PIDs into <file> during startup.
-deDisables the use of epoll(7). epoll(7) is available only on Linux 2.6 and some custom Linux 2.4 systems.
-dpDisables the use of poll(2). select(2) might be used instead.
-dSDisables the use of splice(2), which is broken on older kernels.
-dRDisables SO_REUSEPORT usage.
-drIgnores server address resolution failures.
-dVDisables SSL verify on the server side.
-sf <pidlist>Sends the “finish” signal to the PIDs in pidlist after startup. The processes which receive this signal wait for all sessions to finish before exiting. This option must be specified last, followed by any number of PIDs. Technically speaking, SIGTTOU and SIGUSR1 are sent.
-st <pidlist>Sends the “terminate” signal to the PIDs in pidlist after startup. The processes which receive this signal terminate immediately, closing all active sessions. This option must be specified last, followed by any number of PIDs. Technically speaking, SIGTTOU and SIGTERM are sent.
-x <unix_socket>Connects to the specified socket and retrieves all the listening sockets from the old process. Then, these sockets are used instead of binding new ones.
-S <bind>[,<bind_options>…]In master-worker mode, creates a master CLI. This CLI enables access to the CLI of every worker. Useful for debugging, it’s a convenient way of accessing a leaving process.

For more details on HAProxy command line options, refer to Management Guide of HAProxy and General Commands Manual of HAProxy.

Configure HAProxy

A configuration template is generated when you use yum to install HAProxy. You can also customize the following configuration items according to your scenario.

  1. global                                     # Global configuration.
  2.    log         127.0.0.1 local2            # Global syslog servers (up to two).
  3.    chroot      /var/lib/haproxy            # Changes the current directory and sets superuser privileges for the startup process to improve security.
  4.    pidfile     /var/run/haproxy.pid        # Writes the PIDs of HAProxy processes into this file.
  5.    maxconn     4096                        # The maximum number of concurrent connections for a single HAProxy process. It is equivalent to the command-line argument "-n".
  6.    nbthread    48                          # The maximum number of threads. (The upper limit is equal to the number of CPUs)
  7.    user        haproxy                     # Same with the UID parameter.
  8.    group       haproxy                     # Same with the GID parameter. A dedicated user group is recommended.
  9.    daemon                                  # Makes the process fork into background. It is equivalent to the command line "-D" argument. It can be disabled by the command line "-db" argument.
  10.    stats socket /var/lib/haproxy/stats     # The directory where statistics output is saved.
  12. defaults                                   # Default configuration.
  13.    log global                              # Inherits the settings of the global configuration.
  14.    retries 2                               # The maximum number of retries to connect to an upstream server. If the number of connection attempts exceeds the value, the backend server is considered unavailable.
  15.    timeout connect  2s                     # The maximum time to wait for a connection attempt to a backend server to succeed. It should be set to a shorter time if the server is located on the same LAN as HAProxy.
  16.    timeout client 30000s                   # The maximum inactivity time on the client side.
  17.    timeout server 30000s                   # The maximum inactivity time on the server side.
  19. listen admin_stats                         # The name of the Stats page reporting information from frontend and backend. You can customize the name according to your needs.
  20.    bind 0.0.0.0:8080                       # The listening port.
  21.    mode http                               # The monitoring mode.
  22.    option httplog                          # Enables HTTP logging.
  23.    maxconn 10                              # The maximum number of concurrent connections.
  24.    stats refresh 30s                       # Automatically refreshes the Stats page every 30 seconds.
  25.    stats uri /haproxy                      # The URL of the Stats page.
  26.    stats realm HAProxy                     # The authentication realm of the Stats page.
  27.    stats auth admin:pingcap123             # User name and password in the Stats page. You can have multiple user names.
  28.    stats hide-version                      # Hides the version information of HAProxy on the Stats page.
  29.    stats admin if TRUE                     # Manually enables or disables the backend server (supported in HAProxy 1.4.9 or later versions).
  31. listen tidb-cluster                        # Database load balancing.
  32.    bind 0.0.0.0:3390                       # The Floating IP address and listening port.
  33.    mode tcp                                # HAProxy uses layer 4, the transport layer.
  34.    balance leastconn                       # The server with the smallest number of connections receives the connection. "leastconn" is recommended where long sessions are expected, such as LDAP, SQL and TSE, rather than protocols using short sessions, such as HTTP. The algorithm is dynamic, which means that server weights might be adjusted on the fly for slow starts for instance.
  35.    server tidb-1 10.9.18.229:4000 check inter 2000 rise 2 fall 3       # Detects port 4000 at a frequency of once every 2000 milliseconds. If it is detected as successful twice, the server is considered available; if it is detected as failed three times, the server is considered unavailable.
  36.    server tidb-2 10.9.39.208:4000 check inter 2000 rise 2 fall 3
  37.    server tidb-3 10.9.64.166:4000 check inter 2000 rise 2 fall 3

To check the source IP address using SHOW PROCESSLIST, you need to configure the PROXY protocol to connect to TiDB.

  1.    server tidb-1 10.9.18.229:4000 send-proxy check inter 2000 rise 2 fall 3       
  2.    server tidb-2 10.9.39.208:4000 send-proxy check inter 2000 rise 2 fall 3
  3.    server tidb-3 10.9.64.166:4000 send-proxy check inter 2000 rise 2 fall 3

Use HAProxy - 图4

Note

Before using the PROXY protocol, you need to configure proxy-protocol.networks in the configuration file of the TiDB server.

Start HAProxy

To start HAProxy, run haproxy. /etc/haproxy/haproxy.cfg is read by default (recommended).

  1. haproxy -f /etc/haproxy/haproxy.cfg

Stop HAProxy

To stop HAProxy, use the kill -9 command.

  1. Run the following command:

    1. ps -ef | grep haproxy

  2. Terminate the process of HAProxy:

    1. kill -9 ${haproxy.pid}