1

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.

6
  • What is your version of MySql/MariaDB?
    – panosdotk
    Mar 5, 2023 at 17:00
  • Mariadb 10.4 @panosdotk
    – myfixone
    Mar 6, 2023 at 5:21
  • 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’ and optimizer_use_condition_selectivity = 1. This will speed up reindex procedure
    – panosdotk
    Mar 6, 2023 at 11:36
  • Additional DB information request, please. Any SSD or NVME devices on MySQL Host server? Post TEXT data on justpaste.it and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*), sum(data_length), sum(index_length), sum(data_free) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; G) SHOW ENGINE INNODB STATUS; for server workload tuning analysis to provide suggestions. Mar 14, 2023 at 20:52
  • Post TEXT data on justpaste.it and share the links. Additional very helpful OS information includes - please, htop 1st page, if available, TERMINATE, top -b -n 1 for most active apps, top -b -n 1 -H for details on your mysql threads memory and cpu usage, ulimit -a for list of limits, iostat -xm 5 3 for IOPS by device & core/cpu count, df -h for Used - Free space by device, df -i for inode info by device, free -h for Used - Free Mem: and Swap:, cat /proc/meminfo includes VMallocUused, for server workload tuning analysis to provide suggestions. Mar 14, 2023 at 20:52

5 Answers 5

1

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

3
  • I 've increased memory_limint to 8G and max_execution_time = 18000
    – myfixone
    Mar 5, 2023 at 8:32
  • set max_execution_time=72000
    – Harun
    Mar 5, 2023 at 8:34
  • thank you, I will try it.
    – myfixone
    Mar 5, 2023 at 8:37
1

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

1

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.

0

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.

0

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.
2
  • what is third-party indexing solution?
    – myfixone
    Mar 6, 2023 at 4:10
  • If you go to over Google and search reindex third party module, you will get various vendor modules like Amasty, mirasvit etc... Mar 6, 2023 at 10:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.