I have an OpenCart project with about 50,000 products in the database. I've switched the web hosting from a VPS to a dedicated server and I've seen some improvements, but still the website is laggy.
On the VPS all the tables were MyISAM with latin1_swedish_ci collation on mysql 5.1.x.x . On the new server I've switched to TokuDB and MariaDB 10 . For some tables I had to drop the FULLTEXT indexes and I saw some drop in performance (all the tables are now on TokuDB).
Is it a smart decision to switch some tables back to MyISAM (just to use the FULLTEXT indexes)? OpenCart has some queries joining a lot of tables ... if some are on TokuDB and some on MyISAM is there a penalty? I'm looking just for performance, the resources are on the 2nd place.
Thanks a lot!
Its OK if you know what you're doing and why you are doing it.
Different engines use memory and disk storage very differently. For an OLTP type system InnoDB is usually more sensible than MyISAM (did you check the contention before trying a different engine?). But any memory you add to the buffer pool (to improve InnoDB performance) is no longer available to the VFS or for sort buffers (helping MyISAM performance).
I'm really struggling to imagine how TokuDB makes any sense as a storage substrate for eCommerce site. Its all about getting faster writes for inserts and updates, and low maintenance on SSD - select performance is rarely better than the other engines.