Re: High Performance Loader and DB cloning

In article <34C53968.99C@eds.com>, Greg Moye <greg.moye@eds.com> writes >Has anybody managed to clone a large (i.e. lots of tables and lots of >data) database with the HPL? It looks very table by table oriented to >me after a scan through the manual. > >I occasionally need to clone an 8000+ table database, the faster the >better. Obviously unloading/loading table by table isn't going to get >it for me. > True. >Optimally I'd like to do something like "unload this entire database to >that bunch of disks" with it's schema then "load everything on that >bunch of disks to this other database, and apply schema to yield a >perfect clone" Am I dreaming?? > I think a program would be useful. The way I see it running is:- a) unload a dbschema file with table columns,indexes, contraints etc. That should be a single process run up front. This is because it will hit the system catalogs hard and you want to make sure that they are cached to give really good performance. Also you want to keep the disk heads over the system catalogs so that they can be unloaded as fast as possible. Interleaving access to table pages would probably move the disk heads around a lot and so reduce overall transfer performance. b) unload table data in parallel. One unload per chunk (assuming one chunk per disk). c) Load the tables again in parallel If this is going over a network and a clever program was written then what with waiting for the disk and the network card there should be some CPU resources left over. A small lightwieght compression program either end of the network connection could reduce network traffic and speed up the transfer. Something like unload | compress | rsh other_machine "uncompress | load" [Pipes are wonderful things - I like UNIX!!] Over course by using a sutiable fragmentation scheme and some intelligence we could achieve load balancing across the disks. To minimise disk seeks we would have to make sure that for each disk only one fragment on that disk was being loaded at any one time. Otherwise the disk heads would be seeking back and forth across the disk between the locations of the two (or more) fragments being loaded. If think I have discovered another programming project, now I have 4GL and hence ESQL/C at home I can start on them! >Thanks - Greg -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care