AnsweredAssumed Answered

Mysql : Improve performance

Question asked by dranakan on Jan 11, 2013
Latest reply on Jul 24, 2013 by antoniosoler
Hi,

I'am trying to optimize the database (Mysql).
I think it could be good for the system to analyse and optimize monthly (or weekly) the database. I am suprise to have found nothing on the forum about it (Alfresco and Mysql). That's why I prefer a confirmation before doing it…

Can I run theses commands without risk for Alfresco ? (the command makes lock… don't do it when users are working…)


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;

I am also tuning Mysql and need to change some values (in my.cnf). Can we restart mysql without stopping Alfresco (when no user are connected) ?

Command alter table can be create with :
select CONCAT("Alter Table `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` ENGINE = InnoDB;")  from information_schema.tables where engine = 'InnoDb' and table_schema='alfresco';


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

Outcomes