Mysql – When MySQL table gets too large

MySQL

I have a really large MySQL table counting more than 12 billion rows at the moment (table scheme: InnoDB). From time to time the problems get more and more. The biggest problem is that altering the table takes a really long time (more than 48h) and every minute downtime is one minute too much. So whenever I need to add a new index or add a new column, the problem with the altering is there.

Are 12 billion rows too much for MySQL to handle? What would you suggest to do?

Best Answer

48 hours ALTER to a 12 billion rows table is not out of the ordinary in InnoDB.

You could:

Use Percona online schema change \ Facebook php online schema change tools to ALTER the table online.
Switch to TokuDB engine which does these alters online and also improves performance when scaling up from InnoDB.

Both solutions are free.

Related Solutions

How to Update Rank on a Large Table in MySQL

Rhetorical Question: Do you really need to rank all 12,000,000 records?

At my employer's web hosting company, we have a gaming client that ranks its top 10,000 players per game platform. They use the same construct you do. You should just limit it to the top 10,000 or whatever number is reasonable

SET @r=0;
UPDATE records SET rank= @r:= (@r+1) where type = 2 ORDER BY seconds DESC LIMIT 10000;

I would run your original query at midnight to get them all

If you must try to rank all, maybe you can try something like this:

DROP TABLE IF EXISTS ranktable;
CREATE TABLE ranktable
(
    rank INT NOT NULL AUTO_INCREMENT,
    rec_id INT NOT NULL,
    PRIMARY KEY (rank)
);
INSERT INTO ranktable (rec_id)
SELECT rec_id FROM records WHERE type=2 ORDER BY seconds DESC;
UPDATE records A INNER JOIN ranktable B USING (rec_id) SET A.rank = B.rank;

What may further help is to index your records table like this:

ALTER TABLE records ADD INDEX type_seconds_ndx (type,seconds);

Mysql – Error “#1118 – row size too large” on the first row of the table only

As suggested from this bug report, you may need to look into the following:

File Format

Run SHOW VARIABLES LIKE 'innodb_file_format%';
- innodb_file_format
- innodb_file_format_max
You might see values Antelope, Barracuda

Row Format

Run ALTER TABLE tblname ROW_FORMAT=DYNAMIC; to make the row format Dynamic
Run ALTER TABLE tblname ROW_FORMAT=COMPRESSED; to make the row format Compressed

Suggestions

Workaround: use new InnoDB file format Barracuda and row_format=DYNAMIC

This was added to the change log in MySQL 5.5.29

Perhaps an upgrade to the latest MySQL 5.5. is in order

Give it a Try !!!