Heap vs InnoDB vs MyIsam

[NancyJ]

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?

[raf]

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?
InnoDB will always be slower then myisam, because it's extra functionalitys create extra overhead.
a heap should be a lott faster but i realy have no idea if it would be a good idea to load a 600MB table into memory. if i'm not mistaken, you also can't text or blob field in a heap. so if you're searching on those ....
Maybe you can just load the columns you actually search on into memory?
overall:
- make sure that your colmumntypes are as small as possible --> saves memory on selects
- try to avoid data-manipulations during the day (do your inserts/deletes/updates during the day)
- keep in mind that indexes slow down your inserts --> might be required to drop the index before large number of inserts, and then recreate is again
- keep your table as small as possible --> delete or archive older records
- measure how long the selects actually take --> it's a known fact that most users have the impression that applications are getting slower and slower, while this is actually not happening at all.

[Beagle]

You might be looking in the wrong area. Is it possible that you could restructure the database for better performace instead of trying different technologies?

Have you checked to figure out where your bottle neck is? Is it, in fact, at the DBMS level? Are the algorithms in your script to blame?

Before trying to solve a problem, you should identify it's causes.

If you know it's the DBMS, you might want to look into distributing the DB over multiple machines. It sounds like your database may start to require a more robust hardware layer than a single server, and you might want to research it to help you increase your potential for growth.

But really, if you want, we can look over your DB structure to determine if your being efficient in your data storage.