Professional Web Applications Themes

converting to innodb with minimal downtime? - MySQL

Given a database with MyISAM tables, that one does not want to take offline for more than is absolutely necessary, what's the quickest (in terms of offline time) way to convert those tables to InnoDB? The database I'm contemplating doing this on takes about 6 hours to load into InnoDB tables from a mysqldump dump. Is there anything faster/better than this method? 1. Get a snapshot of the database with mysqldump. Note the position in the binary logs. 2. For each table foo, create an InnoDB table, new_foo, and restore new_foo from the snapshot of foo. 3. Stop clients from ...

  1. #1

    Default converting to innodb with minimal downtime?

    Given a database with MyISAM tables, that one does not want to take offline
    for more than is absolutely necessary, what's the quickest (in terms of
    offline time) way to convert those tables to InnoDB?

    The database I'm contemplating doing this on takes about 6 hours to load
    into InnoDB tables from a mysqldump dump.

    Is there anything faster/better than this method?

    1. Get a snapshot of the database with mysqldump. Note the position in the
    binary logs.

    2. For each table foo, create an InnoDB table, new_foo, and restore new_foo
    from the snapshot of foo.

    3. Stop clients from accessing the database (either by unplugging the
    network connection, or by restarting mysql on a port other then 3306).

    4. For each table foo, rename foo to old_foo, and new_foo to foo.

    5. Use mysqlbinlog to play back the binary logs from the time of step #1.

    6. Plug the network cable back in, or restart mysql on the normal port
    (depending on what was done in #3).

    7. Drop old_foo, for each table foo.

    Total unavailable time this way is about 10 minutes to get the snapshot, and
    another few minutes to swap the tables and play back the logs, which beats
    the heck out of 6 hours. Is there something even faster, or easier without
    being much longer?

    --
    --Tim Smith
    Tim Smith Guest

  2. #2

    Default Re: converting to innodb with minimal downtime?

    >Given a database with MyISAM tables, that one does not want to take offline
    >for more than is absolutely necessary, what's the quickest (in terms of
    >offline time) way to convert those tables to InnoDB?
    Is the existing server capable of handling InnoDB tables?

    If not, I suggest:
    - set up for use with InnoDB in my.cnf files.
    - If needed, replace mysqld with one compiled to use InnoDB,
    preferably of the SAME version of MySQL.
    - Shut down and restart the server.

    Then, whether you had to upgrade the server or not, convert the tables.
    >The database I'm contemplating doing this on takes about 6 hours to load
    >into InnoDB tables from a mysqldump dump.
    I suggest

    ALTER TABLE foo ENGINE=InnoDB;

    for each of the relevant tables. If your web site does not use
    transactions (considering the existing tables are MyISAM, I hope it
    doesn't), there's no need to lock out access at this point, but access
    might be stalled while the ALTER TABLE is running.

    Gordon L. Burditt
    Gordon Burditt Guest

  3. Moderated Post

    Default Re: converting to innodb with minimal downtime?

    Removed by Administrator
    Axel Schwenke Guest
    Moderated Post

  4. Moderated Post

    Default Re: converting to innodb with minimal downtime?

    Removed by Administrator
    Tim Smith Guest
    Moderated Post

  5. #5

    Default Re: converting to innodb with minimal downtime?

    Tim Smith <reply_in_groupmouse-potato.com> wrote:
    > In article <2tn57e.nn1.lnxl.homelinux.org>, Axel Schwenke wrote:
    >>
    >> Loading from dump is not particularly fast, because it is single-
    >> threaded. Also some people have AUTOCOMMIT on - this dramatically
    >> slows down the load.
    >
    > When I tried a load with AUTOCOMMIT off, a couple hours in it was killed.
    Do you set innodb_buffer_pool_size? The default of 8MB is *very*
    small and will cause failure of bigger transactions. There should
    be a clear message in the error log if this happens.
    >> 4. convert the tables to InnoDB (ALTER TABLE ... ENGINE=InnoDB)
    >
    > I've tried ALTER TABLE, and the result is a few hours in, it gets killed,
    > with a very large amount of stuff in the logs.
    Same thing.
    > In broad outline, that's essentially the same as my scheme, right? The
    > basic idea is get a second copy of the tables somewhere (in the same
    > database my way, in another database under another server your way), and use
    > the binary logs to catch up (using mysqlbinlog my way, tion your
    > way), and then swap the tables (rename my way, switch servers you way).
    The difference is, that tion works online. Under normal
    conditions a slave instance is less than a second behind the master.
    So you don't have to wait for the InnoDB instance to catchup with
    updates collected during table conversion.
    > What I like about your way is that the conversion work is done on a second
    > server, so if there are any problems, they don't affect the first server.
    It's still running on the same hardware. You may wish to have more
    memory. Bulk loading as well as table conversion profits much from
    a lot of memory. In your case you should increase the InnoDB buffer
    pool size as much as possible. InnoDB has to (re)create all indexes,
    so big buffers will save a lot of I/O.

    If you have a spare machine, you can install MySQL (but *exactly* the
    same version) and do the conversion there. Then copy the InnoDB table-
    space (ibdata + iblogs) back.


    XL
    --
    Axel Schwenke, Senior Software Developer, MySQL AB

    Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

Similar Threads

  1. MInimal requirements for developer edition
    By frankytown in forum Macromedia Flash Flashcom
    Replies: 0
    Last Post: April 27th, 07:40 PM
  2. Installation on minimal installed RedHAt
    By pokoyo in forum Macromedia Flash Flashcom
    Replies: 0
    Last Post: April 10th, 09:23 AM
  3. Minimal Install - CGI
    By ChronoFish in forum PHP Development
    Replies: 2
    Last Post: November 4th, 02:26 PM
  4. Replies: 0
    Last Post: September 16th, 01:19 PM
  5. forum downtime next weekend!
    By dave milbut in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 0
    Last Post: August 9th, 12:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139