MySql tres sensible aux surchages et aux requetes simultanees

MySql tres sensible aux surchages et aux requetes simultanees - SQL/NoSQL - Programmation

Marsh Posté le 18-09-2010 à 05:47:09    

Salut !

 

Mon MySql est extrèmement sensible aux petites surcharges du système et aux requetes faites la meme seconde.
J'ai équipé mes cgi (perl) de chronometres et peux ainsi tracer le temps moyen en ms et voir quelle partie du code est longue.
Quand tout se passe bien, le cgi au complet s'execute en 120ms - Et lorsque 5 requetes tombent la meme seconde, ca pousse parfois le tout à 4 secondes et plus ! et ce sont les requetes sql qui sont longues à ce moment.
Ce site par exemple, a 5000 visiteurs par jour et je dois avouer qu'il est très équipé en sql. (il y a 1 ou 2 tables de 200000 lignes mais 50MB chacune env)

 

=== SERVEUR : ===

Code :
  1. Xeon X3360 4 @ 2.83GHz
  2. 8GB DDR
  3. Linux 2.6 grsec slackware
  4. Mysql 5.1.46


=== MY.CNF : ===

Code :
  1. skip-external-locking
  2. key_buffer_size = 256M
  3. key_cache_age_threshold = 300
  4. key_cache_division_limit = 85
  5. key_cache_block_size = 4096
  6. max_allowed_packet = 16M
  7. table_open_cache = 256
  8. sort_buffer_size = 2M
  9. read_buffer_size = 128K
  10. tmp_table_size = 128M
  11. max_heap_table_size = 128M
  12. query_prealloc_size = 32M
  13. thread_cache_size = 64
  14. read_rnd_buffer_size = 16M
  15. net_buffer_length = 2K
  16. thread_stack = 128K
  17. query_cache_size = 512M
  18. query_cache_limit = 2M
  19. slow_query_log
  20. long_query_time = 0.5
  21. bind-address = 127.0.0.1
  22. server-id = 1


+ relay config...
parce que j'ai répliqué sur un slave server-id=2 avec basse priorité et petite mémoire juste pour mysqldump

 

Le master est nice -7 ; slave est nice 8
Apache est nice -8

 

=== MY SQL STATISTICS GRAPH : ===

Code :
  1. J'ai créé un programme qui trace d'intéressants graphiques de l'utilisation de sql
  2. Voici l'exemple d'un mauvais jour (charge) :
  3. http://img.misterbark.com/mysql-2010-09-11.png
  4. Et un bon jour (sql fraichement relancé) :
  5. (malgré les parfois longues requetes qui viennent ruiner la moyenne des pics rouges)
  6. http://img.misterbark.com/mysql-2010-09-17.png


Comme vous pouvez le voir :
- mon disque est tres peu utilisé (moins de 1 pour 1000)
- mon cache est très utile: 15 à 20% des requetes viennent du cache et il n'est pas plein
- j'ai 1 connexion par /s et 10 queries /s
 
- dans la courbe du 2010-09-11 on voit parfois jusqu'a une somme de 60 secondes de slow queries, par minute. (somme des queries plus longues que 0.5s) ce sont les petites surcharges du serveur qui me poussent parfois la moyenne des cgi à pres de 7s !

 


Alors comment pourrais-je etre sur que MySql réponde toujours dans un temps raisonnable ?
Est-ce que je devrais prioriser MySql plus que apache ?
Je ne vois pas trop comment donner plus de pouvoir à MySql sachant que son cache n'est pas plein et que tous les .MYI rentrent dans key_buffer_size.
Merci beaucoup par avance pour vos idées !


Message édité par MisterBark le 18-09-2010 à 08:42:14

---------------
La vie c'est comme une boite de chocolats, on ne sait jamais sur quoi on va tomber. (Forrest Gump)
Reply

Marsh Posté le 18-09-2010 à 05:47:09   

Reply

Marsh Posté le 18-09-2010 à 13:55:05    

passe un coup de http://blog.mysqltuner.com/ pour voir ce qu'il te dit

Message cité 1 fois

---------------
uptime is for lousy system administrators what Viagra is for impotent people - mes unixeries - github me
Reply

Marsh Posté le 18-09-2010 à 21:18:49    

black_lord a écrit :

passe un coup de http://blog.mysqltuner.com/ pour voir ce qu'il te dit


