Le server MySQL rencontre des connections de haute tension / max

The Short: Nous atteignons les connections maximales et voyons des performances très faibles de MySQL pendant les heures de pointe et ne pouvons pas déterminer s'il s'agit d'un problème de chargement ou d'un problème de configuration.

The Long: Pour commencer, voici nos spécifications de server hébergées sur Digital Ocean:

Cœurs: 20

Mémoire:

total utilisé gratuit partagé buff / cache disponible Mem: 62G 48G 4.7G 216M 10G 14G

MySQL WorkBench Dashboard Snapshot:

Tableau de bord MySQL Workbench

Nous avons généralement plus de 200 inputs dans processlist, presque toutes les "tables d'ouverture", "tables de fermeture" et, un peu less fréquentes, "nettoyage". Les inputs de nettoyage ont COMMAND killed et NULL INFO ( SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST ). Les seules questions que nous voyons vraiment prendre très longtime sont des INSERTS dans des arrays avec une utilisation intensive, et des commands UPDATE avec la key primaire dans l'OÙ pour les arrays avec de lourds nous. Leur état est "mise à jour" dans la list des process.

Paramètres MySQL .cnf:

 key_buffer_size=1G myisam_sort_buffer_size=1073741824 max_length_for_sort_data=8388608 max_sort_length=8388608 sort_buffer_size=1073741824 join_buffer_size=1073741824 preload_buffer_size=1073741824 read_buffer_size=1073741824 read_rnd_buffer_size=1073741824 thread_cache_size=450 max_allowed_packet=100M max_connections=400 lock_wait_timeout=50 wait_timeout=120 table_open_cache_instances=10 table_open_cache=6000 table_definition_cache=6000 innodb_flush_method=O_DIRECT innodb_buffer_pool_size=46G innodb_buffer_pool_instances=46 

Nous disposons de plus de 800 bases de données sur notre server unique en ligne et de plusieurs milliers d'users utilisant une application qui fait des requests fréquentes de servers avec beaucoup de mises à jour ou d'insertions d'loggings individuels qui doivent être synchronisées avec plusieurs users.

La question:

Compte tenu des parameters .cnf et de nos spécifications de server, en relation avec l'instantané du tableau de bord MySQL WorkBench, devrions-nous connaître le type de ralentissement / connections que nous voyons avec cette configuration? Nous SQLSTATE[HY000] [2002] Resource temporarily unavailable constamment nos connections maximales et obtenons SQLSTATE[HY000] [2002] Resource temporarily unavailable erreurs en PHP de PDO.

Est-ce que quelqu'un a des suggestions pour améliorer la performance? Différents parameters de configuration? Nous connaissons des solutions à long terme, comme la mise en place de grands clients sur des servers distincts, entre autres, mais à court terme, existe-t-il un moyen d'optimiser notre server? Notre application et notre site Web sont à peine utilisables pendant les heures de pointe.

Statuts supplémentaires:

Linux 4.0.4-301.fc22.x86_64 25/04/2016 _x86_64_ (20 CPU)

avg-cpu:% user% nice% system% iowait% steal% ralenti

8,51 0,01 0,67 0,28 0,35 90,18

Dispositif: tps kB_read / s kB_wrtn / s kB_read kB_wrtn vda
269,78 1681.90 2180,52 37679232421 48849706700

Mon argent est sur la «configuration» – mais probablement de la manière opposée que vous prévoyez … Je suppose que vous l'avez trop configuré.

Il me regarde comme si vous étiez "accordant" la performance de votre server directement dans le sol .

Si vous utilisez un script "tuning", arrête de le faire.

Par exemple … table_definition_cache=6000 et table_open_cache=6000 … quelle est la justification de cela?

La plupart des parameters que vous avez configurés loin de leurs parameters par défaut semblent être des conjectures.

Sauf si vous avez évalué chacune de ces personnalisations, individuellement, avant et après vous les avez faites, individuellement … eh bien, vous voyez probablement où je vais … ils sont probablement injustifiés, et vous êtes probablement mieux sans eux . Plus grand n'est pas toujours mieux. Plus grand peut être plus difficile à jongler.

Je m'attends à de bonnes choses si vous voulez simplement commenter presque tous ces parameters sauf pour innodb_* et laisser le server exécuter avec plus de parameters à leurs valeurs par défaut.