Unfortunately, the deletion criteria are based both on the host it's coming
from, and the date of the records. If I start to break it up along too many
lines, the management starts to outweight the benefits of it being in the
database.
Sounds like looking at innobase is definately a good way to go as well... I
might give that a shot.
-- Nathan
> -----Original Message-----
> From: Jeremy D. Zawodny [mailto:[EMAIL PROTECTED]]
> Sent: Monday, March 19, 2001 4:01 PM
> To: Neulinger, Nathan R.
> Cc: '[EMAIL PROTECTED]'
> Subject: Re: suggestions for implementing heavily used log tables with
> nightly delete
>
>
> 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
