By default, for every client connection the MySQL server spawns a separate thread which will process all statements for this connection. This is the ‘one-thread-per-connection’ model. It’s simple and efficient until some number of connections N is reached. After this point performance of the MySQL server will degrade, mostly due to various contentions caused by N threads that are trying to access shared resources: either system ones like CPU, IO, memory or MySQL specific: structures/locks/etc.
Beware of bugs in the above code; I have only proved it correct, not tried it.
Bugs in software are a fact of life. MySQL, as part of Oracle, issues of Critical Patch Updates and Security Alerts notices. You may have seen Daniel van Eeden‘s blog on the January announcement.
When we are hired for a MySQL performance audit, we are expected to review the MySQL configuration and to suggest improvements. Many people are surprised because in most cases, we only suggest to change a few settings even though hundreds of options are available. The goal of this post is to give you a list of some of the most critical settings.
Early this month I spoke at linux.conf.au in Perth, Australia, where I presented “Past, Present and Future of MySQL and variants.”Here’s my presentation in its entirety. Please let me know if you have any questions or comments. (linux.conf.au is widely regarded by delegates as one of the best community run Linux conferences worldwide.)
The other day I was working on an issue where one of the slaves was showing unexpected lag. Interestingly with only the IO thread running the slave was doing significantly more IO as compared to the rate at which the IO thread was fetching the binary log events from the master. A closer inspection showed that this had to do with the error log getting filled up with error messages apparently suggesting that the slave IO thread was disconnecting and reconnecting repeatedly.Below is the type of error messages that were being generated and were filling up the error log: