Hi! Thanks for the detailed reply!
Regarding the hot backup method that the other guys use, sounds like a dodgy method of doing anything to be honest. It would have to have a fairly decent performance hit... Regarding the rollback of ALTER, DROP and RENAME statements, the main use that I've seen for it is upgrades of custom software. It's handy should an ALTER TABLE fail or similar. That's the sole reason that one developer I know of deploys SQLBase to their clients. Thanks again! Regards, Chris On Sun, 2003-12-14 at 08:00, Harrison Fisk wrote: > Hi, > > On Sat, 13 Dec 2003, Chris Nolan wrote: > > > 1. We all know that InnoDB can be backed up "hot" (by various means). I > > know that there are a few MS SQL Server (ick) and DB2 lovers in the > > group I'll be meeting with this week. I also know that these two > > databases do a form of online backup. > > > > Given that they are not multiversioned, how on earth do they actually > > provide this functionality? I guess this ties in with how they implement > > the READ REPEATABLE isolatation level. Any comments on implementation, > > performance and other info would be gladly received! > > I believe they handle it by doing a dirty read first of the database, that > is while other people are writing to the files they just copy it. Then > they use the undo/redo logs in order to correct it, just as if a crash > occured. I could be wrong here, I am not an expert on those databases. > > > 2. I've been told on good authority (by persons on this fine list) that > > Sybase and PostgreSQL (and, from personal experience, SQLBase) support > > ROLLBACK of DDL statements such as DROP TABLE, ALTER TABLE, RENAME TABLE > > etc. From what I can gather, neither BDB nor InnoDB do this. > > > > Does anyone know what sort of technical challenges making the above > > statements "undoable" involve over and above INSERT, DELETE and UPDATE > > statements? Would this functionality be something that MySQL AB / > > Innobase Oy would be interested in developing should it be sponsored? > > First it isn't that common to want to undo these. I would ask yourself > first, why do you want to do this? Generally these commands are not run > on production servers. The only time that I could see them being useful > is if you are running an "upgrade" of your schema to switch to a newer > version of some application you wrote. In this case you would want to > take a backup before this was done anyways. > > Of course if you are really really interested, you should contact > [EMAIL PROTECTED] to get more details about cost and effort. I suspect that > it is more than just a little effort, but I could be entirely mistaken. > > > 3. At the moment, the MySQL API seems to have a size limit of 16 MB for > > data sent over the wire (I have seen that the MySQL 4.1 libraries allow > > for sending information in chunks along with prepared statements). I > > take it the best method of inserting greater amounts of data into a > > column would be by first writing the file somewhere on the database > > server and using LOAD DATA? Any comments on this of any type from the > > learned populace of MySQL users? :-) > > No. MySQL 3.23 has the 16M limit for max_allowed_packet. In MySQL 4.0 it > can be setup to around 1G of size. Keep in mind that moving around 1G > rows however will take a lot of overhead in the form of memory (both on > the client and the server). > > > 4. In a DB server that has 2 physical disks running MySQL 4.1.1 Alpha > > and utilising the multiple table space feature of InnoDB, what > > distribution of files (ibdata, log files, individual table space files) > > is likely to result in the best performace? Any insights of similar type > > for using MyISAM tables? > > Obviously splitting up usage across many disks is a good thing (if you > don't have a RAID system which effectively does this for you...) > > Where you put things depends on whether you are going for more performance > or for more reliablity. For reliability the Binary Update logs should > generally go on their own disk. That way you can still recover your > data if the main hdd stops working. This is true with both MyISAM and > InnoDB. For performance with InnoDB the InnoDB log files should be moved > to a seperate disk if possible as well. Then if you still have disks left > over you can begin moving individual tables to seperate disks as well. > > With only two regular hdds it is a tough decision. For reliability you > should keep the binary update logs and the data on seperate hdds. > However then you don't really gain much performance, so it is tempting to > move the InnoDB logs or spread the tables across both drives. For > performance the InnoDB logs ideally will be on a different harddrive > especially if innodb_flush_logs_at_tx_commit is set to 1. > > Hope that helps some! > > Regards, > > Harrison -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
