MySQL Forums
Forum List  »  Performance

Re: 350+ million rows -- is this as fast as it can get? (too slow at the moment!)
Posted by: Rick James
Date: February 10, 2009 12:28AM

myisam_max_sort_file_size may be the critical one.

Try this: Run your program to build the table, but don't have any indexes on it. When fully populated, do
ALTER TABLE foo
  ADD INDEX ...,
  ADD INDEX ...
;
Do ALL the indexes in a single ALTER (if possible).

key_buffer_size = 4096M
worries me -- there is a hard-coded limit of 4GB. This smells like you hit the limit. It wraps! So you may actually have 0! (Suggest changing to 4000M and restarting the server.)

All of the above assumes you are using MyISAM, not InnoDB; right? If you are using InnoDB, let's start over.

Your program -- how does it do the inserts? There are probably faster ways:
* Batch the inserts to get 10x speed improvement.
INSERT INTO foo (a,b) VALUES (1,2),(3,4),...;
Recommend 100 rows or 1MB-long string, whichever is smaller.
* Have your program create a CSV file, then use LOAD FILE.
* Or create the CSV FILE, then
ALTER TABLE foo
ADD INDEX ...
ENGINE = MyISAM;
(Yeah, CSV is another storage engine.)

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.