This is all fine advice for performance tuning DBI app and worth doing. But there's always a limit to what can be achieved on the client. So it's worth exploring what can be done on the server side, beyond standard tuning practices.
I wrote a high volume log insertion app using mysql many years ago. I can't remember the performance figures, but it was fast. A major cost of insertion is maintaining indices. So for maximum insertion speed you want to have no indices. But to query the log records you'll probably want indices. The way I approached this was to have per-hour tables: log_YYYYMMDDHH The loader streams the records into the table as fast as it can. (From memory I used a multi-row INSERT statement, no placeholders.) Every time it switches to a new hour it triggered the building of indices on the previous hour's table. It also triggered recreating per-day views (log_YYYYMMDD) that abstracted the hourly tables. The same technique could be applied at whatever time granularity meets your needs, from table-per day to table-per minute. If you can delay the loading of the log data by whatever period you're using (eg hourly) then you have the option of preparing the log data as hourly text files and then, when each is complete, using a proprietary bulk-loading tool to load it. Tim.
