Thanks.
We wiped out the email file as we downloaded solarwinds mysql monitor on a free trial for 2 weeks, and that come back saying any queries that were accessing the email addresses were slowâŚ
So we got rid of that. It was 750Meg out of 3gb total database and then just added back the email addresses we need, and itâs a bit quicker but still nowhere near normal itâs slow as well just saving a task or note.
Also the server currently has around 26gb of memory, which should happily be enough, we have set 12GB of that to being for mysql in the sql config file. Should this be upped to more, it doesnât seem to be using anywhere near this amount of memory in practise.
Iâd still try the two suggestions in my last post, that clue is worth investigating because itâs reproducible, you can see whatâs happening in the logs.
If you have a problem with a simple Accounts query, everything in SuiteCRM is going to slow down because thatâs a central concept, everything is connected to Accounts table in one way or another.
OK DONE!
Iâve installed phpMyAdminâŚ
Got it working, woohoo!! i count that as a win 
YES 582 thousand dead rowsâŚ
Now do I have to do the same but with delete instead of select? <---- scary!!
Showing rows 0 - 499 (582408 total, Query took 0.0035 seconds.)
SELECT * FROM accounts WHERE deleted= 1
The only thing is as you saw itâs really quick coming back with the result.
First, I would pause to consider why it has so many rows. Do you recognize that sort of huge activity in your company? Both the creation and the deletion? Why is that? Are you creating leads as Accounts?
Go and check the number again. It would be nice to see if after a few hours it has increased significantly (is something creating and deleting accounts continuously on your system?).
Then, to apply the fix, make sure you have proper backups of your database. Normally, deleting rows with âdeleted=1â doesnât affect anything, itâs part of the normal âprune databaseâ process. But itâs your data, and your responsibility, so backup appropriately.
Hi.
Yeah. we have gone from test to live on one server and basically in the past we did large imports and if it wasnât right just deleted it and started again the accounts now are pretty static, but, we know why all those old accounts are there, we just thought more of them had been deleted in the background by the system. so the account make sense
As you say, time to snapshot the VM, take a copy and also dump a copy somewhere else and give it a go
On my test serverâŚ
I have deleted the records the same 550,000 records
Iâve then run optimize table on a few of the larger tables in the database. So I now have a lot cleaner database as well.
Then I rebooted the test server to ensure everything was clean, checked the database size⌠a lot smaller
Then I ran prune database⌠and itâs actually running itâs been going for a few minutes. But itâs working⌠Itâs doing stuff instead of crapping out after 20 seconds
This looks promising the test system had sped up, but now of course i want to wait for the Prune to finish and see what itâs like then.
Humm
Lots of errors there and grumbling about slow queries⌠looks like i need to do a lot of manual deleting where deleted = 1
Thu Mar 23 15:10:02 2017 [2414][1][FATAL] Query Failed: SELECT count(*) c FROM (SELECT tasks.created_by AS âCreated_By0â, tasks.date_entered AS âDate_Created1â, tasks:accounts_cstm.wg_accountnumber_c AS âAccount_Number2â, tasks:accounts.name AS âName3â, tasks:accounts.billing_address_state AS âCounty4â, tasks.name AS âSubject5â, tasks.description AS âDescription6â, tasks.assigned_user_id AS âAssigned_to7â, tasks.id AS âtasks_idâ, tasks:accounts.id AS âtasks:accounts_idâ FROM tasks LEFT JOIN accounts tasks:accounts ON tasks.parent_id=tasks:accounts.id AND tasks:accounts.deleted=0
AND tasks.parent_type = âAccountsâ
LEFT JOIN tasks:accounts`_cstm` `tasks:accounts_cstm` ON `tasks:accounts`.id = `tasks:accounts_cstm`.id_c WHERE ( `tasks`.date_entered >= DATE_ADD(tasks.date_entered, INTERVAL - 1 week) AND `tasks`.created_by = 'd4fc0a4a-68f5-6aa8-86a0-56b8b66370b9' ) AND tasks.deleted = 0 ) as n: MySQL error 1103: Incorrect table name '' Thu Mar 23 15:10:02 2017 [2414][1][FATAL] Query Failed: SELECT `tasks`.created_by AS 'Created_By0', `tasks`.date_entered AS 'Date_Created1', `tasks:accounts_cstm`.wg_accountnumber_c AS 'Account_Number2', `tasks:accounts`.name AS 'Name3', `tasks:accounts`.billing_address_state AS 'County4', `tasks`.name AS 'Subject5', `tasks`.description AS 'Description6', `tasks`.assigned_user_id AS 'Assigned_to7', `tasks`.id AS 'tasks_id', `tasks:accounts`.id AS 'tasks:accounts_id' FROM `tasks` LEFT JOIN accounts `tasks:accounts` ON `tasks`.parent_id=`tasks:accounts`.id AND `tasks:accounts`.deleted=0 AND `tasks`.parent_type = 'Accounts' LEFT JOINtasks:accounts_cstm tasks:accounts_cstm ON tasks:accounts.id = tasks:accounts_cstm.id_c WHERE ( tasks.date_entered >= DATE_ADD(tasks.date_entered, INTERVAL - 1 week) AND tasks.created_by = âd4fc0a4a-68f5-6aa8-86a0-56b8b66370b9â ) AND tasks.deleted = 0 ORDER BY tasks.created_by ASC, tasks.date_entered ASC: MySQL error 1103: Incorrect table name ââ
Thu Mar 23 15:14:02 2017 [2483][1][FATAL] Slow Query (time:0.12881684303284
SELECT * FROM accounts_meetings_1_c WHERE deleted = 1
Thu Mar 23 15:14:39 2017 [2483][1][FATAL] Slow Query (time:36.430998086929
SELECT * FROM aod_indexevent WHERE deleted = 1
Thu Mar 23 15:14:45 2017 [2483][1][FATAL] Slow Query (time:6.6914110183716
DELETE FROM aod_indexevent WHERE deleted = 1
Thu Mar 23 15:15:02 2017 [2494][1][FATAL] Slow Query (time:0.10001087188721
INSERT INTO job_queue (assigned_user_id,id,name,deleted,date_entered,date_modified,scheduler_id,execute_time,status,resolution,target,requeue,job_delay,client)
VALUES (â1â,âeea844af-9f11-ff21-c176-58d3e6a3ae76â,âProcess Workflow Tasksâ,0,â2017-03-23 15:15:01â,â2017-03-23 15:15:01â,â303c73c1-1e89-4616-eb47-57b6e57e14b5â,â2017-03-23 15:15:01â,âqueuedâ,âqueuedâ,âfunction::processAOW_Workflowâ,0,0,ââ)
Thu Mar 23 15:15:09 2017 [2483][1][FATAL] Slow Query (time:23.060338020325
SELECT * FROM aos_products WHERE deleted = 1
Thu Mar 23 15:15:14 2017 [2483][1][FATAL] Slow Query (time:4.6666240692139
DELETE FROM aos_products WHERE deleted = 1
Thu Mar 23 15:15:35 2017 [2483][1][FATAL] Slow Query (time:21.496330976486
SELECT * FROM aow_processed WHERE deleted = 1
Thu Mar 23 15:15:40 2017 [2483][1][FATAL] Slow Query (time:4.372034072876
DELETE FROM aow_processed WHERE deleted = 1
Thu Mar 23 15:15:55 2017 [2483][1][FATAL] Slow Query (time:15.909296989441
SELECT * FROM aow_processed_aow_actions WHERE deleted = 1
Thu Mar 23 15:15:56 2017 [2483][1][FATAL] Slow Query (time:0.22770714759827
DELETE FROM aow_processed_aow_actions WHERE deleted = 1
Thu Mar 23 15:16:51 2017 [2483][1][FATAL] Slow Query (time:55.234158039093
SELECT * FROM email_addr_bean_rel WHERE deleted = 1
Thu Mar 23 15:16:59 2017 [2483][1][FATAL] Job f3208095-beae-a8e1-8037-58d3e63371ea (Prune Database on 1st of Month) failed in CRON run