Reducing the size of MyPHP Database (EB Blog Revisions)
By Paul Murray on Monday, 20 June 2016
Posted in Technical Issues
Likes 0
Views 544
Votes 0
Hello SI Team

I have been told by my hoster. (Siteground)
That my database has exceeded the allowed size.
On my current plan I am only allowed 750MB.
They have given me until Friday to get this under control.

I have managed to enter the following database query in the phpMyAdmin section in the cPanel.

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;


And got the following results:

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

It looks as of Blog revisions alone are taking up to almost half of the allowed data base!
(Currently @ 759MB)
Is there any way I can reduce this number.
Split it up into another data base.

Do some thing to get comfortably under my allowed 750MB for my current plan.

Real database name and login details are included in the "Additional Message" info section below.

thanks in advance

Paul
Hey Paul,

I am sorry for the delay of this reply,

May i know your `Currently broken` post is it still having the issue? Because it seems all work fine now, may i know is it you restore back your previous backup?
Arlex Wong
·
Friday, 24 June 2016 18:13
·
0 Likes
·
0 Votes
·
0 Comments
·
Hey Paul,

Unfortunately that was not possible to reduce the size from the Easyblog revision table is because the following all the action also related to the revision data :
1. When you open the composer
2. When you view blog post from frontend
3. When you edit your existing post and save it, it will store another revision data in the revision table
4. When you edit your existing post if you would like to revert back your changes, you can open back your previous revision which is related the revision data.

Hm, I would suggest you to upgrade your current plan storage to higher.
Arlex Wong
·
Monday, 20 June 2016 23:38
·
0 Likes
·
0 Votes
·
0 Comments
·
Hey Arlex

This is kind of unfortunate:

1. When you open the composer

- I probably do this 100 times per post!
- I know it is insane

2. When you view blog post from frontend

- Does "you" mean me as a super user.
- Or any one who looks?

3. When you edit your existing post and save it, it will store another revision data in the revision table

- See point 1.
- I do this a 100 times
- Again I know it is insane

4. When you edit your existing post if you would like to revert back your changes, you can open back your previous revision which is related the revision data.

After editing my post 100 times I am happy.
This means that I have 99 revisions that I know I will never return to.

I know I can throw more storage space at the problem.
But the problem remains the same.
I edit an insane amount of versions per post.
Is there some place where revisions are stored?
Some where where they can be deleted.

eg Post ABC

I only want the last version
Then delete the previous 99 versions?

This logic
Works for EVERY Blog post on my site.
The only exception is:
FCP X Subtitles : The Missing Webinars 04
But I could delete this as it is only a very, very,very basic draft version of a Blog article.

thanks

Paul
Paul Murray
·
Tuesday, 21 June 2016 00:10
·
0 Likes
·
0 Votes
·
0 Comments
·
Do I need to do something like this but for revisions?

http://stackideas.com/forums/db-query-to-delete-stream-items-for-specific-user
Paul Murray
·
Tuesday, 21 June 2016 00:41
·
0 Likes
·
0 Votes
·
0 Comments
·
Hey Paul,

This is kind of unfortunate:

1. When you open the composer

- I probably do this 100 times per post!
- I know it is insane

I am really sorry that I didn't explain very details from my above reply regarding this, what I actually mean is when you open the composer, the system will generate a temporary revision for the post.

But you do not worry about this because the system will auto clear these blank revision post from this #__easyblog_revisions table.


2. When you view blog post from frontend

- Does "you" mean me as a super user.
- Or any one who looks?

Yes, what I mean is for everyone who visit your blog on your site, the system also will see your revision table and populate the blog content on your page.


3. When you edit your existing post and save it, it will store another revision data in the revision table

- See point 1.
- I do this a 100 times
- Again I know it is insane

The revision tables is used to keep each revision of the blog content whenever the user edit the blog post.
If user edit one blog post for 100 times, there will be 100 records created in the revision table.


4. When you edit your existing post if you would like to revert back your changes, you can open back your previous revision which is related the revision data.

After editing my post 100 times I am happy.
This means that I have 99 revisions that I know I will never return to.

I know I can throw more storage space at the problem.
But the problem remains the same.
I edit an insane amount of versions per post.
Is there some place where revisions are stored?
Some where where they can be deleted.

eg Post ABC

I only want the last version
Then delete the previous 99 versions?

This logic
Works for EVERY Blog post on my site.
The only exception is:
FCP X Subtitles : The Missing Webinars 04
But I could delete this as it is only a very, very,very basic draft version of a Blog article.

Currently only possible delete these revision through your composer page, you can take a look of my screenshot here : http://take.ms/lDgq4



ps two more things I am wondering about:

5) If I trash an article is in really deleted from the data base?

I just trashed 27 unpublished articles that were tests and similar.
It does not seem to have made any difference to the size of "fbug_easyblog_revisions"

If you delete it from backend > Easyblog > blog listing , these deleted post will under `trashed` state, if you really want to delete it completely, you have select state filter to `trashed` (screenshot : http://screencast.com/t/y0i3oaknk ) then delete it all again.


6) I have Archived posts here:

http://www.finalbug.net/component/easyblog/archive?Itemid=937

Saturday, 21 October 2006 through to Monday, 21 October 2013.
Would it be possible to move these off into a different data base?

eg fbug_easyblog_archieves

in a data base eg fbug_Years-2006-2013_archieves

