I have an app which takes heartbeats (a simple http request) from hosts, typically the host generates one request every x minutes. This results in a large number of completely independent php pages runs which does a read queries then (possibly) generates one single row insert to the RDS database, which doesn't really matter if it succeeds (one missed beat isn't a reason for alarm, several are)
However, with mysqli I have a significant overhead in IOPs - it sends a BEGIN, my single line insert, then a COMMIT - and therefore appears to use three IOPs where I only need one.
Is there any way to avoid the transactions entirely? I could change auto_commit, but it's useless as each run of the handler is separate, so there is no other insert to group with this one. Even turning auto_commit off still runs a transaction, but only ends it when the connection closes (which happens after one insert anyway.)
Or should I switch to raw mysql handling for efficiency (lots of work)? The old mysql php library (deprecated)? Something else?
If you really don't need transactions you can use MyISAM storage engine for your tables that doesn't support transactions.
I use always :
SET AUTOCOMMIT=1
for my InnoDB databases where I don't need use transactions just after connecting to database.