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]
