Comments on: MySQL Challenge: 100k Connections https://www.percona.com/blog/mysql-challenge-100k-connections/ Sat, 03 Feb 2024 01:16:05 +0000 hourly 1 https://wordpress.org/?v=6.5.2 By: Rohit Khurana https://www.percona.com/blog/mysql-challenge-100k-connections/#comment-10972273 Thu, 28 May 2020 08:19:02 +0000 https://www.percona.com/blog/?p=55427#comment-10972273 We are getting segmentation fault (core dump) after increasing thread to 4000

]]> By: Simon J Mudd https://www.percona.com/blog/mysql-challenge-100k-connections/#comment-10972158 Fri, 24 Apr 2020 07:42:28 +0000 https://www.percona.com/blog/?p=55427#comment-10972158 It might be interesting to show the memory usage (per client connection) and how it changes as the number of connected threads increases. MySQL documentation about expected memory use on a server has always been rather vague in this area so when you add more connections you have to be careful to avoid running the server out of memory. At the same time we want to use as much memory as reasonably possible for the buffer pool to optimise access times to data.
So even with the close to default settings you were using are there any surprises in memory usage and how does the memory usage change when going switching to using the thread pool?

]]> By: Vadim Tkachenko https://www.percona.com/blog/mysql-challenge-100k-connections/#comment-10970716 Fri, 26 Apr 2019 13:26:15 +0000 https://www.percona.com/blog/?p=55427#comment-10970716 Gurnish,

I’ve used all defaults . The default behavior will be to set number of threads == number of CPU in the system

]]> By: Adam https://www.percona.com/blog/mysql-challenge-100k-connections/#comment-10970577 Fri, 05 Apr 2019 14:31:43 +0000 https://www.percona.com/blog/?p=55427#comment-10970577 You are right. The DB is serving quick and small sets of rows also makes good use of innodb buffer pool and query cache. The indices and queries are also optimized so that looking up rows in a billion-rows table is fast as light

]]> By: Gurnish Anand https://www.percona.com/blog/mysql-challenge-100k-connections/#comment-10970520 Fri, 29 Mar 2019 00:53:00 +0000 https://www.percona.com/blog/?p=55427#comment-10970520 what were your settings for the thread-pool?

]]> By: Vitaly https://www.percona.com/blog/mysql-challenge-100k-connections/#comment-10970408 Wed, 06 Mar 2019 08:31:06 +0000 https://www.percona.com/blog/?p=55427#comment-10970408 I see, thank you.

]]> By: vadimtk https://www.percona.com/blog/mysql-challenge-100k-connections/#comment-10970407 Tue, 05 Mar 2019 22:37:08 +0000 https://www.percona.com/blog/?p=55427#comment-10970407 Vitaly,

It really depends on the workload, but this is the point I wanted to highlight – in these cases thread pool would provide a protection for MySQL to not get overloaded.

]]> By: Vitaly https://www.percona.com/blog/mysql-challenge-100k-connections/#comment-10970405 Tue, 05 Mar 2019 10:49:16 +0000 https://www.percona.com/blog/?p=55427#comment-10970405 >. Some applications use a “re-connect and repeat if the query is too slow” technique, which is a terrible practice. It can >lead to a snowball effect, and could establish thousands of connections to MySQL in a matter of seconds.
IMHO, in such situation MySQL server becomes unusable because tons of *queries* running, before we’ll reach 100K connections.

]]> By: graykingw https://www.percona.com/blog/mysql-challenge-100k-connections/#comment-10970374 Thu, 28 Feb 2019 03:56:13 +0000 https://www.percona.com/blog/?p=55427#comment-10970374 I think you may loss some CPU cores number: 24 Physical Cores @ 2.2 GHz

]]> By: Yashada https://www.percona.com/blog/mysql-challenge-100k-connections/#comment-10970366 Tue, 26 Feb 2019 18:35:37 +0000 https://www.percona.com/blog/?p=55427#comment-10970366 Do you have the memory consumption metrics as the number of connections was stepped up? Curious about it.

]]> By: Lazuardi Nasution https://www.percona.com/blog/mysql-challenge-100k-connections/#comment-10970365 Tue, 26 Feb 2019 17:48:54 +0000 https://www.percona.com/blog/?p=55427#comment-10970365 Hi Adam, I don’t understand why you only get 500 concurrent connections if there is thousands HTTP request per second. My real live case was coming from single old E5-2640v2 processor where there is 8000 request per second (peak) with 400 concurrent connections to MySQL on the same server with web server. Do you use the same small resources?

]]> By: Adam https://www.percona.com/blog/mysql-challenge-100k-connections/#comment-10970363 Tue, 26 Feb 2019 14:32:01 +0000 https://www.percona.com/blog/?p=55427#comment-10970363 Any real life example of this case & solution? I am running a cloud infrastructure, having billions of http requests per day (peak thousands per second) and I never even come close to 500 concurrent connections (using 2 database replicas)

]]> By: Alex https://www.percona.com/blog/mysql-challenge-100k-connections/#comment-10970362 Tue, 26 Feb 2019 08:19:37 +0000 https://www.percona.com/blog/?p=55427#comment-10970362 Excellent write-up! Thanks.

]]> By: Alexey Kopytov https://www.percona.com/blog/mysql-challenge-100k-connections/#comment-10970361 Tue, 26 Feb 2019 08:17:49 +0000 https://www.percona.com/blog/?p=55427#comment-10970361 For this number of connections you want to disable JIT in sysbench with the –luajit-cmd=off option. The option is available in 1.1 prereleases. In 1.0 the same could be achieved by adding a single line (“jit.off()”) to oltp_common.lua.

]]> By: John Haugeland https://www.percona.com/blog/mysql-challenge-100k-connections/#comment-10970359 Mon, 25 Feb 2019 23:14:49 +0000 https://www.percona.com/blog/?p=55427#comment-10970359 next time try jmeter or tsung instead of sysbench

]]> By: Rudi https://www.percona.com/blog/mysql-challenge-100k-connections/#comment-10970358 Mon, 25 Feb 2019 23:13:21 +0000 https://www.percona.com/blog/?p=55427#comment-10970358 Mr. Vadimtk, which part should be changed i i use sever with 32GB of RAM and 100 computer client connected to Database Server.

]]> By: vadimtk https://www.percona.com/blog/mysql-challenge-100k-connections/#comment-10970355 Mon, 25 Feb 2019 19:41:56 +0000 https://www.percona.com/blog/?p=55427#comment-10970355 No, it means it cannot be achieved without thread pool plugin. Thread pool is available in earlier versions too.

]]> By: Lazuardi Nasution https://www.percona.com/blog/mysql-challenge-100k-connections/#comment-10970353 Mon, 25 Feb 2019 18:54:57 +0000 https://www.percona.com/blog/?p=55427#comment-10970353 Interesting, is this article mean that it cannot be achieved with MySQL version prior to 8?

]]> By: rautamiekka https://www.percona.com/blog/mysql-challenge-100k-connections/#comment-10970352 Mon, 25 Feb 2019 16:26:42 +0000 https://www.percona.com/blog/?p=55427#comment-10970352 Not being able to server 100k connections == useless database software.

And when MySQL (or rather, Percona and MariaDB) becomes useless, you enter The Dark Ages.

]]> By: Vardan https://www.percona.com/blog/mysql-challenge-100k-connections/#comment-10970351 Mon, 25 Feb 2019 16:13:45 +0000 https://www.percona.com/blog/?p=55427#comment-10970351 I think you missing in your final my.cnf following:
thread_handling=pool-of-threads

]]>