[MDEV-28311] aria_block_size minimum size 8192 Created: 2022-04-14  Updated: 2022-05-15

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - Aria
Affects Version/s: 10.6
Fix Version/s: 10.6

Type: Bug Priority: Major
Reporter: MG Assignee: Michael Widenius
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Linux

Issue Links:
Duplicate
duplicates MDEV-26688 Can no longer modify aria_block_size Confirmed
Problem/Incident
is caused by MDEV-24312 master_host has 60 character limit, i... Closed
Relates
relates to MDEV-19805 Changing aria block size to 1024 will... Closed

 Description   

According to KB aria-system-variables/#aria_block_size and MDEV-19805 I should be able to set `aria_block_size` to 4096 but this causes an initialization failure:

ERROR 1071 (42000) at line 31 in file: 'scripts/mysql_system_tables.sql': Specified key was too long; max key length is 1233 bytes
 
ERROR 1071 (42000) at line 103 in file: 'scripts/mysql_system_tables.sql': Specified key was too long; max key length is 1233 bytes
 
ERROR 1071 (42000) at line 114 in file: 'scripts/mysql_system_tables.sql': Specified key was too long; max key length is 1233 bytes
 
ERROR 1071 (42000) at line 121 in file: 'scripts/mysql_system_tables.sql': Specified key was too long; max key length is 1233 bytes
 
ERROR 1071 (42000) at line 152 in file: 'scripts/mysql_system_tables.sql': Specified key was too long; max key length is 1233 bytes
 
ERROR 1071 (42000) at line 297 in file: 'scripts/mysql_system_tables.sql': Specified key was too long; max key length is 1233 bytes



 Comments   
Comment by Daniel Black [ 2022-04-14 ]

Corresponding tables/keys;

    mysql.db PRIMARY KEY /*Host */(Host,Db,User)
 
    roles_mapping UNIQUE (Host, User, Role)
 
    tables_priv ( PRIMARY KEY (Host,Db,User,Table_name))
 
    columns_priv (PRIMARY KEY (Host,Db,User,Table_name,Column_name) )
 
    procs_priv (PRIMARY KEY (Host,Db,User,Routine_name,Routine_type))
 
    proxies_priv ((Host,User,Proxied_host,Proxied_user))

Comment by MG [ 2022-04-14 ]

It is worth pointing out that I was indeed able to use --aria_block_size=16384 but it reduced performance of my test query while using an internal temporary table.

On the other hand, increasing tmp_memory_table_size and max_heap_table_size to 64M each improved performance about 2x for the test query.

I did not recompile mariadb with myisam as the internal temp engine.

On a "cold" start (did not disable buffer pool loading), and with defaults for temp table settings, perf bubbled up:

_ma_get_pack_key (~10%)
 
ha_key_cmp (~6%)
 
_ma_seq_search (~2%)
 
my_strnncollsp_utf8mb4_general_ci (~3%)

When avoiding Tmp_table_on_disk by increasing tmp_memory_table_size and max_heap_table_size to 64M each the query takes half as long and perf shows:

   5.66%  mariadbd                  [.] my_strnncollsp_utf8mb4_general_ci
 
   5.66%  mariadbd                  [.] my_charlen_utf8mb4
 
   5.57%  mariadbd                  [.] my_charpos_mb
 
   4.27%  mariadbd                  [.] _ma_get_pack_key
 
   3.31%  mariadbd                  [.] rec_get_offsets_func
 
   3.25%  mariadbd                  [.] my_hash_sort_utf8mb4_nopad

Generated at Thu Feb 08 09:59:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.