| Subject | Re: Very fast read only transactions |
|---|---|
| Author | Adam |
| Post date | 2009-01-21T22:37:45Z |
Jeff,
up a bit on the concepts to make sure you do not reinvent wheels, but
I have a concern here about concurrent users.
Are you intending on sharing the single connection/transaction
instance between your web users? If so, you are going to encounter
some thread-safety issues or some serialisation bottlenecks. In simple
terms, what happens to user 2 if when they connect to your web
application, user 1 is already using the connection?
If your plans involve critical sections, then I will venture to
suggest that scalability will immediately suffer, and it some cases it
may perform worse than your original instantiate connection per user
model.
Usually in a connection pool, you establish multiple connections to
the database (configurable). For example, you may establish 20
connections that then go into a pool. When your web application needs
a connection, it looks for an available connection from the pool, and
when done the connection is returned to the pool.
There are other variations, such as setting a maximum pool size. In
this model, connections are created whenever the connection pool is
empty (like your original model), although they are returned to the
pool instead of returned. If the maximum pool size is reached, you
need to return some friendly 503 error page.
Anyway, back to Firebird; I would imagine the overhead of starting and
committing a transaction is significantly lower than the overhead of
waiting for an available connection from the pool of 1. You should do
some profiling to see the time taken to start and commit a read only
transaction to see whether you are fighting a problem that only exists
in your mind.
Adam
> Today I wrote a second test which (in order):What you propose is fairly typical of connection pooling, maybe read
> - application is executed and
> - creates a single instance of the database then
> - connects (stays connected indefinately)
> - creates a single instance of a transaction
> - starts the transaction
> - creates a statement instance
> - prepares the sql statement:
> SELECT FIRSTNAME, LASTNAME FROM LEADS WHERE PRIMARYKEY=?
>
up a bit on the concepts to make sure you do not reinvent wheels, but
I have a concern here about concurrent users.
Are you intending on sharing the single connection/transaction
instance between your web users? If so, you are going to encounter
some thread-safety issues or some serialisation bottlenecks. In simple
terms, what happens to user 2 if when they connect to your web
application, user 1 is already using the connection?
If your plans involve critical sections, then I will venture to
suggest that scalability will immediately suffer, and it some cases it
may perform worse than your original instantiate connection per user
model.
Usually in a connection pool, you establish multiple connections to
the database (configurable). For example, you may establish 20
connections that then go into a pool. When your web application needs
a connection, it looks for an available connection from the pool, and
when done the connection is returned to the pool.
There are other variations, such as setting a maximum pool size. In
this model, connections are created whenever the connection pool is
empty (like your original model), although they are returned to the
pool instead of returned. If the maximum pool size is reached, you
need to return some friendly 503 error page.
Anyway, back to Firebird; I would imagine the overhead of starting and
committing a transaction is significantly lower than the overhead of
waiting for an available connection from the pool of 1. You should do
some profiling to see the time taken to start and commit a read only
transaction to see whether you are fighting a problem that only exists
in your mind.
Adam