I recognize that there are a ton of posts like this, but after reading up on InnoDB performance problems, nothing I tried has helped.

I have a database containing four InnoDB tables. The largest table currently holds 700m rows. I'm running a java application that is consistently inserting into/updating the database through a single connection.

Initially query performance was ok, but I had noticed that occasionally it would become unbearably slow. As was pointed out to me in the answer to this question, the problem lies with the caching of the tables, and can be fixed by manually loading the .idb files into the server cache. However, not only does this feel wrong, but in addition to that it didn't work when I extended my MySQL server like so: I cloned the structure of the initial database 9 times, so that I would have ten tables with identical structure (but different data). Then I run my java application ten times, so that I have one connection per database, each consistently inserting or updating.

Since implementing the one database -> ten databases change, the manual loading of .idb files only fixes the slow query problem for a very short time, and queries occasionally take much much longer (peaks of several seconds, averages of 200ms for a 80 row insert where ~70 already exist and get ignored, and the other 10 trigger 3 updates each). That is even with using a collective amount of about 2% of the data accross the ten databases compared to the single database I used before, and the ten java applications collectively sending as many queries as the single application did before (in reality it's even less, because the queries take so long). The server itself also responds much slower when executing the program for ten databases.

my.cnf [mysqld] sub-section:

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

innodb_file_per_table
innodb_autoinc_lock_mode = 0
innodb_fast_shutdown=0

innodb_thread_concurrency=0
innodb_buffer_pool_size=12G
innodb_log_file_size=1600M
innodb_additional_mem_pool_size=1M
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=0

innodb_write_io_threads=20

key_buffer_size     = 1024M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8

myisam-recover         = BACKUP

query_cache_limit   = 1M
query_cache_size        = 16M

log_error = /var/log/mysql/error.log

expire_logs_days    = 10
max_binlog_size         = 100M

The server has 24G ram and 4 cpu cores. If the queries are super slow, cpu usage goes down to single digit percent.

Table structure:

CREATE TABLE `table1` (
  `table1_id` int(13) NOT NULL DEFAULT '0',
  `epoch` bigint(13) NOT NULL,
  ...
  [a few enum columns]
  PRIMARY KEY (`table1_id`),
  KEY `epoch` (`epoch`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `table2` (
  `performanceId` bigint(20) NOT NULL AUTO_INCREMENT,
  `table4_id` int(10) NOT NULL,
  `column3` int(3) NOT NULL,
  `column4` enum(...) NOT NULL,
  `table1_id` int(12) NOT NULL,
  PRIMARY KEY (`performanceId`),
  UNIQUE KEY `uniqueKey` (`table4_id`,`table1_id`),
  KEY `secondaryKey` (`table1_id`,`table4_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

CREATE TRIGGER `myTrigger` AFTER INSERT ON `table2`
 FOR EACH ROW BEGIN
 UPDATE table4
 SET counter=counter+1
 WHERE table4.table4_id=NEW.table4_id;
 UPDATE table4
 SET column5=100-column5
 WHERE table4.table4_id=NEW.table4_id AND counter >= 7;
END


CREATE TABLE `table3` (
  `performanceId` bigint(20) NOT NULL AUTO_INCREMENT,
  `table4_id` int(10) NOT NULL,
  `table1_id` int(12) NOT NULL,
  ...
  [about 20 more NOT NULL integers columns]
  PRIMARY KEY (`performanceId`),
  UNIQUE KEY `uniqueKey` (`table4_id`,`table1_id`),
  KEY `secondaryKey` (`table1_id`,`table4_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

CREATE TABLE `table4` (
  `table4_id` int(10) NOT NULL,
  `column2` bigint(13) NOT NULL DEFAULT '0',
  `column3` bigint(13) NOT NULL DEFAULT '0',
  `column4` varchar(30) NOT NULL DEFAULT '',
  `column5` bigint(13) NOT NULL DEFAULT '500',
  `counter` int(3) NOT NULL DEFAULT '1',
  PRIMARY KEY (`table4_id`),
  KEY `key1` (`column4`),
  KEY `key2` (`column2`),
  KEY `key3` (`column5`,`column3`),
  KEY `key4` (`counter`,`column3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

A typical cycle of queries from my application looks like this:

INSERT IGNORE INTO table1 ([columns]) VALUES ([10 rows])
INSERT IGNORE INTO table2 ([columns except primary]) VALUES ([~80 rows])
INSERT IGNORE INTO table3 ([columns except primary]) VALUES ([10 rows])
INSERT IGNORE INTO table4 (table4_id) VALUES ([~80rows])
UPDATE table4 SET column2=UNIX_TIMESTAMP()*1000, column3=0, counter=0, column5=[some value] WHERE table4_id=[some value]

Where about 15% of the table1 and table2 rows, 50-90% of the table3 and below 1% of the table4 rows actually get inserted and the rest is ignored because it already exists.

Edit: I forgot to add something about the data: As you can see, table2 and table3 each connect the entities of table1 and table4 via n:m relation. table4 grows by less than 50k rows a day, table1 grows by about 1m rows a day. each entity in table1 is has up to 10 rows in table2 and table3 (average 8). each entity in table4 has more and more rows in table2 and table3 as time goes by.

Edit: To show the unreliability of the queries, I have attached this figure, which displays the number of query cycles per 5min interval, since program start.

This is on the ten databases server, I have disabled 8 of the 10 connections and this is one of the remaining two:

This is the last 24h on the single database server (that one only has an innodb_buffer_pool_size of 6GB, maybe that's why it's more volatile?):