MYSQL Connection Maxing out
Got a busy site we've recently migrated to Server 2008..
I see connections in mySQL were peaking, mainly in the SLEEP state - so I began tweaking with the settings. I have reviewed over all other DB calls, all seem the same - open db, execute, close - yet I havent seen any other of the INSERTS/QUERIES connections stay like I do with this.
mySQL settings:
Interactive timeout - 30,
Wait timeout - 15 (I have been playing around with this)
Max connections - 100
Is there a reason the max connections is 100? Would it do any harm to increase this (assuming enough system resources)
How low can you go with Wait Timeout?
After the tweaking (as you can probably tell I am no DB expert!) I have tried using adAsyncExecute - it fends OK for a few hours, but as the system gets busier (peak is in the evening) the connections get too much for it, and maxes the connections.
This is my function:
set DBShopHELO = Server.CreateObject("ADODB.Connection")
DBShopHELO.open ShopHELOConnString
DBShopHELO.execute "INSERT INTO HELO (Shop,DStamp,TStamp,OutOfStock) VALUES (" & L & "," & c_date & "," & c_time & ",'" & OOS & "') ON DUPLICATE KEY UPDATE DStamp=" & c_date & ",TStamp=" & c_time & ",OutOfStock='" & OOS & "';",,16
DBShopHELO.close
set DBShopHELO=nothing
Connection String:
DRIVER={MySQL ODBC 5.1 Driver};server=SERVER;user=USER;password=PASS;database=DB;
I occasionally see "Operation cannot be performed while executing asynchronously", but yet entries were going in. I have been playing around with .kill / .dispose / .cancel etc - none of which it likes!
Has anyone had any similar experiences with a busy active DB?
Does ADO not close the connection properly? does it leave it hanging, if so is there a way to terminate it?
Any thoughts on this would be great!