Huge backups -- normal?

[Liam]

SugarCRM database backup files are getting *huge*! Over 9 gigabytes. Is it normal for it to be so huge?
Are there log files or backups that need to be trimmed,or what? If so, how?
I don't know exactly how the client is using SugarCRM, but the size of the database seems excessive for the number of contacts I *think* they have.
For example, table emails_backup hasn't been touched since Oct.

Also -- yikes! -- just listed contacts and, in addition for it taking forever to load, they all look like
J�e�s�s� ��A�d�k�i�n�s�,� �P�h�.�D�.
WTF? They appear normal in the db tables, however.

Also -- noticed a lot of this in the log file:
WHERE st.deleted=0
AND st.scheduler_id = '60423822-21a9-f5fc-d97b-45424d646eb5'
ORDER BY st.execute_time DESC
Sat Apr 7 04:19:49 2007,326 [17979] FATAL SugarCRM - Slow Query (time:708.520688
SELECT st.id, st.execute_time FROM schedulers_times st
WHERE st.deleted=0
AND st.scheduler_id = '60423822-21a9-f5fc-d97b-45424d646eb5'
ORDER BY st.execute_time DESC
Sat Apr 7 04:19:49 2007,327 [18099] FATAL SugarCRM - Slow Query (time:588.519955
SELECT st.id, st.execute_time FROM schedulers_times st
WHERE st.deleted=0
And these:
Fri Dec 15 14:38:09 2006,224 [24776] FATAL SugarCRM - MySQL error 1062: Duplicate entry '1' for key 1
Fri Dec 15 14:38:51 2006,504 [9932] FATAL SugarCRM - MySQL error 1062: Duplicate entry '1' for key 1
Fri Dec 15 14:43:00 2006,601 [23885] FATAL SugarCRM - If you see a whole string of these, there is a chance someone is attacking your system.
Fri Dec 15 14:43:59 2006,831 [23894] FATAL SugarCRM - If you see a whole string of these, there is a chance someone is attacking your system.
Fri Dec 15 22:14:38 2006,224 [24776] FATAL SugarCRM - IP Address mismatch: SESSION IP: 4.22.70.224 CLIENT IP: 71.135.99.2
Fri Dec 15 22:15:00 2006,234 [18283] FATAL SugarCRM - SECURITY: User authentication for admin failed
Fri Dec 15 22:15:00 2006,234 [18283] FATAL SugarCRM - FAILED LOGIN:attempts[1] - admin


Any help would definitely be appreciated!!!! This ended up turning into several different questions, but I suspect they may be related.

System info:
Linux 2.6.12-12mdksmp #1 SMP Fri Sep 9 17:43:23 CEST 2005 i686 Intel(R) Pentium(R) 4 CPU 3.00GHz unknown GNU/Linux
PHP 5.04
Apache/2.0.58
SugarCRM 4.2.1.a
mysql Ver 14.7 Distrib 4.1.12

Some of the larger table sizes are listed below:
-rw-rw---- 1 mysql mysql 4294967216 Oct 30 09:29 emails_backup.MYD
-rw-rw---- 1 mysql mysql 4081846896 Apr 19 15:45 emails.MYD
-rw-rw---- 1 mysql mysql 138516964 Apr 21 11:17 campaign_log.MYD
-rw-rw---- 1 mysql mysql 91193748 Apr 20 14:52 contacts.MYD
-rw-rw---- 1 mysql mysql 75163648 Apr 21 04:02 prospect_lists_prospects.MYI
-rw-rw---- 1 mysql mysql 67648512 Apr 21 09:22 accounts_contacts.MYI
-rw-rw---- 1 mysql mysql 66165760 Apr 21 11:17 campaign_log.MYI
-rw-rw---- 1 mysql mysql 63503360 Apr 20 04:02 emails_contacts.MYI
-rw-rw---- 1 mysql mysql 60017664 Apr 20 04:02 emails.MYI
-rw-rw---- 1 mysql mysql 52322792 Apr 20 07:42 prospect_lists_prospects.MYD
-rw-rw---- 1 mysql mysql 51995184 Apr 19 15:45 emails_contacts.MYD
-rw-rw---- 1 mysql mysql 50579456 Apr 21 09:22 contacts.MYI
-rw-rw---- 1 mysql mysql 43367308 Apr 19 15:45 emailman.MYD
-rw-rw---- 1 mysql mysql 43316276 Apr 20 07:38 accounts_contacts.MYD
-rw-rw---- 1 mysql mysql 23679344 Apr 20 07:38 contacts_cstm.MYD
-rw-rw---- 1 mysql mysql 23206912 Apr 20 04:02 emailman.MYI
-rw-rw---- 1 mysql mysql 18981888 Apr 21 09:21 contacts_cstm.MYI
-rw-rw---- 1 mysql mysql 4981760 Oct 30 09:35 emails_backup.MYI
-rw-rw---- 1 mysql mysql 1501244 Apr 13 15:29 notes.MYD
-rw-rw---- 1 mysql mysql 1432068 Mar 18 20:01 prospects.MYD
-rw-rw---- 1 mysql mysql 1306016 Apr 20 07:38 users_last_import.MYD
-rw-rw---- 1 mysql mysql 1299064 Apr 17 14:00 email_templates.MYD
-rw-rw---- 1 mysql mysql 1144832 Apr 17 21:03 emails_users.MYI
-rw-rw---- 1 mysql mysql 1127432 Apr 17 14:01 emails_users.MYD
-rw-rw---- 1 mysql mysql 745472 Apr 21 04:02 users_last_import.MYI
-rw-rw---- 1 mysql mysql 654336 Apr 14 04:02 notes.MYI
-rw-rw---- 1 mysql mysql 595968 Mar 19 05:02 prospects.MYI
-rw-rw---- 1 mysql mysql 255984 Apr 21 11:24 schedulers_times.MYD
-rw-rw---- 1 mysql mysql 207872 Apr 21 11:24 schedulers_times.MYI
-rw-rw---- 1 mysql mysql 131072 Nov 1 13:13 leads.MYI
-rw-rw---- 1 mysql mysql 128612 Nov 1 13:13 leads.MYD
-rw-rw---- 1 mysql mysql 122432 Apr 20 07:00 user_preferences.MYD
-rw-rw---- 1 mysql mysql 90112 Apr 21 04:02 prospect_list_campaigns.MYI
-rw-rw---- 1 mysql mysql 52340 Apr 20 08:43 prospect_lists.MYD
-rw-rw---- 1 mysql mysql 49536 Aug 10 2006 leads_cstm.MYD
-rw-rw---- 1 mysql mysql 48580 Apr 20 08:43 prospect_list_campaigns.MYD
-rw-rw---- 1 mysql mysql 45056 Aug 11 2006 leads_cstm.MYI
-rw-rw---- 1 mysql mysql 38116 Apr 17 14:12 email_marketing.MYD
-rw-rw---- 1 mysql mysql 32156 Apr 20 07:27 accounts.MYD
-rw-rw---- 1 mysql mysql 31588 Apr 20 07:26 campaign_trkrs.MYD
-rw-rw---- 1 mysql mysql 25516 Apr 20 07:11 campaigns.MYD
-rw-rw---- 1 mysql mysql 23552 Apr 21 09:22 accounts.MYI
-rw-rw---- 1 mysql mysql 23552 Apr 21 04:02 prospect_lists.MYI
-rw-rw---- 1 mysql mysql 21312 Dec 15 14:38 relationships.MYD
-rw-rw---- 1 mysql mysql 20480 Jun 16 2006 acl_actions.MYI
-rw-rw---- 1 mysql mysql 20116 Apr 16 09:55 users.MYD
-rw-rw---- 1 mysql mysql 19456 Oct 13 2006 emails_leads.MYI
-rw-rw---- 1 mysql mysql 19456 Apr 21 04:02 user_preferences.MYI
-rw-rw---- 1 mysql mysql 17408 Apr 18 04:02 email_marketing.MYI
-rw-rw---- 1 mysql mysql 15360 Apr 21 09:22 campaign_trkrs.MYI
-rw-rw---- 1 mysql mysql 15360 Apr 18 04:02 email_templates.MYI
-rw-rw---- 1 mysql mysql 15136 Nov 1 13:13 leads.frm
-rw-rw---- 1 mysql mysql 14844 Apr 17 02:45 campaigns_audit.MYD
-rw-rw---- 1 mysql mysql 14730 Nov 1 13:04 contacts.frm
-rw-rw---- 1 mysql mysql 14336 Apr 21 04:02 campaigns.MYI
-rw-rw---- 1 mysql mysql 14336 Apr 17 04:02 email_marketing_prospect_lists.MYI
-rw-rw---- 1 mysql mysql 13312 Dec 15 14:58 relationships.MYI
-rw-rw---- 1 mysql mysql 12364 Apr 20 08:43 tracker.MYD
-rw-rw---- 1 mysql mysql 12276 Oct 12 2006 emails_leads.MYD
-rw-rw---- 1 mysql mysql 11856 Jun 2 2006 acl_actions.MYD
-rw-rw---- 1 mysql mysql 11264 Mar 17 05:02 emails_prospects.MYI
-rw-rw---- 1 mysql mysql 10482 Nov 1 13:13 prospects.frm
-rw-rw---- 1 mysql mysql 10258 Nov 1 13:13 users.frm
-rw-rw---- 1 mysql mysql 10252 Aug 28 2006 accounts.frm
-rw-rw---- 1 mysql mysql 10146 Nov 1 13:05 emails.frm
-rw-rw---- 1 mysql mysql 10104 Oct 30 09:27 emails_backup.frm

[roblaus]

Hi,

If this was my server I'd have a good look at my smtp protocols. Seems as if your customer is sending large amounts of emails. Just the emails take more than 8GB of space.
On my box I have appp. 20.000 addresses and some 10.000 mails and the entire db is about 20MB (!!!) as dump.

I definitely helps to prune the database every now and then (scheduler).

rgds
rl

[Liam]

Well, I looked at the tables, and it appears that there are a huge number of mails that are 'archived'.
That seems really excessive. Is it saving an identical (excessively large HTML) message for each recipient? There's got to be a better way to do that!

(I ran the prune action via the scheduler, but it didn't seem to make much difference in the size.)

What do you mean by checking smtp protocols? I think they are sending out around 60,000 messages each time for some of the larger distributions.

[roblaus]

I was just thinking you might host a massive spammer. But they probably wouldn't use Sugar.

And yes, unfortunately a copy of every mail sent is stored under "archived". This is fine for smaller campaigns but bad for large ones where you have 100.000 times the same text/HTML file.
Suggestion: Either clean it up through some SQL commands or get somebody to change your Sugar code so that these things cannot happen anymore.

rgds
rl

[Liam]

What would happen if I removed all the archived files from the emails table? I'm not familiar enough with SugarCRM to do that confidently... What if they were referenced from some other table?

I'm not hosting a spammer, but this client does send out a large number of emails to their customers.

BTW, also had a BIG problem with monthly scheduler pruning this morning.Whoa! Cron kept running 'php -f cron.php' and it kept hanging, then it fired off another one, etc., etc. I was lucky I was able to reboot the system! As it was, the server was out of commission for hours until I was able to figure out what was wrong.

[roblaus]

Imagine the job of going through millions of records in order to check whether they should be deleted...

I had a look into your email issue: If you delete the email then you will lose the history entry for the lead/contact but NOT the campaign entry. Only the related item (the email) will be then represented by a non existing link that should go once you prune the DB next time. But of course you could do this also through a query beforehand so that your prune job will not bring down your server again.

rgds
rl