Update: The reason I used code to write to MySQL server from another server is that this is an experiment. I need to record the time it takes to finish inserting all these rows. Loading data locally to MySQL through other methods is not the concern.
I was trying to insert over 15 million rows of records to a MySQL database that was located on another server. I used "github.com/go-sql-driver/mysql" driver and employed transaction. However, it takes me about 75 min to finish inserting. The bandwidth between the two servers is over 1GB/s. The CPU and memory utilization of MySQL is less than 20% throughout the time with 1 core CPU and 2GB memory. The code snippet is as follows:
fmt.Println("Connecting to MySQL...")
db, err := sql.Open("mysql", "root:password@tcp(ipAddress:3306)/ssc")
if err != nil {
panic(err.Error())
}
fmt.Println("Connected to MySQL")
defer db.Close()
var transaction *sql.Tx
var stmt *sql.Stmt
transaction, err = db.Begin()
stmt, err = transaction.Prepare(`INSERT INTO Star2002PrimaryTracks(primaryTracks, offsetInByte, sizeInByte) VALUE (?, ?, ?)`)
start := time.Now()
// primaryTracksSlice has over 10 million elements.
for i, ele := range primaryTracksSlice {
if i != 0 && i%100000 == 0 {
// commit transaction. init transaction and stmt
elapsed := time.Since(start)
fmt.Printf("time since:%s\n", elapsed)
transaction.Commit()
fmt.Println("transaction commited with 100000 rows each.")
elapsed = time.Since(start)
fmt.Printf("transaction commited. time since: %s\n", elapsed)
elapsed = time.Since(start)
fmt.Printf("time since:%s\n", elapsed)
transaction, err = db.Begin()
stmt, err = transaction.Prepare(`INSERT INTO Star2002PrimaryTracks(primaryTracks, offsetInByte, sizeInByte) VALUE (?, ?, ?)`)
elapsed = time.Since(start)
fmt.Printf("transaction and stmt initialization. time since: %s\n", elapsed)
} else {
// stmt.exec
stmt.Exec(ele.PrimaryTracks, ele.Offset, ele.Size)
}
}
transaction.Commit()
}
transaction.Commit(), db.Begin() and transaction.Prepare() takes way less than a second to finish. It seems that 100000 stmt.Exec()s takes about 30 seconds to finish. I do not think that it should take that long since stmt.Exec() does not have anything to do with transmitting data to MySQL server until transaction.Commit(). I would like to ask you guys out there, what might be wrong?
I feel like I should make an update. The table is created with:
CREATE TABLE `Star2002PrimaryTracks` (`id` INT UNSIGNED AUTO_INCREMENT, `primaryTracks` int, `offsetInByte` int, `sizeInByte` int, primary key (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8;
without adding addition indexes which could be the cause. There's only the primary key.
I think there is something wrong about stmt.Exec(). Why does it takes so long to execute when it does not have to commit data to MySQL server. Is not transaction.Commit() the one method which is supposed to do the job of commiting data to MySQL server and takes a relatively long time to finish?
CREATE TABLE `Star2002PrimaryTracks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`primaryTracks` int(11) DEFAULT NULL,
`offsetInByte` int(11) DEFAULT NULL,
`sizeInByte` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1067367 DEFAULT CHARSET=utf8 |
