Stratus Guides - MySQL Queries Gone Wild

For various reasons, Magento can sometime spurn queries for MySQL that can cause severe slowdown or stoppage for your Magento store. Webscale has solutions and best practices gathered from thousands of deployments over the years.

Accessing MySQL on Webscale STRATUS

To access your Magento MySQL instance on Webscale STRATUS:

Access your Magento install via SSH.
Navigate to the Magento web root (typically, public-html/).
Run n98-magerun db:console (Magento 1) or n98-magerun2 db:console (Magento 2).
Run the query you desire.

To Get Table Sizes

SELECT CONCAT(table_schema, '.', table_name),
      CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows,
      CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,
      CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx,
      CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
      ROUND(index_length / data_length, 2) idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  10;

To “Blackhole” Magento 1 Log Tables

There are a number of log-related tables that get bloated very quickly. These tables are rarely referred to by Magento store owners. If you don’t anticipate needing the data from these tables, then you should empty and “blackhole” them.

delete from log_url;
delete from log_url_info;
delete from log_visitor;
delete from log_visitor_info;
delete from report_event;
alter table log_visitor engine=BLACKHOLE;
alter table log_visitor_info engine=BLACKHOLE;
alter table log_url engine=BLACKHOLE;
alter table log_url_info engine=BLACKHOLE;

Magento 1 Viewed Products Index Table

If you are using recently viewed products on your store, then this will not apply. However, if you don’t use recently viewed products, this is another table that, by blackholing it, it can greatly reduce the size of your MySQL database.

DELETE from report_viewed_product_index;
ALTER TABLE report_viewed_product_index ENGINE=blackhole;

Magento 1 URL Rewrite Table

There are bugs in Magento 1 that cause the core_url_rewrite table to over-inflate with duplicate entries. It is recommended that this table should be periodically cleaned out.

TRUNCATE TABLE core_url_rewrite;

Magento 1 Dataflow Batch Tables

This table store pre-written results for DataFlow export and import. There is a bug in some versions of Magento where these tables are not truncated appropriately before and after an import /export. These tables can be truncated, however they should not be blackholed.

TRUNCATE TABLE dataflow_batch_export;
TRUNCATE TABLE dataflow_batch_import;

Magento 1 Sales Flat Quote Table

This table contains a record for every shopping cart created in a Magento store, whether the cart was abandoned or converted to a purchase. Each row represents one cart.

Due to the potential size of this table, may Magento stores delete records from this table based on a certain criteria (e.g., all non-converted carts after 60 days).

To delete all carts older than 60 days:

DELETE FROM sales_flat_quote WHERE updated_at < DATE_SUB(Now(),INTERVAL 60 DAY) AN is_active=1;

Magento 2 Customer Visitor Table

This table has been known to get quite large and slow down a Magento store. You can delete all customer data older than 3 days, for example, with:

DELETE FROM customer_visitor WHERE last_visit_at < DATE_SUB(NOW(), INTERVAL 3 DAY);

Table of Contents