Thoughts on 1000+ concurrent mysql connections tweaking?
What's your thoughts on this info regarding tweaking linux to allow mysql to handle 1000+ concurrent connections?
http://www.mysql.com/doc/L/i/Linux.html
extract:
If you plan to have 1000+ concurrent connections, you will need to make some changes to LinuxThreads, recompile it, and relink MySQL against the new `libpthread.a'. Increase PTHREAD_THREADS_MAX in `sysdeps/unix/sysv/linux/bits/local_lim.h' to 4096 and decrease STACK_SIZE in `linuxthreads/internals.h' to 256 KB. The paths are relative to the root of glibc Note that MySQL will not be stable with around 600-1000 connections if STACK_SIZE is the default of 2 MB.
If you have a problem with that MySQL can't open enough files, or connections, it may be that you haven't configured Linux to handle enough files.
In Linux 2.2 and forwards, you can check the number of allocated file handlers by doing:
cat /proc/sys/fs/file-max
cat /proc/sys/fs/dquot-max
cat /proc/sys/fs/super-max
If you have more than 16M of memory, you should add something like the following in your boot script (`/etc/rc/boot.local' on SuSE):
echo 65536 > /proc/sys/fs/file-max
echo 8192 > /proc/sys/fs/dquot-max
echo 1024 > /proc/sys/fs/super-max
You can also run the above from the command line as root, but in this case your old limits will be used next time your computer reboots.
You should also add /etc/my.cnf:
[safe_mysqld]
open-files-limit=8192
The above should allow MySQL to create up to 8192 connections + files.
The STACK_SIZE constant in LinuxThreads controls the spacing of thread stacks in the address space. It needs to be large enough so that there will be plenty of room for the stack of each individual thread, but small enough to keep the stack of some thread from running into the global mysqld data. Unfortunately, the Linux implementation of mmap(), as we have experimentally discovered, will successfully unmap an already mapped region if you ask it to map out an address already in use, zeroing out the data on the entire page, instead of returning an error. So, the safety of mysqld or any other threaded application depends on the "gentleman" behavior of the code that creates threads. The user must take measures to make sure the number of running threads at any time is sufficiently low for thread stacks to stay away from the global heap. With mysqld, you should enforce this "gentleman" behavior by setting a reasonable value for the max_connections variable.
If you build MySQL yourself and do not want to mess with patching LinuxThreads, you should set max_connections to a value no higher than 500. It should be even less if you have a large key buffer, large heap tables, or some other things that make mysqld allocate a lot of memory or if you are running a 2.2 kernel with a 2GB patch. If you are using our binary or RPM version 3.23.25 or later, you can safely set max_connections at 1500, assuming no large key buffer or heap tables with lots of data. The more you reduce STACK_SIZE in LinuxThreads the more threads you can safely create. We recommend the values between 128K and 256K.
If you have a problem with that MySQL can't open enough files, or connections, it may be that you haven't configured Linux to handle enough files.
In Linux 2.2 and forwards, you can check the number of allocated file handlers by doing:
cat /proc/sys/fs/file-max
cat /proc/sys/fs/dquot-max
cat /proc/sys/fs/super-max
If you have more than 16M of memory, you should add something like the following in your boot script (`/etc/rc/boot.local' on SuSE):
echo 65536 > /proc/sys/fs/file-max
echo 8192 > /proc/sys/fs/dquot-max
echo 1024 > /proc/sys/fs/super-max
You can also run the above from the command line as root, but in this case your old limits will be used next time your computer reboots.
You should also add /etc/my.cnf:
[safe_mysqld]
open-files-limit=8192
The above should allow MySQL to create up to 8192 connections + files.
The STACK_SIZE constant in LinuxThreads controls the spacing of thread stacks in the address space. It needs to be large enough so that there will be plenty of room for the stack of each individual thread, but small enough to keep the stack of some thread from running into the global mysqld data. Unfortunately, the Linux implementation of mmap(), as we have experimentally discovered, will successfully unmap an already mapped region if you ask it to map out an address already in use, zeroing out the data on the entire page, instead of returning an error. So, the safety of mysqld or any other threaded application depends on the "gentleman" behavior of the code that creates threads. The user must take measures to make sure the number of running threads at any time is sufficiently low for thread stacks to stay away from the global heap. With mysqld, you should enforce this "gentleman" behavior by setting a reasonable value for the max_connections variable.
If you build MySQL yourself and do not want to mess with patching LinuxThreads, you should set max_connections to a value no higher than 500. It should be even less if you have a large key buffer, large heap tables, or some other things that make mysqld allocate a lot of memory or if you are running a 2.2 kernel with a 2GB patch. If you are using our binary or RPM version 3.23.25 or later, you can safely set max_connections at 1500, assuming no large key buffer or heap tables with lots of data. The more you reduce STACK_SIZE in LinuxThreads the more threads you can safely create. We recommend the values between 128K and 256K.
