I've been testing Go in hopes to use it for a new site and wanted to make sure it was as fast or faster than PHP. So I ran a basic test doing bulk inserts in Go and PHP because I'll need bulk inserts.
My tests used transactions, prepared statements, the same machine, the exact same table definition, no index but the PK, and the same logic in the function.
Results:
- 100k Inserts in PHP (mysqli) was 4.42 seconds
- 100k Inserts in Go (Go-MySQL-Driver) was 9.2 seconds
The go mysql driver i'm using is the most popular one 'Go-MySQL-Driver' found here: https://github.com/go-sql-driver/mysql
I'm wondering if anyone can tell me if my code in go is not set up right or if this is just how go is.
The functions add a bit of variability to a few of the row variables just so every row isnt the same.
Go Function:
func fill_table(w http.ResponseWriter, r *http.Request, result_string *string, num_entries_to_add int) {
defer recover_show_error(result_string)
db := getDBConn()
defer db.Close()
var int_a int = 9
var int_b int = 4
var int_01 int = 1
var int_02 int = 1451628000 // Date Entered (2016-1-1, 1am)
var int_03 int = 11
var int_04 int = 0
var int_05 int = 0
var float_01 float32 = 90.0 // Value
var float_02 float32 = 0
var float_03 float32 = 0
var text_01 string = ""
var text_02 string = ""
var text_03 string = ""
start_time := time.Now()
tx, err := db.Begin()
if err != nil {
panic(err)
}
stmt, err := tx.Prepare("INSERT INTO " + TABLE_NAME +
"(`int_a`,`int_b`,`int_01`,`int_02`,`int_03`,`int_04`,`int_05`,`float_01`,`float_02`,`float_03`,`text_01`,`text_02`,`text_03`) " +
"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)")
if err != nil {
panic(err)
}
defer stmt.Close()
var flip int = 0
for i := 0; i < num_entries_to_add; i++ {
flip = ((int)(i / 500)) % 2
if flip == 0 {
float_01 += .1 // add to Value
} else {
float_01 -= .1 // sub from Value
}
int_02 += 1 // add a second to date.
_, err = stmt.Exec(int_a, int_b, int_01, int_02, int_03, int_04, int_05, float_01, float_02, float_03, text_01, text_02, text_03)
if err != nil {
panic(err)
}
}
err = tx.Commit()
if err != nil {
panic(err)
}
elapsed := time.Since(start_time)
*result_string += fmt.Sprintf("Fill Table Time = %s</br>
", elapsed)
}
PHP Function:
function FillTable($num_entries_to_add){
$mysqli= new mysqli("localhost", $GLOBALS['db_username'], $GLOBALS['db_userpass'], $GLOBALS['database_name']);
if ($mysqli->connect_errno == 0) {
$int_a = 9;
$int_b = 4;
$int_01 = 1;
$int_02 = 1451628000; // Date Entered (2016-1-1, 1am)
$int_03 = 11;
$int_04 = 0;
$int_05 = 0;
$float_01 = 90.0; // Value
$float_02 = 0;
$float_03 = 0;
$text_01 = "";
$text_02 = "";
$text_03 = "";
$mysqli->autocommit(FALSE); // This Starts Transaction mode. It will end when you use mysqli->commit();
$sql = "INSERT INTO " . $GLOBALS['table_name'] .
"(`int_a`,`int_b`,`int_01`,`int_02`,`int_03`,`int_04`,`int_05`,`float_01`,`float_02`,`float_03`,`text_01`,`text_02`,`text_03`) " .
"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
$start_time = microtime(true);
if($stmt = $mysqli->prepare($sql)) {
$stmt->bind_param('iiiiiiidddsss', $int_a, $int_b, $int_01, $int_02, $int_03, $int_04, $int_05, $float_01, $float_02, $float_03, $text_01, $text_02, $text_03);
$flip = 0;
for ($i = 1; $i <= $num_entries_to_add; $i++) {
$flip = ((int)($i / 500)) % 2;
if ($flip == 0) {
$float_01 += .1; // add Value
}
else {
$float_01 -= .1; // sub Value
}
$int_02 += 1; // add a second to date.
$stmt->execute(); //Executes a prepared Update
}
$mysqli->commit(); // Transaction mode ends now
$stmt->close(); //Close statement
}
$execute_time = microtime(true) - $start_time;
echo $GLOBALS['html_newline'] . $GLOBALS['html_newline'] .
'FillDataEntryTable Speed: '.$execute_time.' sec' . $GLOBALS['html_newline'] . $GLOBALS['html_newline'];
$thread_id = $mysqli->thread_id; // Get MySQL thread ID
$mysqli->kill($thread_id); // Kill MySQL Server connection
$mysqli->close(); // Close MySQL Server connection
}
}