The activity of altering big tables are done in phases:
- Create a new table with required fields and indexes say in test DB (just structure)
- Dump the data from the existing table and load the same to the newly created table in test DB
- Now announce your downtime :)
- Swap the tables by renaming -
RENAME table ur_db.table_name to test.temp, test.table_name to ur_db.table_name, test.temp to test.table_name;
This is an atomic operation taking fraction of a second.
- Load the extra records to the newly created table ( the records that came after dumping and then loading). This step can be done before Step: 3 also once to reduce your downtime.
- And your system is back
Few notes:
- You need not hit information schema directly like this, try using
SHOW TABLE STATUS from db like 'table_name'
- The speed of alter table is more or less linked with the I/O speed. Last time when we ran a direct alter table(without the above steps), we had 40GB+ table size it took around 4hours. If your 20GB data is taking years, you are working on some outdated machine.
- Also drop of the unwanted indexes like
index_bslt_ondate, index_base_schedule_line_items_loan_base_schedule
as some other indexes has the left most column as the indexed column
Please let me know if you need any clarification on any of these steps.
Edit : A simple python script to automate the process https://github.com/georgecj11/hotswap_mysql_table.git
When you have a Primary Key with an auto_increment it will generate a new ID only if you insert a NULL value. If you set ID=4 in your INSERT
, the ID will be 4 so you'll not loose your ID during your "move" operation.
We don't have the "SEQUENCE" notion like in Oracle database so your "global ID" problem it's not so easy to do.
Maybe you can try something like this (but it'll add complications for just a 4 millions rows table)
Create a table used for generates your "Global ID", with one int filed auto_incremented:
CREATE TABLE test.sequence_table (next_id int primary key auto_increment);
When you want insert a new row in your child table:
Solution 1: With SELECT
in information_schema
BEGIN; -- Start a new Transaction to ensure consistency
INSERT INTO test.sequence_table values (NULL); -- Generate a new ID
SELECT @next_ID:=(auto_increment - 1) FROM information_schema.tables WHERE table_schema="test" AND table_name="sequence_table"; -- Here I use a MySQL Variable but you can store it in PHP or whatever
INSERT INTO child_table values (null, @next_ID, "Max", "SQL"); -- Use your variable
COMMIT; -- Wonderfull :)
Edit after ypercube comment:
Solution 2: With LAST_INSERT_ID()
BEGIN; -- Start a new Transaction to ensure consistency
INSERT INTO test.sequence_table values (NULL); -- Generate a new ID
SELECT @next_ID:=LAST_INSERT_ID(); -- Use of the MySQL function LAST_INSERT_ID()
INSERT INTO child_table values (null, @next_ID, "Max", "SQL"); -- Use your variable
COMMIT; -- Wonderfull :)
Best Answer
Beyond some point, waiting too long to
COMMIT
slows things down. Think of it as having to save more and more "undo" stuff.innodb_autoinc_lock_mode
only applies toAUTO_INCREMENT
.It seems that your real question is about high speed ingestion. Are you
INSERTing
one row at a time? You can get 10x speedup if you insert 100 at a time. That is, a singleINSERT
with 100 rows. Are you inserting from multiple connections? Etc. Here's one discussion on high speed ingestion.