Mysql : Improve performance

[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

[mrogers]

Alfresco always needs the db, even when there are no users.

The other crucial thing to chech is the size of your connection pool,  too big wastes resources, too small delays work.

[dranakan]

Thank you.

@AFaust :

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.

Yes, the tables are already in innodb. I use the "alter table…" to run a "optimize table…".
For innodb, OPTIMIZE TABLE is mapped to ALTER TABLE ("Table does not support optimize, doing recreate + analyze instead", see http://dev.mysql.com/doc/refman/5.5/en/optimize-table.html).

Do you never run a analyse table + optimize table ? (analyse is done with "mysqlcheck -a").

[afaust]

I must admit, I never encountered the necessity to do that - even on larger databases. But then again, I am not a DBA and customer DBAs may have done that in those rare cases that a customer actually uses MySQL for production.

[dranakan]

If a user delete a workflow, it takes a lot of time (more 14 seconds). I have log slow query and found that an access to act_hi_detail is very slow. This is the slow query :

mysql> select *      from ACT_HI_DETAIL HD      WHERE  HD.TASK_ID_ = '5972887';

Empty set (14.03 sec)
‍‍‍‍‍

(The act_hi_detail has 4'000'000 rows)

If I add an index on TASK_ID_, the query is very fast ! (0.01 sec).

create index ACT_IDX_SYS_HI_DETAIL_TASK_ID on act_hi_detail (task_id_);
‍‍‍

Can I safety add custom index on Alfresco tables ?

[antoniosoler]

Adding a new index wont harm your environment "per se" but you need to keep those changes in mind if you upgrade the Alfresco instance to a newer version, during this process during the "schema upgrade step" we drop, alter and create tables and an unexpected index may generate problems.