I'm reading about adaptive hash indexes in MariaDB. It is created in memory, so it will be logically to know how much memory usually used for the index table.

For example, my local development database has only 2GB of RAM. Is it possible theoretically that adaptive index table will use > 50% of total memory?

I tried to search how much memory is usually used for AHI. But here is no info in the internet about memory and how to limit it. The only info I found is innodb-adaptive-hash-index-parts. However here is no explanation about what means a specific partition and what is size of it?

1 Answer 1


The main cache in RAM is innodb_buffer_pool. On a tiny 2GB machine, that should be limited to maybe 500M. And some other settings should be lowered, not raised.

It is probably best to let the engine decide how much to allocate for it.

And I doubt seriously that changing the default setting (8) for innodb_adaptive_hash_index_parts will matter much.

MariaDB shows Innodb_mem_adaptive_hash in the BUFFER POOL AND MEMORY section of the SHOW ENGINE INNODB STATUS; output. Also in SHOW GLOBAL STATUS;

  • unfortunately, here is no variable Innodb_mem_adaptive_hash in MySQL v10.4 :( (version that I'm using)
    – rzlvmp
    Jul 26 at 3:25
  • "10.4" is a MariaDB number, not MySQL. What does SELECT @@version; say?
    – Rick James
    Jul 26 at 5:09
  • Sorry, didn't know that this will be matter, but yes, I'm using MariaDB.
    – rzlvmp
    Jul 26 at 5:56
  • Oops, I missed the fine print: In MariaDB 5.5 and MariaDB 10.1, this system variable is present in XtraDB. In MariaDB 10.2, MariaDB 10.3, and MariaDB 10.4, this system variable is not present. In MariaDB 10.5, this system variable was reintroduced. -- mariadb.com/kb/en/innodb-status-variables/…
    – Rick James
    Jul 26 at 6:57
  • MariaDB 10.4, this system variable is not present ← this. So, the question how to get indexes memory size is still open...
    – rzlvmp
    Jul 26 at 8:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.