[opencms-dev] OpenCMS bug? Delete content everywhere, DB is still too large (10GB+)

[Ignacio Martelli]

I created the bug report. Meanwhile, Is there any way to reduce the DB
size manually? I can think 2 ways:

1) Access the database and execute a query that removes the old data
2) Export the content and reimport it in a new instance

My problem is that I don't have enough knowledge about the internal
SQL relations to make a delete query with confidence it won't break
anything, and no 2 would require downtime in order to execute in our
production servers (not enough free disk space).

Does anybody know other ways to reduce the DB size by removing data
from deleted content?

Thanks,
IM


On Fri, Feb 11, 2011 at 7:11 AM, Michael Emmerich
<m.emmerich at alkacon.com> wrote:
> Hello Ignacio,
>
> I made a little test here to try to reproduce this (on OpenCms 7.5.3)
>
> 1) Copy a complete site and publish it
>
> -> Number of entries in CMS_CONTENTS table increases as expected
>
> 2) Touch the copied site (including rewrite content)
>
> ->  Number of entries in CMS_CONTENTS table increases as expected
>
> 3) Delete the copied site and publish it
>
> ->  Number of entries in CMS_CONTENTS table increases, this seems
> strange and I would not have expected this
>
> 4) Cleanup History tables, keep one version, delete al deleted
>
> -> Number of entries in CMS_CONTENTS stays the same as in 3), this seems
> to be not correct.
>
> So steps 3 and 4 bring up results that must be investigated.
>
> We will take a look at this for the OpenCms 8 release, it would be good
> if you could add a enty in bugzilla.opencms.org.
>
> Kind Regards,
> Michael
>
>
>
>
> Am 10.02.2011 21:10, schrieb Ignacio Martelli:
>> Hi,
>>
>> I found something strange while trying to reduce the size of a big
>> (13GB+) OpenCMS 7.5.2 instance by deleting old content. I first tried
>> to delete the content in the server, but the DB did not decrease it's
>> size. In order to do the process correctly I copied the entire server
>> into a dev VM, and tried to delete ALL the content. Here is what I
>> did:
>>
>> - Delete all the contents of /sites/MySite
>> - In "Administration ->  File History ->  History Settings": change
>> settings to leave 1 stored version and not keep deleted resources.
>> - In "Administration ->  File History ->  Clear History": Cleared all
>> history including all versions of deleted files, left only 1 file
>> versions of normal content.
>> - In MySql: Optimized all tables to reclaim hard disk space.
>>
>> After doing all these steps, I checked the DB size, and found it still
>> weights at over 10GB! Looking at table sizes, the table CMS_CONTENTS
>> is taking 90% of that space.
>>
>> I checked the contents of that table and found content linked only to
>> CMS_HISTORY_RESOURCES and CMS_PUBLISH_HISTORY but not linked to
>> CMS_ONLINE_RESOURCES, CMS_HISTORY_RESOURCES, CMS_ONLINE_STRUCTURE or
>> CMS_HISTORY_STRUCTURE.
>>
>> Checking the CMS_PUBLISH_HISTORY table I found that the path of said
>> content was in a subfolder of /sites/MySite. I see no way to access
>> that path from the workplace.
>>
>>
>> If I understand correctly I have content that I cannot access from the
>> workplace (because it has no resources or structure associated with
>> it). Shouldn't that content have been deleted?????? Is this a bug, or
>> is the content necessary?
>>
>>
>> Regards,
>> IM
>>
>> _______________________________________________
>> This mail is sent to you from the opencms-dev mailing list
>> To change your list options, or to unsubscribe from the list, please visit
>> http://lists.opencms.org/mailman/listinfo/opencms-dev
>
> --
> Kind Regards,
> Michael.
>
> -------------------
> Michael Emmerich
>
> Visit OpenCms Days 2011 Conference and Expo
> May 9 to May 10 2011 in Cologne, Germany
> http://www.opencms-days.org
>
> Alkacon Software GmbH  - The OpenCms Experts
> http://www.alkacon.com - http://www.opencms.org
>
> _______________________________________________
> This mail is sent to you from the opencms-dev mailing list
> To change your list options, or to unsubscribe from the list, please visit
> http://lists.opencms.org/mailman/listinfo/opencms-dev
>