Re[2]: dbexport too slow. Any other ideas?
--simple boundary
Content-Type: text/plain; charset=US-ASCII; name="Message Body"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment; filename="Message Body"
GREG_MAY@Non-HP-Roseville-om1.om.hp.com wrote:
> dbexport exports a complete database. But, its way too slow. Is there
> a way to export and load a database faster.
>
> g.
Greg:
I'm presuming that ontape/onbar etc aren't going to do it for you. There is an alternative, but it requires a little bit of work on your behalf. I hate dbexport - if your database is bigger than about 15GB, it is hopelessly inefficient. It also doesn't let you move tables around and play with extent sizes etc prior to execution (in v5 you couldn't even use -ss). Here's what I did when we migrated from v5 to v7 a while back:
Generate a full dbschema and edit it to suit your new DBspaces, extents etc. Ensure logging is turned off and run it on your new instance. Then on the old instance, execute the following:
UNLOAD TO "table.data" DELIMITER "|"
SELECT tabname, ncols, ncols * nrows
FROM systables
WHERE tabid > 99
ORDER BY 3 DESC;
The order by is to get the tables in rough order from largest to smallest. Then use awk or perl to read this file and generate 4 unload scripts in a round-robin fashion, where each one unloads to a different disk. Depending on what you're trying to achieve, you could possibly use onunload instead. Something like this:
perl -e '
open(Unld0, "> unload_1.sql") || die;
open(Unld1, "> unload_2.sql") || die;
open(Unld2, "> unload_3.sql") || die;
open(Unld3, "> unload_4.sql") || die;
open(Dbld0, "> unload_1.cmd") || die;
open(Dbld1, "> unload_2.cmd") || die;
open(Dbld2, "> unload_3.cmd") || die;
open(Dbld3, "> unload_4.cmd") || die;
@dsk = ("/disk_a","/disk_b","/disk_c","/disk_d"); #array of disks for writes
while(<>){
($tab, $cols) = split(/\\|/);
$r=$n++%4; #modulo to cycle through scripts: $n = Row number
$uno=sprintf("Unld%d", $r);
$dno=sprintf("Dbld%d", $r);
printf $uno "UNLOAD TO %s/%s.unl DELIMITER \\"|\\"\\n", $dsk[$r], $tab;
printf $uno " SELECT * FROM %s;\\n\\n",$tab;
printf $dno "FILE %s/%s.unl DELIMITER \\"|\\" %d;\\n", $dsk[$r], $tab, $cols;
printf $dno "INSERT INTO %s;\\n\\n", $tab;
}' table.data
Then execute the four unload_*.sql scripts in parallel. You can run as many of these as you have disks/capacity. I chose to use four.
As they finish, kick off the corresponding dbload script. One of the other benefits of this method over dbimport is that you don't have to start from scratch if you have a problem. You can fairly easily restart the dbloads from virtually any point.
Actually, when I did this I had one dbload script per table, and three processes polling for completed unloads. As they were identified, the corresponding dbload would be executed. Using this method I moved over 20GB of data in about 10 hours, including rebuilding indexes, statistics etc. The previously attempted dbexport was killed after 56 hours, 'cos we were running out of outage and were nowhere near complete!
Hope this helps.
RET
+------------------------------------------+
| Richard Thomas |
| DBA - Marketing Information Systems |
| Optus IT |
| email: richard_thomas@yes.optus.com.au |
| Ph: +61 2 9342 7188 |
| "My opinions are my opinions" |
+------------------------------------------+
--simple boundary
Content-Type: text/plain; charset=US-ASCII; name="RFC822.TXT"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment; filename="RFC822.TXT"
Received: from oznet16.ozemail.com.au by mail.workcover.nsw.gov.au (ccMail Link to SMTP R6.00.02)
; Wed, 06 May 98 13:07:28 +1000
Return-Path: <owner-informix-list@iiug.org>
Received: from iiug.iiug.org (www.iiug.org [206.175.175.254]) by oznet16.ozemail.com.au (8.8.4/8.6.12) with ESMTP id NAA04236 for <david.ashby@workcover.nsw.gov.au>; Wed, 6 May 1998 13:03:31 +1000 (EST)
Received: (from majordom@localhost) by iiug.iiug.org (8.7.5/8.7.3) id VAA29681; Tue, 5 May 1998 21:29:15 -0500 (CDT)
Received: from safer.optus.com.au (firewall-user@happy.optus.com.au [203.13.126.17]) by iiug.iiug.org (8.7.5/8.7.3) with SMTP id VAA29668 for <informix-list@iiug.org>; Tue, 5 May 1998 21:29:07 -0500 (CDT)
Received: by safer.optus.com.au; id MAA13014; Wed, 6 May 1998 12:24:39 +1000
Received: from prairiedog.optus.com.au(203.13.126.12) by safer.optus.com.au via smap (3.2)
id xman12909; Wed, 6 May 98 12:24:16 +1000
Received: from yes.optus.com.au ([161.43.15.17]) by prairiedog.optus.com.au
(Netscape Messaging Server 3.52) with SMTP id AAA16E1;
Wed, 6 May 1998 12:27:50 +1000
Message-ID: <n1317664772.16727@yes.optus.com.au>
Date: 6 May 1998 10:37:41 +1000
From: "Richard Thomas" <richard_thomas@yes.optus.com.au>
Subject: RE: dbexport too slow. Any other ideas?
To: GREG_MAY@Non-HP-Roseville-om1.om.hp.com,
"Informix Mail Group" <informix-list@iiug.org>
X-Mailer: Mail*Link SMTP for Quarterdeck Mail; Version 4.1.0
Mime-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"; Name="Message Body"
Content-Transfer-Encoding: quoted-printable
Sender: owner-informix-list@iiug.org
Precedence: bulk
--simple boundary
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Greg,
One of the things I do is move the primary key constraint out of the
create table statement and do an alter table to create the primary key
after the table is loaded. I had one table that had loaded about 2
million rows, out of 10 million, after 10 hours. I removed the primary
key constraint/index and it did the full load in around 2 hours.
Regards
David
--simple boundary--