However when they ask if you are above "MySQL\V5" or below it starts to make me nervous. Apparently I am not alone with the concern that InnoDB should be the storage engine of choice.
So I decided to dig a little more.....
I am assuming that you are running a more updated MySQL or at the very least you plan on doing that very soon.
I am assuming that you are running a more updated MySQL or at the very least you plan on doing that very soon.
> SELECT TABLE_SCHEMA, ENGINE, COUNT(*) AS count_tables, SUM(DATA_LENGTH+INDEX_LENGTH) AS size, SUM(INDEX_LENGTH) AS index_size FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'oscommerce' AND ENGINE IS NOT NULL GROUP BY TABLE_SCHEMA, ENGINE \G
*************************** 1. row ***************************
TABLE_SCHEMA: oscommerce
ENGINE: MyISAM
count_tables: 62
size: 795816
index_size: 546816
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, SUM(DATA_LENGTH+INDEX_LENGTH) AS size, SUM(INDEX_LENGTH) AS index_size FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'oscommerce' AND ENGINE IS NOT NULL GROUP BY TABLE_SCHEMA, TABLE_NAME;
+--------------+---------------------------------------+--------+--------+------------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE | size | index_size |
+--------------+---------------------------------------+--------+--------+------------+
| oscommerce | address_book | MyISAM | 1024 | 1024 |
| oscommerce | administrators | MyISAM | 9268 | 9216 |
| oscommerce | administrators_access | MyISAM | 9236 | 9216 |
| oscommerce | administrators_log | MyISAM | 4096 | 4096 |
| oscommerce | administrator_shortcuts | MyISAM | 4096 | 4096 |
| oscommerce | banners | MyISAM | 4096 | 4096 |
| oscommerce | banners_history | MyISAM | 1024 | 1024 |
| oscommerce | categories | MyISAM | 3192 | 3072 |
| oscommerce | categories_description | MyISAM | 11348 | 11264 |
| oscommerce | configuration | MyISAM | 32908 | 7168 |
| oscommerce | configuration_group | MyISAM | 2948 | 2048 |
| oscommerce | counter | MyISAM | 1034 | 1024 |
| oscommerce | countries | MyISAM | 39816 | 30720 |
| oscommerce | credit_cards | MyISAM | 2656 | 2048 |
| oscommerce | currencies | MyISAM | 3192 | 3072 |
| oscommerce | customers | MyISAM | 1024 | 1024 |
| oscommerce | fk_relationships | MyISAM | 7652 | 2048 |
| oscommerce | geo_zones | MyISAM | 2104 | 2048 |
| oscommerce | languages | MyISAM | 5224 | 5120 |
| oscommerce | languages_definitions | MyISAM | 90292 | 24576 |
| oscommerce | manufacturers | MyISAM | 9292 | 9216 |
| oscommerce | manufacturers_info | MyISAM | 4176 | 4096 |
| oscommerce | modules | MyISAM | 2568 | 2048 |
| oscommerce | newsletters | MyISAM | 1024 | 1024 |
| oscommerce | newsletters_log | MyISAM | 4096 | 4096 |
| oscommerce | orders | MyISAM | 1024 | 1024 |
| oscommerce | orders_products | MyISAM | 1024 | 1024 |
| oscommerce | orders_products_download | MyISAM | 1024 | 1024 |
| oscommerce | orders_products_variants | MyISAM | 1024 | 1024 |
| oscommerce | orders_status | MyISAM | 10332 | 10240 |
| oscommerce | orders_status_history | MyISAM | 1024 | 1024 |
| oscommerce | orders_total | MyISAM | 1024 | 1024 |
| oscommerce | orders_transactions_history | MyISAM | 1024 | 1024 |
| oscommerce | orders_transactions_status | MyISAM | 10324 | 10240 |
| oscommerce | products | MyISAM | 8596 | 8192 |
| oscommerce | products_description | MyISAM | 17924 | 15360 |
| oscommerce | products_images | MyISAM | 3216 | 3072 |
| oscommerce | products_images_groups | MyISAM | 3280 | 3072 |
| oscommerce | products_notifications | MyISAM | 1024 | 1024 |
| oscommerce | products_to_categories | MyISAM | 4123 | 4096 |
| oscommerce | products_variants | MyISAM | 4156 | 4096 |
| oscommerce | products_variants_groups | MyISAM | 3216 | 3072 |
| oscommerce | products_variants_values | MyISAM | 4348 | 4096 |
| oscommerce | product_attributes | MyISAM | 4136 | 4096 |
| oscommerce | product_types | MyISAM | 9236 | 9216 |
| oscommerce | product_types_assignments | MyISAM | 10328 | 10240 |
| oscommerce | reviews | MyISAM | 1024 | 1024 |
| oscommerce | sessions | MyISAM | 6816 | 2048 |
| oscommerce | shipping_availability | MyISAM | 3124 | 3072 |
| oscommerce | shopping_carts | MyISAM | 1024 | 1024 |
| oscommerce | shopping_carts_custom_variants_values | MyISAM | 1024 | 1024 |
| oscommerce | specials | MyISAM | 1024 | 1024 |
| oscommerce | tax_class | MyISAM | 2152 | 2048 |
| oscommerce | tax_rates | MyISAM | 4144 | 4096 |
| oscommerce | templates | MyISAM | 2160 | 2048 |
| oscommerce | templates_boxes | MyISAM | 3732 | 2048 |
| oscommerce | templates_boxes_to_pages | MyISAM | 11968 | 11264 |
| oscommerce | weight_classes | MyISAM | 3172 | 3072 |
| oscommerce | weight_classes_rules | MyISAM | 4288 | 4096 |
| oscommerce | whos_online | MyISAM | 10332 | 10240 |
| oscommerce | zones | MyISAM | 375892 | 247808 |
| oscommerce | zones_to_geo_zones | MyISAM | 5147 | 5120 |
+--------------+---------------------------------------+--------+--------+------------+
If you are running a store, with customer data then stability should be an important factor in your database of choice. I would like to update these to InnoDB easily.
>SELECT
CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' ENGINE=InnoDB;') as query
INTO OUTFILE '/tmp/update_oscommerce.sql'
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA') AND ENGINE IS NOT NULL AND TABLE_SCHEMA = 'oscommerce'
GROUP BY TABLE_SCHEMA, TABLE_NAME;
This query creates a simple ALTER TABLE for all of the oscommerce tables. If you have set your tables with a prefix into a database with other tables you can adjust query accordingly.
mysql -p < /tmp/update_oscommerce.sql
So did it work? Yes and you will have to be aware that you will see a different in the size and index size.
> SELECT TABLE_SCHEMA, ENGINE, COUNT(*) AS count_tables, SUM(DATA_LENGTH+INDEX_LENGTH) AS size, SUM(INDEX_LENGTH) AS index_size FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'oscommerce' AND ENGINE IS NOT NULL GROUP BY TABLE_SCHEMA, ENGINE \G*************************** 1. row ***************************TABLE_SCHEMA: oscommerce ENGINE: InnoDBcount_tables: 62 size: 3407872 index_size: 2031616
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, SUM(DATA_LENGTH+INDEX_LENGTH) AS size, SUM(INDEX_LENGTH) AS index_size FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'oscommerce' AND ENGINE IS NOT NULL GROUP BY TABLE_SCHEMA, TABLE_NAME;
+--------------+---------------------------------------+--------+--------+------------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE | size | index_size
+--------------+---------------------------------------+--------+--------+------------+
| oscommerce | address_book | InnoDB | 65536 | 49152
| oscommerce | administrators | InnoDB | 32768 | 16384
| oscommerce | administrators_access | InnoDB | 32768 | 16384
| oscommerce | administrators_log | InnoDB | 65536 | 49152
| oscommerce | administrator_shortcuts | InnoDB | 32768 | 16384
| oscommerce | banners | InnoDB | 49152 | 32768
| oscommerce | banners_history | InnoDB | 32768 | 16384
| oscommerce | categories | InnoDB | 32768 | 16384
| oscommerce | categories_description | InnoDB | 65536 | 49152
| oscommerce | configuration | InnoDB | 81920 | 16384
| oscommerce | configuration_group | InnoDB | 16384 | 0
| oscommerce | counter | InnoDB | 16384 | 0
| oscommerce | countries | InnoDB | 65536 | 49152
| oscommerce | credit_cards | InnoDB | 16384 | 0
| oscommerce | currencies | InnoDB | 32768 | 16384
| oscommerce | customers | InnoDB | 32768 | 16384
| oscommerce | fk_relationships | InnoDB | 16384 | 0
| oscommerce | geo_zones | InnoDB | 16384 | 0
| oscommerce | languages | InnoDB | 65536 | 49152
| oscommerce | languages_definitions | InnoDB | 147456 | 32768
| oscommerce | manufacturers | InnoDB | 32768 | 16384
| oscommerce | manufacturers_info | InnoDB | 49152 | 32768
| oscommerce | modules | InnoDB | 16384 | 0
| oscommerce | newsletters | InnoDB | 16384 | 0
| oscommerce | newsletters_log | InnoDB | 49152 | 32768
| oscommerce | orders | InnoDB | 49152 | 32768
| oscommerce | orders_products | InnoDB | 49152 | 32768
| oscommerce | orders_products_download | InnoDB | 49152 | 32768
| oscommerce | orders_products_variants | InnoDB | 49152 | 32768
| oscommerce | orders_status | InnoDB | 49152 | 32768
| oscommerce | orders_status_history | InnoDB | 49152 | 32768
| oscommerce | orders_total | InnoDB | 32768 | 16384
| oscommerce | orders_transactions_history | InnoDB | 32768 | 16384
| oscommerce | orders_transactions_status | InnoDB | 49152 | 32768
| oscommerce | products | InnoDB | 114688 | 98304
| oscommerce | products_description | InnoDB | 81920 | 65536
| oscommerce | products_images | InnoDB | 32768 | 16384
| oscommerce | products_images_groups | InnoDB | 32768 | 16384
| oscommerce | products_notifications | InnoDB | 49152 | 32768
| oscommerce | products_to_categories | InnoDB | 49152 | 32768
| oscommerce | products_variants | InnoDB | 49152 | 32768
| oscommerce | products_variants_groups | InnoDB | 32768 | 16384
| oscommerce | products_variants_values | InnoDB | 49152 | 32768
| oscommerce | product_attributes | InnoDB | 65536 | 49152
| oscommerce | product_types | InnoDB | 32768 | 16384
| oscommerce | product_types_assignments | InnoDB | 49152 | 32768
| oscommerce | reviews | InnoDB | 65536 | 49152
| oscommerce | sessions | InnoDB | 16384 | 0
| oscommerce | shipping_availability | InnoDB | 32768 | 16384
| oscommerce | shopping_carts | InnoDB | 65536 | 49152
| oscommerce | shopping_carts_custom_variants_values | InnoDB | 81920 | 65536
| oscommerce | specials | InnoDB | 32768 | 16384
| oscommerce | tax_class | InnoDB | 16384 | 0
| oscommerce | tax_rates | InnoDB | 49152 | 32768
| oscommerce | templates | InnoDB | 16384 | 0
| oscommerce | templates_boxes | InnoDB | 16384 | 0
| oscommerce | templates_boxes_to_pages | InnoDB | 65536 | 49152
| oscommerce | weight_classes | InnoDB | 32768 | 16384
| oscommerce | weight_classes_rules | InnoDB | 49152 | 32768
| oscommerce | whos_online | InnoDB | 65536 | 49152
| oscommerce | zones | InnoDB | 606208 | 376832
| oscommerce | zones_to_geo_zones | InnoDB | 65536 | 49152
+--------------+---------------------------------------+--------+--------+------------+
Since I happen to have innodb_file_per_table set I will get .ibd files per table of course as well.
> select @@innodb_file_per_table ;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
| 1 |
+-------------------------+
A quick test of the administration site as well as testing the shopping cart shows everything working just fine so far. An easy fix that will depend on table sizes as to how fast it is done for you. This example is with a fresh install.
If you have it replicated, then being able to turn off the slave and update the tables on the slave first would be a good start. Then rotate the master unless you can afford downtime.
If you do not have it replicated.. then you should look into it . You should also and I hope you do, have it backed up daily at the least.
