As you well know, I have actually not been blogging for the last few weeks, it’s all done by our network blogger, Andrew. (Thank you Andrew btw, you saved my as* during a horribly busy month) – I have updated the author links too so you know who’s writing which blog posts.
I have been busy with legalization of my company lately, sorry if I haven’t been posting for our regular readers.
Anyways, today I got an update on the WordPress Translator Plugin from Angsuman over at Simple Thoughts blog. I’ve always been facinated by his programming and his plugin, even though it’s not free, he has been giving me free updates for last 2 years, that’s called “excellent” customer service.
The thing that interests me the most is not the translator update (I do highly recommend it if you have a blog), but his blog post on how to increase load without failing your server.
(For those of you not familiar with MySQL or website programming, please head over to another blog post as this will be boring for you. 🙂 )
It’s so simple, it’s one line added to your my.cnf file under /etc directory.
max_write_lock_count = 1
I’ve actually tested this configuration on my SiteHoppin server where there’s many, many SELECT and UPDATE MySQL commands as people submit new URLs constantly to the ever-growing database.
After using this configuration, I’ve experienced that my server actually can handle 5-10 times more traffic, which is awesome since I was about to spend another $500 on a quad-core, 16GB dedicated server. 🙂
I cannot really prove how it works other than I trust Angsuman and I know he’s much better programmer than I am so head over to his blog post over here if you want to know the technical stuff behind it.
Here’s an excerpt:
MySQL executes INSERT & UPDATE statements with higher priority. Also INSERT & UPDATE statements require table lock (for MYISAM engines) which requires even table reads (SELECT statements) to be completed before INSERT & UPDATES are executed. This can cause long delays for SELECT statements waiting behind an INSERT or UPDATE statement, which may itself take minimal time to execute, which is waiting for existing long running SQL Select statements to be completed. So even a single INSERT or UPDATE can slow-down a heavily loaded database at unpredictable times.
One solution is to use INSERT DELAYED statement to cause INSERT statements to be run at lower priority in a queue. However similar statement for UPDATE isn’t available. Also in our experiments it proved to be significantly inferior to the solution I will describe next. So are you ready?
I recommend that you add the following line in /etc/my.cnf (MYSQL configuration file in Linux; search for my.cnf in Windows) to drastically reduce the possibility of such bottlenecks, as described above, and improve the performance of heavily loaded servers:
max_write_lock_count = 1
By starting mysqld with a low value for the max_write_lock_count system variable you are forcing MySQL to temporarily elevate the priority of all SELECT statements that are waiting for a table after a specific number of inserts to the table occur. This allows READ locks after a certain number of WRITE locks. Is that clear?
The bottomline is that this simple configuration can drastically improve your MySQL performance especially if your server is heavily loaded. You should try this for optimizing any heavily loaded MySQL server including but not limited to WordPress site databases for example.
One Response to MySQL Optimization Hack – How to Increase Load without Failing your MySQL Server!
Leave a Reply
Pingback: WordPress News for 1/15/2009 | Web2.0