I have my Drupal site hosted at a society that gave me a 400M database quota. After 4 weeks of production, it has reached 100M. I predict that after a year, it will reach the limit.

Is any way to reduce the database size, so I don't have problems with my quota?

  • Do you have cron running? This will clean up old data from the cache and logs. – mpdonadio Dec 8 '12 at 13:20
  • yes of course, i used [drupal.org/project/cron_cache] module to empty the cache – mohammed amine bourkadi Dec 8 '12 at 13:23
  • Which tables are the big ones? – mpdonadio Dec 8 '12 at 14:31
  • 1
    If you have a lot of nodes/fields the search_index table can get pretty large - if so you could always get yourself a SOLR instance (preferably on the same class C net at least) and that will alleviate some of the strain – Clive Dec 8 '12 at 14:56
  • 1
    -1 on not mentioning anything about the content/setup of the site. How can the question be answered when the problem is complete guesswork? If editors are churning out new content on a daily basis, you can't "just shrink the database." – Letharion Dec 8 '12 at 15:41

You could just shrink the database by optimizing every MyISAM you have

You can use to monitor how much space you database is taking up (in MB):

    IFNULL(dbtb,'Total') tablename,
    (datsize+ndxsize)/power(1024,pw) table_size
    SELECT CONCAT(table_schema,'.',table_name) dbtb,
    data_length datsize,index_length ndxsize
    FROM information_schema.tables
    WHERE table_schema NOT IN
) A,(SELECT 2 pw) B

This will add up all used data and index space.

I have other queries for measuring the amount of used space for MyISAM and InnoDB

If there is significant difference between in these queries' output and what your OS says you are using, try shrinking each individual table as follows:

SELECT CONCAT('OPTIMIZE TABLE ',dbtb,';') OptimizeCommand
    SELECT CONCAT(table_schema,'.',table_name) dbtb
    FROM information_schema.tables
    WHERE table_schema NOT IN
) A;

Export the output of this query to a SQL text file and run it:

SQLCMD="${SQLCMD} SELECT CONCAT(table_schema,'.',table_name) dbtb "
SQLCMD="${SQLCMD} FROM information_schema.tables WHERE table_schema NOT IN "
SQLCMD="${SQLCMD} ('information_schema','mysql','performance_schema')) A"
mysql -u... -p... -ANe"${SQLCMD}" > ${SQLFILE}
mysql -u... -p... < ${SQLFILE}

An even faster way without fancy parsing would be to mysqldump the entire dataset and reload it

mysqldump -u... -p... --all-databases --routines --triggers > mydata.sql
mysql -u... -p... < mydata.sql

Give it a Try !!!

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.