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

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) ...