|
|
Posted by boa on 08/20/06 17:59
* Dan Guzman wrote, On 20.08.2006 18:01:
>> Some tables/indexes are very busy, being updated every nth minute. To stop
>> that traffic from blocking reads/writes to other tables, I have decided to
>> place the busiest/largest tables on separate drives. Better to have a
>> little bit lower, but more predictable and consistent performance than to
>> place all tables/indexes on a massive raid-10 array, IMO.
>
> My philosophy is quite the opposite for a general purpose database server.
> I usually segregate only transaction logs and spread all data files evenly
> over available disk storage so that I/O is evenly distributed over the I/O
> subsystem. Every environment is different but I think it's often a mistake
> to deliberately create a disk hotspot. Keep in mind that efficient caching
> is paramount to performance. Slowing down writes to your busy
> tables/indexes will result in more memory pressure and negatively affect
> other applications.
No disagreement here, generally speaking. Our current situation is that
we, among other things, have very resource(disk) consuming import jobs
running 24/7. These jobs access the db approx. every third minute and
then do "other things" for the next 2 minutes. The exact load impact is
not even determined by us, but by external entities which we have no
influence over. At the same time we have other external customers
accessing the database both reading and writing.
The way the import jobs are written, they perform about 500.000 inserts
per 20 minutes, or 1.5" inserts per hour. Each insert is a separate
autocomitted transaction, IIRC. (Now recall that the db currently is
located on a raid-5 array(6 drives) and the transaction log is on the
same array and then add a couple of hundred other statements per second
and even some minor paging activities at times, plus 12 cpu-consuming
and memory loving .net clients AND a web server running on the same
machine AND hourly log backups to the same disk array, then imagine the
great performance we've had... )
Our situation right now is that the import job blocks all other traffic,
rendering the system close to unusable and the customers unhappy. The
idea is to move the import job related tables and indexes to separate
drives to reduce disk seek time during that 1 crucial minute(or 33% of
the time). This will hopefully improve overall throughput more than
having everything on one array.
There are other solutions available to us, one of them are to redesign
the database and rewrite the client applications. We're planning to do
that too, but that'll take weeks/months.
>
> If performance is important to you, it might be worthwhile to run load
> tests under the different disk configurations to see if isolating busy
> tables/indexes is justified.
I hope to find the time to do just that, but with a Disk Write Queue
Length averaging *35* over long periods of time, we need to put the new
disks in production asap. I do plan to run two databases in parallel for
some time and do some tuning and error checking, and then do a final
migration at some point.
The new drives are hopefully best of breed, 15K RPM SAS drives. I expect
that just moving from 10K scsi raid-5 to 15K SAS raid-10 will be a big
boost, moving the transaction log to a separate drive even bigger. If we
in addition to that can remove traffic equaling 1.5" transactions/hour
from our main drives, we've bought ourselves enough time to rewrite the
client apps.
Boa
Navigation:
[Reply to this message]
|