Apache requests currently being processed

Hi ,
So basically , I have 2 vps connected to each other, at least once a day I have to send data from the first vps to the second, so I have a script that opens mysqli connection from the first server to the second and it starts writing in it's database, the problem is , once it starts writing in the database the website in the second server becomes slower, the script keeps working for around 30 minutes, after 5 minutes of writing the website hosted in the second server becomes unaccessible , which means it times out, after investigating I found out that Apache requests currently being processed stays crowded even after the script has stopped , it's like each "insert" in the database that happened in the script is still alive and using memory and cpu, even tho that the database is already filled.
any idea of how to prevent this ?

by the way , the script does only write in the database multiple times

Could try writing a queue script instead of updating/inserting everything at once. So, the first server calls the queue script through an api call to the second server. Second server gets the data into a queue. Then the second server executes the queue one by one, or certain amount of records at a time; so that it doesn't time out/become unresponsive.

Also try closing the mysqli connection by force on each write. That could do the job too.
For closing the mysqli connection, use this:

Edit: P.S. try this one too. Your apache's child process limit could be the problem.

Gogol said:

Could try writing a queue script instead of updating/inserting everything at once. So, the first server calls the queue script through an api call to the second server. Second server gets the data into a queue. Then the second server executes the queue one by one, or certain amount of records at a time; so that it doesn't time out/become unresponsive.

Also try closing the mysqli connection by force on each write. That could do the job too.
For closing the mysqli connection, use this:

Code:

https://www.w3schools.com/php/func_mysqli_close.asp

Edit: P.S. try this one too. Your apache's child process limit could be the problem.

Code:

https://serverfault.com/questions/538988/apache-2-4-not-closing-connections

Click to expand...

it is the same problem in serverfault thread, but they didnt provide a solution.
I am actually writing in the database using a while loop ,I tried closing the connection in the end of the script but still didnt fix the problem, ill try closing it inside the while loop after each insert
thank you for your help

Try adding this at the top (it will give unlimited time for the script to run). I suggest you hire someone, because is hard to give an answer without seeing the code. Could be multiple things.

You might check if there are some transactions that are still active after your script finished, with the following command :


You can then use the `kill` command if you think that a transaction isn't needed anymore.

happylama said:

it is the same problem in serverfault thread, but they didnt provide a solution.
I am actually writing in the database using a while loop ,I tried closing the connection in the end of the script but still didnt fix the problem, ill try closing it inside the while loop after each insert
thank you for your help

Click to expand...

while loops can be PITA some times. For example, in your case. That's because the call takes some time to xecute, and you end up opening multiple web requests.
I strongly recommend limiting the data, and reloading the script; possibly with a time out. Let it breath a little, if that makes any sense.

Gogol said:

while loops can be PITA some times. For example, in your case. That's because the call takes some time to xecute, and you end up opening multiple web requests.
I strongly recommend limiting the data, and reloading the script; possibly with a time out. Let it breath a little, if that makes any sense.

Click to expand...

Smart ! that's probably the reason , the only thing is when the script ends , I restart apache to cancel the request being processed, and I find that the script had already filled the database and all the job was done, which means those requests that were being processed were just stuck at closing the connection.

happylama said:

Smart ! that's probably the reason , the only thing is when the script ends , I restart apache to cancel the request being processed, and I find that the script had already filled the database and all the job was done, which means those requests that were being processed were just stuck at closing the connection.

Click to expand...

Hmm restarting apache2 can actually do the job, but the data will have no integrity; i.e. you will never know if all data got written indeed. Technically, there are open connections when you restart; and some data might not have got written when you kill all.

Update:
so , I modified the code to close the connection after each insert and it seems it has solved the issue currently , many thanks @Gogol

Gogol said:

Hmm that restarting apache2 can actually do the job, but the data will have no integrity; i.e. you will never know if all data got written indeed. Technically, there are open connections when you restart; and some data might not have got written when you kill all.

Click to expand...

I believe that the problem was that I had the mysqli_close in the end of the script, which means as you said, with many insert queries being sent to the database, the mysqli_close was the last thing to happen, and since the process takes up to 30 mins , it is normal for the server to become slow, now I have moved mysqli_close to the end of the while loop and restarted the connection in the begining of it and it seems the problem has been solved.