Geek Answers Handbook

UPDATE / INSERT takes a few seconds from time to time

I have a problem with really simple INSERT / UPDATES on my server. It takes more than a few seconds from time to time to complete a query like these:

2.1062s - INSERT INTO `transaction` SET `idUser` = 72, `currency` = 50, `amount` = '10', `action` = 'buyCoins'; 11.785s - UPDATE `user` SET `cash` = 10, `crystal` = 10, `expPoints` = 10, `energy` = 10 WHERE idUser = 72; 0.6296s - UPDATE `user` SET `lastEnergyUpdate` = CURRENT_TIMESTAMP WHERE idUser = 72;

It seems that the problem does not depend on a specific table. I do not have TRIGGERS on these tables.

Table Definitions:

 CREATE TABLE `user` ( `idUser` int(10) unsigned NOT NULL AUTO_INCREMENT, `expPoints` int(10) NOT NULL DEFAULT '0', `cash` int(10) NOT NULL DEFAULT '1000', `crystal` int(10) NOT NULL DEFAULT '10', `energy` int(4) NOT NULL DEFAULT '0', `name` varchar(50) DEFAULT NULL, `surname` varchar(50) DEFAULT NULL, `age` int(4) unsigned DEFAULT NULL, `sex` enum('men','women','unknown') DEFAULT NULL, `lastEnergyUpdate` timestamp NULL DEFAULT NULL, `lastLogin` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `insertDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`idUser`), UNIQUE KEY `serviceUnique` (`serviceName`,`serviceId`) ) ENGINE=InnoDB AUTO_INCREMENT=5333 DEFAULT CHARSET=utf8 CREATE TABLE `transaction` ( `idTransaction` int(10) NOT NULL AUTO_INCREMENT, `idUser` int(10) unsigned NOT NULL, `currency` enum('crystal','partnerCurrency','cash') DEFAULT NULL, `amount` int(5) NOT NULL, `action` enum('unlockPlace','buyExtra','collectReleased') NOT NULL, `insertDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`idTransaction`), KEY `fk_transaction_user1` (`idUser`), CONSTRAINT `fk_transaction_user1` FOREIGN KEY (`idUser`) REFERENCES `user` (`idUser`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=156329 DEFAULT CHARSET=utf8

On the same server I have more databases (~ 100), but nothing big. The dump of all databases is about 300 MB.

Mysqltunner Output:

  >> MySQLTuner 1.0.1 - Major Hayden < major@mhtx.net > >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.66-0ubuntu0.11.10.2-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 138M (Tables: 267) [--] Data in InnoDB tables: 170M (Tables: 327) [--] Data in MEMORY tables: 0B (Tables: 1) [!!] Total fragmented tables: 329 -------- Performance Metrics ------------------------------------------------- [--] Up for: 20h 45m 57s (558K q [7.468 qps], 58K conn, TX: 685M, RX: 98M) [--] Reads / Writes: 66% / 34% [--] Total buffers: 1.1G global + 6.0M per thread (150 max threads) [OK] Maximum possible memory usage: 2.0G (12% of installed RAM) [OK] Slow queries: 0% (54/558K) [OK] Highest usage of available connections: 6% (10/150) [OK] Key buffer size / total MyISAM indexes: 16.0M/8.8M [OK] Key buffer hit rate: 99.9% (245K cached / 258 reads) [OK] Query cache efficiency: 51.5% (176K cached / 342K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 6% (1K temp sorts / 19K sorts) [!!] Temporary tables created on disk: 34% (2K on disk / 8K total) [OK] Thread cache hit rate: 99% (10 created / 58K connections) [!!] Table cache hit rate: 16% (786 open / 4K opened) [OK] Open file limit used: 32% (714/2K) [OK] Table locks acquired immediately: 99% (329K immediate / 329K locks) [OK] InnoDB data size / buffer pool: 170.3M/512.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_cache gradually to avoid file descriptor limits Variables to adjust: table_cache (> 1024)

Of course, this only happens for ~ 1% of these requests (99% works fine), and then the hard drive is really bussy (13% - 20% on a server with 8 cores)

Should I keep increasing table_cache? Any other ideas what is going on? How can I improve it?

My MySQL Server 5.1.66. I tried to upgrade to 5.5.x, but this did not help me, so I refuse it.

+7
source share
1 answer

Of course, this only happens for ~ 1% of these requests (99% works fine), and then the hard drive is really bussy (13% - 20% on an 8-core server)

  • You are faced with a problem with your disk - I'm sure this happens when your innodb log is flushed to disk - I would experiment with a smaller one (for flushes more often) and with a larger log size (for less frequent flushes) - I expect that you will get more from more frequent flushes.

  • Another thing is that you eat your IO - launch iotop during this time if you can capture the criminal.

  • Otherwise, make sure the tmp, data, and log sections are physically separated if you can.

+1
source

More articles:


All Articles