Mysql – MariaDB Concurrent Connections with Update Statement

mariadbMySQL

I have two high-performance servers (Xeon 2.1 Ghz, 64GB RAM, SSD 120GB) with private IPs (i.e 10.0.0*) and 100Mb/s bandwidth:

SERVER1: Nginx + PHP7.0-FPM (latest versions)
SERVER2: MariaDB (latest version stable)

The database has only 1 row and this is the table structure:

CREATE TABLE users (
       `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
       `added` int(11) DEFAULT 0,
       `email` VARCHAR(150) NOT NULL,
       `files` BIGINT DEFAULT 1,
       UNIQUE KEY `email` (`email`),
       INDEX email_index (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

This is the PHP script that makes just one select and one update to the first (and only) item with ID 1:

$mysqli = new mysqli("p:10.0.XX.XX", "username", "password", "database");

if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

echo "Connected to the database!"."\n";

$result = $mysqli->query("select files from users where id = 1");

if($result)
{
    $row = $result->fetch_array(MYSQLI_ASSOC);
}
else
{
    echo "<p>Failed to select user fields</p>";
    exit();
}

$mysqli->query("update users set files='".intval(intval($row['files'])+1)."' where id = 1");

$mysqli->close();

Here is the changes I made to the default MariaDB config file:

[mysqld]
skip-name-resolve
bind-address            = 10.0.XX.XX
max_connections         = 25000
max_allowed_packet      = 1G
innodb_buffer_pool_size = 55G

** Bin logs is disabled, flush trx is set to 0, general logging is disabled, only slow queries are logged **

** I am not interested in ACID or other redundancy options **

Everything works perfectly without the "update" query, and can handle up to 10K concurrent connections according to https://loader.io/ tests. The problem is when the PHP script has to do the update statement, looks like all "update" queries are queued in the mariaDB server and it takes a lot of time to finish, more than 30 minutes (I then rebooted the mariaDB server because didn't want to wait more). So my questions are:

1) Is there a way to make "update" statement to not be queued and to be processed directly?

2) How many concurrent connections can the 64GB RAM + SSD mariaDB server handle considering the "update" statement?

Best Answer

You cannot have "concurrent" updates of a single row. The best you can do is to make them fast.

If you don't need the value, do it in a single statement:

UPDATE user SET files = files + 1 WHERE id = 1

Also, be sure that autocommit=ON is configured.

This will easily handle 100 increments per second on a spinning drive. If you need even more speed, then set

innodb_flush_log_at_trx_commit = 2

This will cut back significantly on disk hits, at the potential loss of data for up to a second (in the case of a power failure).

max_connections = 25000 is grossly unreasonable; if you get more than a few dozen connections stumbling over each other, the system will appear to 'hang'. At which point, more and more connections will be started, while the running connections run slower and slower. This will because the OS is sharing the CPU, etc, among too many threads to actually get anything completed.

If you need thousands/second, I'll provide you with some other ideas. (Note: I say "per second", not "concurrently".)

Related Question