Indexing takes way too long

[OvalMedia]

I am using asynchindex in a magento 1.9.4.1. Each night an importer pulls thousands (~11k atm) of products from another database using the default magento models (no fancy stuff here).
This has been working nicely until recently when customers started telling us they could not order due to error messages like these:

General error: 1205 Lock wait timeout exceeded; try restarting transaction, query was: SELECT si.*, p.type_idFROMcataloginventory_stock_itemASsiINNER JOINcatalog_product_entityASpON p.entity_id=si.product_id WHERE (stock_id=1) AND (product_id IN(67680, 67850, 74053, 74055, 66988, 66987, 66989, 66990, 94395, 68370, 67769, 67768, 67761, 68372, 67762, 67643, 67642, 68597, 67232, 74325, 67654, 66763, 66761, 66762, 67061, 69664, 66808, 67864)) FOR UPDATE

I must assume that the reason it has been working for a while is because the total amount of products is constantly rising.

I tried to debug and watched the database with "show processlist". The indexer kept working for hours and seemingly slow. On my dev machine it runs a lot faster although it has less power than the live server. So I killed all processes and strightened the index by using the default shell script (php indexer.php --reindexall). This worked fine and all the indexes were rebuild within minutes.

So my question is: why is there such a big difference in the performance? What could I do to get to the core of the issue here?

Thank you

[Flyingmana]

sorry for the late reply.

The issue may be related to this Problem in Core Magento: OpenMage/magento-lts#103

If your live server has constantly new orders, this could explain why your local system is faster.
Another explanation could be, that your live server may be bigger, but your Database Disk is actually slower then you local ssd/nvme storage.

You could have a bigger look at the status of the async status, by monitoring how many index entries are pending, and how fast this goes down after the import. But for this big number its likely faster to just do the --reindexall in general.

In general, the Async Indexing is always slower than indexing All, but therefore its less blocking and can better handle partial updates of the index.