TUNING MYSQL FOR MASSIVE INSERT

$link = mysql_connect(‘localhost’, ‘root’, ‘moses’);
if (!$link) {
die(‘Not connected : ‘ . mysql_error());
}
$db_selected = mysql_select_db(‘tester’, $link);
if (!$db_selected) {
die (‘Can\’t use foo : ‘ . mysql_error());
}
// myisam
//default conf
//BULK_INSERT_BUFFER_SIZE=8388608
//MYISAM_SORT_BUFFER_SIZE=8388608
//KEY_BUFFER_SIZE=8384512

mysql_query(“truncate table load1”,$link);
mysql_query(“SET SESSION BULK_INSERT_BUFFER_SIZE=256217728”,$link); // 256M
mysql_query(“SET SESSION MYISAM_SORT_BUFFER_SIZE=256217728”,$link); // 256M
mysql_query(“SET GLOBAL CONCURRENT_INSERT=2”,$link);
mysql_query(“SET GLOBAL KEY_BUFFER_SIZE=256217728”,$link); // 256M
mysql_query(“alter table load1 disable keys”,$link);
mysql_query(“LOAD DATA INFILE ‘/home/moses/loaddata.csv’ IGNORE INTO TABLE load1 FIELDS TERMINATED BY ‘,'”);
mysql_query(“alter table load1 enable keys”,$link);
mysql_close($link);

//default conf
//innodb_flush_log_at_trx_commit=1
//innodb_flush_method=
//innodb_doublewrite=ON
//innodb_additional_mem_pool_size=1048576 (1M)
//innodb_buffer_pool_size=8388608 (8M)
//innodb_log_file_size=5242880 (5M)

//innodb
mysql_query(“truncate table load1”,$link);
mysql_query(“SET SESSION innodb_flush_log_at_trx_commit=2”,$link);
mysql_query(“SET SESSION innodb_flush_method=O_DIRECT “,$link);
mysql_query(“SET SESSION innodb_doublewrite=0”,$link); // default ON
mysql_query(“SET SESSION innodb_additional_mem_pool_size=26214400”,$link); // => 25M
mysql_query(“SET SESSION innodb_buffer_pool_size=1073741824”,$link); // => 1G
mysql_query(“SET SESSION innodb_log_file_size=268435456”,$link); // => 256M
mysql_query(“LOAD DATA INFILE ‘/home/moses/loaddata.csv’ IGNORE INTO TABLE load1 FIELDS TERMINATED BY ‘,'”,$link);
mysql_close($link);

Pertimbangkan juga dengan menggunakan pada innodb
UNIQUE_CHECKS=0;
FOREIGN_KEY_CHECKS=0