Sergei Golubchik added a comment - 2021-04-28 10:33 Yes, that's what I suggested above. MyISAM/Aria also take advisory locks to prevent data corruption due to misconfiguration (starting multiple instances of the server on the same data files). This protection is disabled if the server is started with --skip-external-locking. It would be logical if InnoDB would also obey this option.

naox added a comment - 2021-04-28 11:02 - edited I'm just reminding that huge number of locks is only present if table_open_cache is set to high number (100k). When I've lowered it problem went away. So it can't be locks used for preventing multiple mysqld instances working on same datadir. Huge number of locks hurts performance of other software running on same machine if it checks for those regulary (seen as "sys" load). So having table_open_cache set to high number currently adversely affect perfomrnace if machine is running anything other than mariadb.

Marko Mäkelä added a comment - 2021-07-23 15:40 serg , I applied the change that you suggested. Please review. Note: Contrary to your claim, the InnoDB function that creates and opens files is named in a similar way to the Microsoft Windows function CreateFile() . Thus, the advisory locking affects all InnoDB files, not only newly created tables. I ran the test that elenst helpfully provided. With the following invocation, I am seeing 422 entries in /proc/locks ; without the extra parameter, there would be just 1 entry. /mtr --mysqld=--external-locking innodb.locks I would not add the test to the regression test suite. The 1 offending entry comes from the Aria storage engine: 10.2 173e562dc2bd339de32d17de73b720e7ca863ff2 #1 0x000055555624aa82 in my_lock (fd=13, locktype=locktype@entry=1, start=start@entry=0, length=length@entry=0, MyFlags=MyFlags@entry=416) at /mariadb/10.2o/mysys/my_lock.c:175 #2 0x0000555556123686 in lock_control_file ( name=name@entry=0x7fffffffd5b0 "/dev/shm/10.2/mysql-test/var/mysqld.1/data/aria_log_control") at /mariadb/10.2o/storage/maria/ma_control_file.c:235 #3 0x000055555612321e in ma_control_file_open ( create_if_missing=<optimized out>, print_error=1 '\001') at /mariadb/10.2o/storage/maria/ma_control_file.c:314 #4 0x00005555561207ae in ha_maria_init (p=<optimized out>) at /mariadb/10.2o/storage/maria/ha_maria.cc:3603

Sergei Golubchik added a comment - 2021-07-26 16:59 I think I'd prefer InnoDB to invoke my_lock() for locking instead. But your patch is ok too.

Marko Mäkelä added a comment - 2021-07-27 07:13 my_lock() does more than we might want in low-level InnoDB code. Note: the default behavior will be changed. Because --skip-external-locking is activated by default, InnoDB will no longer acquire advisory file locks.