Wojciech Kapcia (Tigase) opened 1 year ago
-
Seems reasonable.
I sometime wonder if we shouldn't switch to HikariCP (https://github.com/brettwooldridge/HikariCP; quite tiny [150K], compatible licence). They seem to be quite focused on performance and has this tidbid there: https://github.com/brettwooldridge/HikariCP?tab=readme-ov-file#youre-probably-doing-it-wrong:
AKA "What you probably didn't know about connection pool sizing". Watch a video from the Oracle Real-world Performance group, and learn about why connection pools do not need to be sized as large as they often are. In fact, oversized connection pools have a clear and demonstrable negative impact on performance; a 50x difference in the case of the Oracle demonstration. Read on to find out. (https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing)
-
I was thinking about that for a while, I think that reducing no. of open connections to match no. of CPU cores (instead of matching 4 times the number of CPUs) seams reasonable.
There are a few things to consider.
1. Usage of the pool
Currently we have a few algorithms to select connection from the database pool:
- UserRepositoryPool - selects first available
- DataRepositoryPool - selects connections based on hash of user JID
- PubSubDAOJDBC - uses DataRepositoryPool internally, but uses „pool of hashes” to mimic algorithm used by UserRepositoryPool.
While algorithm used by
DataRepositoryPoolwas choosen to make sure a single entity will not use all connections for fetching data from database, it may cause unnecessary congestion when we would reduce pool size (as hashes will collide more often).On the other hand, UserRepositoryPool (or PubSubDAOJDBC) algorithm, uses connections from the pool one by one, making usage of connection more efficient (no. congestion on the hash of the user to select connection).
2. Time waiting on database
Currently, we have limit set that each request has to finishing within 10ms. Taking that into account and considering the fact that processing of a single packet (if possible) is done using cached data, I think that our threads are not using database for more that 50% of a processing time (it think the actual value would be lower), We do have a lot of threads, but even considering that, we are bound by no. of CPU cores, so having 4 times more connections than CPU cores and limit of 10ms per database query seems very high.
3. Congestion on the database side
Even if Tigase would have 16 cores and 64 connections, and database server would have 16 cores it, database would still execute just 16 queries at once. Sure, some processing could be done due to transmitting data over additional connections (presetting query parameters), but I do not see that it would be very benefitial.
Taking that into consideration that very often Tigase is used in clustered mode, then 3 servers with 16 cores would open 192 connections (more that default MySQL limit), while MySQL would still be able to execute just 16 queries at once (without switching if we had also 16 core database server). Assuming we have correct indexes and enough memory to have them in memory, then context switching wouldn’t have so additional connections would just cause congestion on the database side.
4. Memory usage
Each connection requires memory assigned (for buffers and objects) on Tigase side and on MySQL side. If we need to increase allowed connections on MySQL side, we need to assign additional memory to MySQL for those connections (ie. for caches). Without that, we could use this memory for processing requests (ie. for indexes) making request processing faster.
We are also using prepared statements that need to be created and cached on both sides of the connection using memory.
5. Establishing connections
Establishing of each connection takes time, as well as preparing statements. That makes startup (or reconnection to database) slow. We already experienced that, ie. on iMac with10 cores that were visible as 20 cores due to hyper threading, startup was rather slow as Tigase had to open 80 connections.
Summary
Due to the facts mentioned above, it think that it would be good to change default database connections to CPU cores ratio from
4to1on clustered systems. On non-clustered systems, having1.5ratio seems that should be also enough. We could go with1.2(having 20% overprovisioning) for all installations and in my opinion it should work fine (that would be 20 connections for 16 core machine instead of 64). I think we could start with following algorithm as a default one:db_connections = max(10, ceil(1.2 * no_of_cpu_cores))if we want this overprovisioning.
Additionally, I think it would be good to reimplement
DataRepositoryPoolto have the same connection selection logic asUserRepositoryPooluses and whichPubSubDAOJDBCmimics to reduce congestion on a single database connection.As for @wojtek suggestion about usage of HikariCP instead, we could do that and do not have to keep our implementation in place, but as we already have one, we could just adjust it.
-
I've run some tests to estimate if change in the number of connection would have a large impact on performance of Tigase XMPP Server. Below are my findings.
In test results with multiple processing threads, where each thread uses 1ms CPU and then calls MySQL (MySQL CPU usage was about 100% - 1 core). There is amost no difference if no. of connections is equal or larger than 16 (machine has 12 cores - M3 Pro). CPU processing time of 1ms is almost equal to MySQL query processing time (MySQL running in Docker on the same machine with 3 cores available).
Threads: 16, jids: 128, pool size: 2, useHash: true, executed in 8211 ms, initialized in 455 ms, CPU time: 27 % Threads: 16, jids: 128, pool size: 2, useHash: false, executed in 8000 ms, initialized in 16 ms, CPU time: 29 % Threads: 16, jids: 128, pool size: 8, useHash: true, executed in 3788 ms, initialized in 40 ms, CPU time: 44 % Threads: 16, jids: 128, pool size: 8, useHash: false, executed in 3502 ms, initialized in 41 ms, CPU time: 47 % Threads: 16, jids: 128, pool size: 16, useHash: true, executed in 3758 ms, initialized in 71 ms, CPU time: 44 % Threads: 16, jids: 128, pool size: 16, useHash: false, executed in 3398 ms, initialized in 62 ms, CPU time: 49 % Threads: 16, jids: 128, pool size: 48, useHash: true, executed in 3805 ms, initialized in 161 ms, CPU time: 44 % Threads: 16, jids: 128, pool size: 48, useHash: false, executed in 3963 ms, initialized in 192 ms, CPU time: 42 % Threads: 16, jids: 128, pool size: 96, useHash: true, executed in 3630 ms, initialized in 285 ms, CPU time: 45 % Threads: 16, jids: 128, pool size: 96, useHash: false, executed in 3949 ms, initialized in 322 ms, CPU time: 42 % Threads: 16, jids: 256, pool size: 2, useHash: true, executed in 8055 ms, initialized in 9 ms, CPU time: 27 % Threads: 16, jids: 256, pool size: 2, useHash: false, executed in 7743 ms, initialized in 8 ms, CPU time: 29 % Threads: 16, jids: 256, pool size: 8, useHash: true, executed in 3931 ms, initialized in 23 ms, CPU time: 43 % Threads: 16, jids: 256, pool size: 8, useHash: false, executed in 3685 ms, initialized in 27 ms, CPU time: 44 % Threads: 16, jids: 256, pool size: 16, useHash: true, executed in 3755 ms, initialized in 56 ms, CPU time: 44 % Threads: 16, jids: 256, pool size: 16, useHash: false, executed in 3446 ms, initialized in 55 ms, CPU time: 46 % Threads: 16, jids: 256, pool size: 48, useHash: true, executed in 3539 ms, initialized in 131 ms, CPU time: 45 % Threads: 16, jids: 256, pool size: 48, useHash: false, executed in 3710 ms, initialized in 140 ms, CPU time: 44 % Threads: 16, jids: 256, pool size: 96, useHash: true, executed in 3553 ms, initialized in 244 ms, CPU time: 45 % Threads: 16, jids: 256, pool size: 96, useHash: false, executed in 3481 ms, initialized in 268 ms, CPU time: 48 % Threads: 64, jids: 128, pool size: 2, useHash: true, executed in 8409 ms, initialized in 7 ms, CPU time: 13 % Threads: 64, jids: 128, pool size: 2, useHash: false, executed in 7850 ms, initialized in 8 ms, CPU time: 14 % Threads: 64, jids: 128, pool size: 8, useHash: true, executed in 3854 ms, initialized in 26 ms, CPU time: 32 % Threads: 64, jids: 128, pool size: 8, useHash: false, executed in 3832 ms, initialized in 25 ms, CPU time: 28 % Threads: 64, jids: 128, pool size: 16, useHash: true, executed in 3508 ms, initialized in 49 ms, CPU time: 35 % Threads: 64, jids: 128, pool size: 16, useHash: false, executed in 3646 ms, initialized in 48 ms, CPU time: 33 % Threads: 64, jids: 128, pool size: 48, useHash: true, executed in 3452 ms, initialized in 134 ms, CPU time: 41 % Threads: 64, jids: 128, pool size: 48, useHash: false, executed in 3474 ms, initialized in 137 ms, CPU time: 43 % Threads: 64, jids: 128, pool size: 96, useHash: true, executed in 3629 ms, initialized in 270 ms, CPU time: 39 % Threads: 64, jids: 128, pool size: 96, useHash: false, executed in 3568 ms, initialized in 271 ms, CPU time: 41 % Threads: 64, jids: 256, pool size: 2, useHash: true, executed in 7858 ms, initialized in 8 ms, CPU time: 13 % Threads: 64, jids: 256, pool size: 2, useHash: false, executed in 7928 ms, initialized in 8 ms, CPU time: 17 % Threads: 64, jids: 256, pool size: 8, useHash: true, executed in 4014 ms, initialized in 23 ms, CPU time: 32 % Threads: 64, jids: 256, pool size: 8, useHash: false, executed in 3719 ms, initialized in 23 ms, CPU time: 30 % Threads: 64, jids: 256, pool size: 16, useHash: true, executed in 3701 ms, initialized in 48 ms, CPU time: 31 % Threads: 64, jids: 256, pool size: 16, useHash: false, executed in 3464 ms, initialized in 48 ms, CPU time: 35 % Threads: 64, jids: 256, pool size: 48, useHash: true, executed in 3736 ms, initialized in 145 ms, CPU time: 38 % Threads: 64, jids: 256, pool size: 48, useHash: false, executed in 3515 ms, initialized in 130 ms, CPU time: 40 % Threads: 64, jids: 256, pool size: 96, useHash: true, executed in 3467 ms, initialized in 243 ms, CPU time: 41 % Threads: 64, jids: 256, pool size: 96, useHash: false, executed in 3728 ms, initialized in 250 ms, CPU time: 36 %In similar tests where CPU processing time is set to 0.5ms, the average CPU processing time is 30%. In those results there is less than 5% performance gain on having more database connections than CPU cores. Even with 64 threads running on 12 cores, performance gain is rather low.
For example, with 64 threads, test running 25000 operations ended in 2730ms with 16 connections, while with 48 connections it ended in 2403ms. With 16 connections, processing time is increased and CPU usage time percentage is lower than with 48 connections by 2-3%. That could be explained by additional processing being done while some threads wait for database connections. On the other hand, that gain is not consistent, so it can be just a measurement error (CPU is not only available to tested code and MySQL).
Threads: 16, jids: 128, pool size: 2, useHash: true, executed in 7236 ms, initialized in 477 ms, CPU time: 18 % Threads: 16, jids: 128, pool size: 2, useHash: false, executed in 7003 ms, initialized in 14 ms, CPU time: 18 % Threads: 16, jids: 128, pool size: 8, useHash: true, executed in 3421 ms, initialized in 35 ms, CPU time: 27 % Threads: 16, jids: 128, pool size: 8, useHash: false, executed in 2945 ms, initialized in 109 ms, CPU time: 29 % Threads: 16, jids: 128, pool size: 16, useHash: true, executed in 3013 ms, initialized in 78 ms, CPU time: 29 % Threads: 16, jids: 128, pool size: 16, useHash: false, executed in 2630 ms, initialized in 64 ms, CPU time: 30 % Threads: 16, jids: 128, pool size: 48, useHash: true, executed in 2819 ms, initialized in 150 ms, CPU time: 30 % Threads: 16, jids: 128, pool size: 48, useHash: false, executed in 2759 ms, initialized in 150 ms, CPU time: 30 % Threads: 16, jids: 128, pool size: 96, useHash: true, executed in 2840 ms, initialized in 277 ms, CPU time: 29 % Threads: 16, jids: 128, pool size: 96, useHash: false, executed in 2642 ms, initialized in 286 ms, CPU time: 30 % Threads: 16, jids: 256, pool size: 2, useHash: true, executed in 6853 ms, initialized in 9 ms, CPU time: 19 % Threads: 16, jids: 256, pool size: 2, useHash: false, executed in 6769 ms, initialized in 8 ms, CPU time: 18 % Threads: 16, jids: 256, pool size: 8, useHash: true, executed in 3406 ms, initialized in 57 ms, CPU time: 26 % Threads: 16, jids: 256, pool size: 8, useHash: false, executed in 3031 ms, initialized in 24 ms, CPU time: 28 % Threads: 16, jids: 256, pool size: 16, useHash: true, executed in 2764 ms, initialized in 58 ms, CPU time: 30 % Threads: 16, jids: 256, pool size: 16, useHash: false, executed in 2840 ms, initialized in 53 ms, CPU time: 29 % Threads: 16, jids: 256, pool size: 48, useHash: true, executed in 2694 ms, initialized in 134 ms, CPU time: 30 % Threads: 16, jids: 256, pool size: 48, useHash: false, executed in 2621 ms, initialized in 131 ms, CPU time: 30 % Threads: 16, jids: 256, pool size: 96, useHash: true, executed in 2739 ms, initialized in 265 ms, CPU time: 29 % Threads: 16, jids: 256, pool size: 96, useHash: false, executed in 2719 ms, initialized in 369 ms, CPU time: 30 % Threads: 64, jids: 128, pool size: 2, useHash: true, executed in 7224 ms, initialized in 7 ms, CPU time: 7 % Threads: 64, jids: 128, pool size: 2, useHash: false, executed in 6823 ms, initialized in 12 ms, CPU time: 8 % Threads: 64, jids: 128, pool size: 8, useHash: true, executed in 3477 ms, initialized in 22 ms, CPU time: 20 % Threads: 64, jids: 128, pool size: 8, useHash: false, executed in 3002 ms, initialized in 23 ms, CPU time: 15 % Threads: 64, jids: 128, pool size: 16, useHash: true, executed in 2656 ms, initialized in 50 ms, CPU time: 22 % Threads: 64, jids: 128, pool size: 16, useHash: false, executed in 2455 ms, initialized in 60 ms, CPU time: 16 % Threads: 64, jids: 128, pool size: 48, useHash: true, executed in 2593 ms, initialized in 136 ms, CPU time: 18 % Threads: 64, jids: 128, pool size: 48, useHash: false, executed in 2382 ms, initialized in 129 ms, CPU time: 20 % Threads: 64, jids: 128, pool size: 96, useHash: true, executed in 2379 ms, initialized in 269 ms, CPU time: 19 % Threads: 64, jids: 128, pool size: 96, useHash: false, executed in 2371 ms, initialized in 267 ms, CPU time: 20 % Threads: 64, jids: 256, pool size: 2, useHash: true, executed in 6896 ms, initialized in 10 ms, CPU time: 9 % Threads: 64, jids: 256, pool size: 2, useHash: false, executed in 6967 ms, initialized in 8 ms, CPU time: 8 % Threads: 64, jids: 256, pool size: 8, useHash: true, executed in 3243 ms, initialized in 25 ms, CPU time: 22 % Threads: 64, jids: 256, pool size: 8, useHash: false, executed in 3033 ms, initialized in 23 ms, CPU time: 14 % Threads: 64, jids: 256, pool size: 16, useHash: true, executed in 2730 ms, initialized in 51 ms, CPU time: 17 % Threads: 64, jids: 256, pool size: 16, useHash: false, executed in 2837 ms, initialized in 51 ms, CPU time: 14 % Threads: 64, jids: 256, pool size: 48, useHash: true, executed in 2403 ms, initialized in 135 ms, CPU time: 18 % Threads: 64, jids: 256, pool size: 48, useHash: false, executed in 2713 ms, initialized in 131 ms, CPU time: 18 % Threads: 64, jids: 256, pool size: 96, useHash: true, executed in 2451 ms, initialized in 237 ms, CPU time: 19 % Threads: 64, jids: 256, pool size: 96, useHash: false, executed in 2430 ms, initialized in 267 ms, CPU time: 21 %I’ve run one more test (even with 128 threads) and I’ve seen no gain (there is some improvement for 48 connections, but with 96 connections the improvement is lost, so I think that is rather some error in testing).
Threads: 16, jids: 128, pool size: 2, useHash: true, executed in 7431 ms, initialized in 466 ms, CPU time: 17 % Threads: 16, jids: 128, pool size: 2, useHash: false, executed in 6992 ms, initialized in 17 ms, CPU time: 18 % Threads: 16, jids: 128, pool size: 8, useHash: true, executed in 3469 ms, initialized in 35 ms, CPU time: 27 % Threads: 16, jids: 128, pool size: 8, useHash: false, executed in 2965 ms, initialized in 40 ms, CPU time: 28 % Threads: 16, jids: 128, pool size: 16, useHash: true, executed in 2728 ms, initialized in 66 ms, CPU time: 30 % Threads: 16, jids: 128, pool size: 16, useHash: false, executed in 2757 ms, initialized in 70 ms, CPU time: 30 % Threads: 16, jids: 128, pool size: 48, useHash: true, executed in 2768 ms, initialized in 162 ms, CPU time: 29 % Threads: 16, jids: 128, pool size: 48, useHash: false, executed in 2661 ms, initialized in 150 ms, CPU time: 30 % Threads: 16, jids: 128, pool size: 96, useHash: true, executed in 2722 ms, initialized in 290 ms, CPU time: 30 % Threads: 16, jids: 128, pool size: 96, useHash: false, executed in 2987 ms, initialized in 322 ms, CPU time: 28 % Threads: 16, jids: 256, pool size: 2, useHash: true, executed in 6842 ms, initialized in 8 ms, CPU time: 18 % Threads: 16, jids: 256, pool size: 2, useHash: false, executed in 7358 ms, initialized in 8 ms, CPU time: 19 % Threads: 16, jids: 256, pool size: 8, useHash: true, executed in 3390 ms, initialized in 24 ms, CPU time: 26 % Threads: 16, jids: 256, pool size: 8, useHash: false, executed in 3100 ms, initialized in 46 ms, CPU time: 28 % Threads: 16, jids: 256, pool size: 16, useHash: true, executed in 2822 ms, initialized in 52 ms, CPU time: 29 % Threads: 16, jids: 256, pool size: 16, useHash: false, executed in 2804 ms, initialized in 69 ms, CPU time: 30 % Threads: 16, jids: 256, pool size: 48, useHash: true, executed in 3020 ms, initialized in 135 ms, CPU time: 28 % Threads: 16, jids: 256, pool size: 48, useHash: false, executed in 2628 ms, initialized in 137 ms, CPU time: 30 % Threads: 16, jids: 256, pool size: 96, useHash: true, executed in 2755 ms, initialized in 314 ms, CPU time: 30 % Threads: 16, jids: 256, pool size: 96, useHash: false, executed in 2906 ms, initialized in 290 ms, CPU time: 29 % Threads: 64, jids: 128, pool size: 2, useHash: true, executed in 6849 ms, initialized in 8 ms, CPU time: 8 % Threads: 64, jids: 128, pool size: 2, useHash: false, executed in 6919 ms, initialized in 8 ms, CPU time: 7 % Threads: 64, jids: 128, pool size: 8, useHash: true, executed in 3295 ms, initialized in 30 ms, CPU time: 21 % Threads: 64, jids: 128, pool size: 8, useHash: false, executed in 3220 ms, initialized in 23 ms, CPU time: 17 % Threads: 64, jids: 128, pool size: 16, useHash: true, executed in 2711 ms, initialized in 50 ms, CPU time: 21 % Threads: 64, jids: 128, pool size: 16, useHash: false, executed in 2507 ms, initialized in 51 ms, CPU time: 16 % Threads: 64, jids: 128, pool size: 48, useHash: true, executed in 2386 ms, initialized in 143 ms, CPU time: 20 % Threads: 64, jids: 128, pool size: 48, useHash: false, executed in 2443 ms, initialized in 232 ms, CPU time: 20 % Threads: 64, jids: 128, pool size: 96, useHash: true, executed in 2750 ms, initialized in 266 ms, CPU time: 17 % Threads: 64, jids: 128, pool size: 96, useHash: false, executed in 2430 ms, initialized in 259 ms, CPU time: 23 % Threads: 64, jids: 256, pool size: 2, useHash: true, executed in 7007 ms, initialized in 7 ms, CPU time: 5 % Threads: 64, jids: 256, pool size: 2, useHash: false, executed in 7107 ms, initialized in 8 ms, CPU time: 4 % Threads: 64, jids: 256, pool size: 8, useHash: true, executed in 3271 ms, initialized in 24 ms, CPU time: 22 % Threads: 64, jids: 256, pool size: 8, useHash: false, executed in 3040 ms, initialized in 30 ms, CPU time: 16 % Threads: 64, jids: 256, pool size: 16, useHash: true, executed in 2763 ms, initialized in 58 ms, CPU time: 17 % Threads: 64, jids: 256, pool size: 16, useHash: false, executed in 2497 ms, initialized in 58 ms, CPU time: 17 % Threads: 64, jids: 256, pool size: 48, useHash: true, executed in 2386 ms, initialized in 158 ms, CPU time: 17 % Threads: 64, jids: 256, pool size: 48, useHash: false, executed in 2420 ms, initialized in 146 ms, CPU time: 22 % Threads: 64, jids: 256, pool size: 96, useHash: true, executed in 2692 ms, initialized in 255 ms, CPU time: 18 % Threads: 64, jids: 256, pool size: 96, useHash: false, executed in 2375 ms, initialized in 276 ms, CPU time: 20 % Threads: 128, jids: 128, pool size: 2, useHash: true, executed in 7003 ms, initialized in 6 ms, CPU time: 3 % Threads: 128, jids: 128, pool size: 2, useHash: false, executed in 6888 ms, initialized in 11 ms, CPU time: 2 % Threads: 128, jids: 128, pool size: 8, useHash: true, executed in 3483 ms, initialized in 23 ms, CPU time: 19 % Threads: 128, jids: 128, pool size: 8, useHash: false, executed in 3411 ms, initialized in 22 ms, CPU time: 13 % Threads: 128, jids: 128, pool size: 16, useHash: true, executed in 2754 ms, initialized in 49 ms, CPU time: 20 % Threads: 128, jids: 128, pool size: 16, useHash: false, executed in 2705 ms, initialized in 47 ms, CPU time: 12 % Threads: 128, jids: 128, pool size: 48, useHash: true, executed in 2475 ms, initialized in 125 ms, CPU time: 17 % Threads: 128, jids: 128, pool size: 48, useHash: false, executed in 2393 ms, initialized in 131 ms, CPU time: 16 % Threads: 128, jids: 128, pool size: 96, useHash: true, executed in 2530 ms, initialized in 294 ms, CPU time: 17 % Threads: 128, jids: 128, pool size: 96, useHash: false, executed in 2466 ms, initialized in 383 ms, CPU time: 15 % Threads: 128, jids: 256, pool size: 2, useHash: true, executed in 6880 ms, initialized in 8 ms, CPU time: 2 % Threads: 128, jids: 256, pool size: 2, useHash: false, executed in 7046 ms, initialized in 9 ms, CPU time: 3 % Threads: 128, jids: 256, pool size: 8, useHash: true, executed in 3454 ms, initialized in 23 ms, CPU time: 19 % Threads: 128, jids: 256, pool size: 8, useHash: false, executed in 3112 ms, initialized in 38 ms, CPU time: 12 % Threads: 128, jids: 256, pool size: 16, useHash: true, executed in 2578 ms, initialized in 51 ms, CPU time: 17 % Threads: 128, jids: 256, pool size: 16, useHash: false, executed in 2527 ms, initialized in 50 ms, CPU time: 12 % Threads: 128, jids: 256, pool size: 48, useHash: true, executed in 2655 ms, initialized in 145 ms, CPU time: 12 % Threads: 128, jids: 256, pool size: 48, useHash: false, executed in 2461 ms, initialized in 135 ms, CPU time: 15 % Threads: 128, jids: 256, pool size: 96, useHash: true, executed in 2378 ms, initialized in 256 ms, CPU time: 15 % Threads: 128, jids: 256, pool size: 96, useHash: false, executed in 2471 ms, initialized in 252 ms, CPU time: 19 %The only case in which increased no. of connections was improving performance (still) was with 64/128 threads for tasks that were database bound (no CPU processing, just requests to database), as synchornizations on
PreparedStatements were improving performance with increased no. of connections (due to increased no. ofPreparedStatements). With 128 threads, from 48 connections to 96 connections, there was improvements from 1951 to 1822 (about 6.7% gain with doubled amount of connections). In this test, MySQL reached CPU usage if about 250% (2.5 cores) what with test running on the same machine (total of 12 cores) resulted in congestion on the MySQL server side (limited processing power).Threads: 16, jids: 128, pool size: 2, useHash: true, executed in 5419 ms, initialized in 510 ms, CPU time: 0 % Threads: 16, jids: 128, pool size: 2, useHash: false, executed in 5424 ms, initialized in 17 ms, CPU time: 0 % Threads: 16, jids: 128, pool size: 8, useHash: true, executed in 3245 ms, initialized in 34 ms, CPU time: 0 % Threads: 16, jids: 128, pool size: 8, useHash: false, executed in 2722 ms, initialized in 38 ms, CPU time: 0 % Threads: 16, jids: 128, pool size: 16, useHash: true, executed in 2333 ms, initialized in 66 ms, CPU time: 0 % Threads: 16, jids: 128, pool size: 16, useHash: false, executed in 2376 ms, initialized in 67 ms, CPU time: 0 % Threads: 16, jids: 128, pool size: 48, useHash: true, executed in 2230 ms, initialized in 152 ms, CPU time: 0 % Threads: 16, jids: 128, pool size: 48, useHash: false, executed in 2330 ms, initialized in 151 ms, CPU time: 0 % Threads: 16, jids: 128, pool size: 96, useHash: true, executed in 2323 ms, initialized in 274 ms, CPU time: 0 % Threads: 16, jids: 128, pool size: 96, useHash: false, executed in 2462 ms, initialized in 284 ms, CPU time: 0 % Threads: 16, jids: 256, pool size: 2, useHash: true, executed in 5035 ms, initialized in 8 ms, CPU time: 0 % Threads: 16, jids: 256, pool size: 2, useHash: false, executed in 5293 ms, initialized in 7 ms, CPU time: 0 % Threads: 16, jids: 256, pool size: 8, useHash: true, executed in 2881 ms, initialized in 24 ms, CPU time: 0 % Threads: 16, jids: 256, pool size: 8, useHash: false, executed in 2744 ms, initialized in 26 ms, CPU time: 0 % Threads: 16, jids: 256, pool size: 16, useHash: true, executed in 2521 ms, initialized in 50 ms, CPU time: 0 % Threads: 16, jids: 256, pool size: 16, useHash: false, executed in 2183 ms, initialized in 53 ms, CPU time: 0 % Threads: 16, jids: 256, pool size: 48, useHash: true, executed in 2267 ms, initialized in 137 ms, CPU time: 0 % Threads: 16, jids: 256, pool size: 48, useHash: false, executed in 2283 ms, initialized in 130 ms, CPU time: 0 % Threads: 16, jids: 256, pool size: 96, useHash: true, executed in 2534 ms, initialized in 283 ms, CPU time: 0 % Threads: 16, jids: 256, pool size: 96, useHash: false, executed in 2374 ms, initialized in 251 ms, CPU time: 0 % Threads: 64, jids: 128, pool size: 2, useHash: true, executed in 5147 ms, initialized in 8 ms, CPU time: 0 % Threads: 64, jids: 128, pool size: 2, useHash: false, executed in 5590 ms, initialized in 7 ms, CPU time: 0 % Threads: 64, jids: 128, pool size: 8, useHash: true, executed in 2933 ms, initialized in 22 ms, CPU time: 0 % Threads: 64, jids: 128, pool size: 8, useHash: false, executed in 2999 ms, initialized in 22 ms, CPU time: 0 % Threads: 64, jids: 128, pool size: 16, useHash: true, executed in 2376 ms, initialized in 49 ms, CPU time: 0 % Threads: 64, jids: 128, pool size: 16, useHash: false, executed in 2231 ms, initialized in 47 ms, CPU time: 0 % Threads: 64, jids: 128, pool size: 48, useHash: true, executed in 2329 ms, initialized in 131 ms, CPU time: 0 % Threads: 64, jids: 128, pool size: 48, useHash: false, executed in 1815 ms, initialized in 126 ms, CPU time: 0 % Threads: 64, jids: 128, pool size: 96, useHash: true, executed in 2256 ms, initialized in 263 ms, CPU time: 0 % Threads: 64, jids: 128, pool size: 96, useHash: false, executed in 1783 ms, initialized in 273 ms, CPU time: 0 % Threads: 64, jids: 256, pool size: 2, useHash: true, executed in 5158 ms, initialized in 7 ms, CPU time: 0 % Threads: 64, jids: 256, pool size: 2, useHash: false, executed in 5280 ms, initialized in 8 ms, CPU time: 0 % Threads: 64, jids: 256, pool size: 8, useHash: true, executed in 3012 ms, initialized in 25 ms, CPU time: 0 % Threads: 64, jids: 256, pool size: 8, useHash: false, executed in 3003 ms, initialized in 22 ms, CPU time: 0 % Threads: 64, jids: 256, pool size: 16, useHash: true, executed in 2328 ms, initialized in 54 ms, CPU time: 0 % Threads: 64, jids: 256, pool size: 16, useHash: false, executed in 2271 ms, initialized in 51 ms, CPU time: 0 % Threads: 64, jids: 256, pool size: 48, useHash: true, executed in 1963 ms, initialized in 138 ms, CPU time: 0 % Threads: 64, jids: 256, pool size: 48, useHash: false, executed in 1916 ms, initialized in 139 ms, CPU time: 0 % Threads: 64, jids: 256, pool size: 96, useHash: true, executed in 1911 ms, initialized in 406 ms, CPU time: 0 % Threads: 64, jids: 256, pool size: 96, useHash: false, executed in 1748 ms, initialized in 258 ms, CPU time: 0 % Threads: 128, jids: 128, pool size: 2, useHash: true, executed in 5225 ms, initialized in 7 ms, CPU time: 0 % Threads: 128, jids: 128, pool size: 2, useHash: false, executed in 6320 ms, initialized in 9 ms, CPU time: 0 % Threads: 128, jids: 128, pool size: 8, useHash: true, executed in 3014 ms, initialized in 23 ms, CPU time: 0 % Threads: 128, jids: 128, pool size: 8, useHash: false, executed in 2993 ms, initialized in 22 ms, CPU time: 0 % Threads: 128, jids: 128, pool size: 16, useHash: true, executed in 2424 ms, initialized in 53 ms, CPU time: 0 % Threads: 128, jids: 128, pool size: 16, useHash: false, executed in 2418 ms, initialized in 75 ms, CPU time: 0 % Threads: 128, jids: 128, pool size: 48, useHash: true, executed in 2093 ms, initialized in 121 ms, CPU time: 0 % Threads: 128, jids: 128, pool size: 48, useHash: false, executed in 2031 ms, initialized in 121 ms, CPU time: 0 % Threads: 128, jids: 128, pool size: 96, useHash: true, executed in 2078 ms, initialized in 274 ms, CPU time: 0 % Threads: 128, jids: 128, pool size: 96, useHash: false, executed in 1657 ms, initialized in 240 ms, CPU time: 0 % Threads: 128, jids: 256, pool size: 2, useHash: true, executed in 5398 ms, initialized in 7 ms, CPU time: 0 % Threads: 128, jids: 256, pool size: 2, useHash: false, executed in 5195 ms, initialized in 8 ms, CPU time: 0 % Threads: 128, jids: 256, pool size: 8, useHash: true, executed in 2949 ms, initialized in 21 ms, CPU time: 0 % Threads: 128, jids: 256, pool size: 8, useHash: false, executed in 2995 ms, initialized in 23 ms, CPU time: 0 % Threads: 128, jids: 256, pool size: 16, useHash: true, executed in 2343 ms, initialized in 48 ms, CPU time: 0 % Threads: 128, jids: 256, pool size: 16, useHash: false, executed in 2229 ms, initialized in 48 ms, CPU time: 0 % Threads: 128, jids: 256, pool size: 48, useHash: true, executed in 1951 ms, initialized in 126 ms, CPU time: 0 % Threads: 128, jids: 256, pool size: 48, useHash: false, executed in 1931 ms, initialized in 124 ms, CPU time: 0 % Threads: 128, jids: 256, pool size: 96, useHash: true, executed in 1822 ms, initialized in 253 ms, CPU time: 0 % Threads: 128, jids: 256, pool size: 96, useHash: false, executed in 1722 ms, initialized in 299 ms, CPU time: 0 %To sum this up, currently used 4 times multiplier for number of database connections over no. of CPU cores looks like overkill. We have the same multiplier for the number of threads (default) that means that almost any thread has „its own” database connection.
Of course, my test used database deployed on the same machine, so usage of database on a separate instance could improve performance over multiple database connections (as congestion would be reduced on „slow” network).
However, we expect database to be fast and responsive, with execution time of prepared statements to be lower that 15ms. That with a lot of processing that we are doing using cached data or not requiring database access (ie. parsing of XML, TLS encryption, XML serialization, etc.) strongly suggests that we are not database bound and the numer of connections could be lowered (multiplier changed in to. 1.2).
In my tests I’ve used 2 mechanisms for database connection selection („useHash: true” means that jid hash was used and in other cases I’ve used connection pool that used connections in a loop). This change in mechanism (as done in PubSub), increased performance slightly as it reduced congestion on the database connection (it was less likely that the same connection was used twice).
| Type |
Improvement
|
| Priority |
Normal
|
| Assignee | |
| Version |
none
|
| Sprints |
n/a
|
| Customer |
n/a
|
Currently we calculate size of the repo-pool based on the available CPUs which could result error on machines with better specification:
The problem stems from the fact that MySQL by default has limit of 151 connections (https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_connections) and on bigger machines (like 40 CPU cores or more) we would have gigantic pool.
I think, even on bigger machine, we should have some sane maximum instead of going for "sky is the limit".
Relevant code