Why mysql database hangs sometimes?

Question

PHP

I'm afraid that I'm asking a very stupid question, and I can't even explain it.
Given: export products to xml file. One request is executed, approximately in 4-5 seconds. Goods (50 thousand) are unloaded into an array. Next, I break the array and work with it (I build xml) and give it to the user for downloading. There are 5-6 instant requests in the cycle, this is important.
The cycle builds xml for 10-15 seconds and eats 500 MB of memory.
This all happens quickly if there are not many users on the server. Probably because of them. Not sure. I describe further.
Often the script execution takes 5-8 minutes. I can't understand why.
Although now, at night, when there are almost no users and there is no use of RAM, the script unloaded xml for about 5 minutes. After that, I immediately execute it again and what we see is unloaded in a couple of seconds. How? Why is this happening? How to debug and understand what's wrong? Is it about php?
That is, the problem is that I cannot understand why the script takes a long time to execute when there are many users and they all load the site, although the use of RAM is 1600/4000 mb.

Answer 1

Without providing the database configuration, what you have there, what settings, what type of storage engine, what disks and what else is spinning nearby - this is an appeal to telepaths.
All of the above can affect database performance.
Is there a setting in the database configuration to log slow queries?
What does EXPLAIN say about this query?
Is it possible that this request is being executed by different users?
How many connections are allowed to the database?

Answer 2

1. you need to look at the request itself
2.
requests are queued and executed in turn, if your subrequest uses a table in which something changes, then it will wait until the previous request changes it