For starters, I would not touch the buffer sizes just yet. The sizes youhave in the question are monstrously too big.
Here is another observation: You have BLOB data. Ouch, your temp table is going to eat space rather quickly. You could do somehting like this:
Create a 32GB RAM Disk called /var/tmpfs by adding this line to /etc/fstab
none /var/tmpfs tmpfs defaults,size=32g 1 2
Next, create a folder called /mysqltmp and mount the RAM disk on it
mkdir /mysqltmp
chown mysql:mysql /mysqltmp
mount /mysqltmp /var/tmpfs
Add this to my.cnf and restart mysql
[mysqld]
tmpdir=/mysqltmp
Now, any tmp table made via DDL lands in the RAM disk.
Here is yet another observation: Why not create a separate table that keeps the BLOB data away from the unique names?
CREATE TABLE `data_store_name` SELECT id,uniqname FROM `data_store` WHERE 1=2;
ALTER TABLE `data_store_name` ADD PRIMARY KEY (id);
ALTER TABLE `data_store_name` ADD UNIQUE KEY (uniqname);
ALTER TABLE `data_store_name` ADD INDEX name_id_ndx (uniqname,id);
INSERT INTO `data_store_name` SELECT id,uniqname FROM `data_store`;
This will prevent any moving around of BLOB data when indexing.
From here, you would have to always join data_store using its name like this:
SELECT
A.uniqname,B.data
FROM
(SELECT * FROM data_store_name WHERE uniqname = 'mydataname') A
LEFT JOIN
data_store B USING (id)
;
Making these changes will sidestep this whole mess of dealing with keycache, RAM disks, and tmp tables.
Give it a Try !!!
The MyISAM Storage Engine is furiously notorious for performing full table locks for any DML (INSERTs, UPDATEs, DELETEs). InnoDB would definitely solve that issue in the long term.
I wrote about pros and cons of using MyISAM vs InnoDB
With regard to your current question, here is a possible scenario:
article and article_comments are both MyISAM tablesarticle_comments has one or more indexes with status as a column- Index page updates for
article_comments are cached in the MyISAM Key Buffer (sized by key_buffer_size), causing old index pages out of the MyISAM Key Buffer
- You have SELECT queries that perform JOINs between
article and article_comments
In my suggested scenario, SELECTs against the article table can be held up from allowing writes because of having to wait for article_comments to be free from any DML (in this case, an UPDATE)
Best Answer
There are good reasons
ALGORITHM=INPLACEmay not be used and switches toALGORITHM=COPY.REASON #1
In the link you gave in the question, there is a chart that shows the following:
Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.Some online DDL may still lock and copy because of an index's interaction. Don't forget that each BTREE entry in a secondary index has a hook back to the primary key. Adding a new secondary index may induce row level locks and index locks.
REASON #2
According the limitations page :
InnoDB tables created before MySQL 5.6 do not support ALTER TABLE ... ALGORITHM=INPLACE for tables that include temporal columns (DATE, DATETIME or TIMESTAMP) and have not been rebuilt using ALTER TABLE ... ALGORITHM=COPY.I am sure there are other reasons that can be researched an discussed along these lines.