Allocation of the 80-90% of resources for dbexport

Posted in 2007

Summary

A user on RHEL4/IDS 10 wanted to speed up a 2-hour 'dbexport -q' of a 40GB database, noting the CPU was 90% idle. Replies said dbexport is single-threaded and ASCII-based, so it can't be given more CPU; the bottleneck is likely disk/IO (check with iostat or sar -d), and writing to the journalled ext3 filesystem hurts. Suggested alternatives: use HPL, or parallel per-table UNLOADs with PDQ plus dbschema, or use ontape archives plus logical log backups for moving a database to another server. Side discussion clarified that on Linux 2.6 IDS opens block ('cooked') devices with O_DIRECT, making raw devices unnecessary. No benchmark result or final outcome was reported by the original poster.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Migration, Import/Export & Data Conversion

RUSTEM — Wed, 21 Mar 2007 08:04:25 — source: IIUG Forums & Mailing Lists

Hi, I'm using RHEL4/EMT64T and IDS10. There is a database with 40 GB size. The 'dbexport -q' takes about 2 hours. But the result of the sar is 90 %idle. I would like to speed up that process anyway. How to allocate more resources for dbexport? Or there are any methods/recomendations to reduce the backup time?

↪ replying to RUSTEM

Obnoxio The — Wed, 21 Mar 2007 08:21:45 — source: IIUG Forums & Mailing Lists

RUSTEM GISATULLIN said: > Hi, I'm using RHEL4/EMT64T and IDS10. There is a database with 40 GB size. > The > 'dbexport -q' takes about 2 hours. But the result of the sar is 90 %idle. > I > would like to speed up that process anyway. How to allocate more resources > for > dbexport? Or there are any methods/recomendations to reduce the backup > time? It sounds like your problem is more on the disk side. Are you exporting to a journalled filesystem, like ReiserFS? -- Bye now, Obnoxio "I'm astonished anyone pays real money for this crap." -- Cosmo -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

↪ replying to Obnoxio The

Keith — Wed, 21 Mar 2007 08:37:58 — source: IIUG Forums & Mailing Lists

On 21/03/07, Obnoxio The Clown <obnoxio@serendipita.com> wrote: > RUSTEM GISATULLIN said: > > Hi, I'm using RHEL4/EMT64T and IDS10. There is a database with 40 GB size. > > The > > 'dbexport -q' takes about 2 hours. But the result of the sar is 90 %idle. > > I > > would like to speed up that process anyway. How to allocate more resources > > for > > dbexport? Or there are any methods/recomendations to reduce the backup > > time? > > It sounds like your problem is more on the disk side. Are you exporting to > a journalled filesystem, like ReiserFS? > > -- > Bye now, > Obnoxio > > "I'm astonished anyone pays real money for this crap." > -- Cosmo > > -- Why are you using dbexport as your backup mechanism? You can only restore to this point in time (no roll forward of logged transactions), and you are stopping users accessing your data while the archive takes place. Also you are forcing the engine to pull the data off in ASCII (text) format, which is slow, rather than page images. Keith

↪ replying to Keith

RUSTEM — Wed, 21 Mar 2007 10:51:20 — source: IIUG Forums & Mailing Lists

I'm use ext3 filesystem. I need this backup mechanism for restoring database on other sides/servers. Is it possible to allocate more resources for dbexport process namely?

↪ replying to RUSTEM

Celso Cabral — Wed, 21 Mar 2007 11:10:26 — source: IIUG Forums & Mailing Lists

You should use HPL to speed up your export or many unload together (one for each table) and dbschema. using unload you can use PDQ. dbexport is just a process, to use more cpu resources depends on how many cpu do you have. if you have many cpu, the dbexport will use only one. maybe you have a disk bottleneck. you should use iostat or sar -d to check I/O. Best Regards, Celso Coimbra -----Mensagem original----- De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]Em nome de RUSTEM GISATULLIN Enviada em: quarta-feira, 21 de março de 2007 07:51 Para: ids@iiug.org Assunto: Re: Allocation of the 80-90% of resources for .... [8688] I'm use ext3 filesystem. I need this backup mechanism for restoring database on other sides/servers. Is it possible to allocate more resources for dbexport process namely? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to RUSTEM

