Magento 2

How to deal with a huge Magento 2 database

by , , revisited on


We have by far the largest RPM repository with dynamic stable NGINX modules and VMODs for Varnish 4.1 and 6.0 LTS. If you want to install NGINX, Varnish and lots of useful modules for them, this is your one stop repository to get all performance related software.
You have to maintain an active subscription in order to be able to use the repository!

To make the story short, you likely don’t need the data that bloats your Magento 2 database.
Below will review the tables which are usually grown without control, and how to keep them at bay for performance reasons.

More often than not, a huge Magento database will not scale in terms of performance.
Even MySQL has indexes to speed up queries against the big data, you will surely have a plugin (or even core code), which will make a bad query that doesn’t involve index use.

So essentially, a big Magento 2 database is evil unless you make it perfectly bug-free…
Which as we know is impossible, because Magento 2 is a huge trash bin of bugs.
Closed tickets on GitHub do not equal to solved issues. You will find many issues which are prematurely closed.

Locating the huge tables

Find largest tables in all databases

SELECT table_schema as "Database", table_name AS "Table", 
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" 
FROM information_schema.TABLES 
WHERE (data_length + index_length) > 1000000 
ORDER BY table_schema, (data_length + index_length) DESC

This lists tables from all databases, which exceed 1 MB, sorted by size.

Find largest tables in a specific database

If you’re interested in a specific database only, you can run:

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE (data_length + index_length) > 1000000  AND table_schema = "magento" 
ORDER BY (data_length + index_length) DESC; 

… where magento is your Magento database name,

Huge search_query table

This table is known to give performance problems. There is no cleaning done against it, so it will grow and grow during the lifetime of your website.

How to clean it? Use a monthly cron with db:maintain:search-query command:

@monthly n98-magerun2 db:maintain:search-query --root-dir=/path/to/magento2  

This will keep the related functions functioning while getting rid of irrelevant data (search queries that produce zero results and made more than a month ago).

Why this table being huge is a performance issue? Any time you update a product in admin, all rows in this table are reset to have is_processed set to 0.
The more rows to update, the worse. It can take minutes to update a product, if you’ve never cleaned this table.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.