>>> Forums Updates And Feature Announcements <<<

[thethiny]

For a while it happened very often with downtime of 5 to 20 minutes. After the move to php8 and configuring the DBMS to log deadlock and slow running statement, it hasn't happened once. This I don't like, because it means it is still there, just not presenting itself and I am an expert in multi threaded software development. So it is driving me crazy, but I will figure it out.

Update:
It happened when I tried to submit the above part. A lot of processes but none with queries and no deadlocks. It resolved on its own after 5 minutes. Something is going on.

The only thing I can think of is connection timeout or a very long queue of validation.

[Gambid]

Code: Select all

+--------+-------------+-----------+----------+---------+------+--------------------------+-----------------------+----------+
| Id     | User        | Host      | db       | Command | Time | State                    | Info                  | Progress |
+--------+-------------+-----------+----------+---------+------+--------------------------+-----------------------+----------+
| 483294 | ********    | localhost | ******** | Sleep   |  482 |                          | NULL                  |    0.000 |
| 483343 | ********    | localhost | ******** | Sleep   |  361 |                          | NULL                  |    0.000 |
| 483480 | ********    | localhost | ******** | Sleep   |  237 |                          | NULL                  |    0.000 |
| 483626 | ********    | localhost | ******** | Sleep   |  121 |                          | NULL                  |    0.000 |
| 483642 | ********    | localhost | ******** | Sleep   |  119 |                          | NULL                  |    0.000 |
| 483650 | ********    | localhost | ******** | Sleep   |  118 |                          | NULL                  |    0.000 |
| 483706 | ********    | localhost | ******** | Sleep   |   60 |                          | NULL                  |    0.000 |
| 483715 | ********    | localhost | ******** | Sleep   |   59 |                          | NULL                  |    0.000 |
| 483717 | ********    | localhost | ******** | Sleep   |   58 |                          | NULL                  |    0.000 |
| 483729 | ********    | localhost | ******** | Sleep   |   51 |                          | NULL                  |    0.000 |
+--------+-------------+-----------+----------+---------+------+--------------------------+-----------------------+----------+

That is the only thing I am seeing. Or in other words I see that I cannot see anything. Sleeping means the process is not executing a query, based on the documentation. The question is only why is it waiting then. The site did not forget to stop the connection. I have prepared a script to dump table locks the next time this happens. Something is going wrong and I need to find out what exactly and why.

If these were all just sleeping they wouldn't have prevented the site from rendering, so they are holding on to something.

[thethiny]

Code: Select all

+--------+-------------+-----------+----------+---------+------+--------------------------+-----------------------+----------+
| Id     | User        | Host      | db       | Command | Time | State                    | Info                  | Progress |
+--------+-------------+-----------+----------+---------+------+--------------------------+-----------------------+----------+
| 483294 | ********    | localhost | ******** | Sleep   |  482 |                          | NULL                  |    0.000 |
| 483343 | ********    | localhost | ******** | Sleep   |  361 |                          | NULL                  |    0.000 |
| 483480 | ********    | localhost | ******** | Sleep   |  237 |                          | NULL                  |    0.000 |
| 483626 | ********    | localhost | ******** | Sleep   |  121 |                          | NULL                  |    0.000 |
| 483642 | ********    | localhost | ******** | Sleep   |  119 |                          | NULL                  |    0.000 |
| 483650 | ********    | localhost | ******** | Sleep   |  118 |                          | NULL                  |    0.000 |
| 483706 | ********    | localhost | ******** | Sleep   |   60 |                          | NULL                  |    0.000 |
| 483715 | ********    | localhost | ******** | Sleep   |   59 |                          | NULL                  |    0.000 |
| 483717 | ********    | localhost | ******** | Sleep   |   58 |                          | NULL                  |    0.000 |
| 483729 | ********    | localhost | ******** | Sleep   |   51 |                          | NULL                  |    0.000 |
+--------+-------------+-----------+----------+---------+------+--------------------------+-----------------------+----------+

That is the only thing I am seeing. Or in other words I see that I cannot see anything. Sleeping means the process is not executing a query, based on the documentation. The question is only why is it waiting then. The site did not forget to stop the connection. I have prepared a script to dump table locks the next time this happens. Something is going wrong and I need to find out what exactly and why.

If these were all just sleeping they wouldn't have prevented the site from rendering, so they are holding on to something.

Try for the sake of consistency to change the storage location

[Gambid]

What do you mean by change the storage location?

[thethiny]

What do you mean by change the storage location?

It could be the path to the location that the database lives in is shared

[Gambid]

Nothing has ever changed in this regard. I do have my suspicions about several queries that I had to change lately, but I would like some sort of evidence before I shoot blind and hope that it resolves the situation.

[thethiny]

Nothing has ever changed in this regard. I do have my suspicions about several queries that I had to change lately, but I would like some sort of evidence before I shoot blind and hope that it resolves the situation.

If the problem is the query then it wouldn't be "Sleeping", it would be active. I hope you resolve it soon Gambid we all appreciate what you do here!

[Gambid]

The problem might be the web server and the php code execution. Just happened again, it looks like the execution gets stuck and there is no one to close the connection. Killing DB connections doesn't really help as just new ones will show up, which means the web server is there to open them but not to close them. This at least gives me a place to further investigate. Restarting the web server cleans the request queue and works like a magic bullet, even a better one than just restarting the DB.

Now is time to figure out where it gets stuck and why and this I love doing

[thethiny]

Now is time to figure out where it gets stuck and why and this I love doing

We're all rooting for you!

[Gambid]

Problem may have been discovered. It is now a wait and see situation.

[thethiny]

Problem may have been discovered. It is now a wait and see situation.

What did you discover?

[Gambid]

We were constantly hitting the currently set PHP-FPM's setting for pm.max_children size. Which means php was unable to create more child processes to handle the load. I guess we got a bit to big for the previous default limit. It is only a suspicion and only time will tell if I was right, but the constant warnings are gone after the hard limit was significantly increased.

Problem became more clear after the recent downtime which a DB Server and Apache restart could not resolve, only a PHP-FPM restert did it.

So, I guess we will see, I am fairly optimistic though as the symptoms of the problem described only are absolutely the same as what we were experiencing.

[thethiny]

Nice! Gambid to the rescue!

[Gambid]

Great rescue, took half an year. I hope that that was it for the moment though.

[Gambid]

So that everyone is aware of this. We are currently trying to track down an issue that has been occurring for a while. To do that we are activating and deactivating different forum features to try and pin point whether one of them is causing it. So don't be scared if something is missing. It is only temporary.