Unload / Reload - Making it faster
kevinmcgrain
like to upgrade to 9. The problem is that this is an online
system and we can't afford to take it down to perfom the
upgrade. We have performed a benchmark unload / reload
scenario in our office on the DB and the entire process
takes about 12 hours. This is too long in that by the time
the unload / reload process finishes, the main DB is now out
of synch. I know we could use the log files to bring the
new DB up to date but the whole process seems risky. We
have been wanting to upgrade for some time but feel like we
are stuck due to the amount of time it takes and the risk
involved. My question is: Is there anyway to make this
process faster? Would purchasing a solid state disk for the
conversion be a worthwhile investment? Are we just paranoid
about the process? Our DB is roughly 6GB. How has other
people handled this task of unload / reload on production DB
when the main DB is still running?
Also - is Version 9 the proper one to migrate to? Is it
100% stable. We run on Solaris. Which version will be the
best in terms of reliability and performance? Version 7 has
been rock solid without 1 incident in about 5 years
!!!!!!!!!!!!!!
Thanks for any feedback.
Paul Horan[TeamSybase]
your reload script?
One of the techniques we use is to add the -b switch to the newly init'ed
database startup command, so that it's in BULK mode and none of the LOAD
TABLE statements get logged. And the more cache you can allocate to the
process, the better...
Paul Horan[TeamSybase]
<Kevin McGrain> wrote in message news:43f1f2d1.466...@sybase.com...
Breck Carter [TeamSybase]
is only 6GB in size.
When you run your benchmark, make sure the disk is defragmented and
has lots of free space. Also, run dbunload.exe as a separate step,
followed by dbinit and dbisql to reload. That way, you can determine
which part of the process takes too much time.
It is possible your current database is internally fragmented, which
might slow down the unload step. It might also be slowing down current
operations.
Do you have absolutely *no* window of opportunity to take the database
offline? If the answer is "that's correct" then we can explore
alternatives, but they are probably going to be much more difficult
than taking the database offline.
Version 9.0.2 is the target you should use, probably the latest EBF
build 3249 for Solaris.
You should test your applications on the new database before switching
over. Also, pay close attention to the *several* sections in the Help
that talk about behavior changes for each step between 7.0.x and
9.0.2... it is a fairly large leap, and there may be some changes that
will bite your applications.
FYI V9 has the REORGANIZE statement which means you can repair
internal fragmentation on-the-fly; in V7 you need to unload/reload.
V9 also has a book about it <bg>.
Breck
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bca...@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
Breck Carter [TeamSybase]
instead because LOAD TABLE is much faster than ISQL INPUT. Also, don't
use bulk mode with LOAD TABLE.
Breck
On 14 Feb 2006 07:10:09 -0800, Kevin McGrain wrote:
--
PC
<Kevin McGrain> wrote in message news:43f1f2d1.466...@sybase.com...
Justin Willey
a) are you using v9 to do the unload - safer and faster than using 7 to do
it.
b) are you unloading to a different disk array from where the db files are -
can make a huge difference (obviously the same applies for the reload)
c) avoid RAID 5 if you can for the reload, my experience is that it can be
far slower than a single disk, never mind a raid 0, 0+1 etc
Justin Willey
<Kevin McGrain> wrote in message news:43f1f2d1.466...@sybase.com...
Greg Fenton
> c) avoid RAID 5 if you can for the reload, my experience is that it can be
> far slower than a single disk, never mind a raid 0, 0+1 etc
RAID 5 is designed in such a way that reads are typically slower and
(small) writes typically faster than a single disk.
Here's a pretty good intro to RAID levels:
http://www.adaptec.com/worldwide/product/markeditorial.html?prodkey=quick_explanation_of_raid
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/
Breck Carter [TeamSybase]
is random and in small chunks. Database servers are an example."
I don't think "small random" applies to a reload.
FWIW all other docs I've ever read on the subject say "RAID 5
performance sucks for database servers".
It probably doesn't matter, though; like VHS over Beta, everyone uses
RAID 5 over RAID 1/0 :)... I have certainly never had the opportunity
to compare performance head-to-head (pun intended :)
Breck
On 15 Feb 2006 08:11:10 -0800, Greg Fenton
<greg.fent...@ianywhere.com> wrote:
>Justin Willey wrote:
>> c) avoid RAID 5 if you can for the reload, my experience is that it can be
>> far slower than a single disk, never mind a raid 0, 0+1 etc
>
>RAID 5 is designed in such a way that reads are typically slower and
>(small) writes typically faster than a single disk.
>
>Here's a pretty good intro to RAID levels:
>
>http://www.adaptec.com/worldwide/product/markeditorial.html?prodkey=quick_explanation_of_raid
>
>greg.fenton
--
thankstoall
findings. I will try several different options and
hopefully come up with a benchmark for every combination.
Thanks !!!!!