Dbexport very slow

Posted in 2018

Summary

A user reported that dbexport of a 180 GB database suddenly became much slower after a weekend bulk insert of invoices (only ~57 MB of new data), and asked whether missing/stale UPDATE STATISTICS was to blame (high-mode stats weren't possible due to limited temp space). Replies noted dbexport reads tables sequentially, so statistics should be largely irrelevant; one user claimed stats helped on small databases, another suggested skipping big tables with --no-data-tables. Fernando Nunes advised checking the session's wait state (I/O, CPU, netnorm/client wait), the client machine and destination filesystem, and raising FET_BUF_SIZE (e.g. 256000) to enlarge the client/server buffer. No confirmed cause or resolution is recorded.

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

Topics: Storage & Space Management, Migration, Import/Export & Data Conversion

GUSTAVO ECHENIQUE — Wed, 25 Apr 2018 11:33:21 — source: IIUG Forums & Mailing Lists

Dear colleagues: Since Monday I have noticed that the dbexport of my database takes much longer than usual. Last Saturday a process of massive insertion in the same has been executed, through the generation of invoices. Can the update statistics influence the slowness of the dbexport ?. I can not do an update statistics high because the temporary chunks I have do not reach. How else can the export process be accelerated? Of course, I appreciate your attention.

↪ replying to GUSTAVO ECHENIQUE

TANJA Mï¿½LLER — Wed, 25 Apr 2018 12:05:31 — source: IIUG Forums & Mailing Lists

dbexport supposedly unloads the tables by reading sequential. Update statistics should be irrelevant in this case. What about the amount of data produced by that massive inserts?

↪ replying to TANJA Mï¿½LLER

GUSTAVO ECHENIQUE — Wed, 25 Apr 2018 12:55:20 — source: IIUG Forums & Mailing Lists

Hi Tanja! The total data inserted last Saturday is not much, they are 57 MB. The base has 180 GB. What amazes me is that as of Saturday the slowness began to be noticed.

↪ replying to GUSTAVO ECHENIQUE

Jack Parker — Wed, 25 Apr 2018 13:13:22 — source: IIUG Forums & Mailing Lists

dbexport? Do you mean dbimport? j. > On Apr 25, 2018, at 8:55 AM, GUSTAVO ECHENIQUE <gustavo.echenique@cemdo.com.ar> wrote: > > Hi Tanja! > > The total data inserted last Saturday is not much, they are 57 MB. > > The base has 180 GB. > > What amazes me is that as of Saturday the slowness began to be noticed. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to Jack Parker

GUSTAVO ECHENIQUE — Wed, 25 Apr 2018 13:20:20 — source: IIUG Forums & Mailing Lists

Hi Jack! No, I mean dbexport, for data export.

↪ replying to GUSTAVO ECHENIQUE

Jack Parker — Wed, 25 Apr 2018 13:27:59 — source: IIUG Forums & Mailing Lists

You confused me with the insert. j. > On Apr 25, 2018, at 9:20 AM, GUSTAVO ECHENIQUE <gustavo.echenique@cemdo.com.ar> wrote: > > Hi Jack! No, I mean dbexport, for data export. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to GUSTAVO ECHENIQUE

Ivan Zavis — Wed, 25 Apr 2018 13:34:03 — source: IIUG Forums & Mailing Lists

Update statistics have positive influence on speed of dbexport, definitely. I have small databases, and I do update statics medium (on database = all tables), and after that, dbexport is faster. Maybe is matter update statistics on systables ... Another acceleration is not export all (big) tables every day, if some tables is not very important, with parameter: dbexport database -no-data-tables=some_arhive_table, some_log_table (if you have newest informix version ...) On 25.04.2018 13:33, GUSTAVO ECHENIQUE wrote: > Dear colleagues: > > Since Monday I have noticed that the dbexport of my database takes much longer > than usual. Last Saturday a process of massive insertion in the same has been > executed, through the generation of invoices. > > Can the update statistics influence the slowness of the dbexport ?. I can not > do an update statistics high because the temporary chunks I have do not reach. > > How else can the export process be accelerated? > > Of course, I appreciate your attention. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to Ivan Zavis

GUSTAVO ECHENIQUE — Wed, 25 Apr 2018 13:59:21 — source: IIUG Forums & Mailing Lists

Hi Ivan, very thanks!

↪ replying to Ivan Zavis

Fernando Nunes — Thu, 26 Apr 2018 00:01:52 — source: IIUG Forums & Mailing Lists

I honestly can't imagine a good reason why UPDATE STATS would fasten a dbexport (Obnoxio would probably disagree with me...). The only possible explanation for that, would be that if your databases are really small and you do the export immediately after the stats, some of the data will be in memory... As for the original question, I would try to see what's the status of the database session doing the export... waiting for I/O? Waiting on client (netnorm?) and also the general machine status. The bottleneck could be on the engine side (all the usual ones like I/O, CPU etc.) or on the client side (dbexport command) if there is not enough CPU on the machine, or if the destination filesystem is slow. The session stat will give us leads on what's wrong. Apart from that, if you have a version about 11.70 (can't remember the fixpack) you can define FET_BUF_SIZE=256000 (for example) to increment the default size for the communication buffer between client and server. The default is I believe 4096... you may need to try different values to find the optimum one for your system. This will give you a bit more performance when there is no evident bottleneck like CPU or I/O... Regards. On Wed, Apr 25, 2018 at 2:34 PM, Ivan Zavis <ivan.zavis@mi-system.co.rs> wrote: > Update statistics have positive influence on speed of dbexport,> definitely. > > I have small databases, and I do update statics medium (on database = > all tables), and after that, dbexport is faster. > Maybe is matter update statistics on systables ... > > Another acceleration is not export all (big) tables every day, if some > tables is not very important, with parameter: > dbexport database -no-data-tables=some_arhive_table, some_log_table (if > you have newest informix version ...) > > On 25.04.2018 13:33, GUSTAVO ECHENIQUE wrote: > > Dear colleagues: > > > > Since Monday I have noticed that the dbexport of my database takes much > longer > > than usual. Last Saturday a process of massive insertion in the same has > been > > executed, through the generation of invoices. > > > > Can the update statistics influence the slowness of the dbexport ?. I > can > not > > do an update statistics high because the temporary chunks I have do not > reach. > > > > How else can the export process be accelerated? > > > > Of course, I appreciate your attention. > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

Dbexport very slow

Glossary

Your privacy choices