imran_khan

Well-Known Member
Jun 10, 2013
154
1
16
cPanel Access Level
Root Administrator
Hello,

I am facing CPU high load issue with the server. Server hardware and software details are as follows.
CPU load is continuously above 35. Daily site visitor is 18000. So I am planning to Apache and MySQL performance tuning. Please suggest me on the same.

Hardware Details:-
Hard Disk:- 2000.4 GB X 2 HDD with software RAID-1.
RAM:- 16 GB.
CPU model name AMD Opteron(tm) Processor 3280
Manufacturer: FUJITSU
Product Name: D3090-A1
Version: S26361-D3090-A1
physical id: 1
cpu cores: 4
processor: 8

Software Details:-
Cpanel, Mysql and Apache.

Mysql configuration file details:-

[mysqld]
skip-networking
innodb_file_per_table=1

query_cache_limit = 1M
query_cache_size = 32M

key_buffer_size = 64M

max_heap_table_size = 256M
tmp_table_size = 256M

thread_cache = 128
open_files_limit=2358

max_connections = 1500
max_user_connections = 300

Apache configuration file details:-

Timeout 300
StartServers 5
MinSpareServers 5
MaxSpareServers 10
ServerLimit 256
MaxClients 150
MaxRequestsPerChild 10000
KeepAlive Off
KeepAliveTimeout 5
MaxKeepAliveRequests 100

Thanks,
Imran Khan.
 

imran_khan

Well-Known Member
Jun 10, 2013
154
1
16
cPanel Access Level
Root Administrator
run at least mysqltuner.pl and give the results of CPU and load graphs of munin here
and copy result of top
Hello,

Thanks for the reply. Please find the out put of the command.

# sar -q -f /var/log/sa/sa11

runq-sz plist-sz ldavg-1 ldavg-5 ldavg-15
Average: 19 654 20.93 20.80 20.82

#top c

top - 22:10:40 up 4 days, 1:10, 1 user, load average: 6.24, 9.58, 10.35
Tasks: 232 total, 2 running, 229 sleeping, 0 stopped, 1 zombie
Cpu(s): 56.5%us, 15.0%sy, 2.5%ni, 22.1%id, 3.6%wa, 0.1%hi, 0.3%si, 0.0%st
Mem: 16234832k total, 10328188k used, 5906644k free, 405156k buffers
Swap: 2097136k total, 185596k used, 1911540k free, 6743444k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
11391 mysql 20 0 4382m 249m 3892 S 271.9 1.6 4028:17 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/eagle606.sta
3442 inditv 39 19 1846m 1.8g 848 R 60.7 11.6 0:23.00 /usr/local/cpanel/3rdparty/bin/analog +CIMAGEDIR /images/ +CDOMAINSFILE /usr/local/cpanel/3rdparty/share/an
8721 inditv 20 0 0 0 0 Z 58.7 0.0 0:00.42
PHP:
 <defunct>
 8733 inditv    20   0  307m  26m  16m S 23.5  0.2   0:00.12 /usr/bin/php /home/inditv/public_html/profile_info.php
   60 root      20   0     0    0    0 S  2.0  0.0   0:55.35 [kblockd/2]
 3865 nobody    20   0  244m  69m 2232 S  2.0  0.4   0:01.76 /usr/local/apache/bin/httpd -k start -DSSL
 8732 root      20   0 15156 1320  900 R  2.0  0.0   0:00.01 top c
    1 root      20   0 19228 1008  836 S  0.0  0.0   0:06.30 /sbin/init

# sar -p

		CPU     %user     %nice   %system   %iowait    %steal     %idle
Average:        all     56.02      3.04     14.99      5.17      0.00     20.78

# free -m
             total       used       free     shared    buffers     cached
Mem:         15854      12236       3617          0        396       8039
-/+ buffers/cache:       3801      12052
Swap:         2047        181       1866


# sar -r
		kbmemfree kbmemused  %memused kbbuffers  kbcached  kbcommit   %commit

Average:         5634106  10600726     65.30    207311   7972464  14144229     77.16

