On Mon, Mar 19, 2001 at 02:40:58PM -0600, Neulinger, Nathan R. wrote:
>
> Got any suggestions on how these could be implemented? particular
> table types/setups/etc?
>
> I basically have a situation where 300,000+ rows are being inserted
> into a log table daily, and also needing to be cleared out based on
> a tstamp. What I've found is that the inserts work fantastic, but
> when I got to do the delete, even if it doesn't do anything, it sits
> there and blocks everything on the server for 20-30 minutes.
>
> Is this because of the lack of row-level locking on MyISAM tables?
Yes.
> Should I switch to a different table handler?
Maybe.
> Right now, I'm using multiple merge tables, split up to divvy up the
> space used. Certain log entries are getting kept longer than others
> due to the decreased volume those hosts generate.
I was just about to suggest MERGE tables. If you create a table for
each interval (day, week, hour, whatever your requirements are) it may
improve things quite a bit.
If splitting the tables based on time constraints isn't easily doable,
you might look at testing BDB or maybe Innobase tables (just added to
MySQL).
The BDB tables have page-level locking, so you'll probably get some
relief there. And Innobase has row-level locking with a non-locking
consistent read, so it could be even better. But it's fairly new to
MySQL. The impression I have is that a lot of people are looking at
Innobase tables now and pushing them to see what they're capable
of. If you've got a bit of time to hack on it, that may help.
Jeremy
--
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 328-7878 Fax: (408) 530-5454
Cell: (408) 439-9951
---------------------------------------------------------------------
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
