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.
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
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;
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 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;
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;
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;
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;
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 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)
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, keyword, trigram, frequency);
To drop old:
DROP INDEX 'idx_frequency' ON mst_misspell_index;
(where idx_frequency is index name)`