2

I've a database running on a windows server I'm monitoring with Hyperic. I see the writes per minute ramping up during the day as we approach our peak load. The peaks are increasing gradually over time as we add more users to the system. With other resources (ie CPU, Memory or Disk Space) it's pretty easy to see what the maximum would be and make sure we get additional resources ready before we run out. Is it possible to get even a rough prediction of what the maximum writes per minute is likely to be? Alternatively, do I just need to puh the number of disk writes up and see when things start failing, I assume my disk queue will be the best indicator that I'm exceeding the capabilities of the drive/array.

==EDIT==

By way of some additional details, I'm running SQL Server 2005 and the reason I'm so interested in the writes is that the writes on the disk currently outstrip the reads by about 30x. I'm assuming that writes then are likely to be a bottleneck before any other disk activity. The application it's supporting is mainly OLTP although we do report from the data in there as well.

Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47
Robin
  • 807
  • 3
  • 11
  • 19

1 Answers1

5

Short Answer: Predict - no, benchmark - Yes.

Long Answer: I don't think this will help what your trying to achieve, which I believe is do some capacity planning around the disks your db server is running.

What kind of RMDBS is the server running? What kind of application is the database running? OLTP, decision support, DW/Reporting? different applications and groups of applications will have different disk load profiles, predicting/measuring just write performance is most likely totally meaningless.

Being a SQL Server guy, disk subsystems are important to me. For benchmarking I use SQLIO to test read/write performance when comparing disk subsystems/disk configs. SQLIO is a stand alone executable that you can use to test read/writes in different ways, the SQLIO GUI also helps. You can use this tool to determine your 'maximum writes'.

Generally speaking you shouldn't rely on a single counter as an indicator of an i/o bottleneck. Here are indicators i look for that indicate a server needs more spindles:

• PhysicalDisk Object: Avg. Disk Queue Length / number of disks: A value that is consistently above 2 when the server is under load indicates a bottleneck

• Physical Disk: %Disk Time Anything above 60% is a serious bottleneck

• Avg. Disk Sec/Read and Avg. Disk Sec/Write:

  • 10ms good between
  • 10 & 20ms - okay
  • between 20 & 50ms - bad
  • 50ms -awful
Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47
  • bonnie++ is an excellent benchmarking tool, many linux live cds come with it. It tries to simulate a database load on your system, taxing memory, cpu, and disk at the same time. – Joseph Kern Aug 27 '09 at 13:19
  • 1
    @Nick Disk Idle Time is another counter worth watching. It measures when the disk is physically writing, as RAID caches can give false measurements to other counters. – Joseph Kern Aug 27 '09 at 13:20
  • thanks for the answer, I've added some of the additional detail you asked for. I'll look into the counters you mention as well. Cheers. – Robin Aug 27 '09 at 13:36