Dealing with MySQL database quotas 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?
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


https://dba.stackexchange.com/a/1307/877
https://dba.stackexchange.com/a/5708/877
https://dba.stackexchange.com/a/18952/877


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}" &gt; ${SQLFILE}
mysql -u... -p... &lt; ${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 &gt; mydata.sql
mysql -u... -p... &lt; mydata.sql


Give it a Try !!!