PHP script fast on local MariabDB 10.2.44 but significantly slower on remote MariabDB 10.5.22

Question

I have a PHP 7.2 script which uses MySQLi to connect to a MariaDB database. On an older host running CentOS 7 and MariabDB 10.2.44, the script running locally completes in about 10 seconds. This is what the Mariadb general log shows:

85338332 Connect    myuser@localhost as anonymous on myserver
85338332 Quit   
85338333 Connect    myuser@localhost as anonymous on myserver
85338333 Quit   
85338334 Connect    myuser@localhost as anonymous on myserver
85338334 Quit   
85338330 Query  INSERT INTO auditlog(name, userid, ipaddress, useragent, notes) values ("recordtype", "31", "10.20.30.123", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36", "myfile.csv")
85338330 Quit   
85338335 Connect    myuser@localhost as anonymous on myserver
85338336 Connect    myuser@localhost as anonymous on myserver
85338336 Quit   
85338337 Connect    myuser@localhost as anonymous on myserver
85338337 Quit   
85338335 Query  SELECT COUNT(id) as id FROM case_file 
        WHERE code = '18676'
        AND modelcode = 'PW786756'
85338338 Connect    myuser@localhost as anonymous on myserver
85338338 Quit   
85338339 Connect    myuser@localhost as anonymous on myserver
85338339 Quit
... and so on.

I have migrated this script to a dedicated Rocky 8 Linux host which connects to MariabDB 10.5.22 on a separate host within the same network. Ping times between hosts average 0.5 ms. However, the same script takes over 80 seconds to complete, and on occasions times out. This is what the Mariadb general log shows:

6124914 Connect [email protected] on myserver using TCP/IP
6124914 Quit    
6124915 Connect [email protected] on myserver using TCP/IP
6124915 Quit    
6124916 Connect [email protected] on myserver using TCP/IP
6124916 Quit    
6124912 Query   INSERT INTO auditlog(name, userid, ipaddress, useragent, notes) values ("recordtype", "31", "10.20.30.123", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36", "myfile.csv")
6124912 Quit    
6124917 Connect [email protected] on myserver using TCP/IP
6124918 Connect [email protected] on myserver using TCP/IP
6124918 Quit    
6124919 Connect [email protected] on myserver using TCP/IP
6124919 Quit    
6124917 Query   SELECT COUNT(id) as id FROM case_file 
        WHERE code = '18676'
        AND modelcode = 'PW786756'
6124920 Connect [email protected] on myserver using TCP/IP
6124920 Quit    
6124921 Connect [email protected] on myserver using TCP/IP
6124921 Quit

Also, I noticed on the new host where the PHP script is running, the port/sockets in use increases significantly from approximately 60 to over 22000 by using a command such as this:

netstat -nt | grep -i '10.50.30.40:3306' | wc -l

I have increased the port range on the new host:

# sysctl net.ipv4.ip_local_port_range
net.ipv4.ip_local_port_range = 15000    60999

The queries themselves appear to be running quickly as they don't appear in the slow query log configured to capture any queries that take longer than 1 second (long_query_time = 1.00000).

Any ideas why the script is taking longer to run?

Answer 1

It might be possible that you changed two things at once: from a local server to a remote server and from UNIX domain sockets to TCP sockets. This might explain why it's suddenly slower and why you see more TCP sockets.

MariaDB/MySQL usually use UNIX domain sockets when you use localhost as the host address. They're faster than TCP sockets for local connections and have other benefits such as authentication via credentials passed by the OS.

In the second log output, you see using TCP/IP which is missing from the first one. To see if this is the case, you could try to switch your connection string in PHP to use 127.0.0.1 instead of localhost to make sure it's actually using a TCP socket. Usually this makes the connector use a TCP socket instead of a UNIX domain socket. If this was the case, you should see a slight slowdown even with your local server.

Another thing is that while a ping of 0.5 ms is very fast, a localhost connection is still an order of magnitude faster. Pinging 127.0.0.1 on my laptop gives me 0.04 ms whereas pinging a second server on the same LAN gives me about 0.7 ms. If the query execution in the script is serial, this means that each step now takes roughly ten times more to complete.

Those 22000 TCP sockets are a suggestion that the script is opening up a lot of connections and then closing them very quickly, perhaps to execute only one or two queries. This is a fairly common pattern in PHP applications and which, as you've found out, might cause problems with TCP socket running out.

If we assume that those TCP socket are for new MariaDB connections, each connection to MariaDB involves a few exchanges with the server before a query can be executed. If we assume an ideal connection phase (Handshake, HandshakeResponse and the OK response), there's roughly two roundtrips for one query. Simplifying that into the connection creation and query taking about twice the ping, we get 0.04 ms * 22000 * 2 = 1760 ms for the local connection and 0.5 ms * 22000 * 2 = 22000 ms for the remote one. These numbers do line up at least slightly with your numbers.

You can easily reproduce the slowdown of remote servers with a simple shell script like this:

#!/bin/bash

USER="foo"
PASSWORD="bar"
HOST="localhost"

for ((i=0;i<22000;i++))
do
    mariadb -h HOST -u "$USER" -p"$PASSWORD" -e "select 1" > /dev/null;
done

Comparing the execution times between your local server and the remote one should show that the remote one is a lot slower.

Edit:

Here's a snippet that shows the overhead of connection creation. Swapping out the loop to this will pipe the SQL into the same connection instead of opening a new one for every query:

for ((i=0;i<22000;i++))
do
    echo "select 1;"
done | mariadb -h HOST -u "$USER" -p"$PASSWORD" > /dev/null;

Comparing these two should give you a rough estimate of how much your slowness comes from the overhead of opening connections.