Server performance raid / disk help

[ADMaster]

Hello all,

I’m not sure if this should be in the server or hardware section, feel free to move it.

 

I’ve been having trouble with one of my servers. The main application giving unusable symptoms is PDQ inventory. This server is also sccm / sql server. I recently installed scom to have a play with it too.

I go into resource monitor and the disk queue length jumps quite a bit. I’d guess it averages 10 but frequently spikes to 30 – 50 and occasionally 100.

Going into performance monitor and running the system performance report, all returns normal. The latest report was 215/sec (135 read 80 write)

The resource monitor also shows anywhere from 1 to 10 mbps of disk i/o

If I recall a sata connection is 3 or 6 Gbps so this should not be an issue.

These are 3 15K sas drives in a raid 5 configuration.

I know for this config the ideal queue length is no more then 6, but most of the system seems to be working fine. I just imaged a PC this morning without issue.

Last week in addition to the disk issues, I had high cpu with wsus, I thought that was the problem for a time, but that’s cleared up and I still have issues.

I’ve monitored the disks even when PDQ is working well and it’s about the same, so I’m at a loss on what to try next.

 

The event logs show the scom database is out of room, it didn’t have auto grow enabled. I enabled it and it has not grown yet.

I rebooted the server this morning for good measure, it took about an hour to reboot.

I ran defragler and saw the pdq database was in 9k fragments. I defragmented just that file.

Still no joy.

I’m considering removing scom, but I tried just stopping the services and things didn’t improve.

I’ve also considered throwing more disks at the problem, but can I add to / expand the raid? The chassis has room for 3 more disks.

This is an HP Proliant Gen9

 

Storage is not my strong suit, any advice welcome.

Thank you

[psydii]

More disks will certainly help, but your RAID will need to be rebuilt (a background task) and this can take an age depending on the size of your disks.

 

Before that though, I'd look to disable the System Centre and PDQ and SQL Server services, and time the reboot. It should be pretty snappy without them. If that boot with the services disabled remains very slow then you have some other underlying issue (perhaps an impending disk failure, or other disk intensive software). If it boots fine, re-enable the services and try to identify which processes are generating the most IO (Performance monitor via Task Manager is great at providing file and process I?O summaries)

 

I expect that you may have ramped up the data that needs to be process in order to evaluate you collection membership. And these evaluations have overwhelmed your server's disk i/O capacity. Try this guide https://www.andersrodland.com/sccm-performance-tuning-part-1/ for a step by step investigative process.

 

Basically

• eliminate the possibility of a hardware fault,
  
  
• eliminate a rogue process
  
  
• eliminate inefficient SCCM configuration
  
  
• buy more disks.
  
  

[ADMaster]

Thanks I've had a read through that and I'm sure I have collections with incremental updates.

I will definitely defrag the indexes, i have a script around here for that.

I forgot to say that hour reboot included updates installing.

To your suggestions

 

eliminate the possibility of a hardware fault,

The lights are all green, its not reporting any smart errors.

eliminate a rogue process

the top disk i/o processes are sql and pdq

eliminate inefficient SCCM configuration

working on this thanks for the guide.

buy more disks.

I'm not a fan of rebuilding the array, but will if I have to. Does this mean data loss / backup and restore, or just the raid controller doing its thing to include the other disks?

 

Thanks,

 

EDIT, shortly after my first post pdq started working again, but its been a daily battle for the last few weeks. I'm sure it will crash out again before the day is done.

Edited April 20, 2018 by ADMaster

[HPlum78]

Is this a dedicated SQL server? When you say installed SCOM do you mean it's the DB or the apps as well?

 

Is the 3 disks you are talking about just for the DB's or is that also holding the OS and the applications?

 

From the info supplied my first thoughts are if these disks are holding OS/ Apps/ and the DB instance then this probably needs thinking about, although that's not as simple now. While on DB instances the schemes for SCCM and SCOM applications

