Mysql : Improve performance

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

dranakan

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)

afaust

Hello,

Your database tables should already be using the InnoDB driver - any other setup on MySQL is not supported. Thus your commands should not change anything.

Whats most important with MySQL is optimizing your my.cnf according to your hardware setup. Most important is the amount of buffer memory that you assign to the database process, as this can save a lot of costly synchronous IO. Depending on how many concurrent users you have, there may be other options.

Using MySQL 5.5 and large sets of data, I've also found it useful to optimize the table file formats, i.e. use file-per-table and the advanced storage structures available with the barracuda file formats (e.g. compressed index and data segments).

Generally, if no user is accessing Alfresco,you can restart MySQL without restarting Alfresco. You may though end up with errors in scheduled processes / actions which Alfresco itself may kick off when the database is down.

Regards
Axel