AnsweredAssumed Answered

Mysql : Augmenter les performances

Question asked by dranakan on Feb 15, 2013
Latest reply on Feb 15, 2013 by rguinot
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-upgrades/mysql-improve#

Outcomes