Re: Performance trouble at 35M rows

[daofeng luo]

I store logs in MyISAM table. There are about 100 million logs(about 2G) every day, and I build the indexes on userID and login_ip. I load the logs following the steps:
1: create table log_20041124 like log_template;
2: alter table log_20041124 disable keys;
3: load data infile "***" into log_20041124;
4: alter table log_20041124 enable keys.

It takes about 30 minutes to build two B+Tree indexes.

OS: slackware Linux
MySQL: 4.1.7
Hardware: Xeon 2CPU, 2G RAM
my.cnf:
key_buffer_size 512M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 128M
read_buffer_size = 8M
myisam_sort_buffer_size = 128M


Ted Cui, how do you think if I store the logs in InnoDB? There will be about 4 billion logs in a month. I now use Merge table when I need to query the all logs, but the performance seems not so good. Can InnoDB handle this large volumn of data in high performance?