mysql performance issues on large tables with insert

Question

We have a dedicated server with 8GB of RAM and PHP5.3 with MySQL 5.1

There are about 500 concurrent connections at maximum and each connection performs 1-2 SELECT queries on the smaller tables with the user data and then an INSERT in the big table transactions. The select queries don't take much and we added monitoring between each query to see the response time of each query and there were never problems.

We added tracking to our code and sometimes it results that some simple INSERT queries take 14-15 seconds. This query listed below sometimes takes 14 seconds, sometimes 6 seconds, sometimes 0.2 seconds or less. What could be the issue?

PHP code that sometimes returns these huge delays:

$starT = microtime(true);
echo '&timestampTS_02='.(microtime(true) - $startT);
mysqli_query($GLOBALS['con'],"INSERT INTO `transactions` (`id`,`data`) VALUES('id','some_data')") or die(mysqli_error($GLOBALS['con']));
echo '&timestampTS_03='.(microtime(true) - $startT);

The transactions table has around 2 million entries so far.

CREATE TABLE IF NOT EXISTS `transactions` (
  `id` int(11) NOT NULL,
  `data` varchar(1000) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Answer 1

InnoDB. (Because some of the things below need it.)

Batch inserts. Either by INSERT ... VALUES (1,2,3), (4,5,6), ... or with LOAD DATA. (10x performance boost when you insert 100 rows at once.) If you cannot 'batch', then see the 'staging table' suggestion, below.

innodb_flush_log_at_trx_commit=2 (not the default of 1) (This may be why your tests failed to show good performance.)

innodb_buffer_pool_size = 70% of available RAM. (About 5000M in your 8GB machine).

Upgrade from 5.1. (But this won't solve the problem alone.)

If possible, decrease the number of indexes on transactions.

For really high rate of ingestion, ping-pong two "staging tables" as discussed in High Speed Ingestion.