topic Re: Mysql : Improve performance in Alfresco Archive

Mysql : Improve performance

dranakan — Fri, 11 Jan 2013 14:05:04 GMT

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 command

Re: Mysql : Improve performance

afaust — Sun, 13 Jan 2013 11:30:38 GMT

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

Re: Mysql : Improve performance

mrogers — Sun, 13 Jan 2013 16:42:33 GMT

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.

Re: Mysql : Improve performance

dranakan — Mon, 14 Jan 2013 07:37:51 GMT

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").

Re: Mysql : Improve performance

afaust — Mon, 14 Jan 2013 21:46:07 GMT

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.

Re: Mysql : Improve performance

dranakan — Mon, 22 Jul 2013 11:12:00 GMT

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 ?

Re: Mysql : Improve performance

antoniosoler — Wed, 24 Jul 2013 10:28:00 GMT

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.