Transactions and locks for innodb quarantine database

Thomas Johnson thomas.a.johnson at gmail.com
Thu Jul 19 03:06:40 CEST 2012


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.

Sorry for the vagueness in here - I'm just starting to try to figure
out what is going on, and if someone already went through the process
of tuning a mysql server for this purpose, I thought I'd just start
off by asking.


More information about the amavis-users mailing list