Poor database performance

Why is the database performance for Orion so poor? According to our server engineers, we would need to spread our database writes across 17 hard disks to keep up. Why so many writes? If you are only polling statistics every 5 minutes or so...couldn't you do bulk writes instead of a write for every SNMP query? We have the Fluke Netflow Tracker tool and are taking 22,000 flows per second with no performance issues. Please say this is something you guys are improving in the future.

Find more posts tagged with

Accepted answers

All comments

MarieB

Hi Slinky103--

A couple of questions: what version of Orion are you running? Also, do you have NPM on the same machine as your SQL server and/or any VMs you are running? This can severely affect performance.

bshopp

Also, what additional modules do you currently own?

slinky103

We have SLX and a secondary polling engine. We are running version 9.5 SP4 with hotfix 2, which I just installed today after having the SOAP exception numerous times (thanks for the hotfix btw). NCM is integrated with NPM and we have SLA manager although it is bare-bones right now with only ~12 call paths active.

We are running SQL Enterprise 2005 on a dedicated 64-bit OS with a quad core XEON Dell server with 16GB of memory. The database server houses our NCM, NPM, and EOC databases. EOC has not been rolled out yet as we will be moving half of our devices to another SLX/secondary polling engine in another data center. So the EOC utilization is extremely minimal.

We have a very high-performance SAN which we are going to move the DB to. I have been told by some of your techs that it is not recommended to have the DB on a SAN due to the high amount of writes, but have seen other posts on the forum from people that have seen an improvement. So this move is going to serve as a dual purpose for us. First we will see if there will be an improvement or not. Secondly our data partition is configured as RAID5 right now. If the performance does not improve, we will add two more HD and reformat the partition to RAID1 and move the database back locally. I know simply changing the RAID will improve the performance. I just find it hard to believe that 500 polls per second between two polling engines is bringing our DB server to its' knees...

FormerMember

I would like to see further comment from SW on this post.... i also agree that there could be more work done on the write mechanisms of the product. Orion is one of many tools we use, and it is the only one that has database performance issues.

I have a SAN with 14 disks in a Raid 0+1. It is absolutely hammering the CPU on the database server. I am upgrading the CPU on the database server as i know it will help but I would like to see something where the product was clever about how it cached information, and write bulk transactions to the DB to better utilise the SQL engine...

what are your thoughts SW?

I am currently at an SLX, 4 additional pollers, APM installed to every poller, additional web server, IP SLA, NCM, NTA all installed across these servers. the database server is not a happy chappie, but it is holding together.

I have recently been going through every interface and removing bandwidth utilisation from all interfaces that are not required, and making sure that servers only have the required volumes being monitored just to reduce the impact.

any thoughts would be much appreciated

cheers

dan

Donald_Francis

From what I understand SW does not cache anything. Another words as soon as a poller processes something or gets a newflow (which is pretty constantly) it sends the data. So it is basically sending a million tiny writes to the DB wasting a lot of overhead instead of say caching all the writes for a small amount of time and doing a large write operation. This is technically ok it is just very ineffcient. I have he same issues you guys have although I have a harder time with my read queue than my write queue.

jlintner

We have a similar situation, but seemed to have most of it resolved...at least for now! If we still get SQL server timeouts, then we'll have to go to SAN storage for the databases, and possibly split them all out to seperate LUNS!

Our polling server: Dell 2950, 2 CPU, 8GB memory, running Win2k3R2 64 bit, C: drive is a pair of 73GB drives in a RAID 1 array. We have just over 4000 network elements that we are polling.

Our DB server: Dell 2950, 2 CPU, 16GB memory, running Win2k3R2 64 bit with MSSQL 2005, also 64bit. We had the Solarwinds DB and log files on one local RAID 1 array, and the OS/system databases on the C: drive, also a RAID 1 set of disks.

Disk contention on the C: drive was unbearable although the CPU on the DB server was under 8%. Perfmon showed disk issues on C:, so we added two additional drives as another RAID 1 group, moved the Solarwinds DB files to the F: drive, DB log files to the E: drive, and did the same for the TEMPDB. That, by far made a big improvement - before those changes we were getting frequent SQL timeouts in the polling server Solarwinds event logs (this was done yesterday) and I just checked the logs and not a single SQL timeout! Our next step is to go to SAN space and burn up some disks to get the spindles we need but so far we are ok.

It would be nice if the writes to the DB could be lumped together, making things more efficient, and stretch the useful life of the hardware!

Donald_Francis

I was just repeating what I had been told in the past but I will consider myself informed.

Congo

Slinky,

You are not alone. We also have Fluke's Netfow Tracker, doing 22.84k/s, peak 37.87k/s flows. This server does just fine, disk read/write queues are both below 2. Our Orion database server has exact same hardware, and yet the write disk queue is out of control.

jlintner

For what it's worth, we've been trying to dig through performance issues with our Orion system, and it's been pointing to the database and performance. Recently we've made some changes to the disk layout, and are working on getting a better performing disk solution (EMC SAN=$$$). I've had a DBA looking at the database and noticed a lot of processes on the SQL server waiting, and they were all related to Netflow. Per some recommendations on another thread, we turned off all routers sending netflow data to the polling engine - we had about 20 routers, mostly T1 circuits sending netflow to the polling engine. I thought it was strange that the DBA saw so much activity in a "wait" state on the SQL server (excuse my lack of proper SQL terms) related to netflow, we tried stopping the Netflow service on the plling engine. Voila! disk que time went down to a normal rate and the website was responsive again. To verify this, we started it back up and the problem came back - almost immediately. So for now we have the Netflow service turned off on our polling engine, but Solarwinds developers need to look at what's going on with Neflow and database activity.

Let me know if anyone else sees the same results.

bshopp

Here are a couple things you might want to check out to improve NTA performance

See this post here regarding some optimization that will affect the SQL server and how much data we need to store.

Also, are you using CBQoS actively in NTA? If not, you can disable that as well, see here for more info on that.

Congo

We actually had to go with another product for our Netflow needs, because we were worried about database performance. We are doing 18,000 flows per second collecting from 4 interfaces, at a sampling rate of 500...

I dont want to add that stress to the DB.

Does NTA support sampled netflow? Perhaps this could help with DB performance?

jlintner

Also, are you using CBQoS actively in NTA? If not, you can disable that as well, see here for more info on that.

I tried this fix, since we are not using CBQoS at the time. Now the Netflow service doesn't start - error in the config file. We are using NTA 3.5.

One of the errors from the system log on the server:

Syntax error in manifest or policy file "C:\Program Files (x86)\Solarwinds\Orion\NetFlowTrafficAnalysis\NetFlowService.exe.Config" on line 110.

I copied the config change directly from the post.

<EDIT>You need a "/" at the end of that new line added to the config file. I added that and the service started. I'll monitor DB performance and report back the results.

chris.lapoint

Sorry about that. We'll get this corrected in the KB article.

PLEASE NOTE: The most impactful setting you can implement for improving DB performance is Top Talker optimization (previously Smart Traffic Optimization). See this article for how to enable.