Reducing The Size Of MyPHP Database (ES Stream Trash)
By Paul Murray on Tuesday, 21 June 2016
Posted in Technical Issues
Likes 0
Views 765
Votes 0
Hi Stackideas

This is kind of related to this here:

http://stackideas.com/forums/reducing-the-size-of-myphp-database-eb-blog-revisions

They have given me until Friday to get this under control.


finalbug_NOT-REAL-NAME fbug_easyblog_revisions 363.59
finalbug_NOT-REAL-NAME fbug_social_stream_item 102.53
finalbug_NOT-REAL-NAME fbug_social_mailer 96.76
finalbug_NOT-REAL-NAME fbug_finder_terms 55.44
finalbug_NOT-REAL-NAME fbug_social_notifications 24.36
finalbug_NOT-REAL-NAME fbug_finder_links 16.17
finalbug_NOT-REAL-NAME fbug_finder_links_terms8 9.08
finalbug_NOT-REAL-NAME fbug_finder_links_terms0 7.09
finalbug_NOT-REAL-NAME fbug_finder_links_termse 7.08
finalbug_NOT-REAL-NAME fbug_finder_links_terms4 5.14
finalbug_NOT-REAL-NAME fbug_social_points_history 4.43
finalbug_NOT-REAL-NAME fbug_social_privacy_items 4.21
finalbug_NOT-REAL-NAME fbug_social_stream 3.88
finalbug_NOT-REAL-NAME fbug_admintools_filescache 3.52
finalbug_NOT-REAL-NAME fbug_easyblog_post 3.44
finalbug_NOT-REAL-NAME fbug_social_fields_data 3.12


In short my data base is getting huge and I would like to shrink it.

It is 95% trash any way ;-)

When I go to the ES Back end there is a menu down at the bottom left for “Stream”

I can go in there and seem to have literaly thousands of items that I could trash.

I have tried selecting about 500 items and trashing them.

Thing is that when I set the “Select State” filter to “Trashed” I still see these items.

Which makes me think that they are in a trash can but not thrown away?

I have 100MB of Stream items and I have no problem throwing every thing apart from lets say the last 100 items out!

Please advise

thanks

Paul

Do I need to do something like this?

http://stackideas.com/forums/db-query-to-delete-stream-items-for-specific-user
When you "trash" a stream item, it's not "deleted" from the system altogether. It only sets the state of the item to "delete". This is the same behavior for Joomla articles and EasyBlog posts as well.

To really delete the items, you need to filter the results by "Trashed", select the items and click on the "Delete" button.

The reason that this is done is to prevent admin to have a chance to recover what was deleted in the event they accidentally deleted the stream item.
Mark
·
Wednesday, 22 June 2016 12:53
·
0 Likes
·
0 Votes
·
0 Comments
·
Hey Mark

Thanks for your explenation.
I have done as you suggested.
And trashed and deleted 3000 stream items!!!

Running this here:

SELECT 

     table_schema as `fbug_NOT_REAL_NAME`, 

     table_name AS `Table`, 

     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 

FROM information_schema.TABLES 

ORDER BY (data_length + index_length) DESC;


Still returns

finalbug_nina fbug_social_stream_item 102.53 MB

Which is pretty much where this was at before I deleted and trashed all those stream items!
Do you have any suggestion as to what is going on.
I am hoping to keep the last 300 stream items.

The other 3,000 are as I say already gone.

thanks

Paul
Paul Murray
·
Wednesday, 22 June 2016 16:49
·
0 Likes
·
0 Votes
·
0 Comments
·
I believe you deleted these stream 3000 items is not that heavy stream data.

Because I've checked in your site, it seems like all the heavy data is under `article` stream, you can take a look of my screenshot below.
Arlex Wong
·
Thursday, 23 June 2016 17:42
·
0 Likes
·
0 Votes
·
0 Comments
·
Hi Arlex


I have trashed literally thousands of streamed items.
Currently "only" have 300 items.
I have filtered for trashed items and deleted.
Then I double checked by filtering for trashed items again. There was nothing to be deleted from the trash.



Despite this my Streamed items seem to be just as big as before!

finalbug_NOT-REAL-NAME fbug_social_stream_item 102.53
finalbug_NOT-REAL-NAME fbug_finder_terms 55.09
finalbug_NOT-REAL-NAME fbug_social_notifications 24.60
finalbug_NOT-REAL-NAME fbug_finder_links 16.02
finalbug_NOT-REAL-NAME fbug_finder_links_terms8 9.02
finalbug_NOT-REAL-NAME fbug_finder_links_terms0 7.06
finalbug_NOT-REAL-NAME fbug_finder_links_termse 7.02
finalbug_NOT-REAL-NAME fbug_easyblog_revisions 6.29
finalbug_NOT-REAL-NAME fbug_finder_links_terms4 5.09
finalbug_NOT-REAL-NAME fbug_social_points_history 4.43
finalbug_NOT-REAL-NAME fbug_social_privacy_items 4.21
finalbug_NOT-REAL-NAME fbug_social_stream 3.89

Any ideas what is going on?

thanks

Paul
Paul Murray
·
Monday, 27 June 2016 17:19
·
0 Likes
·
0 Votes
·
0 Comments
·
I am sorry for the delay of this reply,

As I mentioned at above the rest of the 300 stream from this table `fbug_social_stream_item` was stored heavy data in the params columns, because I believe all of these stream is your article content which all stored in the params columns.

Perhaps you can try duplicate this `fbug_social_stream_item` table and try to remove 10 of the data row which context_type which equal to `article` then see is it have reduce the size?
Arlex Wong
·
Tuesday, 28 June 2016 00:49
·
0 Likes
·
0 Votes
·
0 Comments
·
Hi Arlex

I am not going to pretend to understand this.

thanks as always for your help.

Greatly appreciated as always

Paul
Paul Murray
·
Tuesday, 28 June 2016 17:23
·
0 Likes
·
0 Votes
·
0 Comments
·
You are most welcome
Mark
·
Tuesday, 28 June 2016 19:14
·
0 Likes
·
0 Votes
·
0 Comments
·
View Full Post