July 6 2022

Local or remote MySQL Server?

What are the benefits of having MySQL installed on localhost rather than on a remote node? Let's see it together.

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

 

gender transactions / sec queries / sec 95% latency (ms) percentage
Local socket 1159.13 33614.79 2.18 100%
Local TCP 900.29 26108.54 2.81 77,7%
Remote TCP 326.32 9463.20 7.84 28,2%
Remote "socket" 259.16 7515.59 9.73 22,4%

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.

 

About the author

Do you have doubts? Don't know where to start? Contact us!

We have all the answers to your questions to help you make the right choice.

Chat with us

Chat directly with our presales support.

0256569681

Contact us by phone during office hours 9:30 - 19:30

Contact us online

Open a request directly in the contact area.

INFORMATION

Managed Server Srl is a leading Italian player in providing advanced GNU/Linux system solutions oriented towards high performance. With a low-cost and predictable subscription model, we ensure that our customers have access to advanced technologies in hosting, dedicated servers and cloud services. In addition to this, we offer systems consultancy on Linux systems and specialized maintenance in DBMS, IT Security, Cloud and much more. We stand out for our expertise in hosting leading Open Source CMS such as WordPress, WooCommerce, Drupal, Prestashop, Joomla, OpenCart and Magento, supported by a high-level support and consultancy service suitable for Public Administration, SMEs and any size.

Red Hat, Inc. owns the rights to Red Hat®, RHEL®, RedHat Linux®, and CentOS®; AlmaLinux™ is a trademark of AlmaLinux OS Foundation; Rocky Linux® is a registered trademark of the Rocky Linux Foundation; SUSE® is a registered trademark of SUSE LLC; Canonical Ltd. owns the rights to Ubuntu®; Software in the Public Interest, Inc. holds the rights to Debian®; Linus Torvalds owns the rights to Linux®; FreeBSD® is a registered trademark of The FreeBSD Foundation; NetBSD® is a registered trademark of The NetBSD Foundation; OpenBSD® is a registered trademark of Theo de Raadt. Oracle Corporation owns the rights to Oracle®, MySQL®, and MyRocks®; Percona® is a registered trademark of Percona LLC; MariaDB® is a registered trademark of MariaDB Corporation Ab; REDIS® is a registered trademark of Redis Labs Ltd. F5 Networks, Inc. owns the rights to NGINX® and NGINX Plus®; Varnish® is a registered trademark of Varnish Software AB. Adobe Inc. holds the rights to Magento®; PrestaShop® is a registered trademark of PrestaShop SA; OpenCart® is a registered trademark of OpenCart Limited. Automattic Inc. owns the rights to WordPress®, WooCommerce®, and JetPack®; Open Source Matters, Inc. owns the rights to Joomla®; Dries Buytaert holds the rights to Drupal®. Amazon Web Services, Inc. holds the rights to AWS®; Google LLC holds the rights to Google Cloud™ and Chrome™; Facebook, Inc. owns the rights to Facebook®; Microsoft Corporation holds the rights to Microsoft®, Azure®, and Internet Explorer®; Mozilla Foundation owns the rights to Firefox®. Apache® is a registered trademark of The Apache Software Foundation; PHP® is a registered trademark of the PHP Group. CloudFlare® is a registered trademark of Cloudflare, Inc.; NETSCOUT® is a registered trademark of NETSCOUT Systems Inc.; ElasticSearch®, LogStash®, and Kibana® are registered trademarks of Elastic NV This site is not affiliated, sponsored, or otherwise associated with any of the entities mentioned above and does not represent any of these entities in any way. All rights to the brands and product names mentioned are the property of their respective copyright holders. Any other trademarks mentioned belong to their registrants. MANAGED SERVER® is a registered trademark at European level by MANAGED SERVER SRL Via Enzo Ferrari, 9 62012 Civitanova Marche (MC) Italy.

JUST A MOMENT !

Would you like to see how your WooCommerce runs on our systems without having to migrate anything? 

Enter the address of your WooCommerce site and you will get a navigable demonstration, without having to do absolutely anything and completely free.

No thanks, my customers prefer the slow site.
Back to top