Mysql : Augmenter les performances

[dranakan]

Hello,

J'aimerais améliorer les performances de Mysql avec Alfresco. Je constate sur certains serveurs qu'aux heures où beaucoup d'utilisateurs travaillent la CPU est très sollicité par Mysql.
J'ai amélioré au maximum la configuration en m'aidant d'un analyseur (https://launchpad.net/mysql-tuning-primer).
La prochaine étape est de lancer les commandes "analyse" et "optimize". Il pourrait être intéressant de le lancer chaque mois (ou semaine). Je n'ai trouvé aucune information à ces commandes sur les forum Alfresco. C'est pourquoi je préfère une confirmation avant de faire ceci…


Puis-je lancer ces commandes sans risques pour Alfresco ? (cela génère des locks… ne pas le faire à n'importe quel moment…)

mysqlcheck -c alfresco -u root -p
mysqlcheck -a alfresco -u root -p

mysql>
Alter Table `alfresco`.`act_ge_bytearray` ENGINE = InnoDB;                  
Alter Table `alfresco`.`act_ge_property` ENGINE = InnoDB;                   
Alter Table `alfresco`.`act_hi_actinst` ENGINE = InnoDB;                    
Alter Table `alfresco`.`act_hi_attachment` ENGINE = InnoDB;                 
Alter Table `alfresco`.`act_hi_comment` ENGINE = InnoDB;                    
Alter Table `alfresco`.`act_hi_detail` ENGINE = InnoDB;                     
Alter Table `alfresco`.`act_hi_procinst` ENGINE = InnoDB;                   
Alter Table `alfresco`.`act_hi_taskinst` ENGINE = InnoDB;                   
Alter Table `alfresco`.`act_id_group` ENGINE = InnoDB;                      
Alter Table `alfresco`.`act_id_info` ENGINE = InnoDB;                       
Alter Table `alfresco`.`act_id_membership` ENGINE = InnoDB;                 
Alter Table `alfresco`.`act_id_user` ENGINE = InnoDB;                       
Alter Table `alfresco`.`act_re_deployment` ENGINE = InnoDB;                 
Alter Table `alfresco`.`act_re_procdef` ENGINE = InnoDB;                    
Alter Table `alfresco`.`act_ru_execution` ENGINE = InnoDB;                  
Alter Table `alfresco`.`act_ru_identitylink` ENGINE = InnoDB;               
Alter Table `alfresco`.`act_ru_job` ENGINE = InnoDB;                        
Alter Table `alfresco`.`act_ru_task` ENGINE = InnoDB;                       
Alter Table `alfresco`.`act_ru_variable` ENGINE = InnoDB;                   
Alter Table `alfresco`.`alf_access_control_entry` ENGINE = InnoDB;          
Alter Table `alfresco`.`alf_access_control_list` ENGINE = InnoDB;           
Alter Table `alfresco`.`alf_ace_context` ENGINE = InnoDB;                   
Alter Table `alfresco`.`alf_acl_change_set` ENGINE = InnoDB;                
Alter Table `alfresco`.`alf_acl_member` ENGINE = InnoDB;                    
Alter Table `alfresco`.`alf_activity_feed` ENGINE = InnoDB;                 
Alter Table `alfresco`.`alf_activity_feed_control` ENGINE = InnoDB;         
Alter Table `alfresco`.`alf_activity_post` ENGINE = InnoDB;                 
Alter Table `alfresco`.`alf_applied_patch` ENGINE = InnoDB;                 
Alter Table `alfresco`.`alf_audit_app` ENGINE = InnoDB;                     
Alter Table `alfresco`.`alf_audit_config` ENGINE = InnoDB;                  
Alter Table `alfresco`.`alf_audit_date` ENGINE = InnoDB;                    
Alter Table `alfresco`.`alf_audit_entry` ENGINE = InnoDB;                   
Alter Table `alfresco`.`alf_audit_fact` ENGINE = InnoDB;                    
Alter Table `alfresco`.`alf_audit_model` ENGINE = InnoDB;                   
Alter Table `alfresco`.`alf_audit_source` ENGINE = InnoDB;                  
Alter Table `alfresco`.`alf_authority` ENGINE = InnoDB;                     
Alter Table `alfresco`.`alf_authority_alias` ENGINE = InnoDB;               
Alter Table `alfresco`.`alf_child_assoc` ENGINE = InnoDB;                   
Alter Table `alfresco`.`alf_content_data` ENGINE = InnoDB;                  
Alter Table `alfresco`.`alf_content_url` ENGINE = InnoDB;                   
Alter Table `alfresco`.`alf_encoding` ENGINE = InnoDB;                      
Alter Table `alfresco`.`alf_locale` ENGINE = InnoDB;                        
Alter Table `alfresco`.`alf_lock` ENGINE = InnoDB;                          
Alter Table `alfresco`.`alf_lock_resource` ENGINE = InnoDB;                 
Alter Table `alfresco`.`alf_mimetype` ENGINE = InnoDB;                      
Alter Table `alfresco`.`alf_namespace` ENGINE = InnoDB;                     
Alter Table `alfresco`.`alf_node` ENGINE = InnoDB;                          
Alter Table `alfresco`.`alf_node_aspects` ENGINE = InnoDB;                  
Alter Table `alfresco`.`alf_node_assoc` ENGINE = InnoDB;                    
Alter Table `alfresco`.`alf_node_properties` ENGINE = InnoDB;               
Alter Table `alfresco`.`alf_permission` ENGINE = InnoDB;                    
Alter Table `alfresco`.`alf_prop_class` ENGINE = InnoDB;                    
Alter Table `alfresco`.`alf_prop_date_value` ENGINE = InnoDB;               
Alter Table `alfresco`.`alf_prop_double_value` ENGINE = InnoDB;             
Alter Table `alfresco`.`alf_prop_link` ENGINE = InnoDB;                     
Alter Table `alfresco`.`alf_prop_root` ENGINE = InnoDB;                     
Alter Table `alfresco`.`alf_prop_serializable_value` ENGINE = InnoDB;       
Alter Table `alfresco`.`alf_prop_string_value` ENGINE = InnoDB;             
Alter Table `alfresco`.`alf_prop_unique_ctx` ENGINE = InnoDB;               
Alter Table `alfresco`.`alf_prop_value` ENGINE = InnoDB;                    
Alter Table `alfresco`.`alf_qname` ENGINE = InnoDB;                         
Alter Table `alfresco`.`alf_server` ENGINE = InnoDB;                        
Alter Table `alfresco`.`alf_store` ENGINE = InnoDB;                         
Alter Table `alfresco`.`alf_subscriptions` ENGINE = InnoDB;                 
Alter Table `alfresco`.`alf_transaction` ENGINE = InnoDB;                   
Alter Table `alfresco`.`alf_usage_delta` ENGINE = InnoDB;                   
Alter Table `alfresco`.`avm_aspects` ENGINE = InnoDB;                       
Alter Table `alfresco`.`avm_child_entries` ENGINE = InnoDB;                 
Alter Table `alfresco`.`avm_history_links` ENGINE = InnoDB;                 
Alter Table `alfresco`.`avm_merge_links` ENGINE = InnoDB;                   
Alter Table `alfresco`.`avm_node_properties` ENGINE = InnoDB;               
Alter Table `alfresco`.`avm_nodes` ENGINE = InnoDB;                         
Alter Table `alfresco`.`avm_store_properties` ENGINE = InnoDB;              
Alter Table `alfresco`.`avm_stores` ENGINE = InnoDB;                        
Alter Table `alfresco`.`avm_version_layered_node_entry` ENGINE = InnoDB;    
Alter Table `alfresco`.`avm_version_roots` ENGINE = InnoDB;                 
Alter Table `alfresco`.`jbpm_action` ENGINE = InnoDB;                       
Alter Table `alfresco`.`jbpm_bytearray` ENGINE = InnoDB;                    
Alter Table `alfresco`.`jbpm_byteblock` ENGINE = InnoDB;                    
Alter Table `alfresco`.`jbpm_comment` ENGINE = InnoDB;                      
Alter Table `alfresco`.`jbpm_decisionconditions` ENGINE = InnoDB;           
Alter Table `alfresco`.`jbpm_delegation` ENGINE = InnoDB;                   
Alter Table `alfresco`.`jbpm_event` ENGINE = InnoDB;                        
Alter Table `alfresco`.`jbpm_exceptionhandler` ENGINE = InnoDB;             
Alter Table `alfresco`.`jbpm_job` ENGINE = InnoDB;                          
Alter Table `alfresco`.`jbpm_log` ENGINE = InnoDB;                          
Alter Table `alfresco`.`jbpm_moduledefinition` ENGINE = InnoDB;             
Alter Table `alfresco`.`jbpm_moduleinstance` ENGINE = InnoDB;               
Alter Table `alfresco`.`jbpm_node` ENGINE = InnoDB;                         
Alter Table `alfresco`.`jbpm_pooledactor` ENGINE = InnoDB;                  
Alter Table `alfresco`.`jbpm_processdefinition` ENGINE = InnoDB;            
Alter Table `alfresco`.`jbpm_processinstance` ENGINE = InnoDB;              
Alter Table `alfresco`.`jbpm_runtimeaction` ENGINE = InnoDB;                
Alter Table `alfresco`.`jbpm_swimlane` ENGINE = InnoDB;                     
Alter Table `alfresco`.`jbpm_swimlaneinstance` ENGINE = InnoDB;             
Alter Table `alfresco`.`jbpm_task` ENGINE = InnoDB;                         
Alter Table `alfresco`.`jbpm_taskactorpool` ENGINE = InnoDB;                
Alter Table `alfresco`.`jbpm_taskcontroller` ENGINE = InnoDB;               
Alter Table `alfresco`.`jbpm_taskinstance` ENGINE = InnoDB;                 
Alter Table `alfresco`.`jbpm_token` ENGINE = InnoDB;                        
Alter Table `alfresco`.`jbpm_tokenvariablemap` ENGINE = InnoDB;             
Alter Table `alfresco`.`jbpm_transition` ENGINE = InnoDB;                   
Alter Table `alfresco`.`jbpm_variableaccess` ENGINE = InnoDB;               
Alter Table `alfresco`.`jbpm_variableinstance` ENGINE = InnoDB;
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Commande exécutée pour générer les lignes ci-dessus :

select CONCAT("Alter Table `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` ENGINE = InnoDB;")  from information_schema.tables where engine = 'InnoDb' and table_schema='alfresco';
‍‍

Merci
(Alfresco 4.0D, Mysql 5.5, RHEL 5.6)
(See also https://launchpad.net/mysql-tuning-primer)

Ce post est une copie de celui-ci sur le forum anglais : http://forums.alfresco.com/forum/installation-upgrades-configuration-integration/installation-upgrad...

[rguinot]

Cette commande va forcer InnoDB a réorganiser les tables suivant l'ordre des clés primaires, ce qui peut être utile si vous avez de la fragmentation due au fait que vous avez exporté puis rechargé les tables.

(http://www.mysqlperformanceblog.com/2009/11/05/innodb-look-after-fragmentation/).

Cependant, il serait plus utile de déterminer si la configuration de la base (my.cnf) , et votre infrastructure , sont adaptés à vos besoins.

Ces liens basiques peuvent vous être utiles : http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/
http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/


Je vous recommande également de l'outillage pour déterminer les performances de vos requêtes et de votre base de données, voir par exemple :
www.mysqlperformanceblog.com/2009/07/01/gathering-queries-from-a-server-with-maatkit-and-tcpdump/