0

firstly i'm newbie about internals databases, sorry if my question is dumb.

I have read in Stackoverflow(post link at final) that InnoDB clustered index is stored in disk and in memory.

And i have read that Clustered Index Leaf node have row data.

My question is: Based on the above affirmations, if my database has a huge table and such table has a clustered index, such clustered index will consume a large space of my memory?

If the answering for my question is Yes, isn't it a bad thing? isn't better just have secondary indexes in memory and access disk based on secondary indexes results?

https://stackoverflow.com/questions/68699150/is-innodb-index-constructed-in-cache-or-disk/71983900#71983900

2 Answers 2

1

Index and data pages are stored in the innodb buffer pool. Only the active part of these indexes and are loaded. The least recently used data/index is purged out of the innodb buffer pool (memory).

Create a primary key, which is therefore a clustered index, of the smallest, commonly searched, unique column(s) for the table.

Create the secondary indexes you need to improve the performance of queries.

Have enough innodb buffer pool to fit the active working set of data in memory. Look at innodb_buffer_pool_reads vs innodb_buffer_pool_read_requests in the global status to see what is coming off disk and what is still in memory.

1
  • Thank you Danblack. I will look for references about buffer + index.
    – Vitor C
    Commented Apr 24, 2022 at 1:08
1

You set the maximum amount of RAM used by the InnoDB buffer pool. It is the configuration option innodb_buffer_pool_size.

If the table's clustered index is larger than the buffer pool, clearly it can't all be held in RAM at the same time. The buffer pool uses a "least recently used" algorithm to store a subset of the pages for your clustered index. As your queries access different parts of the table, the buffer pool evicts some pages so it can load other pages from disk into RAM. This happens automatically as queries need pages from disk.

There are some other subtleties, because InnoDB really keeps two separate pools of memory. If a given page is accessed multiple times, it is moved to the pool where it has more weight and is not evicted so quickly. Pages that are only used once are more apt to be evicted. This is good because it prevents one mysqldump from blowing away your nice balance of the best pages in RAM.

Do you need the full table to fit in the buffer pool all at once? Probably not, unless you do a lot of table-scans.

A typical application makes frequent accesses only a small part of the table, and the rest of the table is queried less frequently. Over time, the subset of pages held in the buffer pool is likely to be the most valuable subset of pages for the queries you run most often. That is what is called the "working set" of data.

1
  • Thank you Bill. I thought that all indexes was in memory forever. Certainly I will search for references about buffer + index.
    – Vitor C
    Commented Apr 24, 2022 at 1:07

Your Answer

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

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