List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:January 13 2000 9:53pm
Subject:feature request
View as plain text  

>>>>> "Kyle" == Kyle Hayes <kyle540@stripped> writes:

Kyle> Here's one that I don't think I've seen before and judging from the docs,
Kyle> it looks like it _might_ be easy to implement.

Kyle> According to the documentation, running OPTIMIZE on a table does the
Kyle> following:

Kyle> 1) create a new table and copy the old data (without the deleted data)
Kyle> into it.

Kyle> 2) all updates go to the new table.

Kyle> 3) selects go to the old table

Kyle> 4) once the copy is complete, remove the old table and rename the new one
Kyle> to the old name.

Where did you read the above?  Currently we don't provide 2).

I assume you messed the following section from the manual:

While @code{OPTIMIZE TABLE} is executing, the original table is readable
by other clients. Updates and writes to the table are stalled until the
new table is ready.

In other words, it works exactly as if you had a READ LOCK on the table.

Kyle> What I would dearly love to see for back up purposes is this:  Create a
Kyle> command (either command-line or in SQL) that does all the steps of
Kyle> OPTIMIZE, but doesn't do step 4.  Just don't erase the old table.  Rename
Kyle> it or something.

Kyle> This would allow me to get snapshots of a table.  Ideally this could be
Kyle> applied to a database at a time, but it would still be great if it worked
Kyle> only on tables.

Kyle> It seems (at least from the documentation) like this is almost identical
Kyle> to the OPTIMIZE code.  The manual is a little ambiguous as to whether or
Kyle> not read access continues to be allowed during the copy.  I need that. 
Kyle> Write access can stall briefly, but not the entire time a large table
Kyle> takes to copy.

In this case, you can just READ LOCK the tables and then only do a
'copy' of the .frm, .MYD and .ISD files;  This is the fastest way to
get a snapshot!

Kyle> What I want (need really) is the ability to generate a snapshot of the
Kyle> database at a given time.  It seems like this method would be dramatically
Kyle> faster and "more atomic" than trying to do a SELECT * on the table.

Kyle> What I propose is this kind of syntax:

Kyle> SNAPSHOT TABLE <table> ON <new name for old table>

Kyle> I don't want the indices or anything that can be rebuilt, I just want the
Kyle> data.  If I add a column, that is my problem.

Kyle> Is this possible?  I looked through the TO DO list and I didn't see
Kyle> anything about snapshots.  The closest thing I saw was something that
Kyle> would extend mysqladmin to copy a database.

Kyle> Would the gurus please comment?  This would be invaluable not just for
Kyle> back ups, but also for end-of-time-period business processes.  We need to
Kyle> generate several roll-up tables and it is frustrating to have stuff
Kyle> continue to be updated when we do this.  Our roll-up cut off time is
Kyle> blurry.  Not to mention that when trying to do a roll-up of all the data
Kyle> in a table, the server gets hit pretty hard for a bit.  If we could
Kyle> snapshot the table and copy it somewhere else for dissecting, then we could
Kyle> keep the existing table live and just run the roll-up queries on a
Kyle> different server.

If your tables are not VERY big, just doing READ LOCKS and copy should
be fast enough.

(The proposed mysqladmin copy would just do the above)

feature requestKyle Hayes13 Jan
  • feature requestMichael Widenius14 Jan
    • Re: feature requestTim Bunce14 Jan
      • Re: feature requestKyle Hayes14 Jan
        • Re: feature requestTim Bunce14 Jan
          • Re: feature requestKyle Hayes14 Jan