A weird case with 190k req/sec and dropping with mysql query involved

[cayolblake]

Hi @walkor

I'm halving this weird case where on my test machine I am getting around ~130k req/seq when running workerman plain simple, yet, once I add a single query to a MariaDB InnoDB table this drops down to ~10k req/seq.

I am doing proper connection pooling and even tested with webman.

The query is a simple select with a single where condition and its properly indexed, also the table got no more than 10,000 records.

The machine CPU/memory utilization very low.

I tried with normal driver, libevent, libev and even tried the 5.0 tree as well as the stable one and still got the same results.

I know this is not a workerman issue per se but I'd like your involvement in knowing the bottleneck here.

I suspect the mysql driver but not sure.

Any ideas?

[walkor]

It is normal that the operation of the database will affect the system throughput. Because reading and writing the database is a time-consuming operation
You can try to start more processes, such as 2-4 times the CPU, which will make full use of the CPU and increase the throughput to a certain extent. Not that the more processes, the better. Too many processes will lead to higher CPU overhead for process switching. So find a balance.

[joanhey]

All coroutines use process switching too.

[cayolblake]

Yes I did exactly that, and marked the sweet spot as 2x the available CPU cores.

I'm not looking to optimize this, I'm looking at how to find the exact bottleneck and may be possibly apply an enhancement.

Its saddening that the throughput decreases to less than %10 of what possible with the introduction of a single database query.

So far, I believe its the network/db blocking IO is from the driver.

I was also thinking about the possibility that workerman could handle the IO blocking and let through other operations go untill they're done. I'm not sure if this could be possible by workerman + revolt or fibers or something else I am not seeing.

[walkor]

Using revolt or fibers to implement database non blocking IO means using PHP code to parse MSYQL protocol. that consumes so many CPU, and the final effect may not be as high as blocking call database throughput.

[cayolblake]

Yes that would be interesting, especially, with how the PHP interpreter is getting blazing fast and lightweight more every day.

I did an strace on all 4 workerman workers I have, while hitting with 1,000 concurrent requests for 10 seconds and here's what I got.

% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
 45.12    0.716962           4    152570           sendto
 27.03    0.429500           2    153571        70 recvfrom
 25.83    0.410449           5     77289           poll
  0.72    0.011416          11      1013           epoll_wait
  0.39    0.006212           3      2002           epoll_ctl
  0.33    0.005207           5      1018           close
  0.31    0.004948           4      1003         2 accept
  0.24    0.003881           1      2090           fcntl
  0.01    0.000176           2        60           mprotect
  0.01    0.000137           2        57         4 newfstatat
  0.00    0.000070           2        25           read
  0.00    0.000038           2        17           openat
  0.00    0.000033          11         3           munmap
  0.00    0.000023          11         2           mmap
  0.00    0.000005           1         4           lseek
------ ----------- ----------- --------- --------- ----------------
100.00    1.589057           4    390724        76 total

What could be my options? I want to squeeze more juice out of this before I give up 🤔

[walkor]

Try to use swoole's mysql connection pool.
https://wiki.swoole.com/#/coroutine/conn_pool?id=pdo

[cayolblake]

Alright I'll have it tested. Not sure what will it do.

I assume workerman's workers each have a single live connection with the database. What could Swoole's DB pool doing differently 🤔 !?

[cayolblake]

Okay, so I've gone through the swoole's mysql conn pool and its results are promising.

So for now, assuming I have the following workerman code, how can I create a pool per each worker to use ?!

<?php
use Workerman\Worker;
require_once __DIR__ . '/vendor/autoload.php';
$http_worker = new Worker('http://0.0.0.0:2345');
$http_worker->count = 4;

$http_worker->onMessage = function ($connection, $request) {
    $connection->send("Hello World");
};

Worker::runAll();

[walkor]

<?php
require_once __DIR__ . '/vendor/autoload.php';
use Workerman\Worker;
use Swoole\Coroutine;
use Swoole\Database\PDOPool;
use Swoole\Database\PDOConfig;

Worker::$eventLoopClass = 'Workerman\Events\Swoole';
$worker = new Worker('http://0.0.0.0:2345');
$worker->count = 4;
$worker->onMessage = function($connection, $request)
{
    static $pool;
    if (!$pool) {
        $pool = new PDOPool((new PDOConfig)
            ->withHost('127.0.0.1')
            ->withPort(3306)
            // ->withUnixSocket('/tmp/mysql.sock')
            ->withDbName('test')
            ->withCharset('utf8mb4')
            ->withUsername('root')
            ->withPassword('123456')
        );
    }
    $pdo = $pool->get();
    $statement = $pdo->prepare('SELECT ? + ?');
    $a = mt_rand(1, 100);
    $b = mt_rand(1, 100);
    $statement->execute([$a, $b]);
    $result = $statement->fetchAll();
    $pool->put($pdo);
    $connection->send($result[0][0]);
};

Worker::runAll();

I'm not sure if it's the correct usage.

[joanhey]

Sorry, but do you think that a request with a database call will not affect the request time ??

[joanhey]

Swoole don't have a connection pool to the database.
They use a class to do it!!

Like I said before in this repo, async or not async the time to receive data from the database is the same !!!

[xpader]

I have use amphp async mysql component with wind-framework, It's non-blocking, and have connection pool, got very good performance.

[cayolblake]

For what it's worth, I've tried Swoole's pdo pool and it's almost the same result.

@xpader wind-framework is on my today's testing list on how to get out of this bottleneck situation.

[joanhey]

If you use swoole, amp, ... async or not async, or any other language.
The result will be almost the same.

When you call a database the req/s will drop drastically.

Using the Thechempower benchmark data

Framework	Hello world	1 single query
Workerman	2,063,689	394,020
Swoole	1,152,764	349,992
Amp	273,819	11,409

And the same using Rust, Java, ....

[cayolblake]

So, it appears...

workerman + swoole pdo pool    -> 31k req/sec
workerman + swoole mysqli pool -> 34k req/sec
workerman + mysqli             -> 40k req/sec

I read before here somewhere in the issues how people are wondering how workerman beats swoole in a number of tests but I never got to experience it firsthand. Guess the fact that workerman lives within the same PHP process makes it avoid the overhead of bridging between both PHP and C++ where Swoole is living. Not entirely sure tho.

Thanks @walkor for the PDOPool sample code.

[cayolblake]

I tested with workerman+ memcached with a 10 bytes object, and it's weird contrary to my expectations I got around 51k req/sec

Given that memcached is an in-memory data store, I guess this means that the fact that if there're any sort of network IO we look at a drop of an average of %75 in request serving capacity.

May be if there was a memcached UDP client I could've tested for the cost of TCP.

So, since I'm in the phase of architecting the system, I guess heavily hit data shall live within the workerman/PHP realm and nothing further if I'm looking to gain machine performance to the last drop. Clustering this would still be a challenge yet worth the time and effort versus the reward.

[joanhey]

It's normal this drop when using any TCP connection. And happen with any language.

I think that you must read this comment #694 (comment)

[xpader]

Did you test with another mysql server, maybe it's mysql configuration issue, like max_connections, or other.