It's an InnoDB database. The query cache has 1.2GB in size with 300k queries in the cache. Most of the cached queries are related to 3 major tables (260MB data length plus 600MB index length altogether). Everytime I tried to insert new entry or update existing entries in these 3 tables, MySQL slowed down a lot just like the server is dead. I got "Timeout" if I open my website in browser.
I check the Munin stats, it shows 95% of the query cache are cleared after the operation so I guess the sudden overload was caused by inserting entries to heavy-read tables. And during the overload, Munin just shows a white strip on the graph so you may get an idea on how bad the situation was.
How can I optimize this?