2

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

enter image description here

  • how many store in your system? – Amit Bera May 7 '15 at 16:56
  • this is pretty fascinating story - but - what is your db instance specs??? by the way 31/20 GB in DB size looks unusually VERY big to me, can you also post your biggest tables? – user2857 May 7 '15 at 19:28
  • @AmitBera there is one store with two views (desktop/mobile). – Vinícius May 8 '15 at 13:01
  • @ADM edited the post with the biggest tables. I'm afraid I won't be able to give you exact specs about the DB (since I don't have direct access to the client's account), though we're running 2 RDS instances for reading and 1 for writing. – Vinícius May 8 '15 at 13:04
  • thats the question, what exactly you are trying to optimize then??? please find RDS instance name/size, storage size, IOPS, etc. have you tried to run mysqltuner.pl to see whats going on? please add these to your question. – user2857 May 8 '15 at 18:17
0

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

enter image description here

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.