Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13507

The query ate Memory cache

    XMLWordPrintable

Details

    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
      

      Attachments

        1. index.csv
          0.6 kB
        2. 2.png
          2.png
          46 kB
        3. 1.png
          1.png
          71 kB

        Activity

          People

            Unassigned Unassigned
            HamoonDBA Hamoon Mohammadian Pour
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.