[Maia-users] database issues

[Kurt Buff]

Karl Latiss wrote:
> 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.latiss at atvert.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.

This machine is dedicated to Maia, so mysql isn't shared. Can I guage from the following my.cnf settings that my default table type is InnoDB?

[client]
port            = 3306
socket          = /tmp/mysql.sock
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
interactive-timeout = 86400
wait-timeout = 86400
key_buffer = 16M
max_allowed_packet = 10M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
# skip-networking
log-bin=mysql-bin
server-id = 1
innodb_data_home_dir = /var/db/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/db/mysql/
innodb_log_arch_dir = /var/db/mysql/
innodb_buffer_pool_size = 128M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 32M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 150
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout = 86400