Every time MySQL Queries are getting locked

[Mirza]

I have built a system in processwire, which has more than 600K pages.

A team of 40 people is using the system, DB is from AWS with 16GB Ram.

But still, select queries are getting locked.

It would be great if someone suggests how to solve this problem.

Also note: We have around 48 fields in one template.

Thanks in advance.

[dragan]

Try to avoid using persistent connections?

I see you bumped up the timeout from 10 to 50 seconds already - did you change the wire core file?

https://github.com/ryancramerdesign/ProcessWire/blob/master/wire/modules/Session/SessionHandlerDB/SessionHandlerDB.module#L69

There's an alternative session handler here: https://modules.processwire.com/modules/session-handler-redis/

But regardless, 600k pages is a lot... some tweaking for performance will probably be necessary on any setup.

Perhaps somebody else can chime in with suggestions. I never had to deal with such huge PW-installs.

[kongondo]

Queries locking code be about not setting a 'reasonable' limit to number of pages to return. It could also be about too many joins. Hard to say without know exactly what's going on. These threads should be useful:

 

 

Edited July 20, 2017 by kongondo

[Mirza]

Thank you all, for the quick Response.

[bracketfire]

Mirza, 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.

[elabx]

I just ran into a similar problem a couple weeks ago. The fix that did it for us was increasing innodb_buffer_pool_size from 4G to 20G BUT, after that just a couple weeks ago I got errors with data corruption, can't say if it's related. 

[flydev]

14 hours ago, elabx said:

from 4G to 20G BUT, after that just a couple weeks ago I got errors with data corruption, can't say if it's related. 

Hey @elabx ,  I am tempted to say that yes it's related - (edit) it's related to your issue, not the locking one - Are you on MySQL 32bit or 64bit ? 

 

Quoting the official doc :

Quote

Default Value	134217728
Minimum Value	5242880
Maximum Value (64-bit platforms)	2**64-1
Maximum Value (32-bit platforms)	2**32-1

The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data. The default value is 134217728 bytes (128MB). The maximum value depends on the CPU architecture; the maximum is 4294967295 (2³²-1) on 32-bit systems and 18446744073709551615 (2⁶⁴-1) on 64-bit systems.