Hanging on copying to tmp table
Hi all,
I have a database with a lot of traffic on one single table.
Sometimes it hangs on "copying to tmp table", and also "sending data", but mostly on "copying...".
It does not happen everyday, and I have not found a way to recreate it, so I cannot explain why it happens.
I have the following information:
SHOW CREATE TABLE test_testcase;
'test_testcase', 'CREATE TABLE `test_testcase` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(200) NOT NULL,
`status_id` int(10) unsigned DEFAULT NULL,
`steps` text,
`description` text,
`priority_id` int(10) unsigned DEFAULT NULL,
`lft` int(10) unsigned NOT NULL,
`rgt` int(10) unsigned NOT NULL,
`known_error` varchar(100) DEFAULT NULL,
`dependent_from_id` int(10) unsigned DEFAULT NULL,
`is_folder` tinyint(4) NOT NULL DEFAULT '0',
`latest_user_edit` varchar(45) NOT NULL,
`bug` text,
`creation_time` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
PRIMARY KEY (`id`),
KEY `indx_testcase_rgt` (`rgt`),
KEY `indx_testcase_lft` (`lft`),
KEY `tst_testcase_status` (`status_id`),
KEY `tst_testcase_priority` (`priority_id`),
KEY `tst_testcase_dependencies` (`dependent_from_id`),
CONSTRAINT `tst_testcase_priority` FOREIGN KEY (`priority_id`) REFERENCES `test_priority` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION,
CONSTRAINT `tst_testcase_status` FOREIGN KEY (`status_id`) REFERENCES `test_status` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=5394 DEFAULT CHARSET=latin1'
SHOW TABLE STATUS LIKE 'test_testcase';
'test_testcase', 'InnoDB', 10, 'Compact', 8745, 301, 2637824, 0, 4521984, 18874368, 5394, '2012-02-13 12:05:27', '', '', 'latin1_swedish_ci', , '', ''
EXPLAIN
SELECT SQL_CACHE
`node`.`is_folder`
, `node`.`id`
, `node`.`name`
, (COUNT(`parent`.`name`)-1) AS `level`
, `node`.`lft`
, `node`.`rgt`
, `node`.`status_id`
, `node`.`bug`
, `node`.`known_error`
FROM `test_testcase` AS `node`, `test_testcase` AS `parent`
WHERE `node`.`lft` BETWEEN `parent`.`lft` AND `parent`.`rgt`
GROUP BY `node`.`lft`
ORDER BY `node`.`lft`;
1, 'SIMPLE', 'node', 'ALL', 'indx_testcase_lft', '', '', '', 8745, 'Using temporary; Using filesort'
1, 'SIMPLE', 'parent', 'ALL', 'indx_testcase_rgt,indx_testcase_lft', '', '', '', 8745, 'Range checked for each record (index map: 0x6)'
SHOW VARIABLES LIKE '%buffer%';
'bulk_insert_buffer_size', '8388608'
'innodb_buffer_pool_size', '2621440000'
'innodb_log_buffer_size', '1048576'
'join_buffer_size', '8388608'
'key_buffer_size', '33554432'
'myisam_sort_buffer_size', '8388608'
'net_buffer_length', '16384'
'preload_buffer_size', '32768'
'read_buffer_size', '2097152'
'read_rnd_buffer_size', '16777216'
'sort_buffer_size', '8388608'
'sql_buffer_result', 'OFF'
It makes everything hanging and perfomance becomes very slow, can anyone help with a suggestion to solve this problem?