MySQL InnoDB Engine Restart

Question

I have a very large table with around 1M records. Due to bad performance, I have optimized the queries and needed to change the index.

I changed it using ALTER, now I am really not sure how this works in InnoDB. Do I need to restart MySQL server? If I need to restart MySQL server, how do I keep data integrity between tables (so that I don't miss data which is there in memory and did not get written to DB)?

I Googled and found that in the case of MySQL restart, I need to use global variable innodb_fast_shutdown -- what does it do when I set it and what if I don't? It is not very clear.

I am new to MySQL area with InnoDB. Any help is really appreciated.

Answer 1

So changed it using ALTER, now i am really not sure about how this works in innodb?

You are saying you added the index with ALTER TABLE ... ADD INDEX ... (or ADD KEY -- they are two ways of asking for exactly the same thing) presumably?

Once the ALTER TABLE finishes executing and your mysql> prompt returns, there is nothing else needed. At that point, the table has its new index and the index is fully populated.

You're done, and there is no need to restart the server.

Since you mentioned it, I'll also try to help clear up your misconceptions about innodb_fast_shutdown and the memory/disk divide in InnoDB.

InnoDB makes a one-time request for a block of memory the size of innodb_buffer_pool_size from the operating system when MySQL server starts up, in this example from the MySQL error log from one of my test servers:

130829 11:27:30 InnoDB: Initializing buffer pool, size = 4.0G

This is where InnoDB stores table and index data in memory, and the best performance is when this pool is large enough for all of your data and indexes. When rows are read, the pages from the tablespace files are read into the buffer pool first, then data extracted from there. If changes are made, the changes are written to the in-memory copies of table data and indexes in the buffer pool, and eventually they are flushed to disk. Pages in the pool are either "clean" -- meaning they are identical to what's on disk, because they've not been changed since they were loaded, or if changed, the changes have already been written to disk -- or "dirty" meaning they do not match what is on disk.

However, InnoDB is ACID-compliant -- and this could not be true if it only wrote the changes in memory and the changes were not persisted immediately somewhere prior to the in-memory changes even being made ... and that "somewhere" is the redo log -- on disk -- that stores what changes to be made in memory, immediately, in a format that allows this operation to be much faster than updating the actual tablespace files themselves in real-time would be.

In turn, the innodb_fast_shutdown variable determines whether MySQL finishes up everything written to the redo log before shutdown -- or after it starts back up. It works fine, either way, but if you need to shut the server down faster, it's faster and perfectly safe to let it pick everything up later, no matter what changes you have made.

Importantly, I don't know what you have read, but in routine operations, you never need to mess with the value of innodb_fast_shutdown unless you are shutting down in preparation for doing an upgrade to your version of MySQL server (and then it is primarily a safety precaution). The data on disk is always consistent with what is in memory, either because the tablespace files are already consistent with the memory representation of the data, or because the pending changes to the tablespace files are safely stored in the redo log, where they will be properly processed when the server comes back online.

In the case of ALTER TABLE anything pending for the table prior to the ALTER would have already been take care of, since InnoDB typically rebuilds entire the table in response to this command, so the only possible "pending" changes would be DML that occurred after the ALTER.