On Fri, 16 Nov 2001 [EMAIL PROTECTED] wrote: > Date: Fri, 16 Nov 2001 11:40:06 +0000 (GMT) > From: [EMAIL PROTECTED] > To: Rick Robinson <[EMAIL PROTECTED]> > Cc: "Mysql \\(E-mail\\)" <[EMAIL PROTECTED]> > Subject: Re: What maintenance is needed for MyISAM tables? > > On Thu, 15 Nov 2001, Rick Robinson wrote: > > I'm using a MyISAM table in a high availability environment as a rudimentary > > persistent FIFO queue. The table definition is simple - the key is an > > auto_increment unsigned bigint and the data is a blob (it holds the contents > > of a C structure with a length of 200-400 bytes). I've got a couple of > > daemon processes that do inserts into the table, one daemon that reads the > > earliest row from the table (min) in a loop, and multiple 'consumers' that > > do something useful with the data and then delete the row. > > Ok, I am swearing in church, but here goes: in such a case, unless you > really -must- have your job queue in a SQL database for reasons not > mentioned here, if I were you, I would actually rather set up this FIFO in > a NFS-mounted directory, where the job info is stored in uniquely named > files. More or less the way that the Maildir format works (ref: qmail, > courier), as a matter of fact. If your system is running a *nix dialect, > that should be a no-brainer to set up (and you mention Solaris below), it > is far more scalable (oops, better put on my flamesuit now) and less > complicated to use than a DB. > > In other words: you yourself labeled this as "simple". Don't use a > complicated tool to fix it. MySQL's CPU resources can be spent on other > things instead. > > My 2 cents. :-) I may not be understanding the problem, but why not put the filenames in the database and the "blob"s out in the file system. This will result in fixed length records in the database and greatly reduce/eliminate the need for maintenance. I am assuming you need to have several programs/processes/threads access the FIFO at the same time. My 1.5 cents.
> > > My questions revolve around what type (if any) maintenance I need to do and > > what pitfalls I may encounter. > > - What type of maintenance do I need to do on this table? How often? I > > can't really take the table out of service. It processes up to 500,000 > > inserts/deletes an hour. > > - Are there known problems with this type of usage (heavy insert/delete) > > with MyISAM files? What should I be looking for from a monitoring > > perspective? > > - Is there a recommended version I should be using? Is 4.0 okay for this? > > I am currently using 3.23.42 on a Sparc Solaris 8 built with gcc 2.93. > > > > Thanks in advance for any advice (provided that it's good, of course :) ). > > > > regards, > > Rick > > [EMAIL PROTECTED] > > Sincerely, > Jan > > -- > Mr. Jan-Aage Bruvoll IT Project Manager > 20 Min Holding, Thurgauerstrasse 40, CH-8050 Zurich > Zurich office: +41 1 307 4293, fax: +41 1 307 4281 > Office/fax: +44 2072408283 Mobile: +44 7740291600 > > > --------------------------------------------------------------------- > 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 > Sincerely, William Mussatto, Senior Systems Engineer CyberStrategies, Inc ph. 909-920-9154 ext. 27 --------------------------------------------------------------------- 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
