Bonjour à tous.
Après un serveur avec une R3 de OVH et l'impossibilité de migrer la version de PHP sans tout casser, j'ai repris une nouvelle machine (SP-128) sur CentOS 7 avec Plesk.
J'ai du mal à configurer MySQL pour tirer un maximum des performances du serveur. Il y a tellement de paramètre qui peuvent influencer … j'ai passé pas mal de temps à chercher mais je préfère venir demander un peu d'aide aux pros 
[root@ns3049052 /]# ./mysqltuner.pl
>> MySQLTuner 1.6.1 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '–help' for additional options and output filtering
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.50-MariaDB
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[–] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM
[–] Data in MyISAM tables: 2G (Tables: 16390)
[–] Data in InnoDB tables: 14M (Tables: 391)
[!!] Total fragmented tables: 496
-------- Security Recommendations -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'drivup@%' hasnt specific host restriction.
[!!] There is no basic password file list!
-------- Performance Metrics -------------------------------------------------
[–] Up for: 2d 11h 51m 41s (44M q [206.942 qps], 1M conn, TX: 497B, RX: 10B)
[–] Reads / Writes: 98% / 2%
[–] Binary logging is disabled
[–] Total buffers: 109.6G global + 154.5M per thread (200 max threads)
[!!] Maximum reached memory usage: 111.6G (88.67% of installed RAM)
[!!] Maximum possible memory usage: 139.8G (111.08% of installed RAM)
[OK] Slow queries: 0% (584/44M)
[OK] Highest usage of available connections: 6% (13/200)
[OK] Aborted connections: 0.01% (177/1725232)
[OK] Query cache efficiency: 47.8% (35M cached / 74M selects)
[!!] Query cache prunes per day: 73353
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 712K sorts)
[!!] Joins performed without indexes: 16302
[OK] Temporary tables created on disk: 1% (14K on disk / 1M total)
[OK] Thread cache hit rate: 99% (13 created / 1M connections)
[OK] Table cache hit rate: 99% (18K open / 18K opened)
[OK] Open file limit used: 33% (33K/100K)
[OK] Table locks acquired immediately: 99% (5M immediate / 5M locks)
-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 36.0% (386M used / 1B cache)
[OK] Key buffer size / total MyISAM indexes: 1.0G/794.9M
[OK] Read Key buffer hit rate: 100.0% (560M cached / 181K reads)
[!!] Write Key buffer hit rate: 40.9% (1M cached / 706K writes)
-------- InnoDB Metrics -----------------------------------------------------
[–] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 104.0G/14.6M
[!!] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 0.02% (1446 used/ 6815743 total)
[OK] InnoDB Read buffer efficiency: 99.62% (363335 hits/ 364714 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1216 writes)
-------- AriaDB Metrics -----------------------------------------------------
[–] AriaDB is disabled.
-------- Replication Metrics -------------------------------------------------
[–] No replication slave(s) for this server.
[–] This is a standalone server.
Et voici mon fichier config :
max_heap_table_size = 4G
bulk_insert_buffer_size = 2G
tmp_table_size = 4G
query_cache_type = 1
#query_cache_size = 4G
query_cache_size = 512M
query_cache_limit = 4G
#query_cache_strip_comments = 1
innodb_buffer_pool_size = 104G
thread_concurrency = 24
thread_cache_size = 16K
thread_stack = 512K
max_connections = 200
table_open_cache = 64K
table_cache = 64K
table_definition_cache = 64K
read_rnd_buffer_size = 16M
key_buffer_size = 1G
key_buffer = 1G
sort_buffer_size = 128M
join_buffer_size = 8M
read_buffer_size = 2M
interactive_timeout = 1200
wait_timeout = 1200
slow_query_log=1
slow_query_log_file=/var/log/mariadb/mysql-slow.log
long_query_time = 1
Pour résumer, le serveur héberge une application pour auto-écoles. Il y a pas mal de requête SELECT pour les plannings. En heure de pointe, on est entre 150 et 180 connectés simultanés.
Les tables sont au format MyISAM … dois-je migrer en InnoDB ?
Version PHP 5.6.27
Serveur MariaDB 5.5.50
Pourquoi mon message ?
J'ai des warnings dans PHPMyAdmin qui me disent que le cache est mal utilisé, etc…
Et mon soucis, c'est que de manière aléatoire, MySQL plante genre 20-30 secondes et repart …
Merci de votre aide par avance.
Bonjour,
c'est toi qui gère tout de A à Z ? (Web et Mysql ou juste Mysql) ? C'est toi qui voit la structure des tables aussi ? il y a quoi sur le serveur ? Mysql ? Web ? Tu veux optimiser quoi ?
c'est tout ce que te donne mysqltuner.pl ? il ne donne pas plus d'infos ?
Serait-il possible d'avoir le résultat des stats MYSQL/MariaDB avec http://www.day32.com/MySQL/
Pour les tables fragmentées, tu dois pouvoir arranger çà en lançant périodiquement la nuit
mysqlcheck -u root -ppassword --auto-repair --check --optimize --all-databases
Bonsoir Buddy,
Oui, je gère tout de A à Z.
Le serveur fait Web et MySQL.
Merci pour la commande optimize, je vais mettre ça déjà en place aussi.
Voici le rapport COMPLET de mysqltuner :
[root@ns3049052 /]# ./mysqltuner.pl
>> MySQLTuner 1.6.1 - Major Hayden mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '–help' for additional options and output filtering
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.50-MariaDB
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[–] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM
[–] Data in MyISAM tables: 2G (Tables: 16448)
[–] Data in InnoDB tables: 14M (Tables: 391)
[!!] Total fragmented tables: 391
-------- Security Recommendations -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'drivup@%' hasnt specific host restriction.
[!!] There is no basic password file list!
-------- Performance Metrics -------------------------------------------------
[–] Up for: 2d 21h 33m 42s (82M q [327.895 qps], 2M conn, TX: 1085B, RX: 15B)
[–] Reads / Writes: 99% / 1%
[–] Binary logging is disabled
[–] Total buffers: 108.7G global + 154.5M per thread (100 max threads)
[!!] Maximum reached memory usage: 110.8G (88.04% of installed RAM)
[!!] Maximum possible memory usage: 123.8G (98.35% of installed RAM)
[OK] Slow queries: 0% (610/82M)
[OK] Highest usage of available connections: 14% (14/100)
[OK] Aborted connections: 0.01% (184/2730140)
[OK] Query cache efficiency: 46.2% (62M cached / 135M selects)
[!!] Query cache prunes per day: 64783
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 33457
[OK] Temporary tables created on disk: 0% (24K on disk / 4M total)
[OK] Thread cache hit rate: 99% (14 created / 2M connections)
[!!] Table cache hit rate: 2% (671 open / 28K opened)
[OK] Open file limit used: 0% (482/100K)
[OK] Table locks acquired immediately: 99% (18M immediate / 18M locks)
-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 23.3% (15M used / 67M cache)
[OK] Key buffer size / total MyISAM indexes: 64.0M/818.3M
[OK] Read Key buffer hit rate: 99.1% (467M cached / 4M reads)
[OK] Write Key buffer hit rate: 96.5% (9M cached / 323K writes)
-------- InnoDB Metrics -----------------------------------------------------
[–] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 104.0G/14.7M
[!!] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 0.03% (2231 used/ 6815743 total)
[OK] InnoDB Read buffer efficiency: 99.85% (901879 hits/ 903259 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 3217 writes)
-------- AriaDB Metrics -----------------------------------------------------
[–] AriaDB is disabled.
-------- Replication Metrics -------------------------------------------------
[–] No replication slave(s) for this server.
[–] This is a standalone server..
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Restrict Host for user@% to user@SpecificDNSorIp
Reduce your overall MySQL memory footprint for system stability
Increasing the query_cache size over 128M may reduce performance
Adjust your join queries to always utilize indexes
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Beware that open_files_limit (100000) variable
should be greater than table_open_cache ( 49895)
Variables to adjust:
*** MySQLs maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 512M) [see warning above]
join_buffer_size (> 8.0M, or always use indexes with joins)
table_open_cache (> 49895)
innodb_buffer_pool_instances(=64)
[root@ns3049052 /]#
Edit pour Tuningprimer
[root@ns3049052 /]# ./tuning-primer.sh
– MYSQL PERFORMANCE TUNING PRIMER –
- By: Matthew Montgomery -
MySQL Version 5.5.50-MariaDB x86_64
Uptime = 2 days 21 hrs 40 min 36 sec
Avg. qps = 327
Total Questions = 82195233
Threads Connected = 1
Server has been running for over 48hrs.
It should be safe to follow these recommendations
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service
SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 1.000000 sec.
You have 611 out of 82195254 that take longer than 1.000000 sec. to complete
Your long_query_time seems to be fine
BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html
WORKER THREADS
Current thread_cache_size = 16384
Current threads_cached = 13
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 1
Historic max_used_connections = 14
The number of used connections is 14% of the configured maximum.
Your max_connections variable seems to be fine.
INNODB STATUS
Current InnoDB index space = 7 M
Current InnoDB data space = 6 M
Current InnoDB buffer pool free = 99 %
Current innodb_buffer_pool_size = 104.00 G
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory
MEMORY USAGE
Max Memory Ever Allocated : 106.69 G
Configured Max Per-thread Buffers : 15.08 G
Configured Max Global Buffers : 104.57 G
Configured Max Memory Limit : 119.66 G
Physical Memory : 125.86 G
Max memory limit exceeds 90% of physical memory
KEY BUFFER
Current MyISAM index space = 818 M
Current key_buffer_size = 64 M
Key cache miss rate is 1 : 110
Key buffer free ratio = 68 %
Your key_buffer_size seems to be fine
QUERY CACHE
Query cache is enabled
Current query_cache_size = 512 M
Current query_cache_used = 39 M
Current query_cache_limit = 3.99 G
Current Query cache Memory fill ratio = 7.65 %
Current query_cache_min_res_unit = 4 K
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL wont cache query results that are larger than query_cache_limit in size
SORT OPERATIONS
Current sort_buffer_size = 128 M
Current read_rnd_buffer_size = 16 M
Sort buffer seems to be fine
JOINS
./tuning-primer.sh: ligne 402 : export: « 2097152 » : identifiant non valable
Current join_buffer_size = 8.00 M
You have had 33455 queries where a join could not use an index properly
You have had 5 joins without keys that check for key usage after each row
join_buffer_size >= 4 M
This is not advised
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
OPEN FILES LIMIT
Current open_files_limit = 100000 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine
TABLE CACHE
Current table_open_cache = 49895 tables
Current table_definition_cache = 65536 tables
You have a total of 16880 tables
You have 16966 open tables.
The table_cache value seems to be fine
TEMP TABLES
Current max_heap_table_size = 4.00 G
Current tmp_table_size = 4.00 G
Of 4827970 temp tables, 0% were created on disk
Created disk tmp tables ratio seems fine
TABLE SCANS
Current read_buffer_size = 2 M
Current table scan ratio = 242 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 1 : 205520
Your table locking seems to be fine
Pourquoi ne pas appliquer déjà les recommandations proposées par les 2 scripts ?
Concrètement, si ton serveur SQL est fortement chargé, il peut planter le serveur puisque tu l'autorise à utiliser plus que la ram installée sur le serveur.
Il te conseille également de voir les requetes avec les Join. Soit modifier les requêtes SQL, soit modifier la structure / les indexs des tables.
Bonjour,
En effet les indications sont assez claires.
[quote]
Variables to adjust:
*** MySQLs maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 512M) [see warning above]
join_buffer_size (> 8.0M, or always use indexes with joins)
table_open_cache (> 49895)
innodb_buffer_pool_instances(=64)
Attention lorsqu'on augment le nombre de pool il faut diminuer la taille de ceux-ci en conséquence genre :
innodb_buffer_pool_size = 1G
query_cache_size = 1G (cette valeur est surtout intéressante lorsqu'on a pas de cache applicatif comme memcached ou APC)
table_open_cache = 65536
join_buffer_size = 16 ou 32M
Ensuite pense à augmenter fortement le nombre de connexion à la base de données.
Si tu as 200 utilisateurs simultanés avec une base qui ne peut avoir que 100 connexions, tu risques d'avoir des soucis, sans compter qu'un utilisateur doit générer pas mal de requêtes différentes.
Il y aura sans doute d'autres ajustements à faire pour adapter ta configuration et la rendre optimale.
Il faut jouer avec les paramètres indiqués dans les rapports pour avancer correctement.
Pour le choix entre Myisam et Innodb –> http://sql.sh/1548-mysql-innodb-myisam
Bonne soirée
https://www.captainadmin.com
Merci pour vos réponses !!!
Je vais activer les requêtes sans index déjà et travailler correctement sur tout ça.
Autre point il serait bon de voir pour avoir un cache des requêtes mysql si tu as bcp de SELECT… Surtout avec beaucoup de personnes en ligne en même temps il vaut mieux éviter d'aller chercher dans MySQL en permanence. Le cache de base serait d'avoir le résultat d'une requête SELECT dans un fichier txt. C'est plus léger pour le serveur de lire un (petit) fichier txt que d'interroger MySQL.
Bonjour,
Alors, déjà :
- Sich : Les bases de données, ça ne sert à rien, c'est bien connu ! C'est d'ailleurs pour ça qu'on a arrêté les projets Elastic Stack, PostgreSQL, MongoDB…Current innodb_buffer_pool_size = 104.00 G
Pour… 13 Mo de données ! Sérieusement…
J'y reviendrai plus tard.Current MyISAM index space = 818 M<br />Current key_buffer_size = 64 M
Là, c'est trop bas. L'idéal serait de pouvoir stocker tous les index en RAM.Current query_cache_limit = 3.99 G
Si tu sors plus de 4Go de données dans une seule requête, il faut impérativement revoir le code de l'application, le problème ne vient pas de MySQL !
ATTENTION : MySQL n'est pas fait pour stocker des fichiers binaires (dans le sens où c'est contreperformant), il est plus efficace de pointer vers un emplacement du disque dur où l'OS saura optimiser ces accès que MySQL a du mal à gérer.Current sort_buffer_size = 128 M
Ca sent les requêtes mal écrites. Il faut absolument restreindre la quantité de données retournées au strict minimum.Table cache hit rate: 2% (671 open / 28K opened)
Il y a un problème ici ! Tu n'as pas beaucoup de tables, mais le hitrate est particulièrement mauvais. T'as touché les réglages en cours à chaud ?
Une autre chose qui me surprend : à raison de 320 requêtes par secondes, tu arrives à n'avoir que 14 connexions MySQL en parallèle. Le serveur a plutôt l'air de bien s'en sortir.
Voici un rapport récent, après 6 jours :
**[root@ns3049052 /]# ./mysqltuner.pl
>> MySQLTuner 1.6.1 - Major Hayden mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '–help' for additional options and output filtering
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.50-MariaDB
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[–] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM
[–] Data in MyISAM tables: 1G (Tables: 16912)
[–] Data in InnoDB tables: 14M (Tables: 391)
[!!] Total fragmented tables: 443
-------- Security Recommendations -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User drivup@% hasnt specific host restriction.
[!!] There is no basic password file list!
-------- Performance Metrics -------------------------------------------------
[–] Up for: 6d 1h 18m 10s (207M q [396.806 qps], 5M conn, TX: 2303B, RX: 28B)
[–] Reads / Writes: 99% / 1%
[–] Binary logging is disabled
[–] Total buffers: 6.4G global + 210.5M per thread (150 max threads)
[OK] Maximum reached memory usage: 9.9G (7.85% of installed RAM)
[OK] Maximum possible memory usage: 37.2G (29.58% of installed RAM)
[OK] Slow queries: 0% (302/207M)
[OK] Highest usage of available connections: 11% (17/150)
[OK] Aborted connections: 0.11% (5629/5306784)
[OK] Query cache efficiency: 47.4% (155M cached / 328M selects)
[!!] Query cache prunes per day: 365142
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 32077
[!!] Temporary tables created on disk: 30% (3M on disk / 11M total)
[OK] Thread cache hit rate: 99% (17 created / 5M connections)
[!!] Table cache hit rate: 15% (35K open / 220K opened)
[OK] Open file limit used: 51% (51K/100K)
[OK] Table locks acquired immediately: 99% (24M immediate / 24M locks)
-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 37.6% (403M used / 1B cache)
[OK] Key buffer size / total MyISAM indexes: 1.0G/657.9M
[OK] Read Key buffer hit rate: 99.5% (1B cached / 7M reads)
[!!] Write Key buffer hit rate: 65.9% (6M cached / 2M writes)
-------- InnoDB Metrics -----------------------------------------------------
[–] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 1.0G/14.6M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 3.28% (2147 used/ 65535 total)
[OK] InnoDB Read buffer efficiency: 99.96% (3560248 hits/ 3561638 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 12038 writes)
-------- AriaDB Metrics -----------------------------------------------------
[–] AriaDB is disabled.
-------- Replication Metrics -------------------------------------------------
[–] No replication slave(s) for this server.
[–] This is a standalone server..
Je préfère le tuning-primer.sh ! 
Je dis simplement que chercher sans cesse la même réponse qui ne bouge quasiment jamais dans MySQL est plus couteux côté ressources que de stocker les petits résultats dans un fichier txt.
Quand tu as 5000 visiteurs en même temps sur le site ça fait du bien au cpu. Si il existe des solutions de cache sur les CMS c'est justement pour ne pas avoir à interroger MySQL à chaque chargement de page.
Ce qui ne veux pas dire que MySQL ne sert à rien, où ai je dis ça ? Bref, c'est hors sujet par rapport à la question de départ.
Plus généralement, c'est la construction des pages qui coûte cher en CPU / RAM (module par module, composant par composant…). D'où la compilation des pages par les différents CMS. Tu serais surpris de voir la puissance d'un MySQL réglé aux petits oignons avec des tables bien indexées !
Après, il est évident que les requêtes "stupides" plombent le serveur.
Oui, bien construire sa bdd et ses requêtes est extrêmement important. Mais pour la construction de la page PHP se débrouille très bien quand il n'a pas besoin de faire 10 requêtes SQL pour la moindre page.
Pour en revenir au sujet de base on voit effectivement qu'il y'a des caches bien trop important qui ne sont pas utilisés… Cela ne sert à rien…
Surtout que trop de cache peux aussi entrainer des problèmes sur MySQL.
Salut,
je profite du poste je pousse ma conf qui tourne sur un 128Go RAM aussi. L'instance est plutôt pour des traitements qui manipule de gros volume de table et qui dure longtemps… (d'où le `wait_timeout` affreux)…
si vous avez des avis …
mariadb 10.1
[mysqld]
datadir = /home/mysql/
tmpdir = /home/tmp/
innodb_buffer_pool_size = 48G
innodb_buffer_pool_instances = 48
query_cache_type = 0
query_cache_limit = 1G
query_cache_min_res_unit = 2k
query_cache_size = 128M
join_buffer_size = 1G
sort_buffer_size = 1G
tmp_table_size = 2G
max_heap_table_size = 2G
wait_timeout=1800
slow-query-log = 1
slow-query-log-file = /home/mysql/mysql-slow.log
long_query_time = 3
log_slow_verbosity = query_plan
#log_queries_not_using_indexes = 1
innodb-defragment = 1
#http://anothermysqldba.blogspot.fr/2013/09/mysql-optimization-tip-threadcachesize.html
thread_cache_size = 50
max_connections = 50
max_allowed_packet = 1G
skip-name-resolve
# https://www.percona.com/blog/2012/08/28/differences-between-read-committed-and-repeatable-read-transaction-isolation-levels/
transaction-isolation=READ-COMMITTED
innodb_flush_log_at_trx_commit=2
sync_binlog=1
innodb_flush_method=O_DSYNC
innodb_log_buffer_size = 2M
innodb_file_per_table=1
innodb_file_format=barracuda
#log = /var/log/mysqld.log
log-error = /var/log/mysqld_error.log
on y pense pas souvent mais le `tmpdir` par défaut est celui du système… donc si vous manipulez d'énorme `SELECT` tu peux exploser ton `tmpdir`.
Avec quelques contraintes, tu peux déplacer ton tmpdir… en RAM via un tmpfs… Si tu manipules beaucoup ton tmpdir, ça peut avoir des résultats impressionnants !innodb_buffer_pool_size = 48G<br />innodb_buffer_pool_instances = 48
Soit 48 instances de 1 Go A mon avis, c'est pas ce que tu souhaitais !join_buffer_size = 1G<br />sort_buffer_size = 1G
Ces valeurs sont atrocement élevées… Il faut manipuler des volumes colossaux de données pour en arriver là… Et à mon avis, c'est juste le signe que tes requêtes sont mal faites ou que ton schéma mérite une grosse optimisation.
En fonction de ton volume de données écrites, tu peux avoir un intérêt à modifier le innodb_log_file_size… Attention à le faire "calmement" en arrêtant proprement MySQL sous peine d'avoir de vrais problèmes de données !
J'approuve fortement le tmp_dir en ramdisk, je le fais systématiquement et le gain de perfs est souvent fort appréciable.
Ensuite concernant les optimisations elles sont souvent bcp plus efficaces sur les applis que sur la config… De bonnes requêtes bien faites et une bdd bien développée fait bien plus de miracles que le peaufinage du fichier de config (même si ce point est également important).
ouais j'ai passé des heures à reconstruire les requetes dans tous les sens et à ajouté index et/ou des infos redondantes. parfois un modèle trop carré est l'ennemis de la perf …
ouais je manipule des dizaines de millions de lignes j'ai 60Go de données sur disque hors index
j'avoue que `innodb_buffer_pool_instances` ça c'est pas clair partout ce que c'est…
par contre pour le tmpdir en tmpfs j'y ai pensé mais je vois pas l'intéret vs augmenter les valeurs dans le ficher mysql ? sauf si les valeurs sont liées à la mémoire par connexion dans ce cas c'est pas mal
Ben manifestement, il va falloir y passer encore plus de temps !
60Go de données, c'est pas non plus énorme.Uptime = 29 days 5 hrs 25 min 26 sec<br />Avg. qps = 1413<br />Total Questions = 3568645302<br />Threads Connected = 35
Et pourtant : SORT OPERATIONS<br />Current sort_buffer_size = 2 M<br />Current read_rnd_buffer_size = 256 K<br /><br />JOINS<br />Current join_buffer_size = 1.00 M<br />You have had 871851 queries where a join could not use an index properly<br />You should enable "log-queries-not-using-indexes"
sur une base d'environ 40Go.
Quant au tmpdir, il a un très gros avantage. Certaines requêtes sont nécessairement manipulées dans le tmpdir… Le mettre en RAM revient à gruger MySQL quant à sa gestion de la performance.
(attention, l'utilisation de tmpfs a des contraintes dans le cas d'une réplication)
ok merci pour les conseils !