Has anyone notices that the dbunload will slow down to in the middle of its process. I have 800 tables to unload and reload (going from 8 to 12 using -ar). At about 270 table processed it goes from using 40 percent of the CPU to under 1 percent. Sometimes is goes to 0 for upto a minute. The database is 30 gig in size and the process is at a virtual standstill. It work out to about 15 minutes for the first 260 tables and the next 5 have taken over 30 minutes and counting. Here is the command line I used. "C:\Program Files\SQL Anywhere 12\Bin32\dbunload.exe" -v -c "UID=dba;PWD=####;DBF=E:\ASA12work\ASA8Extract\ksWicfull.db" -ii -ar "E:\ASA12Messages"

ASA version info: SQL Anywhere Personal Server Version 12.0.1.3298

asked 02 May '11, 16:22

Jturner's gravatar image

Jturner
1816714
accept rate: 66%

edited 02 May '11, 19:03

Graeme%20Perrow's gravatar image

Graeme Perrow
8.5k371109


I stopped the procees and rebooted my desktop and now things seem to be moveing along just fine. I expect that it will take somewhere around 14 hours to complete. The working enviroment is a USB drive not the best but I needed the room to work.

permanent link

answered 02 May '11, 19:19

Jturner's gravatar image

Jturner
1816714
accept rate: 66%

There could be a number of reasons why you are seeing the unload appear to slow down:

  • a change in usage pattern on the computer. I.e. the database (or computer) may be used move heavily than when the unload started
  • the table(s) that you are unloading when the slowdown occurs could be much larger than the ones unloaded during the initial (faster) period - i.e. larger tables take longer to unload than smaller ones
  • the table(s) that you are unloading when the slowdown occurs could be much more fragmented and hence more I/O is required to "jump around" the table in order to unload the data in primary key order
  • combination of the two above: the table(s) is really large - larger than what can fit into the cache and the table is fragmented so the server ends up thrashing (i.e. pitching one page out of the cache to read another only to find out that it needs to reread a page that it has pitched earlier)
  • change in the disk usage pattern on the computer; other programs (or the database server itself) could be using more I/O for other purposes and hence slowing down the I/O to complete the unload
  • an example of increased I/O for other purposes: a checkpoint may have been started during the unload and this will (briefly) stop all other operations from running and then will run for a period of time (in the background) to flush all dirty cache pages from the cache. I.e. an unload which is done during the same time that (a varying amount of) updates are occurring on the database could result in varying unload performance.

You could try using -u switch so that the data is unloaded unordered to see if this improves the unload time. Increasing the cache size (-c switch) may also help.

permanent link

answered 02 May '11, 16:49

Mark%20Culp's gravatar image

Mark Culp
23.2k9132272
accept rate: 40%

edited 02 May '11, 16:50

Mark, the process has been stuck on a single table for about 50 minutes now. I am the only one using the computer and not doing anything but watching to see the progress. The process has unload tables of over 8,000,000 rows earlier and not spent more than 2 minutes. It is now siting at the same listed file. The database window keeps reporting the cache size is being adjusted but the value reported is the same as the last cache adjustment. PROCEXP show no cpu actiity and no disk IO to speak of. Would you recommend that I start fresh?

(02 May '11, 16:56) Jturner
Replies hidden

Your description does make it sound like the unload is stuck. The question is "Is it really stuck" or is it just moving really slowly for some reason.

The fact that the cache size is constantly changing size (presumably increasing in size?) could mean that the server is thrashing. Stopping and then restarting with a large cache size could help. What size is the server currently using?

How many rows are in the table where the server appears stuck?

Can you determine if any progress is being made - e.g. is the size of any of the dat files increasing in size?

Do you have any dbspaces?

(02 May '11, 17:17) Mark Culp

Mark, the database files were created fully sized so I am not seeing file changes. There are no dbspaces. The database is checkpointing every 20 minutes. I am going to restart the process and see if it will complete.

(02 May '11, 18:04) Jturner
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×41

question asked: 02 May '11, 16:22

question was seen: 4,850 times

last updated: 02 May '11, 19:19