Skip navigation links

MySQL Forums :: Performance :: Hanging on copying to tmp table


Advanced Search

Hanging on copying to tmp table
Posted by: Brian Dalby ()
Date: April 23, 2012 06:55AM

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?

Options: ReplyQuote


Subject Views Written By Posted
Hanging on copying to tmp table 4464 Brian Dalby 04/23/2012 06:55AM
Re: Hanging on copying to tmp table 2382 Rick James 04/24/2012 10:16AM
Re: Hanging on copying to tmp table 1790 Brian Dalby 04/24/2012 02:44PM
Re: Hanging on copying to tmp table 1580 Brian Dalby 04/25/2012 01:08PM
Re: Hanging on copying to tmp table 1527 Rick James 04/25/2012 08:43PM
Re: Hanging on copying to tmp table 1510 Brian Dalby 04/26/2012 12:35AM
Re: Hanging on copying to tmp table 1745 Brian Dalby 04/26/2012 02:17AM
Re: Hanging on copying to tmp table 1495 Seweryn Ożóg 04/26/2012 03:28PM
Re: Hanging on copying to tmp table 1169 Brian Dalby 04/27/2012 07:02AM
Re: Hanging on copying to tmp table 1433 Rick James 04/28/2012 01:37PM
Re: Hanging on copying to tmp table 1422 Brian Dalby 04/28/2012 02:00PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.