ART KAGEL, — Wed, 21 Mar 2007 13:01:06 — source: IIUG Forums & Mailing Lists

Databases on journaling filesystems is a VERY bad idea. IDS and most RDBMSes perform best on RAW devices (on Linux using O_DIRECT on COOKED devices seems to perform equivalently and the latest releases of 9.40, 10.00, & 11.00 use O_DIRECT on Linux, so COOKED devices with no filesystems are OK on Linux). Use ontape archives and logical log backups to restore the server to another machine. It's safer and more reliable anyway. Art S. Kagel ----- Original Message ----- From: Rustem Gisatullin <ids@iiug.org> At: 3/21 6:52:03 I'm use ext3 filesystem. I need this backup mechanism for restoring database on other sides/servers. Is it possible to allocate more resources for dbexport process namely? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to ART KAGEL,

RUSTEM — Wed, 21 Mar 2007 14:39:52 — source: IIUG Forums & Mailing Lists

I mean, I'm using raw devices of course for all database spaces. The file system of ext3 type is just location for unloaded files by dbexport. Sorry for my question, but tell me please, how and where to set up O_DIRECT flag for cooked devices.

↪ replying to RUSTEM

ART KAGEL, — Wed, 21 Mar 2007 15:12:38 — source: IIUG Forums & Mailing Lists

IDS does this (opens the chunk files with the O_DIRECT flag set) automatically in the latest releases. You don't have to do anything. I don't remember what version of IDS you're running, but IBM tech support can certainly tell you if your release has that feature or if you should upgrade to obtain it. Art S. Kagel ----- Original Message ----- From: Rustem Gisatullin <ids@iiug.org> At: 3/21 10:40:26 I mean, I'm using raw devices of course for all database spaces. The file system of ext3 type is just location for unloaded files by dbexport. Sorry for my question, but tell me please, how and where to set up O_DIRECT flag for cooked devices. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to ART KAGEL,

RUSTEM — Wed, 21 Mar 2007 15:21:13 — source: IIUG Forums & Mailing Lists

Thanks a lot, I've got IDS 10.00.FC3R1. But I need to perform any benchmark before use cooked files rather than raw devices.

↪ replying to RUSTEM

ART KAGEL, — Wed, 21 Mar 2007 15:24:05 — source: IIUG Forums & Mailing Lists

It would be prudent to test this yourself, yes. Art S. Kagel ----- Original Message ----- From: Rustem Gisatullin <ids@iiug.org> At: 3/21 11:22:00 Thanks a lot, I've got IDS 10.00.FC3R1. But I need to perform any benchmark before use cooked files rather than raw devices. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to RUSTEM

Chris Salch — Wed, 21 Mar 2007 15:36:42 — source: IIUG Forums & Mailing Lists

As a note, ext3 is a journaling file system. You could still be seeing a performance hit due to that. On Wed, 2007-03-21 at 05:51 -0500, RUSTEM GISATULLIN wrote: > I'm use ext3 filesystem. I need this backup mechanism for restoring database > on other sides/servers. > > Is it possible to allocate more resources for dbexport process namely? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- -------------------------------------------- Chris Salch Programmer/Analyst LeTourneau University 903-233-3537

↪ replying to ART KAGEL,

RUSTEM — Thu, 22 Mar 2007 05:54:26 — source: IIUG Forums & Mailing Lists

You wrote earlier "..on Linux using O_DIRECT on COOKED devices seems to perform equivalently and the latest releases of 9.40, 10.00, & 11.00 use O_DIRECT on Linux, so COOKED devices with no filesystems are OK on Linux..." Please explain what do you mean - "so COOKED devices with no filesystems". How to create cooked files without filesystems?

↪ replying to RUSTEM

Andreas — Thu, 22 Mar 2007 09:13:12 — source: IIUG Forums & Mailing Lists

