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
}
}
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…
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.
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.
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.
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!!