On Oct 25, 2010, at 2:39 PM, Robert Michael Erickson wrote:
> it seems to process three records every second, the sql looks like this:
> INSERT INTO tblAttendance SET fkRegNum=90703, fkUsername="xxxxxx", fldDate='2010-10-25', fldPresent='N'
> and it has been slightly better recently. there would be about 50 records that need processing. does the size of the database make any difference? one table has a blob field which does have 9,674 records. the blob field holds a file which can be a word document, spreadsheet, access database, picture etc.
A table of that size shouldn't have any write speed issues. You really shouldn't have write speed issues with tables of any realistic size -- even up to hundreds of millions of rows -- unless you have many simultaneous connections all writing to the same tables, or complex INSERTs/UPDATEs that reference other tables. In those situations, you can start to run into locking issues, particularly with MyISAM format tables, which lock the whole table in order to make a change. OTOH, webmail uses MyISAM tables without issues, so even then it takes a lot.
On the other hand, filesystem writes can be slow. If I tracked down the right code of yours <g> , it looks like you are keeping a log of your database updates, presumably for debugging. The logging code opens, writes, and closes the log file each time you update your tables. According to our resident NFS expert Ben, the web farm's NFS filesystems are not doing asynchronous writes, meaning that the remote server has to confirm each write before acknowledging it back to your code, slowing things down. Try commenting that out and see what happens.