On some WordPress systems a simple edit to your configuration file can speed up communications between WordPress and your database. If your WordPress installation is on Linux, and your DB_HOST setting is either localhost or 127.0.0.1, it may be possible to make use of Unix Domain Sockets to speed up your system. This tutorial will show you how do it, explain the reasoning behind it, and test the results.
The WordPress codex mentions this technique in the documentation on editing wp-config.php, but it does not go into much detail. If your web server has a typical setup on Linux, the procedure could be as simple as opening the /wp-config.php file in an editor, then searching for a line that looks like this:
define( 'DB_HOST', 'localhost' );
define( 'DB_HOST', '127.0.0.1' );
and replacing it with this:
define( 'DB_HOST', 'localhost:/var/run/mysqld/mysqld.sock' );
Using localhost is preferred over 127.0.0.1 because the underlying PHP function that connects to the database, mysqli_real_connect(), immediately assumes the connection is to the local host if the host name is either NULL or ‘localhost’. Using 127.0.0.1 will work too, but may be slightly less efficient. In the end, the operating system call that makes the connection does not use the host name, only the socket path.
If the setting shown above does not work, and you have shell access to your web host, you can verify the Unix Domain Socket name by using the lsof (List Open Files) command:
$ lsof -U | grep mysql mysqld 737 mysql 37u unix 0xffffa0a4b9628400 0t0 15179 /var/run/mysqld/mysqld.sock type=STREAM
This command will work for both mySQL and MariaDB databases. If you do not see an output from this command similar to what is shown above, then a Unix Domain Socket is not available to you. If you do get a listing, but the socket path is different from /var/run/mysqld/mysqld.sock, copy the correct socket path and paste it into your wp-config.php file after localhost: in the DB_HOST setting.
BAD INFORMATION DEBUNKED
There is some bad information floating around on the internet regarding this subject. The following should dispel any fears.
- Unix Domain Sockets are not reliable and data corruption is possible.
FALSE – At least in this case. While there is a type of UDS that is defined as unreliable (SOCK_DGRAM), the type=STREAM shown by the lsof command indicates that this UDS is using SOCK_STREAM data transmission which guarantees delivery of a stream of ordered data. It is highly unlikely that a database server would ever be configured with a datagram UDS. For reference, see the definition of SOCK_STREAM.
- On modern Linux systems, using TCP/IP with localhost is just as fast as Unix Domain Sockets.
FALSE – If the socket() function is called with a socket domain of AF_INET or AF_INET6 (internet protocol v4 or v6), it does not matter what IP address is used, all communications will have the overhead of the TCP/IP protocol. Unix Domain Sockets are a type of Inter-process Communication (IPC) which has far less processing overhead than TCP/IP.
- Even if there is less overhead, the difference in performance is not that great.
POSSIBLY – The TCP/IP local loopback driver in Linux does some optimizations, such as skipping checksum generation and verification. However, benchmarks show that switching from TCP/IP to Unix Domain Sockets will improve connection latency and data transfer speed. The more data being transferred per query, the greater the data transfer speed. Since WordPress uses large data sets and many database queries when saving or displaying a web page, using a UDS will improve performance. However, when the WordPress database is located on the local host, queries only take 3% to 6% of the execution time during a page load. You will probably only notice an improvement in speed on very busy, data-intensive web sites. The real answer depends on your system, your web traffic, and your point of view.
HOW IT WORKS AND TEST RESULTS
TCP/IP is a networking communications protocol. A three-way handshake must first occur to establish a TCP/IP network connection before any data can be sent. Then, any data sent is first broken into smaller packets (this is necessary for effective network bandwidth sharing). The source and destination is added to an envelope around each data packet. Error checking data is calculated and added to the envelope too. A sequence number is also added to the envelope so the receiver can correct any out of order data packets. Additional data is also added to each packet envelope to help identify and route the packet on local networks. The receiver must check and correct any out-of-order packets, check each packet for errors, send an acknowledgement for each packet, and reassemble the original block of data from the data within each packet. Note: For localhost connections checksum calculation and error checking is bypassed on modern Linux systems.
Unix Domain Sockets need none of this additional fragmenting and processing of data. Instead, they work more like reading and writing to a file. But unlike a regular file, the passing of data happens in the system memory instead of on a disk drive. Because of this, Unix Domain Sockets move data faster and more efficiently than TCP/IP.
The chart shows timing samples of a WordPress page loading using TCP/IP and Unix Domain Sockets as the database connection method. There was a 25% average speed improvement with Unix Domain Sockets. The error bars also show that Unix Domain Sockets reduced variance in execution time by 20%.
As mentioned earlier, less than 6% of the execution time is spent on database queries when WordPress is loading a page (if the database is located on the local host). The overall effect of switching from TCP/IP to UDS will hardly be noticeable except on very busy web sites, or data intensive web sites such as large e-commerce sites. But for such an easy change, why not save a little on processing time and energy usage?