1

I'm loading multiple files into the database using the LOAD DATA LOCAL INFILE command combined with Golang database/sql driver like this:

mysql.RegisterLocalFile(fileName)
_, err = dbConnection.Exec(loadDataCommand)

This works as expected after the load has finished I run some SELECT queries (I'm reusing dbConnection for those SELECT queries and all other queries) on newly loaded data and this is where the problem occurs. The above mentioned SELECT queries are unusually slow (due to high CPU usage).

While running LOAD DATA database CPU usage spikes which is expected but after the LOAD DATA finishes CPU usage should drop but it doesn't unless I close and reopen dbConnection CPU usage stays high.

This is how I'm "reseting" the database connection:

_ = dbConnection.Close()
dbConnection, err = sql.Open(databaseDriver, databaseUser+":"+databasePassword+"@tcp("+databaseAddress+":3306)/"+databaseName+"?multiStatements=true")

This is the database configuration.

Why is this happening? The above code is running on a development database so there are no other connections to it.

0

As expected.

The updating of secondary indexes (INDEXes other than PRIMARY KEY) happens later. Such changes are stored in the Change Buffer; they are actually written to disk later in a more efficient way than trying to do them as the rows are being inserted. You will see both CPU and I/O activity.

The net effect should be that your LOADs and SELECTs will complete faster (elapsed time) than without the Change Buffer.

  • I understand that but I don't understand why does the CPU usage drops if I reset the database connection (in my code). All data is loaded and index creation should start or be in progress. – Uranus Oct 18 at 20:13
  • @Uranus - I can't explain that part. – Rick James Oct 18 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.