Skip to content

MYSQL Alerts

We will send you a MySQL health alert notice:

  • if your store has any query running for over an hour
  • if there are 5 queries running for more than 10 seconds

These queries are proactively killed to protect your store from downtime. The alert will contain the queries that were active at the time.

Below are some common issue seen with Magento databases. Please back up a copy of your database before performing any recommended fixes here or you may contact MageMojo Support to help you.

Indexer mode

Indexers set to update on save causing locks Please read over our kb article on how to fix this issue and what causes it.

log_

INSERTS for visitor log tables : log_url, log_url_info, log_visitor, log_visitor_info

We can empty these tables and blackhole them. We often do this on request. We find that customers rarely look at the data in these tables and they cause a large strain on the database due to the large amount of data that is recorded with each customer visit. For prevention, we can perform the following:

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; alter table report_event engine=BLACKHOLE;

core_cache

the core_cache table + REDIS + Amasty FPC : core_cache

If the core_cache table is being written to, even though you have Redis set for backend caching. Please read over this thread. The documentation for configuring Amasty FPC correctly for Redis is in the Amasty docs

report_viewed_product_index

Report tables: report_viewed_product_index.

More info here This table is used for the recently viewed products We can clear and blackhole this table, though if these reports are viewed or there is a "latest products viewed" section on the site, contents of these tables may be wanted.

DELETE from report_viewed_product_index; ALTER TABLE report_viewed_product_index ENGINE=blackhole;

core_url_rewrite

Magento 1 large table : core_url_rewrite.

There are bugs within Magento that cause the core_url_rewrite table to over-inflate with duplicate entries. It is definitely recommended to clean that table out once in a while, and either let it rebuild on its own, or use a module to rebuild it automatically. You can find some more information on this, along with possibilities on how to correct that behavior in posts such as this one

TRUNCATE TABLE core_url_rewrite;

enterprise_logging_event

Large table: enterprise_logging_event.

This table can be truncated and blackholed, however it contains login information for admins. If you want to continue to collect admin logs, you should not blackhole this table.

TRUNCATE TABLE enterprise_logging_event;

dataflow_batch_

Large table: dataflow_batch_export, dataflow_batch_import.

These tables can be truncated, however, they should not be blackholed. These 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.

DELETE FROM dataflow_batch_export; DELETE FROM dataflow_batch_import; TRUNCATE TABLE dataflow_batch_export; TRUNCATE TABLE dataflow_batch_import;

core_session

If you are seeing inserts in the core_session table, then you are using MySQL to store sessions. This is a large strain on the database. Session storage should be switched to either Redis or Memcache. Please contact support if you need assitance.

sales_flat_quote

sales_flat-quote contains records on every shopping cart created in your store, whether they were abandoned or converted to a purchase. Each row represents one cart. Due to the potential size of this table, many businesses delete records from this table after a certain set of criteria are met (i.e. delete all non-converted carts 60 days after created).

To delete older than 60 days:

DELETE FROM sales_flat_quote WHERE updated_at < DATE_SUB(Now(),INTERVAL 60 DAY)

customer_visitor

This table has known to get quite large and slow things down. You can delete all customer data older than 3 days with the following:

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