I'm using MySQL-Max-3.23 and have an InnoDB table that's getting hit with lots of inserts .. I'm pushing them as fast as I can to strain the box and getting 200-300/sec. The table consists of a timestamp (timestamp(14)) and a message (char(255)). I need to keep the table from exceding a certain size and need it to keep only the newest records (fifo-like). I plan on running a cron job that checks the size of the table (with show table status, looking at the Data_length field) and if its close to the max, executes a delete statement to remove some chunk of the oldest records.
I'm having a few issues.. First is that 3.23 doesn't support "delete from .. order by .." -- this was added in MySQL 4.x .. Ideally I'd use "DELETE FROM table ORDER BY datestamp ASC LIMIT 10000", but of course this wont work. How are people doing this in 3.23.xx? So, as an alternative I'm grabbing the min(timestamp), adding some value (an hour for example) and executing "delete from table where timestamp <= ($min_timestamp + (60 * 60))". This would be okay, but it doesn't work -- I get a lock timeout error message after a long wait. While there delete is running, the InnoDB Monitor indicates that the thread running the delete transaction builds up a lot of "lock structs" (upwards of 700) and a lot of "undo log entries" (upwards of 35000) before it fails. I should point out that during the delete there are no selects or updates, just insertions. While its running (before it fails), the monitor indicates that there are ~200 inserts/sec and between 0 and 60 deletes/sec (this matches reads/sec .. why?) -- so obviously this isn't going to work. I need to be able to delete at least as fast as I can insert. Maybe relevant : I have ...trx_commit=0 in my.cnf The other problem is that after the delete I need to see if enough has been removed, but the "Data_length" field seems to fluctuate inconsistently -- is there a better way to get a reading of how much data is used (or how much space is left)? What is the relationship between deleting and inserting? It seems like there is a common semaphore or lock but I don't understand what the relationship is? Thanks, Eric Mayers Software Engineer --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
