Regarding the usage of mutex in the mysql connection threading
Hi, We migrated a NMS project from oracle 7.3 database to mysql 4.0.23. In the migration we kept the mutex mechanism followed in the oracle for connection threading. Actually these mutex are mainly used before executing the sql statements which returns multiple number of records and released immediately after the execution. Could any one of you please advise us, whether this mutex mechanism is really required in mysql 4.0.23 or not?. Is there any problem if we did not use mutex for connection threading?. Please advise us and send the reaply asap as it is very urgent. Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Regarding the usage of mutex in the mysql connection threading
[EMAIL PROTECTED] wrote on 08/30/2005 08:54:44 AM: Hi, We migrated a NMS project from oracle 7.3 database to mysql 4.0.23. In the migration we kept the mutex mechanism followed in the oracle for connection threading. Actually these mutex are mainly used before executing the sql statements which returns multiple number of records and released immediately after the execution. Could any one of you please advise us, whether this mutex mechanism is really required in mysql 4.0.23 or not?. Is there any problem if we did not use mutex for connection threading?. Please advise us and send the reaply asap as it is very urgent. Thanks, Narasimha It sounds as though you SHARE at least one connection between several processes/threads. If that is what you do, then YES. You will need a mutex to prevent one process from using a connection currently being used by another process. A more scalable solution for a managed connection environment would be to build a connection pool and allow your processes to borrow connections to the pool only as long as they need them. That way each process has their OWN CONNECTION and you won't have to worry about concurrent requests. One caution with this technique: MySQL variables and temporary tables are connection specific. If you do not properly manage your variables or your temporary tables when entering or exiting a borrowed connection you may run into data created by a process that previously the connection (This is true in any SHARED connection scenario). Ensure that when your application ends, that all of the connections are properly closed. The best solution may be for each process/thread to manage its own connection independently. An effective way to prevent connection overload on your server is to make sure you properly close every connection as soon as you are through using it. If your application performs database activity in bursts (do some database work, wait for user, do more database work, wait for user...) it may be an optimal design choice to close the connection after each burst. However, that bit of tuning is best decided by benchmarking on your equipment with your software operating under both normal and abnormal loads. Use whichever connection plan works best during testing. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Regarding the usage of mutex in the mysql connection threading
Hi Green, Thanks a lot for your reply. In brief if I explain the architecture it something goes like this:- - Element management Apllications uses MySQL database through DB access layer. - DB access layer provides the application some APIs needed for DB opearations. - for Each DB transactions the applications login to the database- gets free connection id from pool (local data structure maintained in application) - do the operation - logout - return the connection id to the pool. - we are going to MySQL through ODBC which identifies the transactions by there connection handles. So every parralel transactions are having separate connection id and separate handles for ODBC. Now in this kind of implementation do we need to put any of the sql statement execution / reading from result set opearation into a critical section (mutex)? Please send the reply asap. Regards, Narasimha, -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 30, 2005 6:40 PM To: Lakshmi NarasimhaRao (WT01 - Voice Next Generation Networks) Cc: mysql@lists.mysql.com Subject: Re: Regarding the usage of mutex in the mysql connection threading [EMAIL PROTECTED] wrote on 08/30/2005 08:54:44 AM: Hi, We migrated a NMS project from oracle 7.3 database to mysql 4.0.23. In the migration we kept the mutex mechanism followed in the oracle for connection threading. Actually these mutex are mainly used before executing the sql statements which returns multiple number of records and released immediately after the execution. Could any one of you please advise us, whether this mutex mechanism is really required in mysql 4.0.23 or not?. Is there any problem if we did not use mutex for connection threading?. Please advise us and send the reaply asap as it is very urgent. Thanks, Narasimha It sounds as though you SHARE at least one connection between several processes/threads. If that is what you do, then YES. You will need a mutex to prevent one process from using a connection currently being used by another process. A more scalable solution for a managed connection environment would be to build a connection pool and allow your processes to borrow connections to the pool only as long as they need them. That way each process has their OWN CONNECTION and you won't have to worry about concurrent requests. One caution with this technique: MySQL variables and temporary tables are connection specific. If you do not properly manage your variables or your temporary tables when entering or exiting a borrowed connection you may run into data created by a process that previously the connection (This is true in any SHARED connection scenario). Ensure that when your application ends, that all of the connections are properly closed. The best solution may be for each process/thread to manage its own connection independently. An effective way to prevent connection overload on your server is to make sure you properly close every connection as soon as you are through using it. If your application performs database activity in bursts (do some database work, wait for user, do more database work, wait for user...) it may be an optimal design choice to close the connection after each burst. However, that bit of tuning is best decided by benchmarking on your equipment with your software operating under both normal and abnormal loads. Use whichever connection plan works best during testing. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
RE: Regarding the usage of mutex in the mysql connection threading
hi , Could any one of you please let me know in mysql/myODBC that the session control is now under their management meaning - handles different queries/write from different threads within the same connection - handles different queries (or batch fetches) from multi-connections Please reply asap. Thanks, Narasimha -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 30, 2005 6:40 PM To: Lakshmi NarasimhaRao (WT01 - Voice Next Generation Networks) Cc: mysql@lists.mysql.com Subject: Re: Regarding the usage of mutex in the mysql connection threading [EMAIL PROTECTED] wrote on 08/30/2005 08:54:44 AM: Hi, We migrated a NMS project from oracle 7.3 database to mysql 4.0.23. In the migration we kept the mutex mechanism followed in the oracle for connection threading. Actually these mutex are mainly used before executing the sql statements which returns multiple number of records and released immediately after the execution. Could any one of you please advise us, whether this mutex mechanism is really required in mysql 4.0.23 or not?. Is there any problem if we did not use mutex for connection threading?. Please advise us and send the reaply asap as it is very urgent. Thanks, Narasimha It sounds as though you SHARE at least one connection between several processes/threads. If that is what you do, then YES. You will need a mutex to prevent one process from using a connection currently being used by another process. A more scalable solution for a managed connection environment would be to build a connection pool and allow your processes to borrow connections to the pool only as long as they need them. That way each process has their OWN CONNECTION and you won't have to worry about concurrent requests. One caution with this technique: MySQL variables and temporary tables are connection specific. If you do not properly manage your variables or your temporary tables when entering or exiting a borrowed connection you may run into data created by a process that previously the connection (This is true in any SHARED connection scenario). Ensure that when your application ends, that all of the connections are properly closed. The best solution may be for each process/thread to manage its own connection independently. An effective way to prevent connection overload on your server is to make sure you properly close every connection as soon as you are through using it. If your application performs database activity in bursts (do some database work, wait for user, do more database work, wait for user...) it may be an optimal design choice to close the connection after each burst. However, that bit of tuning is best decided by benchmarking on your equipment with your software operating under both normal and abnormal loads. Use whichever connection plan works best during testing. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
RE: Regarding the usage of mutex in the mysql connection threading
Hi, Please advise us whether mysql/myodbc - handles different queries/write from different threads within the same connection or not? - handles different queries (or batch fetches) from multi-connections or not? Thanks, Narasimha -Original Message- From: Lakshmi NarasimhaRao (WT01 - Voice Next Generation Networks) Sent: Tuesday, August 30, 2005 7:34 PM To: mysql@lists.mysql.com Cc: '[EMAIL PROTECTED]' Subject: RE: Regarding the usage of mutex in the mysql connection threading hi , Could any one of you please let me know in mysql/myODBC that the session control is now under their management meaning - handles different queries/write from different threads within the same connection - handles different queries (or batch fetches) from multi-connections Please reply asap. Thanks, Narasimha -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 30, 2005 6:40 PM To: Lakshmi NarasimhaRao (WT01 - Voice Next Generation Networks) Cc: mysql@lists.mysql.com Subject: Re: Regarding the usage of mutex in the mysql connection threading [EMAIL PROTECTED] wrote on 08/30/2005 08:54:44 AM: Hi, We migrated a NMS project from oracle 7.3 database to mysql 4.0.23. In the migration we kept the mutex mechanism followed in the oracle for connection threading. Actually these mutex are mainly used before executing the sql statements which returns multiple number of records and released immediately after the execution. Could any one of you please advise us, whether this mutex mechanism is really required in mysql 4.0.23 or not?. Is there any problem if we did not use mutex for connection threading?. Please advise us and send the reaply asap as it is very urgent. Thanks, Narasimha It sounds as though you SHARE at least one connection between several processes/threads. If that is what you do, then YES. You will need a mutex to prevent one process from using a connection currently being used by another process. A more scalable solution for a managed connection environment would be to build a connection pool and allow your processes to borrow connections to the pool only as long as they need them. That way each process has their OWN CONNECTION and you won't have to worry about concurrent requests. One caution with this technique: MySQL variables and temporary tables are connection specific. If you do not properly manage your variables or your temporary tables when entering or exiting a borrowed connection you may run into data created by a process that previously the connection (This is true in any SHARED connection scenario). Ensure that when your application ends, that all of the connections are properly closed. The best solution may be for each process/thread to manage its own connection independently. An effective way to prevent connection overload on your server is to make sure you properly close every connection as soon as you are through using it. If your application performs database activity in bursts (do some database work, wait for user, do more database work, wait for user...) it may be an optimal design choice to close the connection after each burst. However, that bit of tuning is best decided by benchmarking on your equipment with your software operating under both normal and abnormal loads. Use whichever connection plan works best during testing. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
RE: Regarding the usage of mutex in the mysql connection threading
[EMAIL PROTECTED] wrote on 08/30/2005 10:03:51 AM: Could any one of you please let me know in mysql/myODBC that the session control is now under their management meaning - handles different queries/write from different threads within the same connection - handles different queries (or batch fetches) from multi-connections [EMAIL PROTECTED] wrote on 08/30/2005 09:54:54 AM: In brief if I explain the architecture it something goes like this:- - Element management Apllications uses MySQL database through DB access layer. - DB access layer provides the application some APIs needed for DB opearations. - for Each DB transactions the applications login to the database- gets free connection id from pool (local data structure maintained in application) - do the operation - logout - return the connection id to the pool. - we are going to MySQL through ODBC which identifies the transactions by there connection handles. So every parralel transactions are having separate connection id and separate handles for ODBC. Now in this kind of implementation do we need to put any of the sql statement execution / reading from result set opearation into a critical section (mutex)? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 30, 2005 6:40 PM To: Lakshmi NarasimhaRao (WT01 - Voice Next Generation Networks) Cc: mysql@lists.mysql.com Subject: Re: Regarding the usage of mutex in the mysql connection threading [EMAIL PROTECTED] wrote on 08/30/2005 08:54:44 AM: Hi, We migrated a NMS project from oracle 7.3 database to mysql 4.0.23. In the migration we kept the mutex mechanism followed in the oracle for connection threading. Actually these mutex are mainly used before executing the sql statements which returns multiple number of records and released immediately after the execution. Could any one of you please advise us, whether this mutex mechanism is really required in mysql 4.0.23 or not?. Is there any problem if we did not use mutex for connection threading?. Please advise us and send the reaply asap as it is very urgent. Thanks, Narasimha It sounds as though you SHARE at least one connection between several processes/threads. If that is what you do, then YES. You will need a mutex to prevent one process from using a connection currently being used by another process. A more scalable solution for a managed connection environment would be to build a connection pool and allow your processes to borrow connections to the pool only as long as they need them. That way each process has their OWN CONNECTION and you won't have to worry about concurrent requests. One caution with this technique: MySQL variables and temporary tables are connection specific. If you do not properly manage your variables or your temporary tables when entering or exiting a borrowed connection you may run into data created by a process that previously the connection (This is true in any SHARED connection scenario). Ensure that when your application ends, that all of the connections are properly closed. The best solution may be for each process/thread to manage its own connection independently. An effective way to prevent connection overload on your server is to make sure you properly close every connection as soon as you are through using it. If your application performs database activity in bursts (do some database work, wait for user, do more database work, wait for user...) it may be an optimal design choice to close the connection after each burst. However, that bit of tuning is best decided by benchmarking on your equipment with your software operating under both normal and abnormal loads. Use whichever connection plan works best during testing. Shawn Green Database Administrator Unimin Corporation - Spruce Pine I will respond to both emails at once (or at least try to). First, I have to ask: Why are you trying to use ODBC? I ask because you describe critical sections and mutexes which are lower level programming concepts. If you are working at that level you have a much easier and more direct access to the database if you use the MySQL C API (Chapter 23 in the current manual). The DLLs, .h files, and .lib files should already exist on your system. You explain that each thread/process gets its own connection (without sharing) from the ODBC connection manager pool. You asked if you now needed to synchronize access so that your various connections are only in use one-at-a-time. The answer is no because you do not SHARE connections. You do not need to worry about concurrent connection usage so long as only one thread or process is using any single connection at any one time. Now, because you are using a connection pool, a minimum number of connections are created and maintained by the pool manager. Each connection has specific to it any user variables or temporary
RE: Regarding the usage of mutex in the mysql connection threading
[EMAIL PROTECTED] wrote on 08/30/2005 10:09:27 AM: Hi, Please advise us whether mysql/myodbc - handles different queries/write from different threads within the same connection or not? - handles different queries (or batch fetches) from multi-connections or not? Thanks, Narasimha Please consider this a friendly warning. This is not a help desk. This is a community-supported mailing list. We all have our regular jobs to do. Sometimes it takes DAYS for a good response. You happen to be lucky enough that I was ALREADY working on a response to the FIRST time you posted this and that I did not see this repeat before I sent my response. NEXT TIME... I will sit on my response for an entire week before if I see one more ASAP or repeated message show up from you. I am sure there are others as frustrated with your lack of consideration as I am. There is nothing time-critical about this list or how we respond to it. If you need time-critical consultation, buy some. Otherwise, please be thankful that so many experts contribute to this list freely and as their time alows. There may be as many as 5 people actually paid to respond to this list, tops. Everyone else does it just to be helpful. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Regarding the usage of mutex in the mysql connection threading
Hi Green, We are at the last stage of the project (migration from Oracle to MySQL). We are demanded to adopt the MYODBC as a customer requrement. As mutex was implemented for oracle so it is there in our code. Now we are thinking to remove that because application level we have connection id. So, please advise us in this case can we remove the mutex?. I beleive that in the ODBC and in MySQL mutex will be handled automatically, right?. Regards Narasimha -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 30, 2005 8:03 PM To: Lakshmi NarasimhaRao (WT01 - Voice Next Generation Networks) Cc: mysql@lists.mysql.com Subject: RE: Regarding the usage of mutex in the mysql connection threading [EMAIL PROTECTED] wrote on 08/30/2005 10:03:51 AM: Could any one of you please let me know in mysql/myODBC that the session control is now under their management meaning - handles different queries/write from different threads within the same connection - handles different queries (or batch fetches) from multi-connections [EMAIL PROTECTED] wrote on 08/30/2005 09:54:54 AM: In brief if I explain the architecture it something goes like this:- - Element management Apllications uses MySQL database through DB access layer. - DB access layer provides the application some APIs needed for DB opearations. - for Each DB transactions the applications login to the database- gets free connection id from pool (local data structure maintained in application) - do the operation - logout - return the connection id to the pool. - we are going to MySQL through ODBC which identifies the transactions by there connection handles. So every parralel transactions are having separate connection id and separate handles for ODBC. Now in this kind of implementation do we need to put any of the sql statement execution / reading from result set opearation into a critical section (mutex)? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 30, 2005 6:40 PM To: Lakshmi NarasimhaRao (WT01 - Voice Next Generation Networks) Cc: mysql@lists.mysql.com Subject: Re: Regarding the usage of mutex in the mysql connection threading [EMAIL PROTECTED] wrote on 08/30/2005 08:54:44 AM: Hi, We migrated a NMS project from oracle 7.3 database to mysql 4.0.23. In the migration we kept the mutex mechanism followed in the oracle for connection threading. Actually these mutex are mainly used before executing the sql statements which returns multiple number of records and released immediately after the execution. Could any one of you please advise us, whether this mutex mechanism is really required in mysql 4.0.23 or not?. Is there any problem if we did not use mutex for connection threading?. Please advise us and send the reaply asap as it is very urgent. Thanks, Narasimha It sounds as though you SHARE at least one connection between several processes/threads. If that is what you do, then YES. You will need a mutex to prevent one process from using a connection currently being used by another process. A more scalable solution for a managed connection environment would be to build a connection pool and allow your processes to borrow connections to the pool only as long as they need them. That way each process has their OWN CONNECTION and you won't have to worry about concurrent requests. One caution with this technique: MySQL variables and temporary tables are connection specific. If you do not properly manage your variables or your temporary tables when entering or exiting a borrowed connection you may run into data created by a process that previously the connection (This is true in any SHARED connection scenario). Ensure that when your application ends, that all of the connections are properly closed. The best solution may be for each process/thread to manage its own connection independently. An effective way to prevent connection overload on your server is to make sure you properly close every connection as soon as you are through using it. If your application performs database activity in bursts (do some database work, wait for user, do more database work, wait for user...) it may be an optimal design choice to close the connection after each burst. However, that bit of tuning is best decided by benchmarking on your equipment with your software operating under both normal and abnormal loads. Use whichever connection plan works best during testing. Shawn Green Database Administrator Unimin Corporation - Spruce Pine I will respond to both emails at once (or at least try to). First, I have to ask: Why are you trying to use ODBC? I ask because you describe critical sections and mutexes which are lower level programming concepts. If you are working at that level you have a much easier and more direct access to the database if you use the MySQL C API (Chapter 23
RE: Regarding the usage of mutex in the mysql connection threading
I can not and will not make an absolute recommendation to keep or lose the mutex before I performed a full code review. IMHO, the final stages of a project is one of the worst times to be making this kind of decision as it could impact so much work already accomplished. If you are certain that each connection can only be used by one thread/process at a time, then you should not need to synchronize (serialize) access to any connection. Connection pooling IS NOT connection sharing. It sounds to me that the previous version of your application shared a single connection between several threads and required a transaction-level mutex to ensure proper SQL command serialization. You probably do not need that now as you are not _sharing_ a single common connection between more than one execution at a time. If you DO share a connection between two or more threads or processes, you will need to keep the mutex to keep one thread from clobbering the SQL being executed by the other. Does MySQL or ODBC handle the mutex? No, that is part of your execution environment (usually handled by the OS). Will ODBC pool connections? Yes, if you allow it to do so. MySQL keeps all connections separated from each other until the point data is committed to the database (transactional boundaries). How your application uses transactions is up to you. Under most circumstances, what one connection is doing is invisible to what another connection is doing up to the point at which one of them commits their changes to the database. Then those changes may, depending on your transaction isolation level, become instantly visible to the other connection or not. As I said, it all depends. If each thread/process establishes its own connection to the database server, you are in a situation (from the point of view of the database server) identical to what would be happening if all of your processes were connecting in from different physical machines. If you need a mutex to serialize access under that scenario, then you should keep it. Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 08/30/2005 10:56:20 AM: Hi Green, We are at the last stage of the project (migration from Oracle to MySQL). We are demanded to adopt the MYODBC as a customer requrement. As mutex was implemented for oracle so it is there in our code. Now we are thinking to remove that because application level we have connection id. So, please advise us in this case can we remove the mutex?. I beleive that in the ODBC and in MySQL mutex will be handled automatically, right?. Regards Narasimha
