Onderwerp: Hoge load door MySQL

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

Je my.cnf is wel erg basic. Kijk eens met mysqltuner of deze suggesties heeft (MySQL moet daarvoor 24+ uur niet herstart zijn). Want zonder de omgeving verder te kennen is er zo te zien nog heel wat te optimaliseren.

Er wordt normaliter geadviseerd een thread_concurrency van 8 te gebruiken. Kijk ook eens naar:
http://dimitrik.free.fr/blog/archive...ncurrency.html

Verder zie ik in je my.cnf alleen INNODB dingen staan, maar tabel level locks komen normaliter alleen bij myisam voor. Loop eens na of je niet gemixed storage engines gebruikt.

-Edit Ik zie dat je ook memory engine gebruikt. Dat verklaart de locks dan inderdaad. Kijk ook eens of de maximale grootte niet wordt overschreven?

Verder is de MEMORY engine niet echt bedoeld voor veel writes:

Performance Characteristics

MEMORY performance is constrained by contention resulting from single-thread execution and table lock overhead when processing updates. This limits scalability when load increases, particularly for statement mixes that include writes.

Despite the in-memory processing for MEMORY tables, they are not necessarily faster than InnoDB tables on a busy server, for general-purpose queries, or under a read/write workload. In particular, the table locking involved with performing updates can slow down concurrent usage of MEMORY tables from multiple sessions.

Depending on the kinds of queries performed on a MEMORY table, you might create indexes as either the default hash data structure (for looking up single values based on a unique key), or a general-purpose B-tree data structure (for all kinds of queries involving equality, inequality, or range operators such as less than or greater than). The following sections illustrate the syntax for creating both kinds of indexes. A common performance issue is using the default hash indexes in workloads where B-tree indexes are more efficient.

http://dev.mysql.com/doc/refman/5.6/...ge-engine.html

Bedankt voor de reacties tot dusver. Er zal zeker het een en ander veel beter te tunen zijn, ik ga intern op een test machine proberen de load te simuleren en dan is met mysqltuner kijken.

De memory tabellen lijken juist super snel, er staan niet veel records in trouwens dus hij zal niet uit de max grote lopen. Er zitten max 100 records in, wel veel updates en selects. Wij merken geen performance problemen alleen de hoge load average valt op waardoor ik me afvraag of er nou direct een probleem is of niet, omdat ik het niet volledig snap.

Als jij ZIET dat 200 queries op een lock staan te wachten, dan héb je een performance/schalings probleem, of je dat nu zo ervaart of niet.
Normaal zie je een lock niet eens langskomen namelijk.
De grootte van de mem tabel maakt niet veel uit, het is de table lock die nodig is die ervoor zorgt dat het trager wordt.
Mogelijk ben je beter af door een temptable te maken ipv van die mem table en dat /tmp te mounten als een ramdisk. Dan heb je wel de ram based opslag voor de tabel, maar ben je waarschijnlijk van de locks af. (Tenzij dezelfde records worden gelocked natuurlijk).

Oorspronkelijk geplaatst door systemdeveloper

Als jij ZIET dat 200 queries op een lock staan te wachten, dan héb je een performance/schalings probleem, of je dat nu zo ervaart of niet.
Normaal zie je een lock niet eens langskomen namelijk.
De grootte van de mem tabel maakt niet veel uit, het is de table lock die nodig is die ervoor zorgt dat het trager wordt.
Mogelijk ben je beter af door een temptable te maken ipv van die mem table en dat /tmp te mounten als een ramdisk. Dan heb je wel de ram based opslag voor de tabel, maar ben je waarschijnlijk van de locks af. (Tenzij dezelfde records worden gelocked natuurlijk).

