esql/c multithreaded connection handling question...

It seems to me... that there's a major inefficiency with the handling of connections in a multithreaded environment under the current Informix e/sqlc development environment. Specifically, we've been told by a consultant (and documentation) that the way one would handle this is to simply try setting the connection name, and if it fails with a -1802, to try again. For obvious reasons, we've got some issues with this. On one hand, we're capable of opening <n> connections to SAP; why can't this be done with something like signals or semaphores instead? Surely *someone* out there has solved, or at least addressed, this problem. Is the only way to solve this to do one's own pool management, taking that role out of the e/sql and coding it by hand? If so, how is this best done? Is there a more efficient API to use than the e/sql would generate? Does anyone have any experience with this that they could enlighten me with? We've got a situation where we need to share a pool of n connections across a great deal many more threads (roughly 100, with many more than just 1 in a wait mode as a result) and this continuous looping is a noticeable impact on our system. While we could add delays to the loop, that isn't a very efficient solution, as we'd still like these transactions to take place at their earliest possible time to maximise the use of these connections. :plur, Greg

In article <301119990023270396%gregory.block@freesbee.fr>, Gregory Block <gregory.block@freesbee.fr> wrote: > It seems to me... that there's a major inefficiency with the handling > of connections in a multithreaded environment under the current > Informix e/sqlc development environment. > > Specifically, we've been told by a consultant (and documentation) that > the way one would handle this is to simply try setting the connection > name, and if it fails with a -1802, to try again. > > For obvious reasons, we've got some issues with this. On one hand, > we're capable of opening <n> connections to SAP; why can't this be done > with something like signals or semaphores instead? Surely *someone* > out there has solved, or at least addressed, this problem. > > Is the only way to solve this to do one's own pool management, taking > that role out of the e/sql and coding it by hand? If so, how is this > best done? Is there a more efficient API to use than the e/sql would > generate? > > Does anyone have any experience with this that they could enlighten me > with? We've got a situation where we need to share a pool of n > connections across a great deal many more threads (roughly 100, with > many more than just 1 in a wait mode as a result) and this continuous > looping is a noticeable impact on our system. While we could add > delays to the loop, that isn't a very efficient solution, as we'd still > like these transactions to take place at their earliest possible time > to maximise the use of these connections. > > :plur, > Greg > I ran into a problem in that esql/c multi-threaded applications connected to the database over TCP. TCP is incredibly inefficient when compared to shared memory, so this is what I did. Instead of making the application strictly multi-threaded, I wrote the application to fork off children that each created their own shared memory connection to the database. This made it impossible for the children to share a single cursor, but it increased the speed that independant transaction ran significantly. You can still write your own job management via ipc message queues (best in this case, I think) or shared memory or semaphores. Am I even close to answering your question? -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.

In article <820k5j$der$1@nnrp1.deja.com>, mars1972@my-deja.com wrote: > I ran into a problem in that esql/c multi-threaded applications > connected to the database over TCP. TCP is incredibly inefficient when > compared to shared memory, so this is what I did. Instead of making the > application strictly multi-threaded, I wrote the application to fork off > children that each created their own shared memory connection to the > database. This made it impossible for the children to share a single > cursor, but it increased the speed that independant transaction ran > significantly. You can still write your own job management via ipc > message queues (best in this case, I think) or shared memory or > semaphores. > > Am I even close to answering your question? Yes, sort of. :/ Essentially, though, even though I know doing it this way would be faster, I can't. 1) I'm doing this through a plug-in interface of a commercial product that is not co-located with the database. 2) There are several of these accessing the database at any one point in time; the server acts as a kind of "cache" for the content, in some ways. (I'm trying really hard not to explain exactly what I'm doing because I don't want details ending up in my competitors' developer inboxes.) Essentially, I'm not unhappy with the performance of the TCP connection, per-se; the amount of multithreading I can do simultaneously during the request-processing phase is theoretically enormous on the server side, upwards of 100 threads are configured at any one point in time. And I'm not very worried about the service time of the transaction because it's not the transaction's performance that is wasting the majority of the CPU: It's the pool code. It's setting the connection name, and looping in a while until I get what I'm looking for. Something signal/semaphore-related would be a better choice, something that waited instead of polled. But I don't see any obvious way of getting this out of the system without doing all of the semaphore handling on my own and throwing away any and all pool connectivity within the embedded sql code. The transactions I'm performing aren't complex - there's just a lot of them, happening across a great many threads - and once you hit a situation where someone has to wait for a connection to be released, you essentially torch that thread in a continuous loop, which becomes a huge, ugly wad of CPU utilization where there needn't be any. If there's another mechanism I can use instead of this, even if it means dumping all of the sql/c code, I'm all for it - I just need a pointer on where to get the API details. If I can fix this problem within sql/c, I'll be halfway to having the problem fixed. (The other half being that when there are any problems on the database, such as logical logs filling up because the backup system has done something stupid to itself, the TCP connection handling adds an enormous amount of overhead to the timing, and I just can't see how to tune this to wait less time or to not block so viciously.) Still, it's good advice - maybe the right thing to do here is to not use an SQL/TCP connection, but to use something like IIOP to perform the transactions remotely, directly on the host database, through shared memory access. It gracefully (or not so gracefully, depending on whether or not you like or dislike CORBA) gets around the problem... Sort of. :) :plur, Greg

