[MDEV-13507] The query ate Memory cache Created: 2017-08-12  Updated: 2017-11-05  Resolved: 2017-11-05

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Temporary
Affects Version/s: 10.0.31-galera
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Hamoon Mohammadian Pour Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: galera, mariadb, need_feedback, order-by-optimization, temporary

Attachments: PNG File 1.png     PNG File 2.png     File index.csv    

 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



 Comments   
Comment by Elena Stepanova [ 2017-08-12 ]

Please paste the query that was running, the output of

SHOW CREATE TABLE table_name;
 
SHOW INDEX IN table_name;

for every table involved in the query, and attach your cnf file(s).

Comment by Hamoon Mohammadian Pour [ 2017-08-27 ]

Hi again.
I updated my post

Comment by Daniel Black [ 2017-09-19 ]

Added order-by-optimization label as a temporary table was only created because it didn't use the auto_id index, the limit would of alluded this to a reasonable index choice however this isn't implemented. It would of used this key if it was a primary key. Use:

EXPLAIN SELECT...

to confirm this.

Odd that auto_id, and id are both bigint unique keys. If you don't have a good reason migrating to one would avoid this problem.

Comment by Elena Stepanova [ 2017-10-01 ]

For me, even with deliberately ignored auto_id index, it still does not create a temporary table:

MariaDB [test]> flush status;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> explain Select * from posts ignore index (auto_id) where auto_id > 2200 order by auto_id limit 100;
 
+------+-------------+-------+------+---------------+------+---------+------+---------+-----------------------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra                       |
 
+------+-------------+-------+------+---------------+------+---------+------+---------+-----------------------------+
 
|    1 | SIMPLE      | posts | ALL  | NULL          | NULL | NULL    | NULL | 1200530 | Using where; Using filesort |
 
+------+-------------+-------+------+---------------+------+---------+------+---------+-----------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]>  Select * from posts ignore index (auto_id) where auto_id > 2200 order by auto_id limit 100;
 
...
 
100 rows in set (1.10 sec)
 
 
 
MariaDB [test]> show status like '%tmp%';
 
+-------------------------+-------+
 
| Variable_name           | Value |
 
+-------------------------+-------+
 
| Created_tmp_disk_tables | 0     |
 
| Created_tmp_files       | 0     |
 
| Created_tmp_tables      | 0     |
 
| Handler_tmp_update      | 0     |
 
| Handler_tmp_write       | 0     |
 
| Rows_tmp_read           | 0     |
 
+-------------------------+-------+
 
6 rows in set (0.01 sec)

HamoonDBA, could you please paste the output of {{EXPLAIN SELECT ... }} as Daniel suggested above?
Do you see "copying to tmp table" every time when you execute this query?
Please also attach your cnf file(s).

Generated at Thu Feb 08 08:06:07 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.