RavenxKnight
[online]
wrote on
December 27, 2011, 12:13am
MySQL
If you are not a database admin, skip this entry. It'll be gibberish to you.
I had MySQL nice and tuned more than 6 months ago. Everything was swell.. Fast queries and low server loads.
Last week I converted some heavy usage tables over to InnoDB from MyISAM. I did this so I could add numerous foreign keys and utilize referential integrity (cascading updates and deletes).
After the conversion from MyISAM to InnoDB tables, mysqld CPU utilization, as reported by top, jumped from around 5% to 80+%. Queries started dragging out..
No doubt this was due to the foreign keys, but I wasn't expecting such a large hit on performance. I've since gotten mysqld CPU utilization to hold under 40% (during peak periods) by adjusting 3 MySQL variables: table_cache, query_cache_limit, and sort_buffer_size.
Query_cache_limit I reduced by 100%. Sort_buffer_size I reduced by 75%. Table_cache I increased by 50%.
