1

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!

valicu2000
  • 441
  • 5
  • 19
  • Yes. Perfectly acceptable, but this could be construed as opinion. – Strawberry Mar 14 '15 at 17:00
  • Each engine has its strengths and weaknesses. Learn [about them](http://stackoverflow.com/q/12614541/4265352) and choose for each table the appropriate engine depending how the table is used by the application. – axiac Mar 14 '15 at 17:06
  • InnoDB now has FULLTEXT. And it seems to be faster than MyISAM's FT. – Rick James Mar 14 '15 at 17:21
  • generally it's fine to mix engines. could you please add my.cnf, RAM, and put table size of most active tables (uncompressed) – ravnur Mar 14 '15 at 18:45

2 Answers2

1

I think it is correct, I have mix storage engines in a databases, and the speed of database is right.

Anyway if you uses opencart with a lot products (50,000), I recomending you that you created index on (all) of your tables, if you do that you can improving your speed.

I did the following: 

    #Tabla category campo parent_id
CREATE INDEX i_parent_id ON category (parent_id);

#Tabla category_description campo language_id
CREATE INDEX i_category_description ON category_description (language_id);

#Tabla category_path campos path_id y level
CREATE INDEX i_category_path ON category_path (path_id,level);

#Tabla category_to_store campo store_id
CREATE INDEX i_category_to_store ON category_to_store (store_id);

#Tabla manufacturer_to_store campo store_id
CREATE INDEX i_manufacturer_to_store ON manufacturer_to_store (store_id);

#Tabla product campos manufacturer_id, date_added, date_modified
CREATE INDEX i_product ON product (manufacturer_id, date_added, date_modified);

#Tabla product campos model, sku, upc, ean,(como veis donde tengais la referencia etc) y con tipo FULLTEXT (si el campo es de caracteres no de números)
CREATE FULLTEXT INDEX i_product_fulltext ON product (model, sku, upc, ean);

#Tabla product_description campo language_id
CREATE INDEX i_product_description ON product_description (language_id);

#Tabla product_to_category campo category_id
CREATE INDEX i_product_to_category ON product_to_category (category_id);

#Tabla product_to_store campo store_id
CREATE INDEX i_product_to_store ON product_to_store (store_id);

#Tabla setting campo store_id, serialized
CREATE INDEX i_setting ON setting (store_id, serialized);

#Tabla url_alias campo query con tipo FULLTEXT
CREATE FULLTEXT INDEX i_url_alias ON url_alias (query);# 6936 filas afectadas.

#Tabla zone campo country_id
CREATE INDEX i_zone ON zone (country_id);

#Tabla zone campo name y code con tipo FULLTEXT
CREATE FULLTEXT INDEX i_zone_fulltext ON zone (name,code);

You will have to put the 'prefix' on your tables.

Something like this: http://www.codigojavaoracle.com/desarrollo-web/mejorar-la-velocidad-en-opencart/

ber2008
  • 323
  • 3
  • 10
0

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.

symcbean
  • 47,736
  • 6
  • 59
  • 94