Huge database

Author Message

Christian Johansen

Saturday 11 July 2009 4:17:54 am

A database dump for my site, which is a blog with a few hundred entries, is coming upwards og 900MB. The size seems excessive, and I was wondering if there are any cleanup utilities to run on it?

I've cleaned out the trash, but that don't seem to help. Also, the database schema is fairly complex, so guessing which table is holding heaps of unused data is not simple. Any hints on how to slim my database?

Heath

Saturday 11 July 2009 4:34:38 am

Sadly the size is quite common.

Save your self some headache and simply adapt.

I've seen much larger sizes in GB of exports of active clean eZ Publish installation databases.

I suggest reading up on the php shell script, flatten.php and cleanup.php

These should help you reduce some database size.

<i>[0] http://ez.no/doc/ez_publish/upgrading/the_system_upgrade_scripts</i>

Cheers,
Heaht

Brookins Consulting | http://brookinsconsulting.com/
Certified | http://auth.ez.no/certification/verify/380350
Solutions | http://projects.ez.no/users/community/brookins_consulting
eZpedia community documentation project | http://ezpedia.org

Christian Johansen

Saturday 11 July 2009 4:54:45 am

Thanks, I'll read up on them. 900MBs of database for a simple blog is insane. The size is not really a problem in terms of storage space, but it is causing me headaches in terms of backup. Takes too long to backup a copy of the database.

If I can't considerably reduce the db size I fear I'll migrate the site to another solution :(

Thanks for your help!

kracker (the)

Saturday 11 July 2009 4:58:31 am

Migrate to wordpress Christian which can be very small.

You will never get eZ to fit the size
of db I know your looking for.

Also your prolly using the content tree
to store comments for your blog
which is also a huge performance
problem just waiting to be understood.

Cheers,
<i>//kracker</i>

Member since: 2001.07.13 || http://ezpedia.se7enx.com/

Karnichi Mohamed

Saturday 11 July 2009 5:49:49 am

Hi Christian,

As eZ Publish stores sessions informations in the database, you have to enable session.gc_probability so the expired sessions are purged:

so in your php.ini check if your have these parameters:

session.gc_probability = 1
session.gc_divisor = 100

If you look at your database size you will find that the ezsession table is huge !

For now you can truncate the ezsession table :

mysql >truncate table ezsession

or use the cleanup script

php update/common/scripts/cleanup.php session
php update/common/scripts/cleanup.php expired_session

Mo

http://www.amiralweb.com Certified eZPublish Expert

Max Keil

Saturday 11 July 2009 2:00:31 pm

Hi Christian,

can you tell us which of the tables causes such a large size of the dump? If it is ezsession then you should run clean up scripts like my previous writers told you - if it is ezcontentobject_attribute you may check the number of versions you store and the number of attributes in this classes.

Mit freundlichen Grüßen
Best regards

Max Keil

____________________________________________________________
eZ Publish Gold Partner - http://www.all2e.com
http://ez.no/partners/worldwide_partners/all2e_gmbh

Christian Johansen

Saturday 18 July 2009 1:07:55 pm

I cleaned up the sessions, and wow. I mean, WOW! Incredible:

-rw-r--r-- 1 christian users 904M 2009-07-18 21:58 20090718-1.sql
-rw-r--r-- 1 christian users  25M 2009-07-18 22:02 20090718-2.sql

That's pre and post session cleanup. Problem solved :) Thanks alot all!

Kracker: I'd be interested to know how you recon storing comments in the content tree is a problem? In what other way can I really store them? If you're thinking about the tree menu, I obviously don't show them there. I have quite a bit of content, and haven't had any real performance trouble yet...

kracker (the)

Saturday 18 July 2009 1:15:42 pm

Wow you sure proved me wrong. That's quite small.

Checkout, http://projects.ez.no/cscomment

Cheers,
//kracker

Member since: 2001.07.13 || http://ezpedia.se7enx.com/

Christian Johansen

Saturday 18 July 2009 2:10:29 pm

Yeah, I know. At first I was certain that my data had been corrupted, but it's still sane. Cool!

Thanks for the link, I'll check it out!

kracker (the)

Sunday 19 July 2009 11:40:19 am

I asked Heath to add a node about this topic on eZpedia,
<i>http://ezpedia.org/en/ez/solution_how_to_shrink_or_reduce_the_site_of_ez_publish_database_backup_dump_files</i>

Cheers,<i>
//kracker</i>

Member since: 2001.07.13 || http://ezpedia.se7enx.com/

Powered by eZ Publish™ CMS Open Source Web Content Management. Copyright © 1999-2014 eZ Systems AS (except where otherwise noted). All rights reserved.