Jump to content


Photo

Myisam Key Cache - Delay_key_write


  • Please log in to reply
5 replies to this topic

#1 Boyd Hemphill

Boyd Hemphill

    Member

  • Members
  • PipPip
  • 26 posts
  • Gender:Male
  • Location:Austin, Texas (USA)

Posted 18 February 2009 - 04:17 PM

I noted this new warning when I upgraded to monyog 3.0.1 in the "Delay updating indexes for MyISAM tables" item:

Setting it ON introduces a high risk of index corruption in the event of a crash. Consider using delayed inserts, or wrapping multiple statements with LOCK TABLES, instead.


While it is true that corruption of the index file is more likely with this on, I would argue that it is so likely anyway that it is not worth the worry. MyISAM is not crash safe (thus Monte's Maria project), and if a server crash, all tables and indexes should be checked and repaired anyway (innodb does this auto-magically).

So can you all provide the source of this advice? When I read three google articles on the matter they all seem to agree with my logic, particularly in the face of the large performance gain.

Thanks for looking into this!
Boyd

#2 manohar

manohar

    Member

  • Members
  • PipPip
  • 16 posts
  • Location:Bangalore

Posted 19 February 2009 - 10:34 AM

Hi Boyd,
We are currently discussing your suggestions. We will get back once we arrive at a decision, within couple of days.

#3 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 19 February 2009 - 12:02 PM

How would you think about something like this (my personal and current idea only):

Setting it ON introduces a high risk of index corruption in the event of a crash. Setting it OFF will force the server to update indexes for every INSERT/UPDATE/DELETE. That may affect performance as the server will then not be able to use idle periods for index maintenance. So this setting is a 'trade-off'. But no matter what: MyISAM tables should always be checked after a server crash.


Computers make your grey hair come off ....

Peter Laursen
Webyog

#4 Boyd Hemphill

Boyd Hemphill

    Member

  • Members
  • PipPip
  • 26 posts
  • Gender:Male
  • Location:Austin, Texas (USA)

Posted 19 February 2009 - 12:51 PM

How would you think about something like this (my personal and current idea only):

This is the essential gist of the articles I read. I appreciate you all taking the time.

Thanks
Boyd

#5 AlexH

AlexH

    Member

  • Members
  • PipPip
  • 12 posts
  • Gender:Male
  • Location:London, UK

Posted 07 March 2009 - 08:50 AM

possible options are {OFF|ON|ALL}

From the Reference manual:
"ON enables delayed key writes for those tables that were created with the DELAY_KEY_WRITE option. ALL delays key writes for all MyISAM tables."

So:
"ON" means that key writes will be delayed only for the tables that had DELAY_KEY_WRITE option set on CREATE TABLE.

my conclusion:
"ALL" might be regarded dangerous because it applies the delay to every table.
"ON" is only respecting a users decision for one ore more tables and is IMHO not really worth a warning.
(I disabled it in our installation of monyog :)

#6 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 08 March 2009 - 07:21 PM

I also think I would modify my previous post. First becase Alex has a good point. But also because a table CHECK and a table REPAIR is not the same.

So I propose this:


"The setting can have values ALL|ON|OFF (ALL indicates that it will always be ON also for tables where it was not specified in table definiton). Setting it ON (or ALL) introduces a high risk of index corruption in the event of a server crash. Setting it OFF will force the server to update indexes for every INSERT/UPDATE/DELETE what reduces risk of index corruption significantly. However no matter the value of this setting you should CHECK TABLES (and REPAIR TABLEs if required) after a server crash. If a table REPAIR is required your database and applications may be rendered more or less useless in the REPAIR period. So though you may achieve a performance inprovement with the setting ON|ALL (as the server will then be able to use idle periods for index maintenance) we generally do not recommend ON|ALL for the reasons mentioned."
Computers make your grey hair come off ....

Peter Laursen
Webyog




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users