Aller au contenu

Aide optimisation Mysql pour du gros trafic

ArnaudSimonetti

Par ArnaudSimonetti
dans SQL

 Partager

Sujets conseillés

ArnaudSimonetti

ArnaudSimonetti

Posté
Posté (modifié)

Bonjour a tous !



Je vous retrace l'historique de mon problème :



J'ai actuellement un site qui génère environs 150 000 - 400 000 visiteurs par jours, mon problème est la, je n'arrive pas a optimiser convenablement mon serveur pour ce genre de trafic. J'ai fais quelque petite optimisation pour que le site reste stable. Actuellement le site est stable mais il crash souvent quand ils y a des pics de connexion ....



Je vous montre un peux ce que me dis " mysqltuner " :




 >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>

 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/

 >>  Run with '--help' for additional options and output filtering

[OK] Logged in using credentials from debian maintenance account.

[OK] Currently running supported MySQL version 5.5.38-0+wheezy1

[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM


-------- Storage Engine Statistics -------------------------------------------

[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM

[--] Data in MyISAM tables: 28B (Tables: 14)

[--] Data in InnoDB tables: 9M (Tables: 39)

[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)

[!!] Total fragmented tables: 39


-------- Security Recommendations  -------------------------------------------

[OK] All database users have passwords assigned


-------- Performance Metrics -------------------------------------------------

[--] Up for: 35m 1s (1M q [606.712 qps], 15K conn, TX: 6B, RX: 145M)

[--] Reads / Writes: 70% / 30%

[--] Total buffers: 692.0M global + 2.7M per thread (151 max threads)

[OK] Maximum possible memory usage: 1.1G (29% of installed RAM)

[OK] Slow queries: 0% (0/1M)

[!!] Highest connection usage: 100%  (152/151)

[OK] Key buffer size / total MyISAM indexes: 16.0M/131.0K

[OK] Query cache efficiency: 92.3% (1M cached / 1M selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4K sorts)

[!!] Joins performed without indexes: 17091

[OK] Temporary tables created on disk: 5% (560 on disk / 10K total)

[OK] Thread cache hit rate: 94% (926 created / 15K connections)

[OK] Table cache hit rate: 25% (128 open / 511 opened)

[OK] Open file limit used: 7% (76/1K)

[OK] Table locks acquired immediately: 100% (125K immediate / 125K locks)

[OK] InnoDB buffer pool / data size: 128.0M/9.2M

[OK] InnoDB log waits: 0

-------- 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

    Reduce or eliminate persistent connections to reduce connection usage

    Adjust your join queries to always utilize indexes

Variables to adjust:

    max_connections (> 151)

    wait_timeout (< 28800)

    interactive_timeout (< 28800)

    join_buffer_size (> 128.0K, or always use indexes with joins)


Je trouve bizarre que msqltuner me dis que j'ai : " [!!] Highest connection usage: 100% (152/151) " alors que voici les configuration que j'ai faite dans mon dossier " my.cnf " :




# * Fine Tuning

#

key_buffer              = 16M

max_allowed_packet      = 16M

thread_stack            = 192K

thread_cache_size       = 8

# This replaces the startup script and checks MyISAM tables if needed

# the first time they are touched

myisam-recover         = BACKUP

#max_connections        = 500

#table_cache            = 10000

#thread_concurrency     = 10

#

# * Query Cache Configuration

#

query_cache_limit       = 5M

query_cache_size        = 516M




J'ai établie mon max connexion a 500 et mysqltuner me dis qu'il est toujours a 151 par défaut.



J'ai pourtant bien restart le service mysql avec " /etc/init.d/mysql restart " mais rien n'as l'air de changer, je ne comprend pas pourquoi, pouvez-vous m'aider smile.gif



Edit : Je suis héberger chez Online avec une Dedibox MD 2014 32GB


Modifié par ArnaudSimonetti
Dadou

Dadou

Posté
Posté

Ben si tu laisses le # devant la ligne, cette dernière est en commentaire, et donc ton paramétrage n'est pas pris en compte


Et dans ce que je vois de mysqltuner, il semble qu'il ne te sert pas d'avoir 32 Go de RAM sur ton serveur

ArnaudSimonetti

ArnaudSimonetti

Posté
  • Auteur
Posté

Merci Dadou,



J'ai retiré le # et effectivement ça fonctionne mais j'ai un nouveau problème de RAM : 





 >>  MySQLTuner 1.1.1 - Major Hayden <major@mhtx.net>

 >>  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.5.38-0+wheezy1

[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM


-------- Storage Engine Statistics -------------------------------------------

[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 28B (Tables: 14)

[--] Data in InnoDB tables: 9M (Tables: 39)

[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)

[!!] Total fragmented tables: 39


-------- Security Recommendations  -------------------------------------------

[OK] All database users have passwords assigned


-------- Performance Metrics -------------------------------------------------

[--] Up for: 4m 0s (99K q [415.483 qps], 1K conn, TX: 506M, RX: 11M)

[--] Reads / Writes: 75% / 25%

[--] Total buffers: 691.0M global + 2.7M per thread (5000 max threads)

[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability

[!!] Maximum possible memory usage: 13.8G (385% of installed RAM)

[OK] Slow queries: 0% (0/99K)

[OK] Highest usage of available connections: 0% (22/5000)

[OK] Key buffer size / total MyISAM indexes: 16.0M/122.0K

[OK] Query cache efficiency: 90.3% (82K cached / 91K selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 889 sorts)

[!!] Joins performed without indexes: 1557

[OK] Temporary tables created on disk: 24% (436 on disk / 1K total)

[OK] Thread cache hit rate: 95% (63 created / 1K connections)

[OK] Table cache hit rate: 25% (98 open / 387 opened)

[OK] Open file limit used: 0% (76/25K)

[OK] Table locks acquired immediately: 100% (12K immediate / 12K locks)

[OK] InnoDB data size / buffer pool: 9.2M/128.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

Variables to adjust:

  *** MySQL's maximum memory usage is dangerously high ***

  *** Add RAM before increasing MySQL buffer variables ***

    join_buffer_size (> 128.0K, or always use indexes with joins)


Cette partie que je n'arrive pas a configurer : 




-------- 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

Variables to adjust:

  *** MySQL's maximum memory usage is dangerously high ***

  *** Add RAM before increasing MySQL buffer variables ***

    join_buffer_size (> 128.0K, or always use indexes with joins)


Peux-tu me dire les commandes a effectuer pour résoudre le problème ? S'il te plait :)


Dadou

Dadou

Posté
Posté

Bien sûr que tu as un problème de RAM, et tu l'auras toujours sur ce serveur :





[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability



Il semble dire que ton système d'exploitation est en 32 bits, du coup, ça va être dûr de lui allouer plus de mémoire


ArnaudSimonetti

ArnaudSimonetti

Posté
  • Auteur
Posté

Merci pour vos réponse,



Dadou, donc je dois faire quoi ? Le changer en 64 bits ?



Khald-ref, tu pourrais plus m'expliquer en détails, je n'ais pas compris





je pense le problème est que toutes les ressources RAM sont exploités par ta requête



Voici un nouveau résultat de mysqltuner : 




 >>  MySQLTuner 1.1.1 - Major Hayden <major@mhtx.net>

 >>  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.5.38-0+wheezy1-log

[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM


-------- Storage Engine Statistics -------------------------------------------

[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 28B (Tables: 14)

[--] Data in InnoDB tables: 8M (Tables: 29)

[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)

[!!] Total fragmented tables: 29


-------- Security Recommendations  -------------------------------------------

[OK] All database users have passwords assigned


-------- Performance Metrics -------------------------------------------------

[--] Up for: 9s (565 q [62.778 qps], 59 conn, TX: 2M, RX: 59K)

[--] Reads / Writes: 96% / 4%

[--] Total buffers: 788.0M global + 6.7M per thread (1500 max threads)

[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability

[!!] Maximum possible memory usage: 10.6G (295% of installed RAM)

[OK] Slow queries: 0% (0/565)

[OK] Highest usage of available connections: 0% (2/1500)

[OK] Key buffer size / total MyISAM indexes: 256.0M/129.0K

[OK] Query cache efficiency: 33.5% (161 cached / 481 selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 57 sorts)

[!!] Joins performed without indexes: 12

[OK] Temporary tables created on disk: 24% (79 on disk / 323 total)

[OK] Thread cache hit rate: 96% (2 created / 59 connections)

[OK] Table cache hit rate: 22% (84 open / 370 opened)

[OK] Open file limit used: 1% (78/7K)

[OK] Table locks acquired immediately: 100% (375 immediate / 375 locks)

[OK] InnoDB data size / buffer pool: 8.7M/300.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

Variables to adjust:

  *** MySQL's maximum memory usage is dangerously high ***

  *** Add RAM before increasing MySQL buffer variables ***

    join_buffer_size (> 512.0K, or always use indexes with joins)
Dan

Dan

Posté
Posté

Tes paramètres de config mysql permettent d'utiliser presque 3 fois la RAM que tu as sur ton serveur !!!




[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability

[!!] Maximum possible memory usage: 10.6G (295% of installed RAM)

Tu tournes en 32 bits ? Tu as combien de RAM ? 3.6GB ?


SStephane

SStephane

Posté
Posté

Avec ton traffic, je pense qu'il est quand même temps de songer à autre chose que du tunning certes utile mais peu efficace au profit de solutions de cache plus radicales : varnish + memcache.


Si les utilisateurs n'ont pas besoin d'être connecté (consultation simple) ; varnish reglera tous tes soucis, sinon memcache peut réduire considérablement le nombre de requêtes à la BDD si tu stockes qq informations dans la ram.



Franchement, si la durée de tes requêtes reste acceptable (pas de slow queries : http://dev.mysql.com/doc/refman/5.0/fr/slow-query-log.html à voir au passage), tunner MySQL c'est cool, mais je doute que ça t'apporte réellement un gain notable.



Dans certains cas, tu ne peux pas faire autrement que de requêter la BDD, après t'as d'autres pistes comme envisager une réplication et un load balancing (avec haproxy par exemple) :


Navré de pas répondre à ta question, mais je doute que le tunning t'apporte satisfaction.


  • Message populaire.
Dadou

Dadou

Posté
  • Message populaire.
Posté

@Dan il a écrit dans son premier message, il a une Dedibox MD 2014 32GB



Tu as un serveur de 32 Go de Ram, mais tu sembles avoir un système 32 bits qui ne gère qu'au mieux 3,6Go, donc si tu veux être sûr de bénéficier des 32 Go de Ram, il va falloir passer à un OS 64 bits, c'est d'ailleurs indiqué dans le rapport de tuning :




[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM




@SStephane, avant de le lancer dans de grands frais et travaux, il serait plus intéressant de lui faire réellement utiliser les ressources du serveur, a mon avis, une bécane avec 32Go de mémoire devrait arriver à tenir la charge.


  • Vote 1
Dan

Dan

Posté
Posté

J'avais zappé l'info sur la Dedibox... et cela explique alors les 3.6GB de RAM maxi pour mysql...



Donc oui, il faut qu'il passe en 64 bits !

Veuillez vous connecter pour commenter

Vous pourrez laisser un commentaire après vous êtes connecté.



Connectez-vous maintenant
 Partager
Aller sur la liste des sujets
×
×
  • Créer...