Menu
SuiteCRM Pull Request Party hosted by SalesAgility
Welcome, Guest
Username: Password: Remember me
  • Page:
  • 1
  • 2

TOPIC: Delete 50 records takes 45 seconds

Delete 50 records takes 45 seconds 1 year 1 month ago #58865

  • n.et
  • n.et's Avatar
  • Offline
  • New Member
  • Posts: 8
  • Karma: 0
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
Last Edit: 1 year 1 month ago by n.et.
The administrator has disabled public write access.

Delete 50 records takes 45 seconds 1 year 1 month ago #58866

  • pgr
  • pgr's Avatar
  • Offline
  • Administrator
  • Posts: 9303
  • Thank you received: 1403
  • Karma: 337
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:

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

My SuiteCRM In-depth blog.
Thank you for always stating your SuiteCRM version, checking your logs, reading the Docs, and searching before you ask!
The administrator has disabled public write access.

Delete 50 records takes 45 seconds 1 year 1 month ago #58868

  • n.et
  • n.et's Avatar
  • Offline
  • New Member
  • Posts: 8
  • Karma: 0
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)
The administrator has disabled public write access.

Delete 50 records takes 45 seconds 1 year 1 month ago #58869

  • pgr
  • pgr's Avatar
  • Offline
  • Administrator
  • Posts: 9303
  • Thank you received: 1403
  • Karma: 337
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.

My SuiteCRM In-depth blog.
Thank you for always stating your SuiteCRM version, checking your logs, reading the Docs, and searching before you ask!
The administrator has disabled public write access.

Delete 50 records takes 45 seconds 1 year 1 month ago #58874

  • n.et
  • n.et's Avatar
  • Offline
  • New Member
  • Posts: 8
  • Karma: 0
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)
Last Edit: 1 year 1 month ago by n.et.
The administrator has disabled public write access.

Delete 50 records takes 45 seconds 1 year 1 month ago #58875

  • pgr
  • pgr's Avatar
  • Offline
  • Administrator
  • Posts: 9303
  • Thank you received: 1403
  • Karma: 337
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

My SuiteCRM In-depth blog.
Thank you for always stating your SuiteCRM version, checking your logs, reading the Docs, and searching before you ask!
The administrator has disabled public write access.

Delete 50 records takes 45 seconds 1 year 1 month ago #58877

  • n.et
  • n.et's Avatar
  • Offline
  • New Member
  • Posts: 8
  • Karma: 0
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

3. "Check for code under custom/Extension/modules"
What should I look for? I see just "form" definitions in .php files.
Last Edit: 1 year 1 month ago by n.et.
The administrator has disabled public write access.

Delete 50 records takes 45 seconds 1 year 1 month ago #58878

  • pgr
  • pgr's Avatar
  • Offline
  • Administrator
  • Posts: 9303
  • Thank you received: 1403
  • Karma: 337
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.

My SuiteCRM In-depth blog.
Thank you for always stating your SuiteCRM version, checking your logs, reading the Docs, and searching before you ask!
The administrator has disabled public write access.

Delete 50 records takes 45 seconds 1 year 1 month ago #58884

  • n.et
  • n.et's Avatar
  • Offline
  • New Member
  • Posts: 8
  • Karma: 0
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).
The administrator has disabled public write access.

Delete 50 records takes 45 seconds 1 year 1 month ago #58889

  • pgr
  • pgr's Avatar
  • Offline
  • Administrator
  • Posts: 9303
  • Thank you received: 1403
  • Karma: 337
I was assuming you knew the basics of SuiteCRM, but I guess it wasn't you who built this system... :-)

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

My SuiteCRM In-depth blog.
Thank you for always stating your SuiteCRM version, checking your logs, reading the Docs, and searching before you ask!
Last Edit: 1 year 1 month ago by pgr. Reason: fix link
The administrator has disabled public write access.

Delete 50 records takes 45 seconds 1 year 1 month ago #58970

  • n.et
  • n.et's Avatar
  • Offline
  • New Member
  • Posts: 8
  • Karma: 0
You are right. I'm just trying to fix it for someone else :)

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!
The administrator has disabled public write access.

Delete 50 records takes 45 seconds 1 year 1 month ago #59002

  • pgr
  • pgr's Avatar
  • Offline
  • Administrator
  • Posts: 9303
  • Thank you received: 1403
  • Karma: 337
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?

My SuiteCRM In-depth blog.
Thank you for always stating your SuiteCRM version, checking your logs, reading the Docs, and searching before you ask!
The administrator has disabled public write access.

Delete 50 records takes 45 seconds 1 year 1 month ago #59026

  • gunnicom
  • gunnicom's Avatar
  • Offline
  • Senior Member
  • Posts: 40
  • Thank you received: 16
  • Karma: 6
You can try to set the mysql option:
innodb_flush_log_at_trx_commit = 2
It helped me speed up deletes and updates significantly.

( dba.stackexchange.com/questions/12611/is...-log-at-trx-commit-2 )
The administrator has disabled public write access.
The following user(s) said Thank You: pgr

Delete 50 records takes 45 seconds 1 year 1 month ago #59063

  • n.et
  • n.et's Avatar
  • Offline
  • New Member
  • Posts: 8
  • Karma: 0
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: pastebin.com/uz48gCZN
The administrator has disabled public write access.

Delete 50 records takes 45 seconds 1 year 1 month ago #59067

  • gunnicom
  • gunnicom's Avatar
  • Offline
  • Senior Member
  • Posts: 40
  • Thank you received: 16
  • Karma: 6
So you didn't even try? Ok. Good luck.
The administrator has disabled public write access.
  • Page:
  • 1
  • 2
Time to create page: 0.135 seconds
Powered by Kunena Forum