Gregory, what you are looking for is either a mutex or a condition. These are standard tools for multithreaded applications to share limited resources. Conditions are typically used You would have a condition for each connection (which also requires a mutex for each thread) and only one thread can hold the condition at atime so if a thread wants to acquire a particular connection it would block on that connections condition variable (pthread_cond_wait()) until it is free. When a thread acquires the condition it enables the connection, uses it, sets it dormant when done with it, and calls pthread_cond_signal() to awaken the next thread waiting on the condition. Similarly you can use only mutexes to lock the connection. When you have acquired the mutex with pthread_mutex_lock() other threads will block on that function until the holding thread releases it. When the thread is done with the connection it would set the connection dormant and release the mutex with pthread_mutex_unlock(). You are responsible to manage the threads' access to the various connections you create. There are other schemes, for instance if you do not care which connection a thread acquires next you can just have two mutexes locking the entire pool, one a true/false valued mutex (call it 'a') and one which contains a count of the number of available connections (call it 'b'). Each process that acquires mutex 'b' and decriments it and blocks on mutex 'a' which controls access to an array of flags indicating which connections are busy so only one thread at a time can modify that data structure. A thread needing a connection that acquires mutex 'a' finds an unused connection by checking the array of flags and updates the flag then releases the mutex 'a'. If there are no available connections then mutex 'b' will be zero and threads will block on it. A thread that is finished with a connection will acquire mutex 'a' and clear that connection's flag (each thread will have to use a tread specific variable or an array entry indexed by its tid to keep track of which conection it owns) and release BOTH mutex 'a' and mutex 'b' in that order. Hope that helps. Art S. Kagel Gregory Block wrote: > > In article <820k5j$der$1@nnrp1.deja.com>, mars1972@my-deja.com wrote: > > I ran into a problem in that esql/c multi-threaded applications > > connected to the database over TCP. TCP is incredibly inefficient when > > compared to shared memory, so this is what I did. Instead of making the > > application strictly multi-threaded, I wrote the application to fork off > > children that each created their own shared memory connection to the > > database. This made it impossible for the children to share a single > > cursor, but it increased the speed that independant transaction ran > > significantly. You can still write your own job management via ipc > > message queues (best in this case, I think) or shared memory or > > semaphores. > > > > Am I even close to answering your question? > > Yes, sort of. :/ > > Essentially, though, even though I know doing it this way would be faster, > I can't. > > 1) I'm doing this through a plug-in interface of a commercial product that > is not co-located with the database. > > 2) There are several of these accessing the database at any one point in > time; the server acts as a kind of "cache" for the content, in some ways. > > (I'm trying really hard not to explain exactly what I'm doing because I > don't want details ending up in my competitors' developer inboxes.) > > Essentially, I'm not unhappy with the performance of the TCP connection, > per-se; the amount of multithreading I can do simultaneously during the > request-processing phase is theoretically enormous on the server side, > upwards of 100 threads are configured at any one point in time. And I'm > not very worried about the service time of the transaction because it's not > the transaction's performance that is wasting the majority of the CPU: > It's the pool code. It's setting the connection name, and looping in a > while until I get what I'm looking for. > > Something signal/semaphore-related would be a better choice, something that > waited instead of polled. But I don't see any obvious way of getting this > out of the system without doing all of the semaphore handling on my own and > throwing away any and all pool connectivity within the embedded sql code. > > The transactions I'm performing aren't complex - there's just a lot of > them, happening across a great many threads - and once you hit a situation > where someone has to wait for a connection to be released, you essentially > torch that thread in a continuous loop, which becomes a huge, ugly wad of > CPU utilization where there needn't be any. > > If there's another mechanism I can use instead of this, even if it means > dumping all of the sql/c code, I'm all for it - I just need a pointer on > where to get the API details. If I can fix this problem within sql/c, I'll > be halfway to having the problem fixed. > > (The other half being that when there are any problems on the database, > such as logical logs filling up because the backup system has done > something stupid to itself, the TCP connection handling adds an enormous > amount of overhead to the timing, and I just can't see how to tune this to > wait less time or to not block so viciously.) > > Still, it's good advice - maybe the right thing to do here is to not use an > SQL/TCP connection, but to use something like IIOP to perform the > transactions remotely, directly on the host database, through shared memory > access. It gracefully (or not so gracefully, depending on whether or not > you like or dislike CORBA) gets around the problem... Sort of. :) > > :plur, > Greg