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?

Reply

Please enter an answer
Tips:

You can mention users to notify them: @username

You can use Markdown to format your question. For more examples see the Markdown Cheatsheet.

> I’m a blockquote.

I’m a blockquote.

[I'm a link] (https://www.google.com)

I'm a link

**I am bold** I am bold

*I am italicized* I am italicized

Community Code of Conduct