Re: Multiple concurrent transactions per connection
Michael McTernan wrote: Hi there, Given this model, each thread is obviously going to want to have multiple transactions outstanding. Is this something that might be added to MySQL in future or am I totally overestimating the expense of using one thread per connection? I'm guessing you mean that each thread will want to have a number of different transactions active at one time. Depending on how your application works, you could nest transactions on one connection through careful use of savepoints and rollbacks to save points (under InnoDb from 4.0.14). This won't help overlapping transactions that don't nest neatly though. Indeed! That was one thing I was thinking of, but the fact that InnoDB has non-saga style "transaction nesting" makes things a bit difficult. Alternatively you can use connection pooling and have it such that you use a connection per transaction. You could make it such that once all the connections were taken, your application blocked the start of further transactions. That was also something I looked at, but was worried about hitting the limit of the connection pool. I think you should be careful though. While a good design will help performance, you might end up finding that the benefits of your design are lost since disk IO or some other larger factor proves to be a much bigger bottleneck! Damned disks...err...I mean I was going to store all of my data in RAM anyway. :-) Thanks, Mike Thanks for your insights! Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multiple concurrent transactions per connection
Hi there, > Given this model, each thread is obviously going to want to have > multiple transactions outstanding. Is this something that might be added > to MySQL in future or am I totally overestimating the expense of using > one thread per connection? I'm guessing you mean that each thread will want to have a number of different transactions active at one time. Depending on how your application works, you could nest transactions on one connection through careful use of savepoints and rollbacks to save points (under InnoDb from 4.0.14). This won't help overlapping transactions that don't nest neatly though. Alternatively you can use connection pooling and have it such that you use a connection per transaction. You could make it such that once all the connections were taken, your application blocked the start of further transactions. I think you should be careful though. While a good design will help performance, you might end up finding that the benefits of your design are lost since disk IO or some other larger factor proves to be a much bigger bottleneck! Thanks, Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple concurrent transactions per connection
checkout http://www.dbmail.org On Sun, 15 Feb 2004, Chris Nolan wrote: > Hi all, > > I'm currently designing an open-source messaging server that will use > MySQL as the data store (in embedded form). > > High performance is one of the goals of this project, so I have been > examining possible I/O models and seem to have settled on a model where > each thread services many requests using non-blocking I/O and keeping > track of how much of the request has been satisfied. > > Given this model, each thread is obviously going to want to have > multiple transactions outstanding. Is this something that might be added > to MySQL in future or am I totally overestimating the expense of using > one thread per connection? > > Regards, > > Chris > > > -- -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple concurrent transactions per connection
Funny you should mention the totally excellent DBMail - I've deployed it for quite a few of my clients. The project I am working on is more of a "sod off" in the direction of Exchange. I'm aiming for something collaborative, with a proper backing store that's easily deployable and an original open source work. Regards, Chris Curtis Maurand wrote: checkout http://www.dbmail.org On Sun, 15 Feb 2004, Chris Nolan wrote: Hi all, I'm currently designing an open-source messaging server that will use MySQL as the data store (in embedded form). High performance is one of the goals of this project, so I have been examining possible I/O models and seem to have settled on a model where each thread services many requests using non-blocking I/O and keeping track of how much of the request has been satisfied. Given this model, each thread is obviously going to want to have multiple transactions outstanding. Is this something that might be added to MySQL in future or am I totally overestimating the expense of using one thread per connection? Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple concurrent transactions per connection
Hi all, I'm currently designing an open-source messaging server that will use MySQL as the data store (in embedded form). High performance is one of the goals of this project, so I have been examining possible I/O models and seem to have settled on a model where each thread services many requests using non-blocking I/O and keeping track of how much of the request has been satisfied. Given this model, each thread is obviously going to want to have multiple transactions outstanding. Is this something that might be added to MySQL in future or am I totally overestimating the expense of using one thread per connection? Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]