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