|
|
Posted by boa on 08/21/06 14:59
* Dan Guzman wrote, On 21.08.2006 14:03:
>> - The slowest thing one can do with a disk is to move the heads(seek
>> time), so avoiding unnecessary seek time will improve overall throughput.
>
> This is true and exactly why it is so important to isolate logs on separate
> disks. Logs are mostly sequential writes so you want to avoid other random
> I/O from interfering and you want to avoid RAID-5 overhead. Importantly,
> SQL Server waits for the physical write to the log to complete during COMMIT
> so log writes are on the critical path. Write caching on the log can help
> greatly but the controller must guarantee eventual write to disk (e.g.
> battery backup).
We do plan to commit to the raid controller cache and trust the battery
backup.
[snip]
>> - The import job writes lots of data to data and index pages on extents
>> located on disk.
>
> If data are imported in sequence, isolating that index can improve disk
> efficiency. However, the other indexes will likely be mantained randomly so
> it's probably best to place those on the same array(s) as other general
> purpose I/O.
Good thinking, I forgot about that part.
>
>> - All we do during import is inserting new rows in tables and the tables
>> reside in their own files and the files reside on their own disks,
>> (separate disks for table data and index data), so hopefully disk head
>> movement will be minimal.
>>
>> - The slowest thing one can do with a disk is to move the heads(seek
>> time), so avoiding unnecessary seek time will improve overall throughput.
>>
>
> Random I/O will likely result in some head movement so the heads are going
> to move regardless of whether or not there are other objects on the drive.
You lost me here, why would the heads move? I assume that noone reads
from the tables during inserts.
[snip]
>> What I aim for is less head movement and more parallell operations. This
>> way the overall throughput will be better than if we only used a big
>> RAID-10 array.
>
> I hope you can find the time to do performance testing with the discussed
> configurations. That's really the only way to tell for sure. I understand
> that you are pressed to come up with a solution sooner than later so maybe
> you can move logs as an interim step to buy some time do run the tests.
I'll probably not find the time. I could of course move just the tx log
and then test for a day or two, but I really hate using production
servers as test platforms. 'Childhood' experiences...
>
> With all the discussion in this thread focusing on disk hardware, don't
> forget the app side. If queries and indexes are poorly designed, you might
> be able to get a dramatic improvement with a few strategic indexes or query
> changes.
>
The app side is not forgotten and there's a lot to gain by changing
things. The problem is that it will take time, more time than we have.
Just a small thing like not commiting every insert will mean a lot as it
will reduce log traffic *a lot* and hopefully also other disk writes(if
more than one row of data is added to the page before it is flushed to disk)
Thanks
Boa
Navigation:
[Reply to this message]
|