Delete 50 records takes 45 seconds

đź’¬ General Discussion
1

Hi,
I’m facing speed issues on SuiteCRM 7.9.7, Sugar 6.5.25. When I delete 50 records it takes 45 second to finish the job. On database SuiteCRM fires about 1.000 queries and CPU is fully consumed by php-cgi. The table which is beeing used for this operation has about 3.000 records. Whole SuiteCRM has 18.500 records in all tables.

Server config:
Debian 8
1 core + 4GB
SSD
MariaDB
PHP 7.1.6 with OPCache

I don’t think that operation like that should take 45 second. I tried changing setting in mysql and php but nothing really helped. In SuiteCRM I disabled developer mode, logs to fatal, no IP check, no memory usage log. That helped a little bit, but not much.

Is there anything I can do or is this normal for SuiteCRM?

Thank you

2

It is possible, if your hardware is not very special, although it does sound too long for my taste.

If you can know what those 1000 queires are, that would help. The first thing I would examine are workflows and logic_hooks. If you have additional code running when these records are changed, it can get messy.

I would put the logs up to DEBUG level and analyze a singly delete to see what it does, and where it spends time.

You can also try some generic database maintenance and clean-up. When you say you have 3000 records in the table, is that seen from within the app, or from the database? You could be in need of pruning deleted records.

This article will give you many clues:

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

3

It is on hosted VPS. The provider has Intel Xeon E5-2650Lv2 1,70 GHz.

There is no additional coding. Righ out of the box.

3.000 records is from phpmyadmin. Half of the records are those with deleted = 0. Automatic pruning deleted records is disabled from begining.

Log from delete action has 4 - 5 thousand of records, so I’m really not sure what should I look for. Many times I see:
Hook called: Users::before_relationship_delete
Hook called: Users::after_relationship_delete
Warning: Multiple links found for relationship

This one takes about sec (another record in log file mostly have another second and the record below)
Creating new instance of hook class AOD_LogicHooks without parameters (called 50 times)

4

You could try disabling AOD just to see if it is the cause of the delay. You will lose the Global Search function.

Which record exactly are you deleting? From which module? You could also examine the related records tables.

5

I can see that in Admin -> Global search (different language) there are all modules in forbidden. No a single one is permited, so I guest we can do that. How to disable AOD?

I’m sorry. I find out out that the module is probably some custom made. The module has one to many relation to another module. Basically worker (main module) -> factory (related module). I’m deleting 50 workers. Related table has about 1.400 records (just one row marked as deleted = 1)

6

If want to disable AOD search, you can do so in admin->AOD Settings and you can just use the basic search.

If it’s a custom module I’m more suspicious that there could be some extra code running.

  1. Check your Workflows for those two modules

  2. Check for code under custom/modules/Worker and custom/modules/Factory, especially logic hooks.

  3. Check for code under custom/Extension/modules

7

I disabled AOD search but without any performance benefit.

By cheching code you mean Admin -> module builder (3. option in Developer options)?

“1. Check your Workflows for those two modules”
I’m not sure what to do. I can’t find any relavant data for this.

“2. Check for code under custom/modules/Worker and custom/modules/Factory, especially logic hooks.”
No logic hooks found in appropriate folders (just Ext (Language, Layoutdefs, Vardefs), language and metadata). Others modules have hooks and event in custom/modules/ there is logic_hooks.php

  1. “Check for code under custom/Extension/modules”
    What should I look for? I see just “form” definitions in .php files.
8

You can use the top menu, “All”, then “Workflows”, to enter the Workflows module. Inside that, check if you have any workflows defined for those two modules.

“Checking code” is just looking at the files in the file system, like you did.

Check inside every logic_hooks.php you find, to see if it mentions our problematic modules.

9

In top menu in All there are Documents, Notes, Workers, Factory and another one… No Workflows. Can I find it somewhere else?

I haven’t find any relevant hook (just some geomap hook and OAD logic hook).

10

I was assuming you knew the basics of SuiteCRM, but I guess it wasn’t you who built this system… :slight_smile:

You can hide and show modules in Admin / Display Modules and Subpanels

Or maybe try accessing it directly via

http://www.yoursite.com/index.php?action=ajaxui#ajaxUILoc=index.php%3Fmodule%3DAOW_WorkFlow%26action%3Dindex%26parentTab%3DAll

11

You are right. I’m just trying to fix it for someone else :slight_smile:

I tried to access workflows via you link and figured out that there are NO workflows… Any other hint or is SuiteCRM really so heavy system?

Btw, thank you for awesome support!

12

If you’re a developer, or have one working for you, you could try XDEBUG and profiling. But don’t ask me for help doing that, I never used profiling.

Maybe you could just run a delete of 2 records with logs in debug mode, and post the full contents of the log here so I can have a look?

13

You can try to set the mysql option:

 innodb_flush_log_at_trx_commit = 2

It helped me speed up deletes and updates significantly.

( https://dba.stackexchange.com/questions/12611/is-it-safe-to-use-innodb-flush-log-at-trx-commit-2 )

1 Like
14

Thank you for MySQL tip. It’s production DB server, so I would rather stay with safer option = 1. But thank you!

Here is pastebin for 1 deleted row. Log was anonymized: https://pastebin.com/uz48gCZN

15

So you didn’t even try? Ok. Good luck.

16

@gunnicom: If I set innodb_flush_log_at_trx_commit=2, restart MySQL service and then innodb_flush_log_at_trx_commit=1 how to safely restart MySQL with flushing transaction log to disk?

17

I had this problem too when deleting Cases and the problem is indeed AOD. In addition to disabling AOD in the web interface I had to comment out the calls to “AOD Index Changes” in custom/modules/Cases/logic_hooks.php

Deletion of Case records was then quick and straightforward. It would be nice if bulk deletion operations took account of this, turning off AOD indexing until their completion.

18

Hi, I want to share what happen to me at the same issues,
before:
I deleted 50 cases (records) take me more than 30-50 S
then after I did this
I deleted 1000 cases (records) took me only 20-40 S

what did I do?

  • I turn off AOD, and disabling scheduler that run AOD

  • on Mysql databases, I make ID as Index, for all tables

then I change Listview items per page from 50 to 1000 and clear repair

and it works

I hope my solution is yours too