dbunload slowdown ASA 12

[Former Member]

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

[Former Member · Accepted solution]

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.

[MarkCulp]

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.

[Former Member]

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?

[MarkCulp]

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?

[Former Member]

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.