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.