Okay bedankt voor die info, ik zal met development gaan praten, zal lastig worden :-(.

Geen idee hoe die queries zijn, maar misschien anders gewoon memcache gebruiken al dan niet vanuit mysql: https://dev.mysql.com/doc/refman/5.6...memcached.html

Oorspronkelijk geplaatst door chielsen

Geen idee hoe die queries zijn, maar misschien anders gewoon memcache gebruiken al dan niet vanuit mysql: https://dev.mysql.com/doc/refman/5.6...memcached.html

Ik weet niet of dat goed gaat werken omdat de data dynamisch is en continue veranderd, het zijn eigenlijk alleen select en update queries die allemaal vanuit SPC's worden aangeroepen.

Je kunt ook eens kijken naar een (kleine) ndb cluster setupje. Ook daar heb je het ram voordeel, maar zit je niet vast aan de table locks. Als je de tabel dan nog zonder logging aanmaakt heb je niet de overhead van de redo log (wat gewoon een killer is bij veel table io) maar wel gewoon rowlocking. Plus natuurlijk de mogelijkheid om te schalen naar zoveel servers als je wilt zodat je toch erg veel moeite moet gaan doen om die load boven de 50% te krijgen.

Huh? Waarom heeft iedereen het over TMP tabellen. Er staat nergens dat MySQL bezig is met het schrijven naar temporary tables. Daarnaast zit de server maar op 0.5% WA. Het lijkt me dus niet echt een i/o issue. Normaal gezien betekend "waiting for table level lock" gewoon dat je MyISAM tabellen gebruikt. MyISAM heeft alleen table level locking. De tabellen omzetten naar InnoDB (row locking) zou het dan moeten oplossen. Dan maak je ook beter gebruik van je 16GB innodb_buffer_pool. De CPU loopt op omdat er veel processen zijn, maar dat valt dus makkelijk op te lossen.

Oorspronkelijk geplaatst door SF-Jeroen

Huh? Waarom heeft iedereen het over TMP tabellen. Er staat nergens dat MySQL bezig is met het schrijven naar temporary tables. Daarnaast zit de server maar op 0.5% WA. Het lijkt me dus niet echt een i/o issue. Normaal gezien betekend "waiting for table level lock" gewoon dat je MyISAM tabellen gebruikt. MyISAM heeft alleen table level locking. De tabellen omzetten naar InnoDB (row locking) zou het dan moeten oplossen. Dan maak je ook beter gebruik van je 16GB innodb_buffer_pool. De CPU loopt op omdat er veel processen zijn, maar dat valt dus makkelijk op te lossen.

Beter lezen wat ts zegt. Hij zegt dat de locks komen door zijn memory tables. Deze zijn per definitie volatile a.k.a. voor tijdelijke resultaten aangezien ze een server restart niet overleven.
Op memory tables heb je altijd een table lock.
Daarnaast worden locks hierop eerst vrijgegeven aan de write-queue, dus met veel writes op die tabellen, blijven je reads langer op de lock wachten wat een hogere load tot gevolg heeft.
Dit kun je natuurlijk oplossen door innodb te gebruiken voor die tabellen maar dan offer je redelijk wat performance op t.o.v. data in ram.

Edit: je kunt een (set van) 'persistent' tabellen gewoon moven naar een andere db en die db directory mounten op een tmpfs deel om de boel in ram te hebben. Dan gebruik je toch alleen ram terwijl je tabellen hebt die door meerdere connecties gebruikt kunnen worden. (Moet je wel even de ram db/tabellen aanmaken als je de server reboot maar dat kan met een eenvoudig start scriptje.

Er was nu wel duidelijk een performance issue merkbaar. Merkwaardig genoeg heeft een mysql restart de load enorm verlaagd (factor 10). Kan het zijn dat het systeem gewoon out of memory is vanwege verkeerde tuning parameters?

Dit soort artikelen zijn ook erg intressant, ik vraag me af of dat het hele issue kan zijn:

http://blog.jcole.us/2010/09/28/mysq...-architecture/
http://blog.jcole.us/2012/04/16/a-br...uma-and-mysql/

Inmiddels intern een test server opgetuigt en ik kan het probleem volledig simmuleren. Bij veel verkeer wordt mysql na 1.5 dag langszaam. Als ik het verkeer dan stop en opnieuw aanzet blijft hij langszaam, alsof een of andere buffer volzit of iets. Bij MySQL restart is het probleem direct weg.

Iemand enig idee wat dit zou kunnen zijn?