How do I make the indexes be automatically generated in RAM?

[kenchucky]

When the MySQL process starts, I want it to automatically read the .MYD and .FRM files and use them to generate an index in RAM, and when an insert/update/delete is made I want it to update the index that is in RAM.

I don't want there to be physical .MYI files on the hard disk.

How can I do this?

[kenchucky]

Bump

[fenway]

You can't... maybe with a MEMORY/HEAP table.

[kenchucky]

I just want this to be done with the indexes, since all of the data they need is in the .myd files.

I can't use memory tables because if there is a power outage data will be lost or corrupted.

[aschk]

Why do you want to do this? There seems to be no logical reason. Also bear in mind that tables/indexes are loaded into RAM for processing anyway

[kenchucky]

Because there is no reason that they should need to be stored on disk since all of the data they need is already stored on disk.

Just like I wouldn't want the query cache to be stored on disk. There is no reason for it to be.

[fenway]

Because there is no reason that they should need to be stored on disk since all of the data they need is already stored on disk.

Just like I wouldn't want the query cache to be stored on disk. There is no reason for it to be.

Huh?

[kenchucky]

Is there any reason at all for the indexes to waste disk I/O access every time a table is updated, and hard drive storage space?

If there is not, please tell me how to stop it from happening.

They are loaded fully into RAM at startup, and all of the information that is needed to generate them is already contained in the .myd and .frm files.

What possible reason could there be to store this information on disk and waste I/O updating it constantly when it is already stored just fine in the .myd and .frm files??

[fenway]

The index has to be loaded initially from a file -- you don't want to generate this constantly.

 

Whenever you change a indexed field, it has be updated (either memory or disk)... and if the server dies, it's quite expensive to build this from scatch (try running ALTER TABLE DROP then ADD index on a big table).  So it updates both.  Very little I/O here... besides, I'd imagine these are write-cached anyway.

 

Assuming you don't write to the table, the index will generally stay in memory anyway (assuming reasonable page buffer sizes, optimized tables, yada yada) -- mysql will handle this for you, don't worry about it.

[kenchucky]

I thought that whenever any kind of change is made to a table that effects an indexed row, it has to update the .myi files also instead of just the .myd files?

I really want to cut down on unneeded I/O and the time it would take to generate the indexes on startup would not be significant at all. Just a few seconds of time once every few months.

 

Also, all of the indexes are loaded at startup anyways, so it's not like it will use more RAM to do this right?

[fenway]

I thought that whenever any kind of change is made to a table that effects an indexed row, it has to update the .myi files also instead of just the .myd files?

Yes, but that doesn't mean it happens instantly... and even if it did, it would be minor.

 

I really want to cut down on unneeded I/O and the time it would take to generate the indexes on startup would not be significant at all. Just a few seconds of time once every few months.

A few seconds per MONTH? Your'e kidding, right? Try rebuilding an index on a table with a few million rows.  We're talking minutes, here, each time!!!

 

Also, all of the indexes are loaded at startup anyways, so it's not like it will use more RAM to do this right?

Says who? They aren't loaded until you need them... and there's only a certain amount of space allocated for indexes in memory.  So which ones are loaded depends on which tables you queries you decide to query, and how often relative to each other.  Yes, there are way to coerce mysql to loading some first (read this article), but the storage engines are usually quite good about this.  Then there's the query cache too, which operates on a totally different level.

 

To summarize -- don't bother.

[kenchucky]

Yes, but that doesn't mean it happens instantly... and even if it did, it would be minor.

What happens if the computer is shut down before it's written then?

 

A few seconds per MONTH? Your'e kidding, right? Try rebuilding an index on a table with a few million rows.  We're talking minutes, here, each time!!!

Yes. The indexes take a few seconds per table to generate, and MySQL should only need to be restarted once in a few months.

 

 

 

Says who? They aren't loaded until you need them... and there's only a certain amount of space allocated for indexes in memory.  So which ones are loaded depends on which tables you queries you decide to query, and how often relative to each other.  Yes, there are way to coerce mysql to loading some first (read this article), but the storage engines are usually quite good about this.  Then there's the query cache too, which operates on a totally different level.