Unfortunately that was not possible to achieve this.
Arlex Wong
·
Tuesday, 21 June 2016 18:44
·
0 Likes
·
0 Votes
·
0 Comments
·
Hey Arlex

Tahnk you for your detailed response.

First. With respect to point 4

a) Is it possible to delete more than one revision at time this way?

I just deleted 300 revisions one by one for this here:

http://www.finalbug.net/component/easyblog/entry/546-fcp-x-subtitles-the-missing-webinars-01?Itemid=937

And another 150ish here:

http://www.finalbug.net/component/easyblog/entry/547-fcp-x-subtitles-the-missing-webinars-02?Itemid=937

and

http://www.finalbug.net/component/easyblog/entry/547-fcp-x-subtitles-the-missing-webinars-02?Itemid=937

The MySQL query above now returns:

fbug_easyblog_revisions 364.69MB

i.e. the revisions on the site are less but there is no noticeable winning of space!

b) Is there a further step that I am missing?

c) is there not some kind of MySQL query that would delete all but the last revision?

I have 100´s of posts times 100s of revisions!

would be very grateful for any input on this.

thanks

Paul
Paul Murray
·
Tuesday, 21 June 2016 20:23
·
0 Likes
·
0 Votes
·
0 Comments
·
Further investigation tells me that 100 revisons per post is exagereated-
I think an average is more like 50
But this is weird.

One post has over 1,000 revisions in 2 days.
I know I am mad but there is no way I made 1,000 revisions within two days!
http://www.finalbug.net/component/easyblog/entry/560-fcp-x-subtitles-the-missing-webinars-03?Itemid=937

Maybe this is part of the problem?
Paul Murray
·
Tuesday, 21 June 2016 21:56
·
0 Likes
·
0 Votes
·
0 Comments
·
Is it possible that some thing like this would do the trick?

http://stackideas.com/forums/reducing-the-size-of-myphp-database-social-mailer

Instead of:

truncate table fbug_social_mailer;


this here:

truncate table fbug_revisions;
Paul Murray
·
Wednesday, 22 June 2016 14:50
·
0 Likes
·
0 Votes
·
0 Comments
·
Hey Paul,

I am really sorry for the delay of this reply,

Currently we do not have an option to delete multiple revision at the same time from the composer yet.

And you can run this sql directly, it will break your blog frontpage listing.
truncate table `fbug_revisions`;


By the way, you can run this following sql queries :
update `fbug_easyblog_post` set `revision_id` = 0;

truncate table `fbug_easyblog_revisions`;


When you load your blog post, it will regenerate again the revision in your table.

Note: Before you process this, you have to backup your database first.
Arlex Wong
·
Thursday, 23 June 2016 16:59
·
0 Likes
·
0 Votes
·
0 Comments
·
Hi Arlex & Any One else who tires this ;-)

Yes I made a back up.
And used the commands:

update `fbug_easyblog_post` set `revision_id` = 0;


truncate table `fbug_easyblog_revisions`;


The good news is that the mySQL data base is now way smaller.
The bad news is that I have lost about half of the articles on the front page.
All in all I am happy with the result and will copy and paste every thing back together if I have to.


http://www.finalbug.net/blog/entry/560-fcp-x-subtitles-the-missing-webinars-03
Currently broken

http://www.finalbug.net/blog/entry/547-fcp-x-subtitles-the-missing-webinars-02
Works

http://www.finalbug.net/blog/entry/546-fcp-x-subtitles-the-missing-webinars-01
Currently broken
I can get to it here:

http://www.finalbug.net/blog/entry/479-roles-beyond-with-role-o-matic
Currently broken

http://www.finalbug.net/blog/entry/544-youtube-red-an-ecletic-retrospective-02
Currently broken

http://www.finalbug.net/blog/entry/528-youtube-red-2015-an-eclectic-retrospective-01
Currently broken

http://www.finalbug.net/blog/entry/526-all-you-need-to-make-your-movie-for-free-03
Works

http://www.finalbug.net/blog/entry/502-all-you-need-to-make-your-movie-for-free-02
Works

http://www.finalbug.net/blog/entry/490-all-you-need-to-make-your-movie-for-free-01
Works


http://www.finalbug.net/blog/entry/475-nle-tracks-roles-part-3-of-3
Currently broken

I guess that the site will be faster when I am done and this does save me a pile of cash with my hoster.

best

Paul

For any one who is interested I am also using this to help me with some of the formatting

Visit Any Website In The Past With Archive
https://www.youtube.com/watch?v=lAyl0D0ywVc

FYI the EB pagination feature seems to throw this off!
i.e. I can not go back to blog posts but not back to previous pages of a blog post!

Lesson learn´t. Keep an eye on the history tab in the Blog Composer!!!
Paul Murray
·
Thursday, 23 June 2016 18:16
·
0 Likes
·
0 Votes
·
0 Comments
·
Hey Arlex

I have pretty much every thing back.
This was some what painful but I deleted junk going back 5 years!!!
I can stick with my present hosting plan which is good.

thanks

Paul
Paul Murray
·
Monday, 27 June 2016 17:24
·
0 Likes
·
0 Votes
·
0 Comments
·
Hi there,

You are most welcome.

As a gentle reminder, kindly start a new thread if you have any other issue in the future so it will be easier for us to manage your inquiry. I will lock and mark this thread as resolved.
Arlex Wong
·
Tuesday, 28 June 2016 00:49
·
0 Likes
·
0 Votes
·
0 Comments
·
View Full Post