# sar -S
		kbswpfree kbswpused  %swpused  kbswpcad   %swpcad

Average:        1889949    207187      9.88     14956      7.22

# iostat -x 1 5
Linux 2.6.32-279.19.1.el6.x86_64 (eagle606.startdedicated.com)  06/11/2013      _x86_64_        (8 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          56.51    2.48   15.35    3.59    0.00   22.07

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda              25.98   215.90   72.92   56.29 11949.36  2167.18   109.25     9.23   71.42   3.94  50.97
sdb              26.00   214.83   74.51   55.73 12186.27  2153.99   110.11     9.21   70.72   4.06  52.84
md1               0.00     0.00   23.74  266.67  1820.06  2129.33    13.60     0.00    0.00   0.00   0.00
md0               0.00     0.00    0.00    0.00     0.01     0.00     5.05     0.00    0.00   0.00   0.00

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          28.14    0.00    8.79   11.81    0.00   51.26

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               1.00     0.00   26.00    5.00   216.00    28.00     7.87     0.98   31.77  23.26  72.10
sdb               0.00     0.00   21.00    5.00   168.00    28.00     7.54     0.49   19.15  16.23  42.20
md1               0.00     0.00   48.00    1.00   384.00     8.00     8.00     0.00    0.00   0.00   0.00
md0               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          18.22    0.00    8.92   12.19    0.00   60.68

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     0.00   51.00    0.00   408.00     0.00     8.00     0.59   11.43  11.45  58.40
sdb               0.00     0.00   36.00    0.00   288.00     0.00     8.00     0.44   12.36  12.22  44.00
md1               0.00     0.00   87.00    0.00   696.00     0.00     8.00     0.00    0.00   0.00   0.00
md0               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          55.25    0.12   16.38    5.25    0.00   23.00

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00    73.00   16.00   27.00   128.00   802.00    21.63     1.11   25.26  15.70  67.50
sdb               0.00    73.00   24.00   28.00   192.00   802.00    19.12     0.80   15.40  13.42  69.80
md1               0.00     0.00   40.00   94.00   320.00   752.00     8.00     0.00    0.00   0.00   0.00
md0               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          88.88    0.00   10.62    0.00    0.00    0.50

Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     2.00    0.00   19.00     0.00   148.00     7.79     0.59   25.79  30.42  57.80
sdb               0.00     2.00   23.00   18.00   216.00   148.00     8.88     0.54    9.93  11.83  48.50
md1               0.00     0.00   22.00   15.00   208.00   120.00     8.86     0.00    0.00   0.00   0.00
md0               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

# sar -d
		DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
Average:       dev8-0     87.51   3965.83   1484.83     62.29      9.48    108.30      6.25     54.72
Average:      dev8-16     91.58   4528.76   1471.60     65.52      9.33    101.82      6.62     60.63
Average:       dev9-1    213.07   2444.37   1436.75     18.22      0.00      0.00      0.00      0.00
Average:       dev9-0      0.00      0.00      0.00      2.86      0.00      0.00      0.00      0.00

I have run the /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl file on the server.


# perl mysqltuner.pl
Name "XML::Simple::PREFERRED_PARSER" used only once: possible typo at mysqltuner.pl line 148.

 >>  MySQLTuner 1.2.0_1 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.69-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 3G (Tables: 357)
[--] Data in InnoDB tables: 208K (Tables: 13)
[!!] Total fragmented tables: 8

-------- Performance Metrics -------------------------------------------------
[--] Up for: 16h 15m 15s (15M q [257.063 qps], 198K conn, TX: 11B, RX: 4B)
[--] Reads / Writes: 92% / 8%
[--] Total buffers: 362.0M global + 2.7M per thread (1500 max threads)
[OK] Maximum possible memory usage: 4.4G (28% of installed RAM)
[OK] Slow queries: 0% (4K/15M)
[OK] Highest usage of available connections: 20% (305/1500)
[OK] Key buffer size / total MyISAM indexes: 64.0M/1.4G
[OK] Key buffer hit rate: 99.8% (11B cached / 21M reads)
[OK] Query cache efficiency: 64.3% (8M cached / 13M selects)
[!!] Query cache prunes per day: 1316472
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 885K sorts)
[!!] Joins performed without indexes: 60921
[OK] Temporary tables created on disk: 8% (109K on disk / 1M total)
[OK] Thread cache hit rate: 99% (874 created / 198K connections)
[!!] Table cache hit rate: 0% (64 open / 5M opened)
[OK] Open file limit used: 1% (95/7K)
[!!] Table locks acquired immediately: 87%
[OK] InnoDB data size / buffer pool: 208.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
    Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
    query_cache_size (> 32M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    table_cache (> 64)

Please check the out put of all the command and suggset me.
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
this

mysql:

Code:
[mysqld]


wait_timeout = 30
connect_timeout = 1
local-infile=0

open_files_limit=25000
default-storage-engine=MyISAM

max_connections = 500
max_user_connections = 150

key_buffer_size = 5G
max_allowed_packet=20M

query_cache_size=50M
query_cache_limit=1M
tmp_table_size=100M
max_heap_table_size=100M
thread_cache_size=50

table_open_cache = 1500
table_definition_cache = 1000

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1
log-queries-not-using-indexes

sort_buffer_size=256K
join_buffer_size=2M
read_rnd_buffer_size=4M

max_write_lock_count = 10
concurrent_insert=2
apache:
Timeout 30
StartServers 10
MinSpareServers 5
MaxSpareServers 10
ServerLimit 300
MaxClients 300


and run this also (before restarting mysql)
wget http://percona.com/get/pt-query-digest
chmod +x pt-query-digest
./pt-query-digest /var/lib/mysql/*-slow.log > slow.txt

and post here slow.txt to see slow queries

after that restart mysql
 

imran_khan

Well-Known Member
Jun 10, 2013
154
1
16
cPanel Access Level
Root Administrator
Hello Thinkbot,

Thanks. Daily site visitors are 18000. So I am planning to optimize Apache as per below. Please suggest me on the same.

Timeout 150
StartServers 50
MinSpareServers 25
MaxSpareServers 50
ServerLimit 256
MaxClients 256
MaxRequestsPerChild 10000
KeepAlive On
KeepAliveTimeout 10
MaxKeepAliveRequests 150

Thanks,
Imran Khan.
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
Timeout 15
StartServers 20
MinSpareServers 10
MaxSpareServers 20
ServerLimit 500
MaxClients 500
MaxRequestsPerChild 10000
KeepAlive On
KeepAliveTimeout 1
MaxKeepAliveRequests 150


You dont need to start at once many servers, they will increase when needed (since MinSpareServers)
No need to keep long timeouts

And keepalive 10 seconds would keep a lot of connections open without a point

Regards
 

imran_khan

Well-Known Member
Jun 10, 2013
154
1
16
cPanel Access Level
Root Administrator
Hello Thinkbot,

Thanks. KeepAlive On is fine or can I set it to Off? Because currently it is Off and Is MaxRequestsPerChild 10000 fine as per my hardware or I need to decrease it.

Thanks,
Imran Khan.
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
KeepAlive speeds up a little bit static content loading, so you can leave it on 1s or set it Off
MaxRequestsPerChild means after how many requests restart the process, you can leave it 10000 or make smaller like 1000

Regards
 

imran_khan

Well-Known Member
Jun 10, 2013
154
1
16
cPanel Access Level
Root Administrator
Hello Thinkbot,

Thank you very much. will this Apache setting decrease CPU load/improve server performance or I need to optimize MySQL with Apache?

Thanks,
Imran Khan.
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
Hey,

Yes both mysql and apache configs will improve overall performance
But the script/code is the one that can take the most CPU time
So the things like script caching, slow queries are still to check if you want to get best performance

Please post mysqltuner results

Regards
 

imran_khan

Well-Known Member
Jun 10, 2013
154
1
16
cPanel Access Level
Root Administrator
Hello Thnikbot,

Thanks. Really appreciate.

Daily site visitors are 18000. so can i set max_connections = 1500 and max_user_connections = 300 instead of max_connections = 500 and max_user_connections = 150?

For MySQL:-
[mysqld]

skip-networking
innodb_file_per_table=1
wait_timeout = 30
connect_timeout = 1
local-infile=0

open_files_limit=25000
default-storage-engine=MyISAM

max_connections = 1500
max_user_connections = 300

key_buffer_size = 5G
max_allowed_packet=20M

query_cache_size=50M
query_cache_limit=1M
tmp_table_size=100M
max_heap_table_size=100M
thread_cache_size=50
thread_cache = 128
table_open_cache = 1500
table_definition_cache = 1000

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1
log-queries-not-using-indexes

sort_buffer_size=256K
join_buffer_size=2M
read_rnd_buffer_size=4M

max_write_lock_count = 10
concurrent_insert=2

Thanks,
Imran Khan.
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
You probably doesnt understand how it works :)

Let's assume that avarage page generation time takes 0.1s
To reach 500 connections limit, you would need to have 500 concurrent (at once) connections in that 0.1s

I dont think you got so much :)

In practise, even 50 max connections is well than enough if script is properly written and executes fast

I was working last few days on the server where there is 400 page req/s and highest usage of available connections was 42, most of the time its 3-4, since page generation time is very fast, lower than 0.01

please post mysqltuner.pl result

Regards
 

imran_khan

Well-Known Member
Jun 10, 2013
154
1
16
cPanel Access Level
Root Administrator
Hello Thinkbot,

Thank you very much for quick response. Please check the below action plan and let me know.
Then I will make the changes because this server is my customer server’s and it is in production.

For Apache:-
• Make the below changes in Apache configuration.
Timeout 15
StartServers 20
MinSpareServers 10
MaxSpareServers 20
ServerLimit 500
MaxClients 500
MaxRequestsPerChild 10000
KeepAlive Off
KeepAliveTimeout 1
MaxKeepAliveRequests 150

• Restart the apache service.

For MySQL:-
• Make the below changes in MySQL configuration.
[mysqld]

skip-networking
innodb_file_per_table=1
wait_timeout = 30
connect_timeout = 1
local-infile=0

open_files_limit=25000
default-storage-engine=MyISAM

max_connections = 500
max_user_connections = 150

key_buffer_size = 5G
max_allowed_packet=20M

query_cache_size=50M
query_cache_limit=1M
tmp_table_size=100M
max_heap_table_size=100M
thread_cache_size=50
thread_cache = 128
table_open_cache = 1500
table_definition_cache = 1000

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1
log-queries-not-using-indexes

sort_buffer_size=256K
join_buffer_size=2M
read_rnd_buffer_size=4M

max_write_lock_count = 10
concurrent_insert=2

• Restart the MySQL service.
• Run the mysqltuner.pl script.
 
Last edited:

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
yes exactly, but you can run mysqltuner.pl first, so we will know how it run for few/several days

and after restarting mysql, let it run few hours and generate new mysqltuner.pl
 

imran_khan

Well-Known Member
Jun 10, 2013
154
1
16
cPanel Access Level
Root Administrator
Hello Thinkbot,

Thanks. I am waiting for customer approval. I will update you once they will approve. Please find the current script output.

#perl mysqltuner.pl
Name "XML::Simple::PREFERRED_PARSER" used only once: possible typo at mysqltuner.pl line 148.

>> MySQLTuner 1.2.0_1 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.69-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 3G (Tables: 357)
[--] Data in InnoDB tables: 208K (Tables: 13)
[!!] Total fragmented tables: 8

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 1h 23m 38s (66M q [250.579 qps], 921K conn, TX: 68B, RX: 19B)
[--] Reads / Writes: 91% / 9%
[--] Total buffers: 362.0M global + 2.7M per thread (1500 max threads)
[OK] Maximum possible memory usage: 4.4G (28% of installed RAM)
[OK] Slow queries: 0% (6K/66M)
[OK] Highest usage of available connections: 19% (294/1500)
[OK] Key buffer size / total MyISAM indexes: 64.0M/1.5G
[OK] Key buffer hit rate: 99.8% (46B cached / 94M reads)
[OK] Query cache efficiency: 66.4% (40M cached / 60M selects)
[!!] Query cache prunes per day: 1068171
[OK] Sorts requiring temporary tables: 0% (4K temp sorts / 3M sorts)
[!!] Joins performed without indexes: 272830
[OK] Temporary tables created on disk: 10% (491K on disk / 4M total)
[OK] Thread cache hit rate: 99% (1K created / 921K connections)
[!!] Table cache hit rate: 0% (64 open / 6M opened)
[OK] Open file limit used: 1% (104/7K)
[!!] Table locks acquired immediately: 94%
[OK] InnoDB data size / buffer pool: 208.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
query_cache_size (> 32M)
join_buffer_size (> 128.0K, or always use indexes with joins)
table_cache (> 64)

Thanks,
Imran Khan.
 

imran_khan

Well-Known Member
Jun 10, 2013
154
1
16
cPanel Access Level
Root Administrator
Hello Thinkbot,

I have made the changes in Apache and MySQL configuration file. Current my.cnf file is as below.

my.cnf file setting:-
[mysqld]
skip-networking
innodb_file_per_table=1
wait_timeout = 30
connect_timeout = 1
local-infile=0

open_files_limit=25000
default-storage-engine=MyISAM

max_connections = 500
max_user_connections = 150

key_buffer_size = 5G
max_allowed_packet=20M

query_cache_size = 50M
query_cache_limit = 1M
tmp_table_size = 100M
max_heap_table_size = 100M
thread_cache_size=50
thread_cache = 128
table_open_cache = 1500
table_definition_cache = 1000

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1
log-queries-not-using-indexes

sort_buffer_size=256K
join_buffer_size=2M
read_rnd_buffer_size=4M

max_write_lock_count = 10
concurrent_insert=2

Run the mysqltuner.pl after changes. mysqltuner.pl script output is as below. Please suggest me on this.

# perl mysqltuner.pl
Name "XML::Simple::PREFERRED_PARSER" used only once: possible typo at mysqltuner.pl line 148.

>> MySQLTuner 1.2.0_1 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.69-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 3G (Tables: 357)
[--] Data in InnoDB tables: 208K (Tables: 13)
[!!] Total fragmented tables: 7

-------- Performance Metrics -------------------------------------------------
[--] Up for: 6m 18s (64K q [170.365 qps], 981 conn, TX: 46M, RX: 16M)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 5.2G global + 6.6M per thread (500 max threads)
[OK] Maximum possible memory usage: 8.4G (54% of installed RAM)
[OK] Slow queries: 4% (3K/64K)
[OK] Highest usage of available connections: 9% (45/500)
[OK] Key buffer size / total MyISAM indexes: 5.0G/1.5G
[OK] Key buffer hit rate: 99.9% (62M cached / 90K reads)
[OK] Query cache efficiency: 63.5% (36K cached / 58K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 5% (114 temp sorts / 1K sorts)
[!!] Joins performed without indexes: 333
[OK] Temporary tables created on disk: 12% (482 on disk / 3K total)
[OK] Thread cache hit rate: 95% (45 created / 981 connections)
[OK] Table cache hit rate: 97% (322 open / 329 opened)
[OK] Open file limit used: 1% (416/25K)
[!!] Table locks acquired immediately: 92%
[OK] InnoDB data size / buffer pool: 208.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Adjust your join queries to always utilize indexes
Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
join_buffer_size (> 2.0M, or always use indexes with joins)
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
Nice

btw.
thread_cache_size=50
thread_cache = 128

those 2 are the same

current version naming is with _size
so you can remove thread_cache = 128

current mysqltuner result was on mysql running for only 6 mins, let it run for at least few more hours and generate mysqltuner and mysqlreport again

[OK] Temporary tables created on disk: 12% (482 on disk / 3K total)
[!!] Table locks acquired immediately: 92%

Table locking is bad one,

please also run pt-query-digest that I've mentioned before, so we can see which queries are locking, and which table

Regards