[MDEV-5527] too slow update on engine=memory/index=hash table Created: 2014-01-14  Updated: 2014-01-17  Resolved: 2014-01-17

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.33a
Fix Version/s: None

Type: Bug Priority: Major
Reporter: tem (Inactive) Assignee: Axel Schwenke
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

OS: FreeBSD 9.1-RELEASE-p4 amd64
CPU: Intel(R) Xeon(R) CPU E5-2650 0 @ 2.00GHz
real memory = 137455730688 (131088 MB)

  1. in my.cnf
    tmpdir=/var/tmp_md
  1. is created as
    /sbin/mdmfs -S -s 4096M -w mysql:mysql -p 755 -o noatime md1 /var/tmp_md

Attachments: File memslow.sql     File memslow4.log     File memslow4.sql    

 Description   

it looks like code for aria/hash index is rewritten, while the same for memory|myisam /hash is the same not so good.

tests are made for tables of:
engine = memory, table names are ttmp_mem_*
engine = aria, while the tables are actually on memdisk: ttmp_aria_*
engine = myisam, while tables are actually on memdis: ttmp_myisam_*
engine = aria, tables on SSD disk: tmp_aria_*
engine = myisam, tables on SSD disk: tmp_myisam_*

so update of randomly filled table of ~0.5mln rows, which affect those index , takes different times:
ttmp_mem_btree: 0.30 sec
ttmp_mem_hash: 38.28 sec # THAT IS TIME I SUPPOSE TO BE A BUG
ttmp_myisam_hash: 38.30 sec
ttmp_myisam_btree: 1.30 sec
ttmp_aria_hash: 1.22 sec # BECAUSE OF THIS
ttmp_aria_btree: 1.63 sec
tmp_myisam_hash: 38.09 sec
tmp_myisam_btree: 1.14 sec
tmp_aria_hash: 1.37 sec
tmp_aria_btree: 1.40 sec

sql-script to create+update tables in attach

 Comments   
Comment by Elena Stepanova [ 2014-01-14 ]

Axel,

Could you please review the provided information and proceed as needed?

Thanks.

Comment by Axel Schwenke [ 2014-01-17 ]

This is not a bug.

1. The Aria engine does not have hash indexes. The requested hash index is silently converted to btree.

2. The behavior seen with the Memory table and the hash index is expected, because both the column type and the initial values in the indexed column will cause heavy hash collisions.

Please see the attached files memslow4.sql and memslow4.log (with execution times and inline comments) for an example how different value distribution affects hash index efficiency.

Comment by Axel Schwenke [ 2014-01-17 ]

demonstration of hash index behavior

Generated at Thu Feb 08 07:05:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.