How to find the cause of the 500 error?

Question

linux

Website on LEMP.
Everything works, except for one of the scripts that runs from the CMS. Going into it, the browser thinks for a long time, as a result, after 40 seconds it falls out into error 500. At the same time, you can’t go to any page for all 40 seconds, and they are also suspended for this period of time.
Htop during these 40 seconds shows the following picture: 100% of one of the 8 processor cores is occupied by mysqld, after 10-15 seconds mysqld releases the percent, and now 100% is occupied by the php-fpm pool. The ram consumption is growing (from 900MB to 1.4GB, with a total memory of 16GB), so is the php-fpm pool.
Nothing fatal in nginx debug logs. There is nothing special in the php-fpm pool logs either.
How to identify a problem area?

Answer 1

Try debuggers, logging a la "position 1", "position 2" after each significant action. But this is not on a production server, of course. It is also likely a hole in the query in the database. Look, maybe there is no "LIMIT 1" of some kind.
After all, it is very similar that since "mysql" eats a processor and a lot of memory, it means a very heavy query, most likely "SELECT".
And 40 seconds is most likely the maximum response time or something, after the web server gives a 500 error. But I'm not sure about this. Maybe just PHP falls from the size of the object.