=46or Linux kernels 2.6 or later the use of 'raw' command is outdated. But= =20 most Linux distributions still maintain it for compatibility. IDS opens=20 the block devices with O_DIRECT flag and the kernel use it like raw=20 devices. O_DIRECT flag was introduced in IDS 10.0 with KAIO. See more in=20 Sandor's developerWorks article at=20 http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0503szabo/= #N100D0 Andreas On Thursday 22 March 2007 06:54, RUSTEM GISATULLIN wrote: > You wrote earlier "..on Linux using O_DIRECT on COOKED devices seems > to perform equivalently and the latest releases of 9.40, 10.00, & > 11.00 use O_DIRECT on Linux, so COOKED devices with no filesystems > are OK on Linux..." > > Please explain what do you mean - "so COOKED devices with no > filesystems". How to create cooked files without filesystems? > > > ********************************************************************* >********** Forum Note: Use "Reply" to post a response in the > discussion forum. =2D-=20 Andreas Breitfeld; Informix Development Munich IBM Deutschland GmbH; Vorsitzender des Aufsichtsrats: Hans Ulrich=20 Maerki; Gesch=C3=A4ftsf=C3=BChrung: Martin Jetter (Vorsitzender), Rudolf Ba= uer,=20 Christian Diedrich, Christoph Grandpierre, Matthias Hartmann, Andreas=20 Kerstan; Sitz der Gesellschaft: Stuttgart; Registergericht: Amtsgericht=20 Stuttgart, HRB 14562; WEEE-Reg.-Nr. DE 99369940

↪ replying to RUSTEM

Andreas — Thu, 22 Mar 2007 09:26:23 — source: IIUG Forums & Mailing Lists

hopefully following text looks better: =46or Linux kernels 2.6 or later the use of 'raw' command is outdated. But most Linux distributions still maintain it for compatibility. IDS opens the block devices with O_DIRECT flag and the kernel use it like raw devices. O_DIRECT flag was introduced in IDS 10.0 with KAIO. See more in Sandor's developerWorks article at http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0503szabo/= #N100D0 Andreas On Thursday 22 March 2007 06:54, RUSTEM GISATULLIN wrote: > You wrote earlier "..on Linux using O_DIRECT on COOKED devices seems > to perform equivalently and the latest releases of 9.40, 10.00, & > 11.00 use O_DIRECT on Linux, so COOKED devices with no filesystems > are OK on Linux..." > > Please explain what do you mean - "so COOKED devices with no > filesystems". How to create cooked files without filesystems? > > > ********************************************************************* >********** Forum Note: Use "Reply" to post a response in the > discussion forum. =2D-=20 Andreas Breitfeld; Informix Development Munich IBM Deutschland GmbH; Vorsitzender des Aufsichtsrats: Hans Ulrich=20 Maerki; Gesch=C3=A4ftsf=C3=BChrung: Martin Jetter (Vorsitzender), Rudolf Ba= uer,=20 Christian Diedrich, Christoph Grandpierre, Matthias Hartmann, Andreas=20 Kerstan; Sitz der Gesellschaft: Stuttgart; Registergericht: Amtsgericht=20 Stuttgart, HRB 14562; WEEE-Reg.-Nr. DE 99369940

↪ replying to RUSTEM

ART KAGEL, — Fri, 23 Mar 2007 12:46:37 — source: IIUG Forums & Mailing Lists

COOKED or block device files - as opposed to filesystem files or RAW (character) devices. Art S. Kagel ----- Original Message ----- From: Rustem Gisatullin <ids@iiug.org> At: 3/22 1:54:46 You wrote earlier "..on Linux using O_DIRECT on COOKED devices seems to perform equivalently and the latest releases of 9.40, 10.00, & 11.00 use O_DIRECT on Linux, so COOKED devices with no filesystems are OK on Linux..." Please explain what do you mean - "so COOKED devices with no filesystems". How to create cooked files without filesystems? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Glossary

dbexport / dbimport