I'm trying to improve performance of our website, the boss is complaining that its too slow (though it is faster than a lot of sites). We experience particular slow down during updates - which are done periodically throughout the day. We have nearly 2 million records that are being searched and theres a lot of information there to pull back.
Currently all the tables are myIsam, and the total size is around 1.5Gb but the tables being searched probably total 600Mb. We're currently using all 2GB memory at high loads.
I tried converting the main search table to InnoDB but that just made everything horrendously slow - I also heard that it limits to 200 inserts per second whereas with myIsam we're getting around 1k - 1.5k per second.
Could the slow down be due to not converting the linked tables to innoDB? Or just lack of caching?
I now HEAP is supposed to be super fast for selects, how is it for inserts? Do you think 600Mb database would be ok to be loaded into 2Gb memory and still cope with high traffic loads (relatively high - approx 500 simultaneous users)
Or should I stick with MyIsam?