Transactions and locks for innodb quarantine database

[Werner Detter]

Am 19.07.12 03:06, schrieb Thomas Johnson:
> I'm playing around with innodb tables in MySQL 5.5 (and testing 5.6).
> I've set things up and have been feeding real data in, and right now
> I've got about 3.7 million addresses in maddr, 7.9 million rows in
> msgrcpt, and 9 million rows in msgs.
> 
> The quarantine table is partitioned, with one partition per ISO week.
> 
> I'm seeing periods where I suspect innodb transaction locks are
> causing things to hang for a bit - I've seen some timeouts in the
> amavis log where "gen_mail_id FAILED", and every once in a while I'm
> seeing simple inserts into maddr taking 10 seconds or longer.
> 
> I'm new to mysql - this is the first time I've really hit it hard, I think.
> 
> I'm wondering if anybody else out there who is using mysql for this
> could share their /etc/my.cnf file?  Or the output of "SHOW GLOBAL
> VARIABLES"?
> 
> I'm trying to figure out if I need to change the tx_isolation from
> read-committed to something else, or if I need to tune something in
> innodb.

InnoDB mostly needs tuning as InnoDB is heavily based on memory and other
settings. I don't use InnoDB for amavisd-new but do administrate a lot of
MySQL-Servers with large InnoDB-Tables.

The probably most important Variable for InnoDB is "innodb_buffer_pool_size".
Set it to an apropriate value. Furthermore "innodb_flush_log_at_trx_commit"
is also important - but depends on your requirements for your transaction
level - if you can live with a lost second set it to "0". Also per default
it's a good idea to activate a tablespace per table ("innodb_file_per_table")
as you can reduce the size by using "OPTIMIZE" statement which you can't with
just a global tablespace. Also make your you set your InnoDB-Logfiles to an
appropriate size ("innodb_log_file_size") as the default is 5MB which is
really small. You could also use the ha_innodb_plugin.so instead of the built
in innodb_plugin. As you can see there are some many performance tweaks for
InnoDB and MySQL in general - everybody has to find out what's best for his
database and workload :)

Cheers,
Werner