We will send a MySQL health alert notice:
- If a 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 a store from downtime. The alert will contain the queries that were active at the time.
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.
Indexers set to update on save causing locks
Please read over our kb article on how to fix this issue and what causes it.
INSERTS for visitor log tables : log_url, log_url_info, log_visitor, log_visitor_info
MageMojo can empty these tables and blackhole them, which is often done on request. 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, MageMojo 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;
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 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;
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.
To clear the table:
TRUNCATE TABLE core_url_rewrite;
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;
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;
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 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)
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);
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`);
To drop old entries ([idx_frequency] is the name of the index):
DROP INDEX `idx_frequency` ON mst_misspell_index;