maria-discuss team mailing list archive

[Reindl Harald <h.reindl@xxxxxxxxxxxxx>]

i started to play around with "thread_handling = pool-of-threads" with 
10.2.7 and removed at that time the @mysqli_real_connect() error 
supression of my database-layer which also has a usleep() and retry-loop 
in case connection failed on so completly burried the issue

PHP Warning:  mysqli_real_connect() [<a 
href='http://at.php.net/manual/de/function.mysqli-real-connect.php'>function.mysqli-real-connect.php</a>]: 
(HY000/2002): Resource temporarily unavailable

you should not see such messages when run a "ab -c 200 -n 500000 -k 
http://corecms/show_content.php?sid=2"; with "max_connections = 300"
_____________________________________

thread_handling  = one-thread-per-connection
[root@srv-rhsoft:~]$ cat php_error.log | wc -l
52596

thread_handling = pool-of-threads
thread_pool_idle_timeout = 900
[root@srv-rhsoft:~]$ cat php_error.log | wc -l
39282

thread_handling = pool-of-threads
thread_pool_oversubscribe = 10
thread_pool_idle_timeout = 900
thread_pool_priority = high
[root@srv-rhsoft:~]$ cat php_error.log | wc -l
24849

since my database-layer makes a usleep(100000) before each retry and the 
retry-lop still has error-supression that means the cms waits 10% of all 
requests at least 0.1 seconds for the mariadb server which means the 
4300 Requests/Second could be much higher if every connection suceeds at 
the first try (at least the thread pool seems to work slightly better 
then without)
_____________________________________

what makes me really worry here is while the load is running (mysqld 
each time restartet before start apache-benchmark)

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Threadpool_idle_threads | 181   |
| Threadpool_threads      | 189   |
+-------------------------+-------+

after the benchmark has finished and the machine is idle:
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Threadpool_idle_threads | 207   |
| Threadpool_threads      | 208   |
+-------------------------+-------+

frankly i would expect at least that this numbers are going up while the 
load is running to at least 200 or not going up that high at all but 
don't refuse conncetions which is IMHO the point of a pool
_____________________________________

the core-cms itself makes exactly two queries per request over 3 MyISAM 
tables, one cache-table with a pirmary key and the second one is a 
simple join on two tables with only 2 records, so not really something 
one should call real load

select SQL_NO_CACHE * from cms1_cache where `hash`='fullmenu_1_2_0de0_0'
select SQL_NO_CACHE * from `cms1_sub` join `cms1_haupt` on sid=2 and 
hid=shid

mysqld as well as httpd had "LimitNOFILE=infinity" in the systemd-unit, 
the connection type is unix socket, so that all should not be a problem 
on a i7-3770 CPU @ 3.40GHz with 32 GB RAM

it takes some time until the errors start to appear in the log, likely 
after httpd (mod_prefork) had forked enough worker to introduce real 
concurrency to the database