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

Advertisements

Tagged:

One thought on “TUNING MYSQL FOR MASSIVE INSERT

  1. virtualpabx September 14, 2011 at 6:28 pm Reply

    $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
    $time_start = microtime_float();
    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);
    $time_end = microtime_float();
    $time = $time_end – $time_start;
    echo “Did nothing in $time seconds\n”;
    function microtime_float()
    {
    list($usec, $sec) = explode(” “, microtime());
    return ((float)$usec + (float)$sec);
    }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

shisdew

Listens until think alike

moses.spaceku@yahoo.com / voip ipbx

Hosted PBX, IP-PBX SOHO/ CALL CENTER, VOICE GATEWAY, VOICE CARD, COST EFECTIVE SOLUTIONS (LCR), GSM/CDMA GATEWAY

%d bloggers like this: