Jump to content

Every time MySQL Queries are getting locked


Mirza
 Share

Recommended Posts

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.

screenshot.jpg

screenshot_1.png

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by kongondo
Link to comment
Share on other sites

  • 2 years later...

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. 

Link to comment
Share on other sites

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 (232-1) on 32-bit systems and 18446744073709551615 (264-1) on 64-bit systems.

 

Link to comment
Share on other sites

 Share

×
×
  • Create New...