Local or remote MySQL Server?

When there are MySQL performance problems, the first solution we hear is that of separate the application or frontend part from the MySQL Server DB. This approach is certainly academically and theoretically correct and appreciable, however, as in many other areas, theory does not always coincide with practice and in the attempt to do well or better, we end up with disaster.

Understanding the advantages of having a local rather than remote MySQL server helps you to consciously ponder what to do and what urban traps and legends you need to be careful of.

Even in cases where the MySQL Server and the application are on the same machine, it is necessary to pay attention and an eye to how the connection will be established, whether through a socket for example, or through a TCP / IP connection on port 3306.

What is a Unix socket?

Un socket, in computer science, indicates a software abstraction designed to use standard and shared APIs for transmitting and receiving data over a network or as a mechanism for IPC. It is the point where the application code of a process accesses the communication channel by means of a door, obtaining a communication between processes working on two physically separate machines. From the point of view of a programmer, a socket is a particular object on which to read and write the data to be transmitted or received.

Socket families

The types of protocols used since socket, they define the family (or domain). We can distinguish, for example, two important families:

• AF_INET: communication between remote hosts, via the Internet;
• AF_UNIX: communication between local processes, on Unix machines. This family is also called Unix Domain Sockets.

Unix Domain Sockets

Un local domain socket o local socket, in computer science, we mean one end of a channel interprocess communication similar to a network connection, but based on sharing data in the file system. Since this communication channel is not implemented on network protocols, it can only be used for communication between processes residing on the same host. Local sockets of this kind are found in the POSIX and Unix operating systems, and are known in each of these contexts with specific names, such as Unix domain socket (Unix domain sockets) or POSIX local IPC socket (POSIX local IPC sockets).

IP or TCP socket

IP sockets (especially TCP / IP sockets) are a mechanism that allows communication between processes on the network. In some cases, TCP / IP sockets can be used to communicate with processes running on the same computer (using the loopback interface), i.e. the famous 127.0.0.1 address

The main differences between Unix Domain and TCP Socket families

UNIX domain sockets know they are running and communicating on the same system, so they can avoid some checks and operations (such as routing); which makes them faster and lighter than IP sockets.

Additionally, UNIX domain sockets are subject to file system permissions, while TCP sockets can only be controlled at the packet filter level.

So, if you plan to communicate with processes on the same host, using Unix Sockets is arguably a better option than IP sockets.

When you connect to a local MySQL instance, you have two commonly used methods: use the TCP / IP protocol to connect to the local address - "localhost" or 127.0.0.1 - or use Unix Domain Sockets .

If you have a choice (if your application supports both methods), use Unix Domain Socket as it is both safer and more efficient.

As more efficient, though? I haven't looked into this for years, so let's see how a modern version of MySQL works on relatively modern hardware and modern Linux.

Objective of the test

Determine if there is a significant difference when using local TCP versus local socket connection to MySQL.

What we will test

• MySQL 8.0 (Percona Server 8.0.22-13)

The following tests will be performed:

• local on the socket/var/run/mysqld/mysqld.sock
• local with TCP connection
• Remote TCP
• fake remote socket withsocat

Regarding the Socat proxy forwarding we have already talked about it in the guide.

The test itself will be performed with sysbench.

#!/usr/bin/env bash 
sysbench \     /usr/share/sysbench/oltp_read_only.lua \     --threads="2" \     --tables=10 \     --table-size=1000000 \     --report-interval=5 \     --rand-type=pareto \     --forced-shutdown=1 \     --time=300 \     --events=0 \     --point-selects=25 \     --range_size=5 \     --skip_trx=on \     --percentile=95  \     --mysql-host=mysql.to.test \     --mysql-port=3306 \     --mysql-user=bench \     --mysql-password=our-fancy-bench-password \     --mysql-db=bench \     --mysql-storage-engine=INNODB \     run

We will only run with 2 threads, so we always compare the same information.

