Post #207,284
5/13/05 3:02:14 PM
|
Rollback is different, some actions are non-reversible
With code, you roll out the new code, find out it's broken, roll back to the previous version. With a DB if you alter a schema and drop a column, that's not reversible, you've just dumped data.
Also, when making schema changes you frequenly have to run scripts to populate the new columns/tables, or modify the values of other columns to point to new key values. None of these operations are trivially recoverable.
Short version is you can snapshot a codebase and roll back to that snapshot. Once your DBs get into the hundreds of GB, snapshots just aren't feasible.
===
Purveyor of Doc Hope's [link|http://DocHope.com|fresh-baked dog biscuits and pet treats].
[link|http://DocHope.com|http://DocHope.com]
|
Post #207,289
5/13/05 3:16:48 PM
|
Ah. OK, I see what you mean. But...
DrooK expounds: With code, you roll out the new code, find out it's broken, roll back to the previous version. With a DB if you alter a schema and drop a column, that's not reversible, you've just dumped data. Huh? But isn't that what backups are for? (And/or SQL scripts, auto-generated just before the alteration.) Also, when making schema changes you frequenly have to run scripts to populate the new columns/tables, or modify the values of other columns to point to new key values. None of these operations are trivially recoverable. Well, perhaps not *totally* "trivially"... But not far from it, AFAICS. Short version is you can snapshot a codebase and roll back to that snapshot. Once your DBs get into the hundreds of GB, snapshots just aren't feasible. But WTF would you want to do that for?!? In your three-system scenario -- dev, test, prod -- only the last, the production system, would have "hundreds of GB"! What the fuck would be the use of having that much data on the other two?!? And the whole idea of having that "test" system in between is that you should *never have to* "recover" something one it's proven enough to go onto the prod system... So no, frankly I still don't see the problem. " Create table XXX_bak as select * from XXX;" just before an alteration on the test system is pretty much all you should need, AFAICS. Oh, and maybe the TOAD's " Save As / Generate Insert Statements" command. Really.
[link|mailto:MyUserId@MyISP.CountryCode|Christian R. Conrad]
(I live in Finland, and my e-mail in-box is at the Saunalahti company.)
Your lies are of Microsoftian Scale and boring to boot. Your 'depression' may be the closest you ever come to recognizing truth: you have no 'inferiority complex', you are inferior - and something inside you recognizes this. - [link|http://z.iwethey.org/forums/render/content/show?contentid=71575|Ashton Brown]
|
Post #207,290
5/13/05 3:53:18 PM
|
Maybe you're used to different hardware than I am
In your three-system scenario -- dev, test, prod -- only the last, the production system, would have "hundreds of GB"! What the fuck would be the use of having that much data on the other two?!? Load testing. How do you know what the response time is going to be like if you're testing against a small subset? Pages that return in <1 sec on a 10MB db might take a minute on a 100GM db. And the whole idea of having that "test" system in between is that you should *never have to* "recover" something one it's proven enough to go onto the prod system... Great. You just broke testing when you rolled Program A. Now how do you recover it to test Program B? "Create table XXX_bak as select * from XXX;" just before an alteration on the test system is pretty much all you should need, AFAICS. Are you used to insane hardware? I've seen that operation take several minutes. You can't do that on a 24/7 system. Clustering might also solve that issue, but it definitely wouldn't work on MySQL. But now we're into DB-specific implementation details, which is why I want a real DBA, not just someone who kind of knows their specific environment. ===
Purveyor of Doc Hope's [link|http://DocHope.com|fresh-baked dog biscuits and pet treats].
[link|http://DocHope.com|http://DocHope.com]
|
Post #207,366
5/14/05 2:34:34 AM
|
couple of points
if you're running MySQL, you need to know a [link|http://sql-info.de/mysql/gotchas.html|few things]. Note, that this isn't a complete list.
Presumably you're developing programs A & B at the some time on the dev system. So moving 1 to staging/test should be coordinated with the other, likewise from staging to production/live. Stable together on staging should mean stable on live. But, as they say "there's no test like production." So you're right to be concerned, but I think the approach can be pretty tightly targetted: you know what the database changes are--you know what can be undone. Some database changes can wait until after you're satisfied with the application change. (Things like dropping tables/views or columns can wait until you're certain you're not going back. Adding tables and columns can be done before the production roll...)
how 'bout mysqldump instead of create table ..._bak.... ?
Have fun,
Carl Forde
|
Post #207,544
5/15/05 11:21:55 PM
|
A mysqldump can be slower than a select * into ...
In fact, a mysqldump is not a terribly fast way to copy or backup the database at all. The quickest way is to grab the global read lock (FLUSH TABLES WITH READ LOCK), copy the files in the OS from one disk to another and release the global lock (UNLOCK TABLES). Actually, that's the second quickest way. The quickest way is to do that on a slave.
Wade.
Is it enough to love
Is it enough to breathe
Somebody rip my heart out
And leave me here to bleed
| |
Is it enough to die
Somebody save my life
I'd rather be Anything but Ordinary
Please
| -- "Anything but Ordinary" by Avril Lavigne. |
|
Post #207,613
5/16/05 11:58:16 AM
|
Not on MySQL any more
Was using that as an example. But as to your other point: Presumably you're developing programs A & B at the some time on the dev system. So moving 1 to staging/test should be coordinated with the other, likewise from staging to production/live. Stable together on staging should mean stable on live. How about when you have seven different programmers developing 12 different apps that all point to the same DB server? It's just not practical to say you can't start testing on any of them until yu're able to start testing on all of them. ===
Purveyor of Doc Hope's [link|http://DocHope.com|fresh-baked dog biscuits and pet treats].
[link|http://DocHope.com|http://DocHope.com]
|
Post #207,619
5/16/05 1:29:44 PM
|
Then you need to slow down. WTF is that place - Fever City?!
|
Post #207,628
5/16/05 2:07:00 PM
|
How many developers in your departement?
And what's the largest you've worked with? You're talking like you've never worked someplace with multiple programmers. Or maybe you're used to places so large that you have multiple programmers on each active project.
In my experience it's common to have one programmer working on an ordering system, one working on an accounting system, one working on reporting, one working on customer service, etc etc etc. All of these at some point touch the same customer and order tables, but none of the projects intersect with each other enough that the programmers need to care about each other.
Do you really work someplace with multiple programmers, and they're all working on the same app?
===
Purveyor of Doc Hope's [link|http://DocHope.com|fresh-baked dog biscuits and pet treats].
[link|http://DocHope.com|http://DocHope.com]
|
Post #207,395
5/14/05 10:13:26 AM
|
You should try an approach that works well for Peoplesoft
They add columns, populate, keep the old columns around until the next change. Then remove the old column when you add/replace another column. IOW Column Deletes are done the following change. It is a matter of PROCEDURE. YOU HAVE TO COMMIT TO IT, or you'll end up with a HUGE amount of data that is not needed.
Yes, it bloats the DB, but then you have a neat feature of Covering Your Ass.
--
[link|mailto:greg@gregfolkert.net|greg],
[link|http://www.iwethey.org/ed_curry|REMEMBER ED CURRY!] @ iwethey
[image|http://www.danasoft.com/vipersig.jpg||||]
|
Post #207,396
5/14/05 10:25:24 AM
|
gotta write that one down, thanx
All tribal myths are true, for a given value of "true" Terry Pratchett
[link|http://boxleys.blogspot.com/|http://boxleys.blogspot.com/]
Any opinions expressed by me are mine alone, posted from my home computer, on my own time as a free american and do not reflect the opinions of any person or company that I have had professional relations with in the past 48 years. meep
questions, help? [link|mailto:pappas@catholic.org|email pappas at catholic.org]
|
Post #207,515
5/15/05 5:57:37 PM
|
Already do that
But there's still two problems.
1) Are you sure nothing else is using that column you're about to drop? Really really sure? Because you may not have been religious about using stored procedures for updates. in fact, if you're using MySQL then you definitely weren't.
2) Yes, you have to commit to it. Who's responsible for making sure it's followed? Who's responsible for defining the procedure to begin with?
Bottom line, any non-trivial DB should be run by a non-trivial DBA. I am a trivial DBA.
===
Purveyor of Doc Hope's [link|http://DocHope.com|fresh-baked dog biscuits and pet treats].
[link|http://DocHope.com|http://DocHope.com]
|
