Hallo Allemaal,
Ik heb een klein probleempje om de load average te begrijpen op onze MySQL DB server. Het enigste wat de machine (VMWare Virtual) doet is inprincipe MySQL. De virtual heeft 8 vCPU's en 32GB RAM toegewezen. Alles draait op een SSD.
Er zijn een aantal piek momenten en dat lijkt overeen te komen met het verkeer wat er is, tijdens de piek momenten is de CPU belasting ergens tussen de 200% ~ 300% (2 of 3 vCPU's in gebruik van de 8). De Disk IO is verder zo goed als Idle. Wat opvalt in MySQL zelf is dat er soms 200 queries staan te wachten op een DB Lock. Dit komt omdat we memory tables gebruiken voor tijdelijke data om alles wat sneller te maken. Die DB Locks zijn ook allemaal binnen een seconde weg.
Ik begrijp niet waarom de Load Average dan toch af en toe boven de 10 komt en ik vraag me af of ik me hier zorgen om moet maken of niet. Ik hoop dat iemand het antwoord weet. Hieronder wat statistieken en configs.
Code:
mysql> show processlist;
+----------+-----------------+-------------------------------+-------------------+---------+-------+------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+-----------------+-------------------------------+-------------------+---------+-------+------------------------------+------------------------------------------------------------------------------------------------------+
|
| 75723153 | sysadmin | %:60660 | xxxx | Query | 1 | Waiting for table level lock | SELECT SQL_CALC_FOUND_ROWS tbm_agent.id, tbm_agent.userId, tbm_agent.`name`, IFNULL(agent_agentgroup | |
| 76254814 | sysadmin | %:42737 | xxxx | Query | 1 | Waiting for table level lock | SELECT ic.call_id, ic.customerId, ic.campaignId, tbm_agent.userId, ic.cli, ic.dnis_fk, ras.stamp_mse | |
| 77323068 | sysadmin | %:37633 | xxxx | Query | 1 | Waiting for table level lock | SELECT customerId, cli, dnis_fk, rdr FROM tbm_incoming_call WHERE tbm_incoming_call.call_id = inCall | |
| 77658174 | sfe | xxxx:54633 | xxxx | Query | 0 | statistics | SELECT name, type, comment FROM mysql.proc WHERE name like 'spc_requestUpdate' and db <=> 'smpp_v1_1 |
| 77670790 | sysadmin | %:38663 | xxxx | Query | 1 | Waiting for table level lock | UPDATE tbm_agent SET state_fk = inState, sessionsId = mySessionRecId, call_id = IF(inState='IDLE',NU | |
| 77726770 | sfe | xxxx:50747 | xxxx | Query | 0 | Opening tables | insert into messages (recorded, status, voicemailboxesID, callerCli, messagePart1, recTime1, hostNam |
| 78153665 | sfe | xxxx:58737 | xxxx | Query | 0 | Opening tables | CALL spc_requestUpdate(@com_mysql_jdbc_outparam_inoutRequestID,0,2,'7d820b6f',@com_mysql_jdbc_outpar | |
| 78396901 | sysadmin | %:56687 | xxxx | Query | 1 | Waiting for table level lock | INSERT INTO tbm_report_call_states (customerId, call_id, call_state, stamp_msec, timezone, info) |
| 78396903 | sysadmin | %:56689 | xxxx | Query | 1 | Waiting for table level lock | INSERT IGNORE INTO tbm_incoming_call(tbm_incoming_call.customerId, tbm_incoming_call.campaignId, sta |
| 78594277 | sysadmin | % | xxxx | Connect | 2 | Sending data | SELECT IFNULL(SEC_TO_TIME(AVG(duration_msec/1000)),'--:--:--'), IFNULL(SEC_TO_TIME(MAX(duration_msec |
| 78594291 | root | localhost | NULL | Query | 0 | init | show processlist |
| 78594311 | sysadmin | % | xxxx | Connect | 2 | Waiting for table level lock | SELECT MAX(id) FROM tbm_report_agent_states WHERE duration_msec IS NOT NULL AND stamp_msec < ( UNIX_ |
| 78594312 | sysadmin | % | xxxx | Connect | 1 | Waiting for table level lock | SELECT
tbl_outbound_requests.id,
tbl_outbound_requests.userId
FROM
|
tbm_agent
| 78594314 | sysadmin | % | xxxx | Connect | 1 | Waiting for table level lock | CALL spc_elementData_agents |
| 78594316 | sysadmin | % | xxxx | Connect | 2 | executing | SELECT sys_exec(inExecString) INTO retVal |
| 78594332 | sysadmin | %:47641 | xxxx | Query | 1 | Opening tables | SELECT
IFNULL(realPosition, '---') AS currentqueue_position,
IFNULL(CONCAT(tbl_campaigns.` |
| 78594334 | sysadmin | %:47643 | xxxx | Query | 1 | Waiting for table level lock | SELECT IFNULL(@oqsize,'---') 'Queue size'
,IFNULL(COUNT( DISTINCT IF(cs.call_state='AGENT_CONN |
| 78594336 | sysadmin | %:47644 | xxxx | Query | 1 | Waiting for table level lock | SELECT IFNULL(@oqsize,'---') 'Queue size'
,IFNULL(COUNT( DISTINCT IF(cs.call_state='AGENT_CONN |
| 78594337 | sysadmin | %:47645 | xxxx | Query | 1 | Waiting for table level lock | SELECT IFNULL(@oqsize,'---') 'Queue size'
,IFNULL(COUNT( DISTINCT IF(cs.call_state='AGENT_CONN |
| 78594338 | sysadmin | %:47646 | xxxx | Query | 1 | Waiting for table level lock | SELECT IFNULL(@oqsize,'---') 'Queue size'
,IFNULL(COUNT( DISTINCT IF(cs.call_state='AGENT_CONN |
| 78594340 | sysadmin | % | xxxx | Connect | 1 | Waiting for table level lock | UPDATE tbm_agent SET tbm_agent.state_fk = 'IDLE', tbm_agent.call_id = NULL, tbm_agent.agent_call_id |
| 78594342 | sysadmin | % | xxxx | Connect | 1 | Waiting for table level lock | CALL spc_elementData_queue |
| 78594343 | sysadmin | %:47647 | xxxx | Query | 1 | Waiting for table level lock | SELECT IFNULL(@oqsize,'---') 'Queue size'
,IFNULL(COUNT( DISTINCT IF(cs.call_state='AGENT_CONN |
| 78594344 | sysadmin | %:47648 | xxxx | Query | 1 | Waiting for table level lock | SELECT IFNULL(@oqsize,'---') 'Queue size'
,IFNULL(COUNT( DISTINCT IF(cs.call_state='AGENT_CONN |
| 78594345 | sysadmin | %:47649 | xxxx | Query | 1 | Waiting for table level lock | SELECT IFNULL(@oqsize,'---') 'Queue size'
,IFNULL(COUNT( DISTINCT IF(cs.call_state='AGENT_CONN |
| 78594348 | sysadmin | %:47652 | xxxx | Query | 1 | Waiting for table level lock | SELECT IFNULL(@oqsize,'---') 'Queue size'
,IFNULL(COUNT( DISTINCT IF(cs.call_state='AGENT_CONN |
| 78594349 | sysadmin | %:47653 | xxxx | Query | 1 | Waiting for table level lock | SELECT IFNULL(@oqsize,'---') 'Queue size'
,IFNULL(COUNT( DISTINCT IF(cs.call_state='AGENT_CONN |
| 78594352 | sysadmin | %:47656 | xxxx | Query | 1 | Waiting for table level lock | SELECT IFNULL(@oqsize,'---') 'Queue size'
Code:
top - 20:43:36 up 56 days, 22:13, 2 users, load average: 7.04, 6.32, 6.04
Tasks: 254 total, 1 running, 251 sleeping, 0 stopped, 2 zombie
Cpu(s): 18.4%us, 0.5%sy, 0.0%ni, 80.2%id, 0.5%wa, 0.0%hi, 0.3%si, 0.0%st
Mem: 32883016k total, 32234200k used, 648816k free, 366860k buffers
Swap: 8331256k total, 233512k used, 8097744k free, 8940560k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
16218 mysql 20 0 24.6g 20g 6720 S 157.5 65.6 67698:29 mysqld
25662 root 20 0 15224 1368 952 R 0.7 0.0 0:01.39 top
461 root 20 0 0 0 0 S 0.3 0.0 5:33.52 jbd2/dm-0-8
3808 sfe 20 0 781m 2296 1376 S 0.3 0.0 10:25.83 httpd
1 root 20 0 19400 1288 1064 S 0.0 0.0 2:35.07 init
2 root 20 0 0 0 0 S 0.0 0.0 0:00.02 kthreadd
3 root RT 0 0 0 0 S 0.0 0.0 0:49.83 migration/0
4 root 20 0 0 0 0 S 0.0 0.0 0:08.32 ksoftirqd/0
5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0
6 root RT 0 0 0 0 S 0.0 0.0 0:00.00 watchdog/0
7 root RT 0 0 0 0 S 0.0 0.0 0:51.56 migration/1
8 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/1
9 root 20 0 0 0 0 S 0.0 0.0 0:26.94 ksoftirqd/1
Code:
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 0.00 9.00 0.00 72.00 8.00 0.00 0.00 0.00 0.00
sdb 0.00 4.50 0.00 47.00 0.00 412.00 8.77 0.01 0.13 0.12 0.55
dm-0 0.00 0.00 0.00 9.00 0.00 72.00 8.00 0.00 0.00 0.00 0.00
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Filesystem: rBlk_nor/s wBlk_nor/s rBlk_dir/s wBlk_dir/s rBlk_svr/s wBlk_svr/s ops/s rops/s wops/s
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 0.00 0.50 0.00 4.00 8.00 0.00 3.00 3.00 0.15
sdb 0.00 5.50 0.50 65.50 4.00 568.00 8.67 0.02 0.33 0.27 1.75
dm-0 0.00 0.00 0.00 0.50 0.00 4.00 8.00 0.00 3.00 3.00 0.15
dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Filesystem: rBlk_nor/s wBlk_nor/s rBlk_dir/s wBlk_dir/s rBlk_svr/s wBlk_svr/s ops/s rops/s wops/s
Code:
[root@xxxx ~]# free -m
total used free shared buffers cached
Mem: 32112 31510 602 0 358 8737
-/+ buffers/cache: 22414 9697
Swap: 8135 228 7907
Code:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld]
bind-address = 0.0.0.0
datadir=/opt/xxxx/xxxx
tmpdir = /tmp
socket = /var/run/mysqld/mysqld.sock
max_connections = 2000
thread_concurrency = 16
thread_stack = 256k
transaction_isolation = REPEATABLE-READ
#Tuning Parameters
innodb_file_per_table = 1
innodb_status_file = 0
innodb_log_file_size = 500M
innodb_thread_concurrency = 4
innodb_buffer_pool_size = 16000M
innodb_log_buffer_size = 40M
innodb_flush_log_at_trx_commit = 1
event_scheduler = 1