Why is MySQL access so slow?

Question

MySQL

I have a c++ console application that takes data from a file and pushes it to MySQL server InnoDB with a query:
"insert into xyz values ​​('123456789012345',1234,56,78,90,12,34,5678,123456,7,123456, 1.12345)".
This request is spinning inside the while.
One record is 15 fields for 65-70 bytes.
I connect via c++ connector v.1.1.0 x64, mysql server 8.

When the database was on SSD, I wrote about 1000 records/sec. those. about 68 kB/s.
When the base is on the HDD, then generally 3-10 records / sec. those. about 210 bytes/second.

The same speeds are saved even if you do not read data from a file, but just stuff it with database data.
Question: why such a fantastically low speed and how to change it?



There is a good article here that says that you need to send multiple records in 1 request or use import directly into the database from a specially formatted file.
https://medium.com/@benmorel/high-speed-inserts-wi...

But I'm more concerned about why such a low speed at all? It's all happening on localhost. It seems to me that the matter is either in the connector of the old version, or in something else software.

Answer 1

Question: why such a fantastically low speed and how to change it?

Are you sure it's fantastically low? 1000 write transactions per second is excellent performance.
mysql (like most databases) by default considers a transaction completed after a successful fsync.
https://www.percona.com/blog/2018/02/08/fsync-perf...
https://docs.google.com/spreadsheets/d/1E9-eXjzsKb... (journal iops column)
so that yes, write to the database not one line at a time, but in batches (reduce the number of transactions). or disable fsync if the data is not particularly important.