MariaDB 10.5 really slow when listen on private IP
Hi,
I've being doing some tests with MariaDB running on the same host as my web server and listening on 127.0.0.1 and everything is extremely fast.
However if I try to connect form my web server to a different VM where MariaDB is running listening only on the private IP:
[mysqld]
skip-networking = 0
bind-address = 192.168.X.X
skip-name-resolve
Then everything becomes extremely slow. For instance same exact website, same queries, same data running web & DB locally around 300 ms
However when web server is on one Linode and DB on another the same page, queries and data takes around 3 seconds if I disable TLS, if I enable TLS it becomes even worse around 9 seconds.
Both Linodes are on the same datacenter (Newark) and both of them have private IPs enabled. I can't use VLANs yet since VLANs are not yet available for Newark datacenter.
This is how I'm running queries from web server to MariaDB from my application. Method wrapping the execution of the queries.
PHP Code
function query( $query ) {
$link = mysqli_init();
mysqli_ssl_set(
$link,
'/etc/mysql/ssl/client-key.pem',
'/etc/mysql/ssl/client-cert.pem',
'/etc/mysql/ssl/ca-cert.pem',
NULL,
NULL
);
mysqli_real_connect($link, $db_server, $db_user, $db_pass, $db_name, $db_port,
NULL,
MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT
);
@mysqli_set_charset( $link, 'utf8' );
$result = mysqli_query( $link, $query );
mysqli_close( $link );
return $result;
}
I have repeated the tests with the firewall fully disabled (just to be 100% sure) and all iptables rules deleted and default policy accept and I've got the same results, 3 seconds without TLS, 9 seconds with TLS.
Any idea why when I try to connect to MariaDB using the private IP (with or without TLS) it's so slow?
Am I missing something to enforce both Linodes to use private IPs?
perhaps I'm missing something at network level or perhaps I'm missing some required configuration at MariaDB level when using private IPs?
Any idea or suggestion is welcome.
Thanks in advance !
1 Reply
Factors to Consider
- Time to establish connections (if you have a lot of queries, connection overhead can multiply rapidly). You may wish to explore persistent database connections.
$begin_connect = microtime(true);
$link = mysqli_init();
$end_connect = microtime(true);
$begin_query = microtime(true);
$result = mysqli_query( $link, $query );
$end_query = microtime(true);
$elapsed_connect = $end_connect - $begin_connect;
$elapsed_query = $end_query - $begin_query;
Software version differences could come into play, although this is unlikely
MariaDB
Are the same optimizations, indexes, etc. in place?PHP
Are the same configurations being used?Apache
Are the same modules loaded on each?
Are the same features enabled on each such as page compression?
Possible disparity of the sizes of the Linodes or datasets
Possible Host Hardware Differences
lscpu
One database could be running completely in memory while the other one is swapping data to and from disk
top
free -m
df -h
What is the latency over the network?