Author Topic: How to reduce the numbers of mysql conneted threads?  (Read 17257 times)

Tim Liu

  • Jr. Member
  • **
  • Posts: 8
How to reduce the numbers of mysql conneted threads?
« on: January 31, 2013, 02:08:31 AM »
Hi, guys

Our supervisor reachs its threads limit frequently.
ERROR: unable to handle connection overflow - spawned max threads: 256

So, I modified the qb.conf.
supervisor_max_threads = 1800
supervisor_max_clients = 1024
I thought increasing the value of supervisor_max_clients would reduce the numbers of threads used by supervisor. Is that true?

But, nothing helps. Still the same error.
ERROR: unable to handle connection overflow - spawned max threads: 1800

After that, I wanted cache some threads, so I set a mysql variable.
mysql> set global thread_cache_size=1000;

But, nothing helps too.
It seems that most of connected threads are sleep.
wait_timeout is 86400 currently. Is this value too big?

mysql> show status like '%thread%';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| Delayed_insert_threads | 0       |
| Slow_launch_threads    | 0       |
| Threads_cached         | 10      |
| Threads_connected      | 1801    |
| Threads_created        | 8222713 |
| Threads_running        | 2       |
+------------------------+---------+
6 rows in set (0.00 sec)

Wish you guys can help me.
Thank you.

Tim


jburk

  • Administrator
  • *****
  • Posts: 493
Re: How to reduce the numbers of mysql conneted threads?
« Reply #1 on: February 01, 2013, 01:48:21 AM »
First of all, 1800 supervisor threads is too many.  Some recommended values (without knowing too much about your configuration) would be:

supervisor_max_threads = 384
supervisor_max_clients = 256


But usually this is just like borrowing from your credit card to pay your bills, it just digs you into a hole faster.  The main issue you need to address is why you're hitting 256 supervisor threads on a frequent basis.

This usually indicates that supervisor processes are getting blocked during normal operation, and 99% of the time it's for disk i/o.  Most of the time they get blocked due to MySQL performance, but if your supe is very, very busy and you're writing the supe logs to the same set of disk spindles as the MySQL server, those two services end up stumbling over each other when writing to disk. 

What sort of filesystem is in your supervisor?  Is it a single spinning disk?

The normal operation of the supervisor is that the number of supe processes remains at or near the supervisor_idle_threads value.  This defines the lower limit on the supervisor's idle thread pool.  When a request comes in, the main supervisor process looks for an idle supervisor process in the thread pool.  If one's available, the main supe process hands the request off to the idle process.  If there are no threads in the idle pool, the main supervisor process will fire up another supervisor process and give the request to the new process.  Unless the total number of supervisor processes is at supervisor_max_threads, in which case you get the "max threads" error message you're seeing.

When a supervisor is done with a request, if it has handled less than supervisor_max_clients transactions, it goes back into the idle pool to wait for another request.  If it has performed enough transactions, it exits.  In this way, the total number of supervisor processes can shrink back to the lower limit of supervisor_idle_threads.

When it takes a single supervisor thread longer than normal to process the request it was given, there are rarely any threads in the idle pool, so the thread count is frequently spiking.  Normally, the reason threads take a long time is that MySQL performance is degrading due to poor disk i/o performance.  Adding more supervisor threads adds more load to an already over-loaded MySQL server, which blocks more supervisor threads, and you get an exponential increase in the supervisor thread count.

Another area that the supervisors can get blocked on is if the supervisor is writing either the supelog or the job logs to a networked filesystem, and the f/s is sometimes slow to finish the write; the supervisor process is blocked until the write is complete.

Are you on a Windows or linux supervisor?    This is more straight-fwd to troubleshoot on a linux supe.  If it's MySQL that's blocking the fileserver, you'll see fairly high load averages (100 or so on an 8-core box), a lot of cpu usage by MySQL, almost none by the supervisor, and the disk system will be very busy.  If it's the networked filesystem blocking the supervisor, you'll see extremely high load avg's (400 or higher on an 8-core box), but very little cpu utilization from either MySQL or the supervisor.

If you have a valid software subscription, you should open a support case with us on this.


Tim Liu

  • Jr. Member
  • **
  • Posts: 8
Re: How to reduce the numbers of mysql conneted threads?
« Reply #2 on: February 26, 2013, 02:51:14 AM »
Hi, jburk

Sorry for my late reply.I just come back to work after my spring festival vacation.

Thanks for your answer.We finally found this problem is not relate to Qube or Mysql.
It's the networked filesystem blocking the supervisor. We had changed the networked filesystem lately because the original one didn't have enough space. After we changed the networked filesystem back to the original one, supervisor started to run smoothly.

Yes, We have a valid ?software subscription. I'm a new Pipeline TD at Original force studio.I would open a support case with you next time.

Thank you

Tim