1

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?

closed as not a real question by Letharion, kiamlaluno Dec 19 '12 at 5:53

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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
2

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):

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

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
FROM
(
    SELECT CONCAT(table_schema,'.',table_name) dbtb
    FROM information_schema.tables
    WHERE table_schema NOT IN
    ('information_schema','mysql','performance_schema')
) A;

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

SQLFILE=/tmp/OptimizeSQLScript.sql
SQLCMD="SELECT CONCAT('OPTIMIZE TABLE ',dbtb,';') FROM ("
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 !!!

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