Multiple connections to the same database - help performance?

Hi!

Our database has been faulting constantly, so we created another connection pointing to the same database, thinking that it might take some load off the database.

Does that actually work or it doesn’t make a difference since the connections are to the same database?

Thanks so much!
Jane

That depends on why the database connection was faulting. Did the new connection work?

Yes the new connection works! We’ve also increased the partition creation frequency from weekly to every 2 days.

I cannot tell from your reply if the issue has been resolved…

So really it would, as @ethomason asked, depend on why the connection is faulting.
You’ve changed the partitioning so I’m going to guess this is for default historical data?
We’ll need much more info to help you out.
Server Specs? And current load?
Are you running your db instance on the same box as ignition?
What db are you running?
How many db connections do you have in total?
Are the dbs you have at max connections? Default 8
What is the current Throughput on the connections you have?

Let us know and we’ll try to help. :slight_smile:

Yes, it’s for historical data.
The DB server is Windows Server 2016 with 12 GB RAM and 6 cores but the DB is only using 4 of the cores.
We are using MS SQL and the DB server is separate from the Ignition server.
We have 5 DB connections in total with 2 pointing to the same History database.
We increased the max connections of the main History database to 25 so that it wouldn’t fault all the time. The 2nd one is still set at default.
Current loading on the main one is around 15/25 and it can go up to 20. The number of Queries / Sec is around 140.

Thanks!

Hmmm…

Well MSSQL is a heavy hitter but it would appear you have things handled from a hardware perspective.
140 queries/sec is basically almost no traffic at all for those spec.

You could change the max connections to -1 that would allow it to take as many connections as it wants.
After, as a test, I’d disable the secondary connection and see what happens.
I’ve got a couple of db connections that stay steady 2 to 4 connections but can burst as high as 30 under load (reports) and had to set the connection limit to -1.

As a side note I’ve got several projects (more than 30 active) on the same gateway and I made the decision a couple years ago that each project would get its own db on the same mySQL engine. This allows me to break up connections by project. Please keep in mind that I don’t know anything about your organization when i say this but, since Ignition allows me to pull from any db location across 30+ projects, is there a way that you might be able to compartmentalize your tags so that you could spread the load across multiple logical connections. Just a thought.

Cheers!

We actually tried the -1 and dialed it back down as we monitored the connection. Is there any side effects of leaving it at -1?

By spreading the load, do you mean creating multiple databases or multiple connections to the same database?

Thanks!

I’ve not found any issues with leaving the busy connections at -1.

Creating separate databases. Like I said just a thought.

For environments where the database load can vary greatly, but certain tasks should always take precedence, it’s handy to make a high priority connection name with a -1 pool limit. Set the other connection to a fixed max, throttling it if usage spikes.

1 Like

Thank you all very much for your help and suggestions!