I have an application which queries a number MySQL databases, up to 9 depending on the user's selection. The databases all sit on the same server. Because the application can make many many calls to the server, I'm trying to speed things up by creating multiple concurrent connections to the server.
Essentially I have a "Subject" object, each subject has an email address which is used to query the databases and determine where the email address appears. Rather than query the first database, then the second, then the third etc. I have created a background worker for each of the databases queries so that, in theory, the queries can all be run at the same time.
It is working with limited success. By monitoring the connections I can see that multiple connections are being made and my processing speed increases significantly. The problem is the number of connections increases, out of control, until too many are running for the MySQL server to handle.
The problem, I think, arises because I can be processing very large numbers of subjects in one go. The subjects are created in a for loop iterating through a list of email addresses, one subject per email. The list of emails can be very long, in the order of tens of thousands.
I think what is happening is that as each subject is created, the background workers each start their queries (up to nine at one), creating 9 connections. Because these connections are made on separate threads, the main body of the processing moves to the next subject and opens up another 9 connections, before all of the first 9 have been closed. So for example, if only 4 for the first 9 have completed and closed, I now have 13 open connections. This cascades until I have too many connections open and the server starts sending back errors.
What I'm trying to do is created a subject, query the nine databases all at once using background workers and once all of the background workers have finished their queries, move on to creating the next subject.
I'm a little confused how to control this. I've tried using .isbusy and runworkercompleted, but without success so far. I think the problem I'm having is the structure of my application. The list of initial emails is looped through, on the submission form presented to the user. For each email in the list "Subject" object is created. During the creation of this object, the calls to the databases are made. Really I need control the creation of the subject objects by waiting until the previous subject has been created.
Does this make sense?
Thanks for any help or suggestions
Essentially I have a "Subject" object, each subject has an email address which is used to query the databases and determine where the email address appears. Rather than query the first database, then the second, then the third etc. I have created a background worker for each of the databases queries so that, in theory, the queries can all be run at the same time.
It is working with limited success. By monitoring the connections I can see that multiple connections are being made and my processing speed increases significantly. The problem is the number of connections increases, out of control, until too many are running for the MySQL server to handle.
The problem, I think, arises because I can be processing very large numbers of subjects in one go. The subjects are created in a for loop iterating through a list of email addresses, one subject per email. The list of emails can be very long, in the order of tens of thousands.
I think what is happening is that as each subject is created, the background workers each start their queries (up to nine at one), creating 9 connections. Because these connections are made on separate threads, the main body of the processing moves to the next subject and opens up another 9 connections, before all of the first 9 have been closed. So for example, if only 4 for the first 9 have completed and closed, I now have 13 open connections. This cascades until I have too many connections open and the server starts sending back errors.
What I'm trying to do is created a subject, query the nine databases all at once using background workers and once all of the background workers have finished their queries, move on to creating the next subject.
I'm a little confused how to control this. I've tried using .isbusy and runworkercompleted, but without success so far. I think the problem I'm having is the structure of my application. The list of initial emails is looped through, on the submission form presented to the user. For each email in the list "Subject" object is created. During the creation of this object, the calls to the databases are made. Really I need control the creation of the subject objects by waiting until the previous subject has been created.
Does this make sense?
Thanks for any help or suggestions
