4

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:

query performance since program start

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?): query performance over one day

2 Answers 2

-2

innoDB

is bad for dml statement, #MyISAM is better. Innodb is try to commit all INSERTing data to disk and returns success after it pined.

when you need a big insert data you can avoid auto-commit=0 to get faster. or you can check this for hint.

-3

Your tables do have several indexes to update, did you remember to run a optimize table from time to time?

/usr/bin/mysqlcheck -o --auto-repair MyDatabaseName

This will optimize all tables and indexes in the Database MyDatabaseName.

Depending on how your data looks this might help with INSERT and SELECT speed. I guess since you have tried everything you have been going through all of the system optimization. If there is really only one process updating the databases, there might be no greater benefit in using InnoDB and MyISAM might do better.
Usually when statements are slow on a MySQL server it is possible to find the bottleneck, the one spot that is your currently most limiting factor. Sometimes these show up in unsuspecting spots like, the linux IO scheduler (deadline or noop are the only sensible options for a DB server, based upon what drives and controllers are used), the query cache can slow execution down a lot since sometimes management of the cache by far outweighs its usefulness (flush frequently or disable), nameresolution enabled and DNS server slow... just to name some popular ones.
My personal check list:

RAM (enough free/cache RAM availabele, sensible use of RAM by MySQL (mysql-tuning.sh) ) SWAP should only contain "unused data and programs", no active transfer to or from swap should ever occur during normal operations!

CPU (load less than cores, no single core all the time on 0% idel => multitreading/locking problem, all CPU usage high (low idle %) needs faster CPU or better optimizing)

IOload / IOwait if this has a noticable high value your storage system or network are the most likely culprits (DB volume on a RAID 5 or RAID 6 volume, lame NAS, stupid SAN setup, etc)

Number of queries, Number of connections, new connections per sec,... anything related to SQL code executed (locking, transaction isolation) or configuration of MySQL.

High amount of new connections per minute con be a real problem due to running out of TCP sockets...

1
  • 2
    Running optimize on InnoDB tables is basically useless unless you have a lot of gaps (and even then), and it won't speedup your indexes. mysql-tuning.sh is a script unmaintained since 2009 github.com/Eyepea/vicidial/blob/master/extras/mysql-tuning.sh . Your recommendations will hurt his performance greatly.
    – jynus
    Aug 27, 2014 at 0:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.