re: "not writing as fast to the smaller database devices"
OK, that could be part of the problem ... but it was 'slow' in what way?
were the smaller database devices running at 100% busy? were the
larger database devices running at something less than 100% busy? were
any disks (dump devices, database devices) running at 100% busy?
--------------------
Did you notice any change in disk statistics at the point where the
database finished loading data and began zero'ing out the 'empty' space
in the database?
How much of the 42 minutes is taken loading data vs. zero'ing out the
'empty' space in the database?
--------------------
As for your question re: number/size of devices ...
I seem to recall that at some point ASE was able to take advantage of
more devices within a database, ie, parallel operations (eg, create
database, load database, etc) against a lot of small(er) devices was
faster than parallel operations against fewer large(r) devices ... but
that's just a lingering cobweb near the back of the noggin' ...
--------------------
re: 11.9.2 loads running faster than 12.5.3 ...
I'm wondering if the older versions of ASE did *not* zero out the
'empty' portion of the database ... or perhaps they did zero out the
database but newer releases of ASE are performing a more detailed 'zero
out' operation. Combined with (relatively) few devices for the smaller
database that may explain some slow down ... while the larger database,
with the larger number of devices, is benefiting from more
parallelization during the load/zero-out process ... ??? Would really
need to get someone with a little more intimate knowledge of 'load
database' internals (both 11.9.2 and current).
Post by Bill Auslanderiostat didn't provide any useful information other
than it confirmed that it is not writing as fast to
the smaller database devices. The slow load
of the smaller database starting happening immediately
after upgrading from 11.9.2 to 12.5.3. Prior to the
upgrade, the dumps and loads were faster with the smaller
database (as expected). After the upgrade, we did not
change any devices, and now the dumps are fine, but the
load of the smaller database consistently takes 3 times as
long as it used to. But, the load of the larger database
takes about the same time as it used to, but is now much
faster than the smaller database.
In 12.5.3, does it matter that the smaller database writes
to fewer, but larger devices, and the larger database writes
to many more devices each of smaller size?
Post by Bill AuslanderReversing the order, didn't change anything (as expected).
We create the dump files on the same OS disk partition.
We use the same number of stripes (12), but the sizes are
not the same. The size of each stripe in the 40Gb database
is about 1.7 GB, and the size of each stripe of the 70 GB
database, is about 2.9 GB. This is expected, too.
I'm analyzing the iostat info now, to see if there's anything
interesting there.
Thanks, Bill
Post by Stefan KarlssonAre you using the same stripe size for the underlying Sybase devices ? IIRC,
backupserver does 54kb IO and if a device is created with a 16kb or 32kb
stripe size there's a penalty in that the disks must be sync'd to perform a
highlevel single read.
/Stefan
Post by Bill AuslanderI'm going to try reversing the order and also looking at the
disk i/o while it is running. I believe the 'iostat' command
on the Tru64 machine will give me the same info as 'sar'.
Thanks -Bill
Post by Mark A. ParsonsI don't have any experience on a Tru64 machine but I'm assuming you have
access to the 'sar' command.
You can use 'sar' to capture disk statistics during the loads. What you
would want to look for is any disks that are at 100% utilization (ie,
disk
Post by Bill AuslanderPost by Mark A. Parsonsbottleneck) ... not only on the database devices but also the dump
devices.
You'll also want to keep track of the point in time where the database
switches from loading data to zero'ing out the database ... see what, if
any, changes show up in the sar report.
I'm wondering if the first database load is running into more disk
bottlenecks which could explain some slowness?
-----------------------
Stupid question # ... uhhh ... I lost track ...
Do you see the same load times if you load the 2 databases in reverse
order, ie, load the larger database first, followed by the smaller database?
-----------------------
As for the Solaris loads running faster ... could be an issue of disks
with faster RPM's (Tru64's 3600 rpm disks are pretty slow compared to
today's standards) ... as well as the layout of the databases on the disks.
Post by Bill Auslander1. The loads are coming from different devices, but the same type (SCSI
HSZ50-AX disks, 3600 rpm).
2. The two databases are being loaded one after the other on the same
dataserver. The first one is loaded and takes 42 minutes
consistently,
and the second one that is almost twice the size, takes about 16
minutes.
Post by Bill AuslanderPost by Mark A. ParsonsPost by Bill Auslander4. The device layouts are different, but the underlying disks are the
same. I don't know how to determine bottlenecks on a device? How do I
do
Post by Bill AuslanderPost by Mark A. ParsonsPost by Bill Auslanderthat? At the time of loading, nothing else is going on, on the server
machine.
5. Both using 12 stripes and same compression level. Same script used
to
Post by Bill AuslanderPost by Mark A. ParsonsPost by Bill Auslanderdump/load both databases.
6. 4,868 KB unused space on the 40GB database, and 26,242 GB unused
space
Post by Bill AuslanderPost by Mark A. ParsonsPost by Bill Auslanderon the 70 GB database.
Interestingly - on a Sun machine, with all else remaining the same, the
40GB database loads faster than the 70 GB database (as expected).
Thanks!
Bill
Post by Mark A. Parsons1 - are the loads coming from the same device and/or type? is one load
coming from tape and the other from disk? any noticeable bottlenecks
during reads from the dump devices?
2 - are the 2 databases (being loaded) on the same dataserver?
3 - if the 2 databases are on different dataservers ... what
differences
of
disk
Post by Bill AuslanderPost by Mark A. ParsonsPost by Bill AuslanderPost by Mark A. Parsonsspeeds? any noticeable bottlenecks on these devices?
5 - are both loads using the same number of stripes and Sybase
compression levels?
6 - how much unused space in each database?
7 - are you loading the smaller database during a full moon? [Just
kidding ... ]
Post by Bill AuslanderA database that is about 40 GB takes about 42 minutes to load (9
minutes
Post by Bill AuslanderPost by Mark A. ParsonsPost by Bill AuslanderPost by Mark A. ParsonsPost by Bill Auslanderto dump),
and another database that is 70 GB on the same server takes about 16
minutes to load (15 minutes to dump). We de-fragmented both databases
before dumping them, so I'm at a loss why the small one takes so much
longer to load. Any suggestions would be greatly appreciated.
Thanks,
Bill