vicidial.org

Posted: **Thu Apr 26, 2018 10:03 pm**

Hey guys. another weird issue I have been getting lately on vicibox 8 that I didn't have on vicibox 7.

We are running a Survey campaign and when we turn the dial level up we get a message

MySQL connect ERROR: Can't create a new thread (errno 11 "Resource temporarily unavailable"); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

I have been changing stuff in my.cnf googling like crazy but can't seem to find what the setting is here.
We are not using anywhere near the 32gb RAM in the database server.

Heres what I have noticed. the error messages starts popping up when I am around 485 threads connected.
I see this number with this in the command line echo 'SHOW STATUS;' | mysql | grep -i threads

Delayed_insert_threads 0
Slow_launch_threads 0
Threadpool_idle_threads 0
Threadpool_threads 0
Threads_cached 1
Threads_connected 484
Threads_created 1612
Threads_running 1

and cat /proc/sys/kernel/threads-max gives me 257173 if that means anything here.

Heres what we have tried.
Reinstalling, raising mysql max connections as high as 8000, lowering mysql max connections as low as 512 Increasing ulimit
cat /proc/sys/kernel/threads-max is 257173

We have changed various my.cnf settings. so much so that I am not even sure what we did anymore.
Heres what we are currently at

datadir = /srv/mysql/data
server-id = 1
slave-skip-errors = 1032,1690,1062
slave_parallel_threads=2
slave-parallel-mode=optimistic
port = 3306
socket = /var/run/mysql/mysql.sock
sql_mode=NO_ENGINE_SUBSTITUTION
skip-external-locking
skip-name-resolve
connect_timeout=60
long_query_time=3
slow-query-log=1
slow-query-log-file=/var/lib/mysql/mysqld-slow.log
log-slow-verbosity=query_plan,explain
max_connections=512
key_buffer_size = 3G
max_allowed_packet = 16M
table_definition_cache=2048
table_open_cache = 3000
sort_buffer_size = 4M
net_buffer_length = 8K
read_buffer_size = 4M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 128M
query_cache_size=32M
query_cache_type=1
thread_concurrency=16
default-storage-engine=MyISAM
expire_logs_days = 3
concurrent_insert = 2
myisam_repair_threads = 4
myisam_recover_options=DEFAULT
tmpdir = /tmp/
thread_cache_size=100
join_buffer_size=1M
myisam_use_mmap=1
open_files_limit=245760
max_heap_table_size=256M
tmp_table_size=16M
key_cache_segments=64

I assume it has to be some setting I need to adjust to let mysql use more resources or use more ram or something. Has anyone ran across this?
Dell R610
32gb ram, Raid 1 SSD

VERSION: 2.14-669a
BUILD: 180411-1647
SVN Version: 2966
DB Schema Version: 1541
Installed using vicibox 8
Asterisk 11.25.3-vici

Posted: **Sat Apr 28, 2018 10:50 am**

Increase limits in /etc/security/limits.conf for mysql user for nproc and nofile to 10240. this should resolve your issue

Posted: **Sat Apr 28, 2018 9:38 pm**

We had already done the following.

mysql soft nofile 10240
mysql hard nofile 40960
mysql soft nproc 10240
mysql hard nproc 40960

This did not resolve it. Perhaps I will try increasing further...

Posted: **Sun Apr 29, 2018 1:20 am**

Take a look here, could be similar issue:

https://www.percona.com/blog/2013/02/04 ... _errno_11/

Posted: **Sun Apr 29, 2018 3:13 pm**

Thanks for the link! we had tried that, we tried it again now and rebooted and still breaking above 485 theads

'SHOW STATUS;' | mysql | grep -i threads
Threads_connected 484

Posted: **Wed May 02, 2018 2:43 pm**

What are the specs and number of agents on the server?

query_cache_size on bigger systems can be 0. MySQL connections should be close to 1000. i have tried bigger thread_cache_size, like upto 256 and it worked better imo.

Posted: **Wed May 02, 2018 3:55 pm**

