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

Performance regression in fil_space_t::try_to_close() introduced in MDEV-23855

    XMLWordPrintable

Details

    Description

      Several operations got much slower starting from 10.5.8 and apparently related to file opening/closing in handler.cc when reaching innodb_open_files limit.

      The more tables you have in the instance the more visible, with some tens of thousands of tables it gets problematic.

      SHOW TABLE STATUS FROM db gets hundreds of times slower

      mysqldump on 600 schemas x 223 empty tables goes from 2:45 minutes to 27 minutes

      Increasing innodb_open_files (with all the capping limits open_files_limit, os user limits, system limits) helps but in some case means keeping hundreds of thousands of files open.

      The change was introduced in CS 10.5.8

      To reproduce you need many thousands of tables(at least higher than innodb_open_files) and execute mysqldump or SHOW TABLE STATUS FROM until the limit is reached, testing with and without increasing innodb_open_files will show the difference.

      I have found this if condition in handler.cc getting slower, the body is not even executed:

        if (unlikely((error=open(name,mode,test_if_locked))))
        {
      	  ms = duration_cast<milliseconds>(system_clock::now().time_since_epoch()).count();
      	  fprintf(stderr,"handler.cc:ha_open 3317 %" PRIu64 "\n", ms);
          if ((error == EACCES || error == EROFS) && mode == O_RDWR &&
      	(table->db_stat & HA_TRY_READ_ONLY))
          {
            table->db_stat|=HA_READ_ONLY;
            error=open(name,O_RDONLY,test_if_locked);
          }
        }
      

      That open() call goes to ha_heap.cc calling:

      int ha_heap::open(const char *name, int mode, uint test_if_locked) ...
      

      Attachments

        1. annotated-perf.txt
          14 kB
        2. show-global-variables.txt
          533 kB
        3. space_list.gdb
          0.3 kB
        4. test-MDEV-30775-10517.log
          2 kB
        5. test-MDEV-30775-10520.log
          3 kB

        Issue Links

          Activity

            People

              vlad.lesin Vladislav Lesin
              claudio.nanni Claudio Nanni
              Votes:
              1 Vote for this issue
              Watchers:
              11 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.