magento 2.4.5 huge category reindex catalogsearch_fulltext is very slow

Question

I have more than 100000+ products and 20 store views, one of the category has 60000+ products, when I reindex catalogsearch_fulltext, It took more than 20 hours, and still doesn't finish.

and the category with 60000+ products also couldn't access got 500 error.

I googled many articles to try to figure out solution, but all doesn't work for me.

my server is 16v CPU, 128G RAM, Mariadb 10.4, Nginx + PHP8.1

skip-name-resolve=1

table_open_cache = 2000

table_definition_cache = -1

performance_schema=ON

join_buffer_size = 8M

innodb_buffer_pool_size = 30G

innodb_log_file_size = 16M

tmp_table_size = 25G

max_heap_table_size = 25G

innodb_log_file_size = 3G

innodb_io_capacity=600

read_rnd_buffer_size=128K

innodb_buffer_pool_instances=16

innodb_lru_scan_depth=100

log_slow_verbosity=query_plan,explain

slow_query_log=ON

I also changed memory_limint to 8G. anyone can help me to solve it? it's been a long time, I've tried all suggestions on google.

Answer 1

Increase the memory limit for PHP and adjust the max_execution_time parameter to allow more time for the indexer to complete

Answer 2

According to Magento Documentation

Reindexing on MariaDB 10.4 takes more time compared to other MariaDB or MySQL versions. To speed up reindexing, we recommend setting these MariaDB configuration parameters:

optimizer_switch=‘rowid_filter=off’

optimizer_use_condition_selectivity = 1

You can find it at the end of page General MySQL guidelines

Answer 3

according to Adobe in this documentation: https://experienceleague.adobe.com/docs/commerce-admin/catalog/catalog/catalog-flat.html?lang=en Use of a flat catalog is no longer recommended as a best practice.

Answer 4

I set

System > Configuration > Catalog > Catalog > Frontend and setting "Use Flat Catalog Category" and "Use Flat Catalog Product" to "Yes".
and optimizer_switch=‘rowid_filter=off’
optimizer_use_condition_selectivity = 1

I also used elasticsearch 7 but when I reindex catalogsearch_fulltext, It still take more than 24 hours and doesn't finish.

Answer 5

It sounds like you're issue experiencing reindexing and accessing a large category. Here are solutions that you can try:

1. Split up the category: If a single category has 60000+ products, it might be worth considering splitting it up into smaller subcategories. This can help with both indexing and browsing performance.
2. Upgrade your hardware: If your server is underpowered, it might struggle with indexing and serving pages for a large store. Consider upgrading to a more powerful server or using a cloud hosting solution.
3. Optimize your database: Large databases can also cause performance issues. Consider optimizing your database and removing any unnecessary data. You can use tools like MySQLTuner or Percona Toolkit to help with this.
4. Enable flat catalog: Enabling flat catalog can help with performance by reducing the number of database queries needed to fetch product data. You can do this by going to System > Configuration > Catalog > Catalog > Frontend and setting "Use Flat Catalog Category" and "Use Flat Catalog Product" to "Yes".
5. Use Elasticsearch instead of MySQL for catalog search: Elasticsearch is a powerful search engine that can handle large volumes of data more efficiently than MySQL. Consider integrating Elasticsearch into your store for faster and more accurate search results.
6. Use a third-party indexing solution: There are third-party solutions available that can help with indexing large Magento stores. These solutions typically use more advanced indexing algorithms and can be faster and more reliable than Magento's built-in indexing.
7. Consider upgrading to Magento 2: Magento 2 is a more modern and scalable platform that can handle large stores more efficiently than Magento 1. If you're still using Magento 1, it might be worth considering upgrading to Magento 2.