What is recommended for a DB with 25K products , MyISAM or InnoDB?
Doesn't really matter what the size but what the tables use. CSC uses MyISAM for all tables.
You can switch blocking tables to innodb but suggest you gather your stats over a long period before you do. Note that reads will be faster but writes slower.
IMHO stick with MyISAM. With InnoDB if something in the db table gets corrupted then you may be SoL.Repairing InnoDB is not an easy task that is it can be repaired.
still is it better to use MyISAM over InnoDB ??
i am running a store with a million products and thinking to switch to InnoDB for every table. Will this be a wrong move?
In my opinion, the vast majority of tables should be MyISAM. MyISAM may lock tables when writing, blocking other read requests, but the number of reads/writes is like 100/1. InnoDb will lock records on writes but every read has the overhead of determining what row(s) it needs to check locks on. I believe it is up the the 'read thread' to check for the locks in InnoDb (I.e. advisory locks) where MyISAM blocking of tables is done by the core server (enforced locks).
You can find plenty of white papers by simply googling "myisam versus innodb performance" (without the quotes).
So unless you're in a constant state of updating your million products, you're better off with MyISAM. The table that can probably benefit from being InnoDB is the user_sessions table and mabye some of the stats tables.
I think you're going to find that the bigger issues are requests that do full table scans of your products versus being able to use indexes. If you add more indexes, then you will also slow write performance, but increase read. It's all about what's the right strategy for your site.
If you're running a site with a million products then you should hire a professional mySQL consultant to determine the best strategy and mySQL server settings for your servers. Percona (I'm not affiliated with them) does excellent work. But you're going to find that a good professional DB consultant is very expensive but well worth the investment if you're running a true commercial site.
thnx for this your tips.. so after reading your post.. and some google stuff i have stopped the transfer from myISAM to InnoDB for now. Also i will get in touch with a professional. thnx for recommending one.
SO as per what i have understood from your post. InnoDB is good for tables which have more writes?? right? means the most important concern is to use InnoDB where table is blocked when writing to it.
i have for now altered few tables to InnoDB like you said, session tables, product popularity, etc.. the ones i could see need more writes.
did i got you right?
Like most system tuning, there are no hard and fast rules. It is more about understanding what's going on and then tuning things to get the greatest gains for your environment.
You'll just have to monitor and see what's blocking and whether that blocking is more costly than the overhead of looking at row-level locks.