Suite crm very slow

đź’¬ General Discussion
1

Hi to all, i have a strange problem with my suitecrm 8version 7.9.17 sugar 6.5.25).
With some accounts, and just for them, it is so slow. Like 11 sec to load the dashboard, and 7-8 to surf in other pages. I tried to repair and rebuild, clear cache and dashlet, but nothing happened. Is there a way to figure out why for that accounts he became that slow?

Thanks to all

2

Check if you have overgrown tables in the database, that is usually the culprit.

Run the query at the start of this post:

https://pgorod.github.io/Database-tables-size/

1 Like
3

Thanks but i’m using SQLserver and not mysql. So i have to try a different way

4

[quote=“GianlucaSedoc” post=80118]

here my disk usage list

5

You can clean up job_queue (all records that are older than a month, for example, and have a status of “done”)

You can check if security_records can be cleaned up, removing orphaned records: the ones that mention records that no longer exist in SuiteCRM. You’ll need to carefully build a clever SQL query for this.

Apart from these efforts, if you run a “tail -f suitecrm.log” while you are using the screens, you can probably see what it is doing during the big delays. Try searching for an equivalent in Windows, t’s basically a way to watch your log growing “live”.

1 Like
6

For “clean” you mean delete or update to deleted=1 the records?

7

I mean delete them completely, since that is what will make the table lighter, and the SELECTs quicker.

But of course - these are destructive commands, you must have backups, test it, and make sure you’re not destroying your database…

the job_queue table is lower risk, the rest you need to be more careful.

If you can first find exactly which one is causing your trouble, you can control your risks better.

1 Like
8

Ok done, and the users affected with this issue have theyr navigation smoother. But i see there are more table with a lot of information, ie accounts_audit. Is there a way to “store” in somewhere else this information? or i need to create a script to do that?

Thanks

9

I don’t know of any additional processes around audit tables, you’ll have to do things yourself.

If you don’t need that information to accumulate, you can turn it off for a complete module, or you can turn it off for specific fields. If you do need it and it generates a lot of rows, you will probably want some policy to expire that information and you can write a custom Scheduler to clean up stuff older than X months, for example.

Again - orphaned records are frequent in SuiteCRM (removing them can’t be automatic, it has to be a decision taken by the organization that understands its own data), this is always a good place to start. If the audit is about a record that has been deleted, it’s just taking up space in the database, for no good reason.

1 Like
10

ok thanks a lot for the informations. you halped a lot

11

So, after digging a lot in my db and testing i’ve found some “bugs” in the executionplan of the query that uses the table security_groups_record.
first problem: the type of the columns.

In sql server the best way to use strings is the nvarchar type. Everytime the crm queries the db it CONVERT_IMPLICIT every “id” in the table in nvarchar format. So, i’ve renamed securitygroup and securitygroups_records in securitygroup_old and securitygroups_records, then i launch this 4 queries:




CREATE TABLE [dbo].[securitygroups_records](
	[id] [char](36) NOT NULL,
	[securitygroup_id] [nvarchar](36) NULL,
	[record_id] [nvarchar](36) NULL,
	[module] [nchar](36) NULL,
	[date_modified] [datetime] NULL,
	[modified_user_id] [nvarchar](36) NULL,
	[created_by] [nvarchar](36) NULL,
	[deleted] [bit] NULL,
 CONSTRAINT [securitygroups_recordspk] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[securitygroups_records] ADD  DEFAULT ('0') FOR [deleted]
GO


INSERT INTO securitygroups_records SELECT * from ssecuritygroups_records_old


CREATE TABLE [dbo].[securitygroups](
	[id] [nvarchar](36) NOT NULL,
	[name] [nvarchar](255) NULL,
	[date_entered] [datetime] NULL,
	[date_modified] [datetime] NULL,
	[modified_user_id] [varchar](36) NULL,
	[created_by] [varchar](36) NULL,
	[description] [nvarchar](max) NULL,
	[deleted] [bit] NULL,
	[assigned_user_id] [varchar](36) NULL,
	[noninheritable] [bit] NULL,
 CONSTRAINT [securitygroupspk] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[securitygroups] ADD  DEFAULT ('0') FOR [deleted]
GO


INSERT INTO securitygroups SELECT * from securitygroups_old

this 4 queries recreate the 2 tables with the right datatype format and this increased the speed of the query by 3-4 seconds.

the second problem are the indexes:
The query that uses securitygroups_records uses everytime securitygroup_id, record_id, module, deleted but there arent indexes linked at them, so i’ve made the missing indexes NONCLUSTERED



CREATE NONCLUSTERED INDEX securitygrouprecord_recordid   
    ON securitygroups_records (record_id);   
GO  


CREATE NONCLUSTERED INDEX securitygrouprecord_module   
    ON securitygroups_records (module);   
GO  




CREATE NONCLUSTERED INDEX securitygrouprecord_deleted  
    ON securitygroups_records (deleted);   
GO

this increased the speed of the query by 6-7 second. with a total decrease of the script from 12 sec to 4 with a table of 210000 records.

I wish this will help to provide a good support of the sql server version.

Reguards

1 Like
12

Nice fix @GianlucaSedoc These table commands for MSSQLServer DB should be added as a PR on github, if not already added. Anyone interested?