MySQL too many connections

[Chris]

I am using the following function in a windows service. It is fired off
by a file system watcher component. It works fine, except for I'll copy
in 20 files it has to process at one time. I'll load up MySqlBrowser to
check the data, and it says I'm out of connections. (the database is
remote) The connections don't clear until I stop the service. Any idea
what I'm missing?

Thanks
Chris

Dim myConnection As MySqlConnection
Dim myCommand As MySqlCommand
Try
Dim myConnectionString As String
myConnectionString = ...

myConnection = New MySqlConnection(myConnectionString)
Dim myInsertQuery As String = GenerateQuery()
myConnection.Open()
myCommand = New MySqlCommand(myInsertQuery)
myCommand.Connection = myConnection
myCommand.ExecuteNonQuery()
For Each S As String In Errors
myCommand.CommandText = "Insert into ImportErrors ....
myCommand.ExecuteNonQuery()
Next
Catch ex As Exception
WriteLog(ex.Message)
Finally
If Not myCommand Is Nothing Then
myCommand.Dispose()
End If
If Not myConnection Is Nothing Then
myConnection.Close()
myConnection.Dispose()
End If
End Try

 

[SStory]

Well,

Are you firing this a bunch? Are you getting a brand new connection each
time?

Do you need a queue? (IN ours we have a queue, so that we don't miss
anything and of course it is multithreaded.)

Thanks,

Shane

 

[Chris]

Well,

Are you firing this a bunch? Are you getting a brand new connection each
time?

Do you need a queue? (IN ours we have a queue, so that we don't miss
anything and of course it is multithreaded.)

Thanks,

Shane

I thought about using a Queue. I have it so that each time a new file
is created it files a new thread which does the function that you saw.
So yes if a bunch of files get thrown up at the same time, it would make
many connections. Would you suggest that I have a worker thread that
just polls the queue once a minute?

Chris

 

[Cor Ligthert]

Chris,

I did not completly check it, however probably is it that you do the
disposing of your connection not in the right place.

A normal way for using the connection is

Try
create new connection
Catch
'handle errors
Finally
dispose connection
End Try

This will be in the VB2005 version inside one using block as it is with C#

I hope this helps,

Cor

 

[Shane Story]

We have a queue.

Are you using the .Synchronized method on the queue? This returns a thread
safe wrapper to the queue.

We enqueue each new file as it comes in. We have one worker thread per
filesystemwatcher (FSW) that does go through the queue handling each file in
the order that it was queued. This is better than a thread per file of
course which would be terrible. (I think you already know that)

Another advantage to enqueuing the file immediately, is that many times a
FSW will fire an event before the file is completely written and this causes
file access problems. Queueing it first seems to take care of this for us.
We have stress tested our app, bombarding it with 1000's of files coming in
one after another with no delay, and with delay, and also in different
directories and multiple FSW's and this seems to work.

HTH,

Shane