Re: database issues

Karl Latiss wrote:
> On Tue, 2007-08-28 at 09:51 -0700, Kurt Buff wrote:
> > I have Maia running with postfix, etc., on a 68gb

> partition. I had the system keeping 30 days of
quarantine, 
> but have just today lowered that down to 25 days, and
am 
> currently running 'expire-quarantine-cache.pl', as disk
space 
> is almost full - about 3gb free at the moment. I'll be

> backing it down in 5 day increments to get quarantine
down to 
> 15 days, in the hopes of freeing disk space.
> > 
> > I'm wondering, though, if something is amiss with
the MySQL 
> installation on this box, as there are database files
in 
> /var/db/mysql that have date stamps well older than 30
days, 
> totalling around 26gbytes.
> > 
> Presuming InnoDB the only way to reduce the datafile
size is 
> to dump and
> restore. InnoDB data files do not automatically reduce
in 
> size when data
> is deleted.
> 
> -- 
> Karl Latiss <karl.latissatvert.com.au>
> Atvert Systems

I kind of suspected that, and yes, they are InnoDB. I've
found a 70gb USB2 drive that I can attach, and use as
scratch, so this is looking like soonish kind of thing,
unless my deletion of older quarantine items has helped.
I'll know more about that tomorrow, but I've been monitoring
disk space, and it's still dwindling - I'm down to 2.5gbytes
free as of this writing, down from 2.9gbytes when I asked
the question.

I think I'll start looking at how to do this - any suggested
URLs, in case my google-fu is weak?

Kurt
_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users

On Tue, 2007-08-28 at 18:29 -0700, Kurt Buff wrote:
> Karl Latiss wrote:
> > On Tue, 2007-08-28 at 09:51 -0700, Kurt Buff
wrote:
> > > I have Maia running with postfix, etc., on a
68gb 
> > partition. I had the system keeping 30 days of
quarantine, 
> > but have just today lowered that down to 25 days,
and am 
> > currently running 'expire-quarantine-cache.pl', as
disk space 
> > is almost full - about 3gb free at the moment.
I'll be 
> > backing it down in 5 day increments to get
quarantine down to 
> > 15 days, in the hopes of freeing disk space.
> > > 
> > > I'm wondering, though, if something is amiss
with the MySQL 
> > installation on this box, as there are database
files in 
> > /var/db/mysql that have date stamps well older
than 30 days, 
> > totalling around 26gbytes.
> > > 
> > Presuming InnoDB the only way to reduce the
datafile size is 
> > to dump and
> > restore. InnoDB data files do not automatically
reduce in 
> > size when data
> > is deleted.
> > 
> > -- 
> > Karl Latiss <karl.latissatvert.com.au>
> > Atvert Systems
> 
> I kind of suspected that, and yes, they are InnoDB.
I've found a 70gb USB2 drive that I can attach, and use as
scratch, so this is looking like soonish kind of thing,
unless my deletion of older quarantine items has helped.
I'll know more about that tomorrow, but I've been monitoring
disk space, and it's still dwindling - I'm down to 2.5gbytes
free as of this writing, down from 2.9gbytes when I asked
the question.
> 
> I think I'll start looking at how to do this - any
suggested URLs, in case my google-fu is weak?
> 
I just dump and restore:

mysqldump -Q --add-drop-table -u <username> -p -h
<hostname> <dbname> >
dbname.sql
drop database <dbname>
create database <dbname>
mysql -u <username> -p -h <hostname>
<dbname> < dbname.sql

This assumes InnoDB is your default table type.

The drop removes the datafiles and create then creates them.
Keep in
mind that if you have other InnoDB based databases using
this same mysql
server then the data files will not be removed and so no
space will be
saved. If you do share the database server it's probably
worth looking
at using the innodb_file_per_table setting.

Karl.

_______________________________________________
Maia-users mailing list
Maia-usersrenaissoft.com
http://www.renaissoft.com/mailman/listinfo/maia-users