MySQL number of processes causes the site to stall

[Xonox]

Hi,

I'm having a problem with one of my sites, something that doesn't happen with any other of my PW implementations. The site stalls due to the number of processes that reaches the server limit (Max. set to 40). It looks like some MySQL requests remain active, without closing.

There's no particular event that I can connect to it - like an increase of traffic or whatever - which make it even more difficult to know what might be causing it. It's not a recurrent problem, but sometimes it happens. It might be once a month, once a week or once every three months and its virtually impossible to replicate in my local server.

Here's what our server managers sent to me to help sort out the problem:

When I run the query in the database it runs without problems

SELECT id, templates_id FROM pages WHERE id IN(1,2,28,29,30,37,38,40)

Returns:  Showing rows 0 - 7 (8 total, Query took 0.0003 seconds.)

The pages that this query return are:

1 - 
2 - admin
28 - access
29 - users
30 - roles
37 - guest
38 - superuser
40 - guest

Looks like some kind of problem with the admin section?

Has anyone ever faced a similar problem? Might it be a server problem? A bug?

I'm thinking on changing the site to another server, to see if it is a server problem.

Thanks.

[dragan]

"waiting for table level lock" would suggest that something is odd on the server side and/or mySQL. Maybe mySQL doesn't have enough memory, or is being re-booted every now and then, and can't handle the queries fast enough.

Perhaps switching from myISAM to InnoDB would help already. Or maybe this is being caused by a large mysqldump process

https://superuser.com/questions/1093610/mysql-5-7-stuck-forever-waiting-for-table-level-lock

 

[Xonox]

16 hours ago, dragan said:

Perhaps switching from myISAM to InnoDB would help already.

Thanks for your suggestion. I've made the change to InnoDB. All I can do now is wait, to see if it solved. This problem is so inconsistent, that I don't know what to do to test it.

16 hours ago, dragan said:

Or maybe this is being caused by a large mysqldump process

As far as I know, there's no dump process. You think it might  be caused by some kind of auto backup process in the server?

Thank you for your help.

[bracketfire]

Did you ever figure out this locking problem?  I'm seeing the same behavior in our application.  Any insight you can share would be appreciated.

[Xonox]

14 minutes ago, bracketfire said:

Did you ever figure out this locking problem?  I'm seeing the same behavior in our application.  Any insight you can share would be appreciated.

Changing table to InnoDB solved it!

Hi, that's what worked, for me, changing that one table (pages) into InnoDB.

Because it was an issue that needed time, to see if it would happen again, I didn't update this issue.

Hope this helps you!

[dragan]

Yep, that's certainly worth a try.

See this announcement from @ryan https://processwire.com/blog/posts/using-innodb-with-processwire/

tl;dr: Row locking versus table locking (Inno vs. ISAM)

[fatalrhymer99]

i got this problem too for 1 week. i tried to the table change innodb from myisam like you but when i changed to innodb i got higher mysql cpu and i returned to myisam again. how can i fix that