The goal: I want to reduce load on the mysql database by combining multiple insert queries into one.
The question: Can I record insert information for multiple new records into a flat text file and load all the data into the database with a single INSERT query? Is this even a good idea?
The scenario: I have a high traffic PHP driven site receiving and recording individual access logs in a mysql database. Currently, each log is entered with its own insert statement in real time. The site writes one to two new entries into the database every second and bursts in traffic are maxing out the CPU.
The reasoning: I’m thinking that if I just write log data to a file and then parse it with a cron job every minute or so, I can reduce the number of INSERT statements and free up system resources.
Any thoughts on this idea or suggestions for other methods are very much appreciated.
Haven’t gotten a response on this, so I came to my own conclusions in case they help someone else.
First, I timed a loop of 1000 records as insert queries against 1000 of the same records into a log file using ‘file_put_contents’ with the FILE_APPEND option. Adding the records to a log file was MUCH faster (10 seconds or so for the queries, 1 second for the flat file).
I’m combining the flat file with the MySql LOAD DATA INFILE (which is extremely fast) to add the stored data to the database. I’ll run this as a cronjob which gives the added benefit of scalability as the load increases (I can run it every minute for now, and increase the time between crons if I need to).
My biggest concern at this point is mal-formed data being run through the LOAD DATA INFILE function which causes it to crash. I’ll have to add some error checking and alert email functionality in case something goes wrong.
Any replies or comments on this would still be appreciated.
When using LOAD DATA INFILE I use tab delimited text files, one record per line of course, not csv, so that a comma in the content doesn’t result in the database seeing the wrong number of fields. Also be sure that you have a value present for every field.