are large and differ largely (I don't know about the PDQ software) but the former 2 should be installed in separate SQL instances. If you are installing the applications on the server as well this also needs some thought and is not ideal but if needs must and you don't have application servers availible then try to carve the apps up on to a separate set of disks or on the OS disks not on the same disks as the DB's.

 

Ultimately more spindles = better performance but in the best practice for SQL deployments this is not always that straight forward.

 

If I had absolutely no options I would have two disks mirrored for the OS these only need to be 10k and then fill the rest of the server up with disks and use them for the DBs and if absolutely necessary the applications as well. Then use sepearte instances for those DBs with large schemes.

[AlanD]

 

Ultimately more spindles = better performance.

 

Maybe "often more spindles = better performance".....but the best latency is with a single drive - as on average the latency is half the rotation time. If you had 10 disks the latency would be nearly twice that. Disk latency is critical for servers - which is why 15K drives make a difference....and SSDs would make even more difference. But yes - for large data applications (not necessarily SQL) you can get a bigger data block in one read with a large RAID. ...not that I'm recommending in single drive....but a mirror often gives best latency.

 

A large chunk of RAM can help with SQL - and usually SQL will use as much as it can lay its hands on.....so I'd be looking at paging use. RAM is (relatively) cheap - so at least 16GB....and perhaps even 64GB or more might be something to aim for.

[HPlum78]

Sorry I thought I was clear, I am not suggesting mixing the 15k with 10k disks I am sugesting that splitting up OS and data in this instance would be the way I would approache this and to be honest I have made assumptions on the configuration so this is best guess. I am also trying to appreciate that unlike the setup I have access to (4 clusters with up to 8 nodes in each with TBs of RAM dedicated OS disks in each node with dedicated data and tmp storage) not all of us are in the same boat! If all the OS/ application and DB's are all located on the same spindles RAM will only go some way to elevate the issues being faced and as I stated in my previous post the schemes of the DBs can and should determine if a single instance should be used or multiple instances, this should be part of the DB tuning and the assessment that is carried out before banging DBs on to SQL servers mind. Edited April 21, 2018 by HPlum78

[psydii]

Remember, in the scenario presented, we have a maximum of six drives to play with.

 

Once we have confirmed whether or not SCCM/SQL optimisations on their own can reduce the queue length to something reasonable (2 is considered a maximum under load - I would say 5 is unacceptable).

 

(If the above identified RAM as bottleneck that will also need to be addressed - if the server is swapping or has less than a couple of handfuls of gigs of 'Available'/Cached/ 'Standby by' + "free" memory you are going to be hurting: Deep dive on memory here: https://channel9.msdn.com/shows/defrag-tools/defrag-tools-6-rammap)

 

 

It becomes likely we need as many spindles as possible to support the IO demands.

 

Losing two spindles to OS in this scenario is a terrible waste IMHO. Add three new drives of the same type as the originals, mark one as hot spare and then get the RAID controller to re-stripe the array across the three existing and two new drives. The array re-striping (expanding) will be quite slow and put an additional hit on performance until completed, Dell will be able to advise on how long it might actually take. It may be quicker to blow away the array and to a 'bare metal' restore, which has the additional benefit of going you a practice run of your DR processes.

Edited April 22, 2018 by psydii

[HPlum78]

Sorry I don't know why Dell would help with the HP server in question here? Or have I missed something? I also could not see a HP gen 9 server that had 6 drive bays at first glance, but again I could have missed something here as well.

[psydii]

They probably wouldn't you are right. It would be safe to assume I meant HP would be able to advise.

 

On the number of available drives, the OP said "These are 3 15K sas drives in a raid 5 configuration. " and "The chassis has room for 3 more disks". I made that six slots, but perhaps I missed something - quite possible given I also mis-remembered the HP line to be Dell! If the chases has eight slots then it seems safe to assume two are already RAID1 for the system and boot volumes and my point about not sacrificing two out of six is moot.

[HPlum78]

@psydii I am only messing I knew what you was getting at :-P yeah I had assumed that two disks are being used for the OS/ some other purpose but its unclear so I have just guessed and made assumptions thus far, but we are all doing that as we do not have info on RAM/ Drive/ CPU config and if its an application server as well as an SQL server...... Edited April 23, 2018 by HPlum78

[ADMaster]

Thank you for the advice so far. I will lay out all the specs and applications running.

I know there is no way I’ll get best practice on everything, one article I read said OS/ apps/ DB /Logs all on separate spindles. That seems a waste of resources. This server has all my System center related stuff sql and pdq.

My environment is 600-700 windows devices so consider that, I don’t need TB’s of DB files / RAM.

The current SQL DB files are this.

Solar Winds Orion 25GB

SUSDB 6GB

SCCM 6GB

Operations Manager DW 1.8GB

Operations Manager 1GB

Virtual Manager 800MB

Report Server 73MB

 

I have reindexed most of the DB’s, defraged some key files including the pdq database and disabled incremental updates on several collections.

The pdq database is in the programdata folder on drive C: it uses sqlite. It is 2GB

 

SQL’s min / max memory is set to 8 / 16 GB

This server runs SCCM, SQL, PDQ, SCVMM and most recently SCOM. It also hosts the DB for my solar winds setup.

The OS and data are on separate volumes, but it is the same underlying raid disks.

 

I just added ScOM very recently to learn, it only has a dozen or so agents attached.

 

The physical server specs are this;

HP ProLiant DL360 Gen9 (P89)

32 GB RAM

12-Core 2.4 GHz Intel Xeon CPU E5-2620 v3 @ 2.40GHz

SKU 780018-S01

 

From the front it looks like I only have a total to 6 drive bays, I may have overlooked some if that is not standard. I know it currently has 3 of the 15K sas drives.

 

When I checked counters this morning the average disk queue was 5 and now it is 8.

When I reindexed the solar winds DB, I lost access to PDQ, but once it was done I could reopen the application.

 

I think with the advice from Friday we have made improvements but are not quite there yet. Just typing this out has be good to realize all the data.

If I recall SQL likes to live in RAM as much as possible, so having less RAM the my DB sizes means I’m paging.

I just read an article that suggested disk queue length isn’t the whole story when it comes to SQL. There are several queries I can run to find bottle necks. I’ll read and learn more on that and report back.

 

Thanks,

[ADMaster]

I think operations manager is a contributing factor. I don't know why I as reindexing the DBs as I have a maintenance plan doing that every week. However checking its history, it has been failing since I installs SCOM. I tried to reindex manually and it says I'm out of space to add files or enable auto growth.

I have enabled / disabled auto growth a few times and restarted the sql service.

The gui says it is enabled, but the errors say it is not.

 

Edit: I increased the intial size my 100MB, then I was able to rebuild, heres hoping it continues to behave.

Thanks

Edited April 23, 2018 by ADMaster

[PotNoodleTech]

What raid card is in it? Id like to think that a server that new should have battery backup'd write cache on it but you never know. If you have not got write cache enabled in the RAID controller BIOS then database write performance will be dire.

[ADMaster]

OK I've just taken a second look and I do have room for a total of 8 drives.

Here are my settings, please advise.

 

Smart HBA H240ar (RAID Mode)

Transformation Priority Medium

Rebuild Priority High

Surface Scan Analysis Priority Idle with delay 3 seconds.

Current Parallel Surface Scan Count 1

Physical Drive Write Cache Disabled

 

Stripe Size / Full Stripe Size 256/512 KiB

 

I should have write cache enabled, will do that soon, any recommendation on stripe size?

 

If I go buy 5 more drives can I convert this to Raid 10 live, or will it be backup destroy / rebuild restore?

 

Thanks,

[ADMaster]

While looking at the RAID configuration I noticed these are only 7200 rpm drives, I'm sure that makes a difference to your recommendations.

A failed drive in one of my other HP servers was a 15K and I assumed they were identical.

 

Thanks

[psydii]

Do not enable the write cache on the individual drives without reading the documentation and guidance from HP on the matter. It is not the same as enabling caching on the controller.

 

Stripe size is now probably immutable. 256kb seems a bit big considering most read/writes will be 8kb for sql database pages.

Edited April 23, 2018 by psydii

[ADMaster]

I'll attach screenshots tomorrow, but all those settings I listed appear to be for the controller, not individual drives.

Also I think I answered one of my own questions. I found a section called migrate raid / stripe (I'll get exact wording tomorrow). It looked like I could change the striping and raid level if I had more drives.

 

I also opened a support ticket with PDQ they gave me a new build to try, so I'll test more tomorrow.

[ADMaster]

Here are the screen shots.

I think the problem is solved. I'll keep an eye on it this week to be sure. I'd still like to improve performance a bit though. If I add one more drive and convert to raid 10, that should be better hen what I have now.

 

I believe the solution was a combination of things.

1. A new build of PDQ is working much better and not crashing. I've stressed the system and crashed the sccm console and pdq kept going.

2. the operations manager db not growing and getting re indexed was causing issues.

3. tuning sccm

 

Thanks,

 

 

 

[houlta]

The H240 is really not a true hardware RAID card, I would get the Smart Array P440 with the battery and connection or similar as an upgrade.

Dell are just the same with their low end so called 'RAID' cards often supplied by default, the H700 or better is a minimum must have imho.

[AlanD]

This is beginning to sound like some random scatter gun approach...whack some stuff in - in the random hope that it might fix a problem.

 

I don't use SCCM - but I'm sure I read somewhere that it is a pretty demanding application. Clearly 7K2 midline drives are going to have twice the latency as 15K drives - albeit at a fraction of the cost. Adding drives is not going to improve latency...and my guess - and yes its only a guess - is that the problem is lots of small writes/reads not about moving massive amounts of data...

 

Yes you could experiment with turning write cache on - but I would either want a UPS on the server or a battery on my RAID card if I was making this permanent. As an experiment this will cost you nothing and is simple to try. (I'm guessing it won't make much difference)

[SYNACK]

Depends on your budget and how stock standard you want it. The full fledged melt my face performance option would be to add more ram, replace the hba with a gen 9 p series raid adapter, add the battery backed cache unit and add a pool of three ssds in raid5 using the smartcache feature to keep it all largely transparent to the OS.

 

Or as your dbs are small a raid1 or 5 of ssds with the current controller would also speed io.

 

Adding another drive or two may increase read speed but decrease write speed for little transactions in some cases.

 

I think the first thing is continue as you have been, optimize as much as you can then work from there.

[SHimmer45]

speaking from using a couple of microservers at home and 7.2K HDD's in various configs, enabling & disabling disk cache makes such a difference in terms of pure throughput its comical.

as its only a testing environment i dont have UPS and the like installed.

 

For me 7.2K drives are not any good when you have a requirement for low latency low seek time etc applications (which is what SQL needs for certain workloads esp if its got something doing alot with it)

SSD sounds like the way forward for the DB's which are being hammered

Edited April 25, 2018 by SHimmer45