Tech Note: MySQL Speed Improvements
Charles Johnson
Sun Mar 30, 2008 at 4:11 pm PDT • Views: 309
After scratching my head, pulling out my hair, consulting the Necronomicon, and Googling like a fiend for terms like “persistent connections” and “query cache bug,” I’ve made some changes to the LGF Blog Engine database layer that seem to have greatly improved performance. I didn’t want to say anything immediately, because the server hears you and then gets rebellious, but after several hours of tweaking and testing it now looks like the speed increase is genuine—and most pages seem to load at least twice as fast, comparing the current speed to the best case speed before the changes.
Technically, it was a matter of:
* setting the MySQL variables ‘interactive_timeout’ and ‘wait_timeout’ to 20 seconds.
* using a “persistent” database connection on the web server, so that connections are shared from a pool of existing connections, and each access doesn’t have to open a new one. The ‘timeout’ variables above set how long those persistent connections are allowed to remain open.
* turning on the MySQL query cache, which I still may turn off again if it exhibits the bug I was Googling for above.
If that’s Martian to you, don’t worry about it, just relax and enjoy the speed improvement…
Frank says: