Hi!
>>>>> "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)
Regards,
Monty
