loadercli timeout

[Former Member]

Hello

I'm unable to export data via loadercli from a perfectly running maxdb instance (7.5.0.34)

I'm able to backup the db but i need to export a single user

The only way i know so far is to use loadercli

with the

TABLEEXTRACT USER DATA OUTSTREAM FILE <file> RECORDS

command.

When i launch this the command simply seems to hang, no output file is open, nor written

and the db seems to continue to work correctly

I look into knldiag.err and other db files but i see no errors there.

After a while (ten minutes or so) i get

ERR -25010

-25009

Failure in db communication (request): 3 = connection broken by TIMEOUT.

and the loadercli stops without any data output written.

If i trace the loadercli i see that it is waiting on a semop system call

(maybe is waiting the database to do the extraction operation?)

I remember to have done this in the past without problems and really nothing has changed

as far as i know since this is a stable release running some portals.

Question are:

What could be the cause?

Is possible to extend this timeout?

There is any other way to export data of a single db user?

thanks in advance

[Former Member]

Hello,

can you execute

select * from domain.locks

Maybe the loader waits for a lock request.

Best Regards

Wolfgang

[Former Member]

Actually

select * from domain.locks

does not return records

[Former Member]

Hello,

did you change from IP4 to IP6 for the ip adresses.

Best Regards

Wolfgang

[Former Member]

No, we are using IP4

[Former Member]

I've solved the problem enlarging the SESSION_TIMEOUT parameter

(original value 15 minutes)

The strange behaviour that i still i don't understand is that

it takes half an hour prior to start writing the data output file

for a database of 100K size.

Strangely i've noticed that when i start loadercli

in db manager i see a lot of sql calls execute while

the database is substantially not used (just loadercli

running to do the export)

Anyway the problem was solved

[markus_doehr2]

> Strangely i've noticed that when i start loadercli

> in db manager i see a lot of sql calls execute while

> the database is substantially not used (just loadercli

> running to do the export)

What's the output of

x_cons <DB> sh ac 1

Do you see any activity when this happens?

Markus

[Former Member]

This is the output of the command you suggest while loadercli running and still not writing data

Loadercli was launched by 3 minutes

Loadercli trace

poppea.company.icteam.it/root> strace -p 8865

semop(71139357, 0xbfffb0e8, 1

x_cons output

poppea.company.icteam.it/root> x_cons ELPVA sh ac 1

SERVERDB: ELPVA

ID UKT UNIX TASK APPL Current Timeout Region Wait

tid type pid state priority cnt try item

T48 7 -1 User 8865 IO Wait (R) 0 0 2 48832(s)

SERVERDB: ELPVA

T48 7 -1 User 8865 IO Wait (R) 0 0 4 49025(s)

SERVERDB: ELPVA

T48 7 -1 User 8865 IO Wait (R) 0 0 2 49245(s)

SERVERDB: ELPVA

T48 7 -1 User 8865 IO Wait (R) 0 0 5 49454(s)

SERVERDB: ELPVA

T48 7 -1 User 8865 IO Wait (R) 0 0 3 49577(s)

SERVERDB: ELPVA

T48 7 -1 User 8865 IO Wait (R) 0 0 4 49779(s)

SERVERDB: ELPVA

T48 7 -1 User 8865 IO Wait (R) 0 0 1 49953(s)

SERVERDB: ELPVA

T48 7 -1 User 8865 IO Wait (R) 0 0 2 50152(s)

SERVERDB: ELPVA

T48 7 -1 User 8865 IO Wait (R) 0 0 5 50345(s)

SERVERDB: ELPVA

T48 7 -1 User 8865 IO Wait (R) 0 0 4 50526(s)

[markus_doehr2]

> This is the output of the command you suggest while loadercli running and still not writing data

... but the database is reading

> ID UKT UNIX TASK APPL Current Timeout Region Wait

> tid type pid state priority cnt try item

> T48 7 -1 User 8865 IO Wait (R) 0 0 2 48832(s)

I don't know how the loadercli works internally but it seems, all the database is first loaded into the cache and then written to the file. Since MaxDB reads by default in 8 kb pages from disk sequentially and depending of the size of the table this can take some time.

You're using a pretty old version (7.5.0.34), if you'd update to 7.6.06.x then you could enable prefetching which would increase reading speed.

As what I can see it works "normal".

Markus

[Former Member]

At the end using a bigger SESSION_TIMEOUT i was able to do the work

The strange behaviour is that the db is just 6Megabytes when exported

so to me it does not make sense so much time to finally export 6 Megabytes.

Furthermore is seems a problem just releated to this istance.

I have other istances where loadercli exports the data in much less time

even for bigger databases (> 1 gigabytes)

Anyway...thank for the support!

[markus_doehr2]

> The strange behaviour is that the db is just 6Megabytes when exported

> so to me it does not make sense so much time to finally export 6 Megabytes.

>

> Furthermore is seems a problem just releated to this istance.

> I have other istances where loadercli exports the data in much less time

> even for bigger databases (> 1 gigabytes)

- same DATABASE_CACHE/CACHE_SIZE on those?

- same version?

Markus

[Former Member]

Same machine

Same maxdb installation

DB1 (slow loadercli)

CACHE_SIZE 6000

DB2 (normal loadercli)

CACHE_SIZE 3000

DATABASE_CACHE ...i didn't found this parameter

I've changed the DB1 CACHE_SIZE to 3000

but nothing changes

thanks

[lbreddemann]

Hello Stefano,

could you tell us a bit more about the database that took so long to export?

How many tables are in it and how many of them did you export?

Do you still have the parameters you used to run the export? If so, please post them.

I would guess (a.k.a. not knowing) that the the large amount of page accesses is due to catalog scans...

regards,

Lars

[Former Member]

The database has just some hundreds records in every tables...it is nearly empty.

The number of tables is around 200.

To export command i use is the following

loadercli -d psfdev -u dba,dba -E 0 <<EOF

USE USER user password

SQLMODE ORACLE

SET MAXERRORCOUNT 999999

CATALOGEXTRACT USER OUTSTREAM FILE 'filename'

EOF

If you need any other information let me know

thanks