I have the following INSERT statement:
cursor.execute('''UPDATE alg SET size=%(size)s, path=%(path)s,
last_modified=%(last_modified)s, search_terms=%
WHERE objectID=%(objectID)s''', data)
It is indexed and usually takes 1/1000th of a second to do. However, once every 200 INSERTs or so, it takes a super long time. Here's an example of it timed --
453
0.000407934188843
454
0.29783987999 <-- this one
455
0.000342130661011
456
0.000318765640259
457
0.000240087509155
The column is indexed and it is using InnoDB. Any idea what the issue might be? It also seems random in that if I run it over and over, different objects cause the INSERTs to be slow -- it's not tied to any particular object.
Also note that I do not have this issue on MyISAM.
You likely have default innodb_log_file_size, which id 5 Megabytes. Set this in your cnf file to be the minimum of 128M or 25% of your innodb_buffer_pool_size. You'll want the buffer pool to be as large as possible for your system. If it's a dedicated mysql server then 70-80% of system RAM would not be unreasonable (leaving some for OS page cache).
Setting the log file size larger will space out times things need to be flushed to tables. Setting it too large will increase crash recovery times on restarts.
Also be sure to set innodb_flush_method=O_DIRECT to avoid OS level page caching.