1

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?

  • Read about 1) concurrency and locking and 2) connection pooling. – mustaccio Jan 25 '17 at 20:34
1

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".)

  • Thanks for the suggestions, will try innodb_flush_log_at_trx_commit = 2 and autocommit=ON in a few hours. Please provide some ideas to handle thousands connections per second so that it can support more "update" statements per seconds. – user2972081 Jan 25 '17 at 23:09
  • Did you also get rid of the SELECT? – Rick James Jan 25 '17 at 23:46
  • Before discussing how to do thousands of connections/sec, please benchmark the rest of the setup -- can you web server handle thousands of PHP request/sec? That is, we may need to scale out both the web server and MySQL at the same time. – Rick James Jan 25 '17 at 23:48
  • Yes the Nginx+PHP7.0-FPM can handle up to 25K connections per second according to loader.io tests. The problem seems to reside in the MariaDB server and specifically when it needs to do the "UPDATE user SET files = files + 1 WHERE id = 1" query (because most "update" queries are queued and the mariaDB server becomes unresponsive). – user2972081 Jan 26 '17 at 0:03
  • In addition, if I correct understand Your situation with only 1 row in table (as I see @RickJames understand similar), for what reason You have - UNIQUE KEY email (email), INDEX email_index (email). At least You do not need second, better remove both - it reduce lock time – a_vlad Jan 26 '17 at 2:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.