473,320 Members | 2,162 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

MySQL too many connections

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
Nov 21 '05 #1
4 3308
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" <no@spam.com> wrote in message
news:e5**************@TK2MSFTNGP15.phx.gbl...
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

Nov 21 '05 #2
SStory wrote:
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" <no@spam.com> wrote in message
news:e5**************@TK2MSFTNGP15.phx.gbl...
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



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
Nov 21 '05 #3
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
Nov 21 '05 #4
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
"Chris" <no@spam.com> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
SStory wrote:
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" <no@spam.com> wrote in message
news:e5**************@TK2MSFTNGP15.phx.gbl...
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



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

Nov 21 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Titus Cheung | last post by:
Hello, Thought I read somewhere saying that there is a limit to how many connections are available per mySQL account or something like that. Can someone please clarify? I wrote a PHP tool...
0
by: Kayra Otaner | last post by:
Hi all, I want to get your opinions on how to increase available/free memory and performance on a heavy volume database server. I have MySQL 4.0.13 running on RH 7.2 replicated to another RH...
0
by: kayra | last post by:
Hi all, I want to get your opinions on how to increase available/free memory and performance on a heavy volume database server. I have MySQL 4.0.13 running on RH 7.2 replicated to another RH...
0
by: Solange Ezveff | last post by:
Object : How to optimize mysql architecture for better web connection ? Hello, I use phpMyAdmin 2.3.2 and MySQL 3.23.58 and I do not know how to well-organize mysql architecture for the user...
3
by: Anthony | last post by:
Hey all, Here's a question for you, my hosts have told me that that one my pages, php, was causing their server to reboot because there were too many open connections and that they should be...
1
by: Good Man | last post by:
Hi there I've noticed some very weird things happening with my current MySQL setup on my XP Laptop, a development machine. For a while, I have been trying to get the MySQL cache to work....
8
by: Fred | last post by:
Hello, Our website is currently developed in ASP/Mysql 4. The dedicated servers on which it is currently hosted arrive at saturation. Here is their configuration: - 1 server PIV 2,8Ghz 1GB...
4
by: Richard | last post by:
Hi All, I've been trying to build a Ruby-on-Rails plus MySQL application. I'm running Ruby 1.8.2, Rails 1.1.4 and MySQL 5.0.15-nt over WinXP-Pro/SP2. I run under an Administrative account. ...
1
by: marcfischman | last post by:
Please help. I have a website running on a linux/apache/mysql/php server. I receive about 8,000-10,000 visitors a day with about 200,000 to 300,000 page views. The server is a RedHat Linux...
3
by: rockdale | last post by:
Hi, all: My web application using MS EntLib for .net 2.0 (Jan 2006) to access my backend database. It works fine with MS SQL 2k. Now we are migrate from MS SQL to mySQL. Everything looks fine...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.