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?