2 Spy Tables Really...4,000,000 entries...

As I was going over tables for my site I was astonished to find the 2 tables used for spy data friends or something close to that and found over 4,000,000 entries in that table which runs in complete conjunction with the other spy table which together were larger than 2 gigs of data!  Now I ask you how in gods name is this not discussed anywhere to be something that should be checked from time to time!  Really 4 million entries all having to be run down for a member!  I find this to be unthinkable as I searched the tables names in the forum and my settings in the module itself are set for keep for 30 days!  The search only produces threads about 2 years old saying the tables are very large and should have crons to clean them!  Perhaps a cron that is not working?  Either way after years of paying attention here at these forums I find it unreal that there is nothing like a warning saying you might want to check the size of these possibly enormous tables to make sure they do not have 4 million entries... Sure it's my fault to assume that nobody would build something that could get that bloated!!!   If I missed a warning or something obvious about this then I am sorry but I've just never seen it mentioned... 

it is strange that not erase the spy data after 30 days, however another problem is that until now, when you delete a news group a picture or anything the news on bx_spy_data remains in any case .. from 7.1 if you delete a group will be deleted also on bx_spy_data .. so as to decrease the size of this table .. I'm applying this modification to the 7.0.9 as it does not update my site to 7.1 .. even if then I will make other sites with dolphin 7.1.. I apply this change even in the third-party modules .. so to solve this problem for those who will want to use dolphin 7.0.9..

Maybe search for "pruning" in the forum:

http://www.boonex.com/trac/dolphin/ticket/2021

http://www.boonex.com/forums/topic/SIZE-OF-TABLE.htm

Possible "solution":

DELETE FROM `bx_spy_data` WHERE `bx_spy_data`.`id` < xxxxx;
DELETE FROM `bx_spy_friends_data` WHERE `bx_spy_friends_data`.`id` < xxxxx;

Replace "xxxxx" with real value before to run this SQL query ;-)

I frequently run this one:

DELETE FROM bx_spy_data WHERE `sender_id` NOT IN (SELECT ID FROM Profiles);

A bug in dolphin, neglects to remove spy data from the bx_spy_data table when the user is deleted... This cleans that up... by deleting all entries which do not have a matching ID in the profiles table (hence deleted user)

I'm not sure what would happen if you do the same to the bx_spy_friends_data... which for me is.... 8.4 million rows...

First time I ran it, it cleaned up 28k rows... now every few days:

mysql> DELETE FROM bx_spy_data WHERE `sender_id` NOT IN (SELECT ID FROM Profiles);

Query OK, 100 rows affected (0.29 sec)

@MichelSwiss  

Maybe search for "pruning" in the forum:

 The search only produces threads about 2 years old saying the tables are very large and should have crons to clean them!   Those are exactly what I was talking about and they are 2 years old.  Thank you for pointing me to the same posts I found.

My question is why do the 2 spy tables not prune or purge what can and did become an enormous or 2 gigs of information.  My entire point is for people who don't spend everyday here looking at things.  It seems quite obvious this is a possible MAJOR consumption of resources and if a person is on a shared server which I am not but this would be the first place I would be looking to take stress of their site.  

It also only makes since to me because the tables fill out so very fast that DOLPHIN should have a warning or a set of benchmarks to look at.  My sites speed with a combination of deleting the contents of both of those tables and converting to fastcgi have been beyond unthinkable to the difference.

There is also an admin setting about tracking guests that for a person like myself could make an enormous difference in the size of those tables.  The trade off being my tracking of people via my table or tracking software via Google analytic's.    

As I mentioned in my reply. It's a bug which AFAIK, will be fixed in 7.1

This is the whole set of SQL queries I was using for database maintenance:

(the 0 value is used to delete actions datas of deleted users
 other values have to be changed with yours)

DELETE FROM `bx_photos_favorites` WHERE `bx_photos_favorites`.`Profile` = 0;

DELETE FROM `bx_photos_views_track` WHERE `bx_photos_views_track`.`viewer` = 0;

DELETE FROM `sys_friend_list` WHERE `sys_friend_list`.`Profile` = 0;

DELETE FROM `bx_spy_data` WHERE `bx_spy_data`.`id` < 136308;
DELETE FROM `bx_spy_data` WHERE `bx_spy_data`.`recipient_id` = 0;

DELETE FROM `bx_spy_friends_data` WHERE `bx_spy_friends_data`.`id` < 1250153;
DELETE FROM `bx_spy_friends_data` WHERE `bx_spy_friends_data`.`sender_id` = 0;

DELETE FROM `sys_profile_views_track` WHERE `sys_profile_views_track`.`ts` < 1297558945;
DELETE FROM `sys_profile_views_track` WHERE `sys_profile_views_track`.`viewer` = 0;

DELETE FROM `bx_sites_views_track` WHERE `bx_sites_views_track`.`viewer` = 0;

DELETE FROM `bx_sounds_views_track` WHERE `bx_sounds_views_track`.`viewer` = 0;

DELETE FROM `sys_ip_members_visits` WHERE `sys_ip_members_visits`.`ID` < 1144;

DELETE FROM `sys_sessions` WHERE `sys_sessions`.`date` < 1303784178;
DELETE FROM `sys_sessions` WHERE `sys_sessions`.`user_id` = 0;

@csampson1

There is a problem with cron, spy data is pruned upon cron execution.

What do you mean there is a problem with cron? My cron does not clean out the spy data, I get like 100,000 rows a day that are not being pruned with the cron. Is there a setting somewhere for this?

It should be:

administration->modules->spy->"Number of days to keep records"

Yup, thanks mscott