# Mysql – poor and/or unreliable InnoDB performance

innodbMySQLperformanceperformance-tuningquery-performance

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
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_buffer_pool_size=12G
innodb_log_file_size=1600M
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=0

key_buffer_size     = 1024M
max_allowed_packet  = 16M

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?):