Magento DB Optimization

Question

We run a pretty big Magento store. Some stats:

• 31 GB in DB size
• Over 700,000 orders
• Over 4,000,000 quotes
• Over 1,000,000 registered customers
• Over 25,000 products

The store runs fine, but we've been experiencing trouble in altering table structures (principally installing new modules that create lots of database structures). By cleaning all the log tables, we can reduce the DB size to 20GB, but we still have quite a lot of trouble installing third-party modules (even in our staging environment, installations may timeout or either run for several hours). Creating these new DB structures is really exhausting.

Sessions are stored with Memcache. We use varnish but we're switching to FPC as son as we find a workaround for all the 6 servers. All the infrastructure is hosted in AWS. We're running Magento CE 1.6.2.0 (updating is in our plans but running the update isn't the fastest thing on this DB and and we're afraid it won't be possible in production without a big disruption of service).

I need alternatives or ideas for optimizing this Magento DB. I've read a few things about Mongogento (magento catalog + mongodb), but our problem isn't really the catalog.

Most topics on the internet talk about cleaning log tables, which improves the usability but doesn't solve all of our problems.

Edit: Biggest tables

Answer 1

First I would use AOE Quote cleaner to remove old quotes from your system

https://github.com/fbrnc/Aoe_QuoteCleaner

I am not sure how you get to 30gb when your biggest table is 2.8gb?

For example the following client has 1.3 million orders

mysql> SELECT table_name AS "Tables", 
    -> round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" 
    -> FROM information_schema.TABLES 
    -> WHERE table_schema = 'database_name'
    -> ORDER BY (data_length + index_length) DESC;
+---------------------------------------------------+------------+
| Tables                                            | Size in MB |
+---------------------------------------------------+------------+
| sales_flat_order_item                         |    2002.13 |
| sales_flat_order                              |    1284.88 |
| customer_address_entity_varchar               |     642.39 |
| enterprise_sales_order_grid_archive           |     552.56 |
| sales_flat_invoice_item                       |     465.56 |
| sales_flat_invoice                            |     460.27 |
| sales_flat_order_address                      |     422.52 |
| enterprise_sales_invoice_grid_archive         |     392.81 |
| sales_flat_order_status_history               |     372.00 |
| 

You should also do some SQL tuning as we never have issues on our database side. You should also consider upgrading to 1.9

The above client uses a MySQL cluster

In addition you can look at an order Archiver http://www.magentocommerce.com/magento-connect/order-archive-by-amasty.html