Chris, ----- Original Message ----- From: "Chris Nolan" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Saturday, December 13, 2003 7:24 AM Subject: Questions about MySQL implementation
> Hi all! > > I've got a few questions that I was hoping some of the fine readers of > this list could help me out with. I'll probably be going into a > development meeting this coming week and will need to have some > information up my sleave to ensure the mighty MySQL is selected as the > database backend for the application being developed. > > 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! the mechanism used in InnoDB Hot Backup is replaying the generated ib_logfile log. It is much lower level than the multiversioning of InnoDB. That is why the same technique would work for DB2 and SQL Server. > 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? Not very difficult: we could keep the 'old' table until the transaction commit. In a rollback we would fall back to the old table. But the demand for such a feature is so low that most databases do not have a rollback of DDL statements. > 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? :-) > > 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? I would just create several ibdata files and place them round-robin on different physical disks. Also, putting the ib_logfiles to a separate disk might be a good idea. For MyISAM, I would use symlinks to distribute big tables to different physical disks. > All responses will be gratefully received! > > Regards, > > Chris Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
