Re: how to copy large data fast

Hate to tell you this, but MySQL has the feature to turn logging on/off per table. Very nice feature. You flip it into MyISAM to load/unload, then flip it back to InnoDB--or whatever other format it was as a logged table. Very easy, and great for lazy people like me. I hate having to think, although they do pay me for some of that. Regarding Informix, it's the ondblog command to turn logging on/off. The biggest issue is that I believe you have run a level-0 backup, but hey, that's life. Certainly easier than all kinds of complicated scripts n shyte. See Also: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.admin.doc/admin464.htm and you can also use ontape: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.admin.doc/admin464.htm l8r Ian Michael Gumby wrote: > > > >> From: Suppository Admin <suppositoryadmin@the-suppository.com> >> If the new database is not being used by anyone, you could certainly >> turn logging off on the receiver database, run the load, then turn >> logging back on. >> >> Same for the database with the data, if no one is in it, turn the >> logging off, run the unload, then turn the logging back on. >> > > Err yes. Thought about that. But since I'm not a DBA, but an architect > and app designer, I wasn't sure how Informix would cope. Thought there > was a new type of table that didn't have logging introduced in 10. But > then again, I was falling asleep during that presentation. ;-) > > But it goes back to my point. > Going directly from one table to another is easier. > >> >> Ian Michael Gumby wrote: >> > >> > >> > >> >> From: "Keith Simmons" <smiley73@googlemail.com> >> > >> >> > >> >> Need to watch logging, both databases need to be the same so there may >> >> be a requirement for lots of log space on the target to avoid long tx, >> >> also the number of locks used may come into play if the tables have a >> >> large number of records. However that aside I agree this is the >> >> fastest method. >> >> >> >> Keith >> > >> > Locks? >> > Hmmm. I guess you haven't tried to lock the table so that there is only >> > 1 lock per table per database? >> > >> > Ok, so here's you other "problem". >> > >> > If you chose the simplest method, of a single insert statement with an >> > embedded select statement, yes, you will have an issue of a long query >> > in the IDS 10 database. >> > Considering that this a new box being built, there shouldn't be anyone >> > else on the box and you can control your log file issues. (Or use rowid >> > to control the select and insert size.... ;-) >> > >> > If you were going to write your own esql/c program, you would run in to >> > the standard librarian/reader mutex issue. One thread to perform the >> > select against IDS 7.x and n threads to read. >> > >> > Haven't played with HPL, but does it allow for a select statement as >> its >> > input or a named pipe? >> > While you'll have some parallelization, you'll also have additional >> > overhead to contend with. >> > >> > Now I know that there are other methods that could be "faster" like >> > using a combination of a shared memory queue and multiple writers and >> > readers for each table. (Note: you could create a system where you >> have >> > a shared memory queue for each table you are downloading. For each >> > shared memory queue, you have multiple readers where each reader >> opens a >> > TCP/IP socket to the other (IDS 10) machine, and on each of those >> > sockets, you have one reader and multiple writers, each being able to >> > control how many transactions they want per commit. (Which would flush >> > the logs.) This would be a fairly complex solution, however, how much >> > faster would it be? You're going to be limited by network bandwidth, >> > amount of memory, processor speed, and disk speed.) >> > >> > So, to wit, >> > The simplest and probably most efficient solution would be to account >> > for the table sizes with respect to logging, and then use an insert >> > statement with an embedded select. >> > >> > But hey! What do I know? >> > I'm talking in terms of theory. I'd take the lazy approach and do the >> > insert statement. >> > (And if necessary, use the rowid to help break down the table in to >> more >> > manageable transactions. ;-) >> > >> > -G >> > >> > _________________________________________________________________ >> > Find sales, coupons, and free shipping, all in one place! MSN Shopping >> > Sales & Deals >> > >> http://shopping.msn.com/content/shp/?ctid=198,ptnrid=176,ptnrdata=200639 >> > >> >> _______________________________________________ >> Informix-list mailing list >> Informix-list@iiug.org >> http://www.iiug.org/mailman/listinfo/informix-list > > _________________________________________________________________ > Your Hotmail address already works to sign into Windows Live Messenger! > Get it now > http://clk.atdmt.com/MSN/go/msnnkwme0020000001msn/direct/01/?href=http://get.live.com/messenger/overview > >