Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a table with 1.4 billion records. The table structure is as follows:

CREATE TABLE text_page (
    text VARCHAR(255),
    page_id INT UNSIGNED
) ENGINE=MYISAM DEFAULT CHARSET=ascii

The requirement is to create an index over the column text.

The table size is about 34G.

I have tried to create the index by the following statement:

ALTER TABLE text_page ADD KEY ix_text (text)

After 10 hours' waiting I finally give up this approach.

Is there any workable solution on this problem?

UPDATE: the table is unlikely to be updated or inserted or deleted. The reason why to create index on the column text is because this kind of sql query would be frequently executed:

SELECT page_id FROM text_page WHERE text = ?

UPDATE: I have solved the problem by partitioning the table.

The table is partitioned into 40 pieces on column text. Then creating index on the table takes about 1 hours to complete.

It seems that MySQL index creation becomes very slow when the table size becomes very big. And partitioning reduces the table into smaller trunks.

share|improve this question

migrated from stackoverflow.com May 11 '10 at 3:12

This question came from our site for professional and enthusiast programmers.

1  
What's wrong with using the normal CREATE INDEX statement? –  Derk May 10 '10 at 17:06
    
I'd suggest this question might be better off on ServerFault - it's more of a DB admin than a programming question. –  therefromhere May 10 '10 at 17:07
    
@Derk: the normal CREATE INDEX approach is too slow. I have to complete the task within 1 day. –  SiLent SoNG May 10 '10 at 17:09
1  
Hmm... I don't think that you can get around this. Building the index requires the DBMS to scan through all the records, gather their "text" fields and insert/change the corresponding tree nodes/subtrees. And this takes much time for 34G... –  chiccodoro May 10 '10 at 17:09
    
How much memory does your DB server have? Have you configured MySQL to use all that memory, or is it limiting itself? –  tster May 10 '10 at 17:10
show 2 more comments

7 Answers

Could it be your system just is not up to the task? I do not use MySQL (SQL Server here), but I know the pain of indexing an 800 million entry table. Basically.... you need the right hardware for that (as in: lots of fast discs). I now use nearly a dozen Velociraptors and performance is great ;)

SQL Servers (not as MS SQL Server, but as database servers using SQL) live and die with disc access, and normal discs are just not up to the task of larger operations.

share|improve this answer
    
My doubt is that index creation is usually very fast if the record count is small; say, millions. But when the count is at billions the index creation becomes so slow. Seems like the time growth is exponential. –  SiLent SoNG May 10 '10 at 17:16
    
Should not really be. MySQL in general has limits, but it is not a crap database, and that would be VERY bad. Index generation gets slower, but by log(n), not (n), so it should not really be THAT bad. –  TomTom May 10 '10 at 17:34
add comment

You might want to create an index on first (for instance, 10) characters of the text field.

From the Docs:

Indexes can be created that use only the leading part of column values, using col_name(length) syntax to specify an index prefix length:

CREATE INDEX ix_text ON text_page (text(10))
share|improve this answer
add comment

I have solved the problem by partitioning the table.

The table is partitioned into 40 pieces on column text. Then creating index on the table takes about 1 hours to complete.

It seems that MySQL index creation becomes very slow when the table size becomes very big. And partitioning reduces the table into smaller trunks.

share|improve this answer
add comment

Set the sort_buffer_size to 4GB (or however much you can depending on how much memory you have).

Right now the create index is doing a sort but since you have a 32MB sort_buffer_size, it is basically thrashing the hard drive needlessly.

share|improve this answer
    
These posts are pretty much directly disagreeing with you: xaprb.com/blog/2010/05/09/how-to-tune-mysqls-sort_buffer_size and better ronaldbradford.com/blog/… It sounds like that's not a global value, it's per-query, so that's 4GB per query you're recommending. Also, when it exceeds 256K it gets mem-mapped to disk rather than being actual in-memory memory. If you keep it small it requires multiple passes, but it avoids disk (it doesn't swap). –  Ry4an Jul 19 '10 at 14:11
add comment

If you do not need to make queries like:

SELECT page_id FROM text_page WHERE text LIKE '?%';

I would suggest creating a new hash column and index the table by the column. The overal size of the table + index might be much smaller.

UPD: By the way, 1.4 billion primary key integers occupy about 6 GB, that is the average length of the string is less than 30 characters, that is indexing on a prefix might be more preferable.

You should also take a look at MERGE storage engine.

share|improve this answer
add comment

One way to do this is to create a new table with the index set and copy the data to the new table.

Also, make sure you have enough temp space.

share|improve this answer
    
I have tried this approach. After 10 hours less than 1% data has been copied over to the new table. –  SiLent SoNG May 10 '10 at 17:08
1  
Dude... it's 1.4 BILLION records. Not million, BILLION. That's a lot. It's gonna take a while regardless. –  Bryan Ross May 10 '10 at 17:09
    
If you do choose to do this method break the copy into smaller chunks. Say about 100 to 200 million for each copy. –  decompiled May 10 '10 at 17:16
1  
@decompiled,breaking it into smaller chunks won't do anything (actually, it might make it less efficient). @Bryan, Even with 1.4 billion records, it shouldn't take 1,000 hours. –  tster May 10 '10 at 17:35
add comment

In case you're still wondering how to do this best, I would suggest you to use an online alter table tool.

There are lot of them on the internet, one of the famous ones are: - Percona online schema change (I use this one) http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html - Facebook OSC, I couldn't find an URL where you could download it

We have the same issues with big tables (more then 500mil records) and the altering goes perfect. It creates a new tmp table, adds trigger on the original table (for the new update/delete/insert records) and in the mean time it copies all the records to the new table (with the new structure)

Good luck!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.