I am developing a data collection application that involves constant insertion into a MySQL database. I am using Python and PyMySQL to accomplish this. I need to insert about 100 rows into the main table a second. The python process is constantly running and maintains a constant connection to the MySQL database which resides on a remote server.
I know that, in general, it is best to insert data as buffers (multiple rows at once) rather than making individual insertions. Would making commits (connection.commit() in PyMySQL) every 100 or so insertions achieve some of the same overhead reductions as inserting large amounts of data at once would?
For syntactical reasons, it is easier to separate the row insertions into individual operations.
You should find that the biggest overhead with a distant server is not processing time, but rather the round-trip time for each query to be sent to the server and return a response... if the server is more than ~10ms distant, inserting 100 rows/sec individually is impossible because too much time is wasted on the wire, waiting.
There are internal reasons that make bulk inserts or infrequent commits perform better, but those become less and less relevant when the server is more distant.  Individual inserts will be always slower from your perspective than bulk inserts, simply because of the number of round trips.
At the server, itself, bulk inserts convey a small advantage... and in a transaction, committing after every n inserts will convey a small advantage... but again any difference in performance from using these strategies will disappear into the noise over a distant connection.
In short, inserting multiple rows in a single query rather than multiple queries is the only meaningful improvement you can make, because server performance is not your primary issue -- it's distance.
Of course, if there is some reason why individual insert queries are more desirable, then using multiple threads in your program and multiple connections to the database is a possible strategy to improve the performance, since n connections can execute n queries in parallel, thus reducing the net practical impact of round trip time t down to something near t / n.
Is buffering commits in MySQL/PyMySQL a viable alternative to inserting multiple rows at once?