{"id":107,"date":"2011-07-19T06:37:36","date_gmt":"2011-07-19T04:37:36","guid":{"rendered":"http:\/\/www.extradrm.com\/?p=107"},"modified":"2020-10-09T22:21:23","modified_gmt":"2020-10-09T20:21:23","slug":"configuration-mysql-par-defaut-et-grosses-bases","status":"publish","type":"post","link":"https:\/\/www.extradrm.com\/?p=107","title":{"rendered":"Configuration mySQL par d\u00e9faut et grosses bases"},"content":{"rendered":"<div>La configuration par d\u00e9faut de mySQL stocke toutes les tables innoDB de toutes les bases mySQL dans un seul fichier linux (\/var\/lib\/mysql\/ibdata1 sur debian)<br \/>\nLorsque vous avez une grosse base de donn\u00e9es (ou plusieurs), ce fichier devient \u00e9norme.<\/p>\n<p>Ce que vous devez \u00e9galement savoir, c&#8217;est que le moteur innoDB, lorsque vous supprimez une ligne (ou une colonne ou une table), lib\u00e8re en interne l&#8217;espace, mais ne lib\u00e8re rien sur le fichier ibdata1. Donc il ne fait que grossir.<\/p>\n<p>Si votre base de donn\u00e9es est importante, ou que vous en avez plusieurs, vous pouvez rencontrer d&#8217;\u00e9normes chutes de performances de mani\u00e8re al\u00e9atoire.<\/p>\n<p>La solution est dans la documentation de mySQL <a title=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/innodb-data-log-reconfiguration.html\" href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/innodb-data-log-reconfiguration.html\">http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/innodb-data-log-reconfiguration.html<\/a>, la partie la plus int\u00e9ressante de cete page \u00e9tant :<\/p>\n<ul>\n<li>Use mysqldump to dump all your InnoDB tables.<\/li>\n<li>Stop the server.<\/li>\n<li>Remove all the existing tablespace files, including the ibdata and ib_log files. If you want to keep a backup copy of the information, then copy all the ib* files to another location before the removing the files in your MySQL installation.<\/li>\n<li>Remove any .frm files for InnoDB tables.<\/li>\n<li>Configure a new tablespace.<\/li>\n<li>Restart the server.<\/li>\n<li>Import the dump files.<\/li>\n<\/ul>\n<p>Cela r\u00e9soud le probl\u00e8me, mais il va r\u00e9appara\u00eetre plus tard.<\/p>\n<p>Voici donc deux suggestions compl\u00e9mentaires :<\/p>\n<ul>\n<ul>\n<li>Ne pas utiliser un seul fichier pour tout stocker. La page <a title=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/innodb-multiple-tablespaces.html\" href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/innodb-multiple-tablespaces.html\">http:\/\/dev.mysql.com\/doc\/refman\/5.1\/en\/innodb-multiple-tablespaces.html<\/a> explique comment faire. Voici le fichier de configuration mySQL complet :<br \/>\n<code><br \/>\n# For explanations see<br \/>\n# <a title=\"http:\/\/dev.mysql.com\/doc\/mysql\/en\/server-system-variables.html\" href=\"http:\/\/dev.mysql.com\/doc\/mysql\/en\/server-system-variables.html\">http:\/\/dev.mysql.com\/doc\/mysql\/en\/server-system-variables.html<\/a><br \/>\n[mysqld]<br \/>\n#<br \/>\n# * Basic Settings<br \/>\n#<br \/>\n#<br \/>\n# * Fine Tuning<br \/>\n#<br \/>\nkey_buffer = 2000M<br \/>\nmax_allowed_packet = 16M<br \/>\nthread_stack = 128K<br \/>\nthread_cache_size = 8<br \/>\n# This replaces the startup script and checks MyISAM tables if needed<br \/>\n# the first time they are touched<br \/>\nmyisam-recover = BACKUP<br \/>\nmax_connections = 500<br \/>\nmax_user_connections = 500<br \/>\n#table_cache = 64<br \/>\nthread_concurrency = 300<br \/>\ntable_cache = 30000<br \/>\nopen_files_limit = 65000 <\/code>#<br \/>\n# * Query Cache Configuration<br \/>\n#<br \/>\nquery_cache_limit = 50M<br \/>\nquery_cache_size = 300M<br \/>\n# * InnoDB<br \/>\n#<br \/>\n# InnoDB is enabled by default with a 10MB datafile in \/var\/lib\/mysql\/.<br \/>\n# Read the manual for more InnoDB related options. There are many!<br \/>\n# You might want to disable InnoDB to shrink the mysqld process by circa 100MB.<br \/>\n#skip-innodb<br \/>\ninnodb_buffer_pool_size=16777216<br \/>\ninnodb_file_per_table<\/li>\n<li>Modifier certaines tables pour qu&#8217;elles soient en myISAM : la table des sessions par exemple, est utilis\u00e9e uniquement pour l&#8217;authentification. Elle grossig beaucoup (une nouvelle session est cr\u00e9\u00e9e pour chaque utilisateur anonyme de l&#8217;OPAC !). innoDB pr\u00e9sente l&#8217;avantage de g\u00e9rer les contraintes d&#8217;int\u00e9grit\u00e9 (vous ne pouvez pas avoir un exemplaire sans sa notice biblio par exemple). MAIS : la table session n&#8217;a pas de contrainte d&#8217;int\u00e9grit\u00e9 ! Il est donc tout \u00e0 fait possible de la mettre en myISAM ! Ajoutez un &#8220;truncate session&#8221; toutes les semaines pour la re-vider, et vous aurez une bonne combinaison optimisant Koha\/mySQL. Voici notre script qui transforme quelques tables en myISAM :<br \/>\n<code><br \/>\n#!\/usr\/bin\/perl<br \/>\nuse strict;<br \/>\nuse warnings;<br \/>\nuse DBI;<br \/>\nmy $debug=$ENV{DEBUG};<br \/>\nmy $db_user=$ARGV[0] || \"koha\";<br \/>\nmy $db_passwd=$ARGV[1] || \"your password here\"; <\/code>my $db=DBI-&gt;connect(&#8220;DBI:mysql:mysql&#8221;, $db_user, $db_passwd);<br \/>\nmy $dbquery=$db-&gt;prepare(qq{show databases});<br \/>\n$dbquery-&gt;execute;<br \/>\nwhile (my $dbname=$dbquery-&gt;fetchrow){<br \/>\nnext unless $dbname=~\/^koha\/;<br \/>\n$debug &amp;&amp; warn $dbname;<br \/>\n$db-&gt;do(&#8220;use $dbname&#8221;);<br \/>\nmy $tablequery=$db-&gt;prepare(qq{show tables});<br \/>\n$tablequery-&gt;execute;<br \/>\nwhile (my $tablename=$tablequery-&gt;fetchrow){<br \/>\n$debug &amp;&amp; warn &#8221; $tablename&#8221;;<br \/>\nif ($tablename=~\/sessions|zebraqueue|temp_upg_biblioitems|pending_offline_operations\/)<br \/>\n{<br \/>\n$db-&gt;do(qq{ALTER TABLE $tablename engine=myisam});<br \/>\n}<br \/>\nelse {<br \/>\n$db-&gt;do(qq{ALTER TABLE $tablename engine=innodb});<br \/>\n}<br \/>\n}<br \/>\n}<\/li>\n<\/ul>\n<\/ul>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>La configuration par d\u00e9faut de mySQL stocke toutes les tables innoDB de toutes les bases mySQL dans un seul fichier linux (\/var\/lib\/mysql\/ibdata1 sur debian) Lorsque vous avez une grosse base de donn\u00e9es (ou plusieurs),&#46;&#46;&#46;<\/p>\n","protected":false},"author":1,"featured_media":2845,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[35,25],"tags":[],"youtube_video":null,"_links":{"self":[{"href":"https:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/posts\/107"}],"collection":[{"href":"https:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.extradrm.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=107"}],"version-history":[{"count":0,"href":"https:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/posts\/107\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.extradrm.com\/index.php?rest_route=\/wp\/v2\/media\/2845"}],"wp:attachment":[{"href":"https:\/\/www.extradrm.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=107"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.extradrm.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=107"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.extradrm.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=107"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}