topic Re: Alfresco database Use huge CPU 90% in Alfresco Archive

Alfresco database Use huge CPU 90%

rafter — Wed, 11 Jun 2014 09:45:04 GMT

Hi,i'll quickly describe you, my architecture:i've 2 servers :1-alfresco server ( tomcat )2-sqlserver 2008 r2 database server where ive created my alfresco database and others like bonita …when i monitor the database server, ifound the sql query that use 90% of the CPU.so the consequence that other

Re: Alfresco database Use huge CPU 90%

kaynezhang — Thu, 12 Jun 2014 06:13:45 GMT

This sql seems to be used by solr indexing.
Solr server talks to alfresco and get transactions that have been committed during begin and end time and then updates the indexes.

Re: Alfresco database Use huge CPU 90%

rafter — Thu, 12 Jun 2014 10:12:55 GMT

Hi,

thank you for replying.

So, how can i resolve this huge use of CPU.

have a nice day

Re: Alfresco database Use huge CPU 90%

kaynezhang — Fri, 13 Jun 2014 05:55:32 GMT

I'm sorry I am not a SQLServer DBA.
Then ony recommendation I can give you is :
Check the columns used in JOIN or WHERE clauses in the query, ensure there are indexes built on these columns.

Re: Alfresco database Use huge CPU 90%

muralidharand — Fri, 13 Jun 2014 06:57:30 GMT

Hi,
You can use sql server query execution plan, to get more information.
If you want to find out missing index, you can try the below link.

http://blog.sqlauthority.com/2011/01/03/sql-server-2008-missing-index-script-download/

Thanks,
Murali

Re: Alfresco database Use huge CPU 90%

mrogers — Sat, 14 Jun 2014 08:24:11 GMT

What version of alfresco are you using? Community does not support SQL server so if you are using enterprise then contact alfresco support.

Sounds like you are missing an index or SQL server has decided not to use it.

Re: Alfresco database Use huge CPU 90%

rafter — Mon, 16 Jun 2014 15:24:54 GMT

hi,

thank you for your replay.

my alfresco version is : 4.1.2
sqlserver : standard edition 2008 R2
i've created some indexes in alfresco database on those tables :
CREATE INDEX [Missing_IXNC_alf_node_type_qname_id_965FB] ON [alfresco].[dbo].[alf_node] ([type_qname_id]) INCLUDE ([transaction_id])
CREATE INDEX [Missing_IXNC_alf_node_type_qname_id_E5F15] ON [alfresco].[dbo].[alf_node] ([type_qname_id]) INCLUDE ([transaction_id])
CREATE INDEX [Missing_IXNC_alf_child_assoc_parent_node_id_CB96A] ON [alfresco].[dbo].[alf_child_assoc] ([parent_node_id]) INCLUDE ([id], [type_qname_id], [child_node_name_crc], [child_node_name], [child_node_id], [qname_ns_id], [qname_localname], [is_primary], [assoc_index])

but, ive alway a huge usage of CPU on my Database base server.

Re: Alfresco database Use huge CPU 90%

mrogers — Mon, 16 Jun 2014 15:41:00 GMT

If you are using 4.1.2 then call alfresco support before attempting to fix it yourself.

Did you create those indexes before or after you had problems?

Re: Alfresco database Use huge CPU 90%

kaynezhang — Mon, 16 Jun 2014 15:47:42 GMT

Have you tried to create an index on "commit_time_ms "column of table "alf_transaction "?

Re: Alfresco database Use huge CPU 90%

rafter — Mon, 16 Jun 2014 15:53:04 GMT

i've create them after the problem .

Re: Alfresco database Use huge CPU 90%

rafter — Mon, 16 Jun 2014 16:00:29 GMT

hi,

when i execut this Missing index sql script, the result dont tell me to create this index :

– Missing Index Script
– Original Author: Pinal Dave (C) 2011
SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

Re: Alfresco database Use huge CPU 90%

kaynezhang — Tue, 17 Jun 2014 02:32:35 GMT

In your cpu consuming sql column "commit_time_ms" is used as where condition,you should build index on this column

Re: Alfresco database Use huge CPU 90%

rafter — Tue, 17 Jun 2014 08:24:25 GMT

hi,

I've created this index like this :

CREATE INDEX [Missing_IXNC_alf_transaction] ON [alfresco].[dbo].[alf_transaction] ([commit_time_ms])

but in the sql execution plan of the request i see that the estimated operator ( 1% ) and CPU (0.0048) Cost are so low.
but for this index : CREATE INDEX [Missing_IXNC_alf_node_type_qname_id_E5F15] ON [alfresco].[dbo].[alf_node] ([type_qname_id]) INCLUDE ([transaction_id]) , the estimated operator ( 28% ) and CPU ( 0.67 ) Cost are so important.

NB : the avg UC use of this request is between 450 and 1500 ms/s.

Re: Alfresco database Use huge CPU 90%

kaynezhang — Tue, 17 Jun 2014 15:02:04 GMT

Then what do you want to ask?

Re: Alfresco database Use huge CPU 90%

rafter — Tue, 17 Jun 2014 15:13:54 GMT

i have always the same problem ( huge usage of cpu by alfresco ).

how can i resolv this problem and thank you