We will review the transactions / queries per second and what the 95th percentile latency is for the connections used.

use of socat

On the MySQL server machine we deactivate the network on the MySQL server.

We will comment on the TCP listening settings and add skip-networkingin our [mysqld]section of the config.

#port         = 3306
#bind-address = 0.0.0.0
skip-networking

On the MySQL server machine we will run socat to expose our socket on port 3306.

socat TCP-LISTEN:3306,reuseaddr,fork \   UNIX-CONNECT:/var/run/mysqld/mysqld.sock

On the remote machine we will also use socat so we can pretend to connect to the local machine socket.

socat UNIX-LISTEN:/var/run/mysqld/mysqld.sock,\ fork,reuseaddr,unlink-early,\ user=mysql,group=mysql,mode=777 \   TCP:mysql.to.test:3306

Note : linewrapping of the comma separated string must not have whitespace!

This way we can pretend to connect to the localhost socket. We will see how well it performs.

local vs remote

We compare the local and remote results. We will use the local socket connection as the baseline and compare the rest with that.

Local socket results:

SQL statistics:
    queries performed:
        read:                            10084576
        write:                           0
        other:                           0
        total:                           10084576
    transactions:                        347744 (1159.13 per sec.)
    queries:                             10084576 (33614.79 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          300.0029s
    total number of events:              347744

Latency (ms):
         min:                                    1.04
         avg:                                    1.72
         max:                                   20.57
         95th percentile:                        2.18
         sum:                               599516.93

Threads fairness:
    events (avg/stddev):           173872.0000/208.00
    execution time (avg/stddev):   299.7585/0.00

Local TCP Results:

SQL statistics:
    queries performed:
        read:                            7832697
        write:                           0
        other:                           0
        total:                           7832697
    transactions:                        270093 (900.29 per sec.)
    queries:                             7832697 (26108.54 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          300.0039s
    total number of events:              270093

Latency (ms):
         min:                                    1.36
         avg:                                    2.22
         max:                                   20.91
         95th percentile:                        2.81
         sum:                               599599.56

Threads fairness:
    events (avg/stddev):           135046.5000/33.50
    execution time (avg/stddev):   299.7998/0.00

Remote TCP Results:

SQL statistics:
    queries performed:
        read:                            2839042
        write:                           0
        other:                           0
        total:                           2839042
    transactions:                        97898  (326.32 per sec.)
    queries:                             2839042 (9463.20 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          300.0074s
    total number of events:              97898

Latency (ms):
         min:                                    3.33
         avg:                                    6.13
         max:                                  225.58
         95th percentile:                        7.84
         sum:                               599783.11

Threads fairness:
    events (avg/stddev):           48949.0000/446.00
    execution time (avg/stddev):   299.8916/0.00

Remote socket results:

SQL statistics:
    queries performed:
        read:                            2255707
        write:                           0
        other:                           0
        total:                           2255707
    transactions:                        77783  (259.16 per sec.)
    queries:                             2255707 (7515.59 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          300.1356s
    total number of events:              77783

Latency (ms):
         min:                                    4.43
         avg:                                    7.71
         max:                                  241.09
         95th percentile:                        9.73
         sum:                               599962.48

Threads fairness:
    events (avg/stddev):           38891.5000/228.50
    execution time (avg/stddev):   299.9812/0.07

Conclusions on performance

When we first looked at the graphs it immediately felt like “ok, local TCP is reasonably ok”.

But if we look at the numbers in more detail, we see that we have an additional 20% throughput if we connect to a local socket.

Again, once we introduce a TCP connection on the cable, the throughput drops dramatically.

Since Unix Domain Sockets are much simpler and optimized for local process communication, one would expect them to perform better than TCP / IP on the loopback interface. They actually perform much better! So if you have a choice, use Unix Domain Sockets to connect to your local MySQL system sand you really want maximum throughput from your MySQL database.