Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Incomplete
-
10.0.31-galera
Description
Today someone executed a big query in server.
It needed to Temporary table too complete.
After 3000 Seconds, It was still running and State was "copying to tmp table".
I checked OS status and I saw The query is eating Memory Cache and Disk space.
The tmp_table_size variable was 32MB and max_heap_table_size was 16MB
But it was very strange for me why MariaDB doesn't switch to use only Disk temporary (copying to tmp table on disk) and stuck to "copying to tmp table".
I attach two pictures about this happen.
Picture one is about Ram status at run time query and picture two is about Disk status at run time query.
Both are increasing.
At the end when I killed query, The Space was released (both Ram and Disk). You can see on pictures.
Update:
Table Structure: |
CREATE TABLE `posts` ( |
`auto_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, |
`id` bigint(20) unsigned NOT NULL, |
`text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL, |
`time` datetime NOT NULL, |
`user_id` bigint(20) unsigned NOT NULL, |
`original_post` bigint(20) unsigned DEFAULT NULL, |
`favorites_count` int(10) unsigned NOT NULL DEFAULT 0, |
`tags` varchar(50) DEFAULT NULL, |
`reposts_count` int(10) unsigned NOT NULL DEFAULT 0 , |
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), |
`lang` char(2) DEFAULT NULL, |
PRIMARY KEY (`id`), |
UNIQUE KEY `auto_id` (`auto_id`), |
KEY `user_id` (`user_id`), |
KEY `original_post` (`original_post`), |
KEY `favorites_count` (`favorites_count`), |
KEY `reposts_count` (`reposts_count`), |
KEY `time` (`time`), |
KEY `tags` (`tags`), |
KEY `lang` (`lang`), |
CONSTRAINT `__posts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`), |
CONSTRAINT `__posts_ibfk_2` FOREIGN KEY (`original_post`) REFERENCES `posts` (`id`) |
) ENGINE=InnoDB AUTO_INCREMENT=2066309126 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 |
Table Index:
I attache output of show index in table_name
This was my query:
Select * from posts where auto_id > 22000000 order by auto_id limit 100 |
