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?
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.
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)
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)
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
“Check for code under custom/Extension/modules”
What should I look for? I see just “form” definitions in .php files.
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.
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?
@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?
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.
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