Stratus MySQL Alerts

Webscale STRATUS will send a MySQL Alert if a store has any query running for over an hour or if there are 5 queries running for more than 10 seconds.

These queries are proactively killed to protect a store from downtime.

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

The alert will contain the queries that were active at the time.

Common Issues

Below are some common issues seen with Magento databases. Please back up a copy of the database before performing any recommended fixes shown or contact MageMojo Support for assistance.

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

Webscale can empty these tables and blackhole them, which is often done on request. Webscale STRATUS 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, Webscale 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

The core_cache table is being written to, even though Redis is set for backend caching. This is caused by the Amasty Full Page cache.The documentation for configuring Amasty FPC correctly for Redis is in the Amasty docs.

report_viewed_product_index

Report tables: report_viewed_product_index

This table is used for the recently viewed products. MageMojo can clear and blackhole this table, although if these reports are viewed or there is a “latest products viewed” section on the site, contents of these tables are needed.

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 flush the table periodically and either let it rebuild on its own, or use a module to rebuild it automatically.

There are some alternatives discussed online.

To clear the table:

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. To continue to collect admin logs, do 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 tables 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 there are inserts in the core_session table, then MySQL is being used 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 orders 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);

mst_misspell_index

This table belongs to the Mirasvit Spellcheck module. There are missing indexes which force MySQL to perform a full table scan which is inefficient. You can add indexes as follows:

ALTER TABLE mst_misspell_index ADD FULLTEXT INDEX `FullText` (`trigram` ASC);
ALTER TABLE `mst_misspell_index` ADD INDEX `covered` (`index_id`);
ALTER TABLE `mst_misspell_index` ADD INDEX `covered1` (`keyword`);
ALTER TABLE `mst_misspell_index` ADD INDEX `covered2` (`trigram`);
ALTER TABLE `mst_misspell_index` ADD INDEX `covered3` (`frequency`);

Note: For MySQL 8, do not place “ASC” at the end of the first query:

To drop old entries ([idx_frequency] is the name of the index):

DROP INDEX `idx_frequency` ON mst_misspell_index;

Last modified January 1, 0001