Code :
  1. >>  MySQLTuner 1.1.1 - Major Hayden <major@mhtx.net>
  2. >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
  3. >>  Run with '--help' for additional options and output filtering
  4. [OK] Logged in using credentials passed on the command line
  6. -------- General Statistics --------------------------------------------------
  7. [--] Skipped version check for MySQLTuner script
  8. [OK] Currently running supported MySQL version 5.1.46-log
  9. [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
  11. -------- Storage Engine Statistics -------------------------------------------
  12. [--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
  13. [--] Data in MyISAM tables: 568M (Tables: 49)
  14. [!!] Total fragmented tables: 31
  16. -------- Security Recommendations  -------------------------------------------
  17. [OK] All database users have passwords assigned
  19. -------- Performance Metrics -------------------------------------------------
  20. [--] Up for: 13d 3h 3m 42s (11M q [9.795 qps], 919K conn, TX: 446M, RX: 1B)
  21. [--] Reads / Writes: 35% / 65%
  22. [--] Total buffers: 896.0M global + 18.4M per thread (151 max threads)
  23. [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
  24. [!!] Maximum possible memory usage: 3.6G (45% of installed RAM)
  25. [OK] Slow queries: 0% (96K/11M)
  26. [OK] Highest usage of available connections: 36% (55/151)
  27. [OK] Key buffer size / total MyISAM indexes: 256.0M/25.2M
  28. [OK] Key buffer hit rate: 99.8% (2M cached / 5K reads)
  29. [!!] Query cache efficiency: 14.6% (532K cached / 3M selects)
  30. [OK] Query cache prunes per day: 0
  31. [OK] Sorts requiring temporary tables: 4% (4K temp sorts / 108K sorts)
  32. [OK] Temporary tables created on disk: 0% (14 on disk / 7K total)
  33. [OK] Thread cache hit rate: 99% (89 created / 919K connections)
  34. [OK] Table cache hit rate: 44% (217 open / 490 opened)
  35. [OK] Open file limit used: 26% (269/1K)
  36. [OK] Table locks acquired immediately: 99% (9M immediate / 9M locks)
  38. -------- Recommendations -----------------------------------------------------
  39. General recommendations:
  40.     Run OPTIMIZE TABLE to defragment tables for better performance
  41. Variables to adjust:
  42.     query_cache_limit (> 2M, or use smaller result sets)


 
query_cache_limit : inutile de la baisser puisque mon cache n'est pas utilisé en plein.
 
OPTIMIZE TABLE : je viens de faire ca sur mes tables les plus grosses.
Tout d'abord, ca m'a crashé une table ! que j'ai vite réparée avec myisamchk.
Et ensuite, j'ai l'impression que beaucoup de choses sont plus rapides, mais j'ai besoin de plus de recule pour voir l'impact que ca a.


---------------
La vie c'est comme une boite de chocolats, on ne sait jamais sur quoi on va tomber. (Forrest Gump)
Reply

Marsh Posté le 20-09-2010 à 08:20:32    

Si tu lances les queries a la main est-ce que tu as le meme probleme?
C'est juste pour etre sur que le probleme est uniquement lié a MySQL et pas une combinaison de choses.
 
Si tu fais le meme tests sur des tables qui ont 2x moins d'enregistrements, ca va 2x plus vite ou pas?
 
On dirais que soit tous les CPU ne sont pas utilisés (peu probable, sinon tu aurais le probleme aussi apres un restart) ou que tes indexes ne sont pas maintenus (reindex/rebuild).

Message cité 1 fois
Reply

Marsh Posté le 20-09-2010 à 09:17:49    

Par contre note bien que tes 8 GB de ram ne servent à rien si tu as un kernel 32 bits comme c'est marqué dans le rapport.

Message cité 1 fois
Reply

Marsh Posté le 20-09-2010 à 16:58:29    

Tout d'abord, merci à tous pour votre aide.
 

Oliiii a écrit :

Si tu lances les queries a la main est-ce que tu as le meme probleme?
C'est juste pour etre sur que le probleme est uniquement lié a MySQL et pas une combinaison de choses.
 
Si tu fais le meme tests sur des tables qui ont 2x moins d'enregistrements, ca va 2x plus vite ou pas?
 
On dirais que soit tous les CPU ne sont pas utilisés (peu probable, sinon tu aurais le probleme aussi apres un restart) ou que tes indexes ne sont pas maintenus (reindex/rebuild).


 
Justement, cette question est très intéressante car j'ai l'impression que la somme des slow queries ne correspond pas du tout à la somme des moyennes de mes sites.
Je veux dire que (lorsque ca se passe mal) les lignes de code perl concernant sql sont largement plus lentes que le temps des requetes.
Pourtant, chacune de ces parties chronométrées n'incluent qu'une requete sql, meme pas sa connection, qui n'est pas généralement ce qui prend du temps (en socket d'ailleurs)
Par ex: une requete sql dans le perl semble prendre 4s en cas de crise, mais ca devrait me faire beaucoup plus que 60s de slow queries cummulées par minutes (voir mon graph) car j'ai alors bien plus de 15 requetes lentes comme ceci dans une minute. (sachant en plus que les slow queries ne sont que >0.5s)
Après, je ne suis par certain de ca, mais j'en ai bien l'impression.
 
Oui, des petites tables c'est rapide, aucun doute.
(reindex/rebuild) -> hum, je vais checker ce que c'est, je ne connais pas !
 

antac a écrit :

Par contre note bien que tes 8 GB de ram ne servent à rien si tu as un kernel 32 bits comme c'est marqué dans le rapport.


 
Oui, j'ai bien vu merci.
Mais pour mon sql 3.5 me suffisent largement il me semble, et j'utilise énormément de tmpfs et ramfs a coté. Donc les 8GB sont pratiquement utilisés à 100%


Message édité par MisterBark le 20-09-2010 à 17:02:40

---------------
La vie c'est comme une boite de chocolats, on ne sait jamais sur quoi on va tomber. (Forrest Gump)
Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed