TCP i/o timeout and Unable to open tcp connections SQL

Hello,
Currently working in a go app that reads lots of messages per second and processing those message into SQL Server via database/sql package. As long as I understand *sql.DB is an connection pool that it takes care of all its connections, I have played around with SetMaxOpenConns, SetMaxIdleConns and SetConnMaxLifetime with no luck so far. My problem is I’m seeing lots of errors related to tcp connections ‘read tcp 10.0.223.183:50788->10.0.222.8:1433: i/o timeout’. Have spent too long already and still don’t have an answer for this. I have one only *sql.DB global instance which is created only once and being accessed simultaneously by plenty goroutines. Can someone please help me find a solution for this? thought *sql.DB takes care of concurrency but there must be something I’m not doing right! Have used mutex and context but still no luck, please find part of the code below:

Thanks in advance!

//globa sql instance
var dgsData *sql.DB
var DoLineChangePrep *sql.Stmt
.
.
.
func InitPreparedStatements() error {
if DoLineChangePrep, err = dgsData.Prepare(ExecMoveLine); err != nil {
return err
}
}

func DgsDoLineChange(…){
err := DoLineChangePrep.QueryRow(…)
.
.
.
}

1 Like

Have you checked if your database can handle the load or does accept that many connections you throw at it at the same time?

The DB might itself has an upper connection limit, it may have a connection pool, it may have a worker pool, there may be looks preventing concurrent queries to be run at the same time, there may be…

1 Like

Hello @NobbZ, thanks for taking the time to reply. Not aware of how many connectios does SQL Server allow, but making a little research on internet it says it supports up to 32767 concurrent connections. My app logs hundreds not thousands of connections, so not sure the SQL Server is the problem.

1 Like

You could throw a dummy script in some other language that fires up as many connections as your Go program and see if it brakes too. In the very least you’ll be assured that it’s not a DB issue.

1 Like

Well, maybe it supports that many, but how many are actually configured?

Also can all of them served at the same time? Or only 10, and the remaining have to wait until a worker has time?

1 Like

thanks for the reply, sorry for the delay, was out… for sure, will create a small script to try that and get back to you shortly, thanks

1 Like

Which sql database are you using? You might forget to import the driver?

https://github.com/golang/go/wiki/SQLDrivers#sql-database-drivers

1 Like

Thanks for your reply, SQL Server, I am using “github.com/denisenkom/go-mssqldb” already

1 Like

I don’t think the issue is about connection limit, in this case you would get too many connections error or something like that. You are probably getting this error because of a timeout query as error says. I would check first sql server connection timeout configuration and connection string(DSN) that you are using in the program.

If you couldn’t find still the problem, I suggest you to check SQL server logs.

Edit: I found this: https://github.com/denisenkom/go-mssqldb/issues/331
You can test it with adding below to DSN

;Connection Timeout=0

1 Like

thank you all for your help, indeed, this was related to the db server hardware limitation, plus I limited the maxopenconnections to 20 to be reused and maxidleconnections as well. Thanks again for your help.I appreciate it!!

1 Like

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.