XF 1.4 1.4.1 upgrade issue

[RyanC]

since upgrading to 1.4.1 we're seeing a lot table locks.

http://screencast.com/t/lCfBOnCIt

This didn't exist before the upgrade...the problem we've had in tuning around this is that while tuning brings down load as soon as the board gets busy again it seems that everything returns to as it was and needs to be re-optimized.

This is a fairly large site that pre upgrade load was rarely over 5 on a 16 core machine early today we saw over 1200.

Any thoughts on getting this back to as it was?

 

[MattW]

Server is running a lot better now after making a few more adjustments to MySQL.

 

[Chris D]

Good to know. I was thinking there must be some sort of optimisation required.

What were the adjustments? And what caused the poorer performance from one version to the next?

 

[MattW]

We still don't know why there were performance issues after the update, but the graphs I've seen clearly show an issue with MySQL spiking load and RAM usage, and then locking up the whole system.

Changes were made to address memory allocation to:
sort_buffer_size
read_buffer_size
join_buffer_size

(MySQL was capable of allocating almost 340GB of RAM with the values that were set)

and some of the other standard InnoDB settings I always apply were made:

< ; Matt's Additions
< innodb_file_per_table = 1
< innodb_log_files_in_group = 2
< innodb_log_file_size = 256M
< innodb_log_buffer_size = 8M
< innodb_flush_log_at_trx_commit = 2
< innodb_thread_concurrency = 0
< innodb_lock_wait_timeout=50
< innodb_flush_method = O_DIRECT