alo wrote:Thanks for the link! we had tried that, we tried it again now and rebooted and still breaking above 485 theads

'SHOW STATUS;' | mysql | grep -i threads
Threads_connected 484

/etc/my.cnf

max_connections=2000

service mysql restart

have a look at /usr/src/astguiclient/trunk/extras/mysql-tuning.sh

Posted: **Wed May 02, 2018 10:12 pm**

Heres what we have tried.
Reinstalling, raising mysql max connections as high as 8000, lowering mysql max connections as low as 512 Increasing ulimit
cat /proc/sys/kernel/threads-max is 257173

I set it to 2000 for tomorrows test. but we tried as high as 8000 and still get the error

I also Set per frequency suggestion:
query_cache_size 0, thread_cache_size 256

Thanks for the suggestions guys. excited to see for tomorrow.

Posted: **Wed May 02, 2018 10:39 pm**

alo wrote:
Heres what we have tried.
Reinstalling, raising mysql max connections as high as 8000, lowering mysql max connections as low as 512 Increasing ulimit
cat /proc/sys/kernel/threads-max is 257173

I set it to 2000 for tomorrows test. but we tried as high as 8000 and still get the error

I also Set per frequency suggestion:
query_cache_size 0, thread_cache_size 256

Thanks for the suggestions guys. excited to see for tomorrow.

I wonder if "the error" is different when you have it set higher. Note that apache often runs out of sockets and fails to connect, which is not a max_connections failure in mysql at all.

Be sure your errors are continuing to increase when you are having your problem, or you may be looking at an old problem leading you on a wild goose chase.

On each server:

Code: Select all: netstat -n | grep TIME_WAIT | wc -l

Showing ... hundreds is normal, thousands Not So Much.

also: I imagine you are experiencing a problem other than this entry in the mysql system: What might that real-world symptom be? AND where do you see the error? Web page? Log? Which log? How often?

How does memory usage look when this happens? (You may actually be running out of memory, too! it happens)

Posted: **Wed May 16, 2018 9:22 pm**

This error is a major PITA.

This is what I did to fix when the above suggestions did not work:

Edit /etc/systemd/system.conf

Added:

Code: Select all: DefaultTasksMax=infinity

To the bottom of the file and rebooted the database server.

You can check the change with:

Code: Select all: systemctl show -all | grep Tasks

Should return something like:

Code: Select all: DefaultTasksAccounting=yes DefaultTasksMax=18446744073709551615

Hope this helps.

Posted: **Wed Aug 08, 2018 1:57 am**

bmorrison wrote:This error is a major PITA.

This is what I did to fix when the above suggestions did not work:

Edit /etc/systemd/system.conf

Added:

Code: Select all
DefaultTasksMax=infinity

To the bottom of the file and rebooted the database server.

You can check the change with:

Code: Select all
systemctl show -all | grep Tasks

Should return something like:

Code: Select all
DefaultTasksAccounting=yes DefaultTasksMax=18446744073709551615

Hope this helps.

https://news.ycombinator.com/item?id=11675129

The maximum applies to kernel "tasks", not to processes. So whilst one can hit it by forking a lot of processes, one can also hit it with only a few processes that happen to use a lot of threads per process. This is of particular concern to MySQL, MariaDB, Percona, and their ilk, which use a thread per client connection. As Brad Mashall discovered, the effect is that the SQL servers start refusing client connections.

vicidial.org

MySQL connect ERROR: Can't create a new thread

MySQL connect ERROR: Can't create a new thread

Re: MySQL connect ERROR: Can't create a new thread

Re: MySQL connect ERROR: Can't create a new thread

Re: MySQL connect ERROR: Can't create a new thread

Re: MySQL connect ERROR: Can't create a new thread

Re: MySQL connect ERROR: Can't create a new thread

Re: MySQL connect ERROR: Can't create a new thread

Re: MySQL connect ERROR: Can't create a new thread

Re: MySQL connect ERROR: Can't create a new thread

Re: MySQL connect ERROR: Can't create a new thread

Re: MySQL connect ERROR: Can't create a new thread