Re: Correctly setting mysql options for a huge DB with intense trafic

[Toni Shocker]

This is how CPU load looks like: http://img59.imageshack.us/img59/8837/cpuusage.png (green-normal, red-kernel, pink-software interrupts[software raid probably], gray-IO wait time)

On the other hand, I rechecked slow log this afternoon and got some hits... and keep getting some every couple of minutes. The strange part is those queries shouldn't be slow, so let's check few of them:

# Query_time: 2.616943  Lock_time: 0.000015 Rows_sent: 0  Rows_examined: 77
use world;
SET timestamp=1290868082;
DELETE FROM creature_respawn WHERE instance = '974';

creature_respawn is in the other database, has ~70k rows and looks like this:

CREATE TABLE IF NOT EXISTS `creature_respawn` (
  `guid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
  `respawntime` bigint(20) NOT NULL DEFAULT '0',
  `instance` mediumint(8) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`guid`,`instance`),
  KEY `instance` (`instance`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Grid Loading System';

# Query_time: 1.092830  Lock_time: 0.000026 Rows_sent: 0  Rows_examined: 0
SET timestamp=1290871896;
INSERT INTO character_spell_cooldown (guid,spell,item,time) VALUES (163510,8690,6948,1290873472), (163510,54401,6948,1290873472), (163510,54403,6948,1290873472), (163510,70195,6948,1290873472), (163510,75136,6948,1290873472);

has ~400k rows

CREATE TABLE IF NOT EXISTS `character_spell_cooldown` (
  `guid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier, Low part',
  `spell` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Spell Identifier',
  `item` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Item Identifier',
  `time` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`guid`,`spell`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LATER EDIT: Please keep in mind what I said, that slow logs recently started popping out of nowhere every couple of minutes (CPU usage looks ok constantly). Worldserver has a 5 hours uptime, if it were to be worldserver's fault it would have been at a bigger uptime, although htop shows 10h.



Edited 3 time(s). Last edit at 11/27/2010 01:59PM by Toni Shocker.