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.