Sorry. I thought someone said earlier that the indexes are loaded at startup. They're not? Well then that's obviously a huge performance problem for selects isn't it? So all the more reason, I think.

 

 

So will you please tell me how? I'll pay you if you want, if you have a paypal account.

 

I feel very strongly even after reading what you have all wrote that eliminating the need for I/O activity from indexes will help a lot.

 

Data will still need written to .myd files, but if all my queries make use of indexes then selects will be much faster I think, since the bottleneck will be CPU instead of hard drive.

[aschk]

Ken, MySQL is a professional product, designed and built by professionals. They already squeeze as much out of it as they can, while giving you an ACID environment (or close enough).

 

There is one good reason (at least) NOT to load the whole index into RAM at startup, and that's the size of the index. I'm working with a table at the minute which has over 1 million rows and an index the size of tibet (217MB). Now I can tell you, on production systems you DON'T want an index that size consuming your most precious resource (ram). And that's just for 1 table...

 

All in all, you can't/shouldn't do it. MySQL will do the thinking for you

Otherwise if you're dissatisfied with the performance you are getting check your logs, slow queries, buffers, caching, environmental variables, and last but not least if you're still unsatisfied write your own database engine, LOL.

 

In summary, let MySQL do it's thing and unless you want to write your own engine I suggest you leave it to the pros.

[kenchucky]

Thank you for your concern. I have more then enough RAM for the indexes, though, and I think that having selects bottlenecked by RAM/CPU instead of HDD will be worth, so please tell me how, that is all I'm asking.

[fenway]

Yes, but that doesn't mean it happens instantly... and even if it did, it would be minor.

What happens if the computer is shut down before it's written then?

It's write-cached... this is a general I/O technique, not specific to mysql... don't worry about it, unless your server crashes, you'll be fine (hence InnoDB has a proper binary log and it really never matters).

 

A few seconds per MONTH? Your'e kidding, right? Try rebuilding an index on a table with a few million rows.  We're talking minutes, here, each time!!!

Yes. The indexes take a few seconds per table to generate, and MySQL should only need to be restarted once in a few months.

You said this before... how big are you tables? I find it hard to believe we're talking about seconds.  And "should only" is wishful thinking.

 

Says who? They aren't loaded until you need them... and there's only a certain amount of space allocated for indexes in memory.  So which ones are loaded depends on which tables you queries you decide to query, and how often relative to each other.  Yes, there are way to coerce mysql to loading some first (read this article), but the storage engines are usually quite good about this.  Then there's the query cache too, which operates on a totally different level.

Sorry. I thought someone said earlier that the indexes are loaded at startup. They're not? Well then that's obviously a huge performance problem for selects isn't it? So all the more reason, I think.

No, it's a huge performance problem to load the indexes for 300K+ tables if you haven't issued a single query yet (for example).  Did you read about the LOAD CACHE INTO INDEX in the link I provided?

 

You definitely don't have enough RAM for *all* of the indexes all the time -- no one does (have you tuned your server variables to make this true?). And besides, you can't possibly be grabbing all of the data out of the index anyway -- just a few fields perhaps, and maybe for some covering indexes -- but the rest of the data is on the disk, so you'll have to load it regardless to get the "actual" data.  An index is basically just a hash (fine, InnoDB is a bit different, but still).  The MySQL Performance Blog will give you hundreds of examples that are disk-bound vs. memory-bound, independent of the server set-up.  That's just how it is.

 

Basically, the answer is that you can't (not only that you shouldn't) -- you can pre-load indexes on-demand (though I think it's per table, not per key) manually, but that's about it. 

 

Moreoever, this shouldn't concern you... I highly doubt that you're referring to a specific query that you're trying to optimize -- and those can be dealt with on a case-by-case basis.

 

You don't have to take our word for it... but that fact that mysql doesn't have such an option should suggest that it's not possible because it's undesirable and inefficient, not that it's some big secret that you have to pay for.