Clustered JDBC: C-JDBC release candidate available

Discussions

News: Clustered JDBC: C-JDBC release candidate available

  1. C-JDBC (Clustered JDBC) is an open source database cluster middleware that allows any application to transparently access a cluster of databases through JDBC.
    The database is distributed and possibly replicated among several nodes and C-JDBC load balances the queries between these nodes.
     
    This new version includes transparent fault-tolerance support, query caching, sql monitoring, advanced load balancing and heterogeneous databases support. Database backends can be added or removed on-the-fly.

    No application or database modification is needed. C-JDBC is a 100% Java middleware licensed under LGPL. C-JDBC has been successfuly tested with Tomcat, JOnAS, JBoss, WebLogic, Oracle, DB2, MySQL, PostgreSQL, Sybase, SAP DB, HyperSonic SQL, Firebird, MS SQL Server and InstantDB.

    Check the C-JDBC web site: http://c-jdbc.objectweb.org.
    Documentation and articles are available at: http://c-jdbc.objectweb.org/doc

    Threaded Messages (25)

  2. cross-database joins?[ Go to top ]

    in a previous thread some time ago, you mentioned that people were talking about supporting cross-database joins via an in-memory DB. Does this release support cross-database joins?

    Thanks
  3. cross-database joins?[ Go to top ]

    No, this release does not include an in-memory database engine to perform distributed joins. However, you have user definable rewriting rules that allow requests to be rewritten on-the-fly to execute on heterogeneous database engines inside the same cluster.

    Emmanuel
  4. Sounds Interesting[ Go to top ]

    This project sounds interesting, but it's a very critical piece. A few questions:

    Is anyone using this in production?

    Does it support XA transactions?

    I assume it sends writes to all DB servers in the cluster... What happens if the write to one server takes a while? Does the write block until all of the writes have been completed? Does it use a 2PC XA transaction to enlist all of the servers in one transaction for the write?

    Does the query cache also participate in the transaction, and what is the isolation level of the query cache? Does it know which queries to flush based on the write?
  5. Sounds Interesting[ Go to top ]

    Also, if we bring up a new node in the cluster, how do we get the initial data set to that node?
  6. Sounds Interesting[ Go to top ]

    Also, if we bring up a new node in the cluster, how do we get the initial data set to that node?


    C-JDBC embedds an ETL tool called Octopus (http://octopus.objectweb.org/) that performs database neutral dumps. The first time you use C-JDBC, we use Octopus to perform a dump of the database. When a new node is added later in time, we restore this dump and replay the missing updates since the dump was done (updates are stored in a RecoveryLog).
    You have an example on how to do this from the console in the documentation (http://c-jdbc.objectweb.org/current/doc/userGuide/html/ar01s05.html#N1069A). We are currently designing a new console GUI so that this procedure just become a one click operation.
  7. Sounds Interesting[ Go to top ]

    Is anyone using this in production?


    Mostly small open source structures. Some large corporations are evaluating it or using it on new configurations but very few of them are willing talk about it (open source is still taboo for a lot of corporations, even more when it concerns data).
     
    > Does it support XA transactions?
    The ExperLog company has tested successfully XAPool (http://xapool.experlog.com/) on top of C-JDBC, which is the standard way to do XA with C-JDBC.
    >
    > I assume it sends writes to all DB servers in the cluster... What happens if the write to one server takes a while? Does the write block until all of the writes have been completed?
    This is completely tunable. You can configure it to return as soon as the first server has completed or wait for a majority or all of them.

    > Does it use a 2PC XA transaction to enlist all of the servers in one transaction for the write?
    No because this is too slow. All transactions occur in parallel at each server but with the exact same serializable order. If one database fails but the other succeed, the failing server is automatically disabled. C-JDBC has built-in tools to rebuild a node that has failed.
    >
    > Does the query cache also participate in the transaction, and what is the isolation level of the query cache?
    The isolation level is the same as the one used with the backend databases (usually read-commited).

    > Does it know which queries to flush based on the write?
    Yes, it parses the query and computes the dependencies to know which queries have to be invalidated.

    Full documentation is available in http://c-jdbc.objectweb.org/doc
  8. I wonder if C-JDBC could be useful in the following situation:

    I am developing a Web Application using Tomcat and Axis. While processing SOAP messages, the application must write to two different databases (Postgresql and Sybase, running on different machines). Can I get a "transaction" which encloses the two writing operations (each one on a different database) without using XA Transactions? (correct me if I am wrong, but I think I would need a full J2EE server such as JoNas or JBoss to do that). After having a look at the User's guide, I've thought it might be possible to just set (using C-JDBC as the JDBC driver) a Datasource of a virtual database and get a JDBC style transaction covering the two writing operations?

    Thanks in advance,
    Fernando Orus
  9. ... While processing SOAP messages, the application must write to two different databases (Postgresql and Sybase, running on different machines). Can I get a "transaction" which encloses the two writing operations (each one on a different database) without using XA Transactions?


    If you want to execute the same query on both databases inside the same transaction then yes, C-JDBC can do this for you. If you want to have a distributed transaction with 2 different queries (one for each backend), then this is only possible if the tables your are writing are not replicated (shared) between the 2 databases. This is known as a RAIDb-0 (full partitioning) configuration in C-JDBC.
    So, that should work in most cases. Don't hesitate to contact us on the mailing list (c-jdbc at objectweb dot org) if you have a problem while trying to build the configuration file for this setup.
  10. Fernando: I am developing a Web Application using Tomcat and Axis. While processing SOAP messages, the application must write to two different databases (Postgresql and Sybase, running on different machines). Can I get a "transaction" which encloses the two writing operations (each one on a different database) without using XA Transactions?

    No.

    (Emmanuel - FWIW, you should have simply answered "no." That is the simple, honest answer, and that will engender more trust in your project than trying to avoid the question.)

    Fernando: (correct me if I am wrong, but I think I would need a full J2EE server such as JoNas or JBoss to do that).

    It depends what you're trying to do. Jonas and JBoss will handle the 2PC processing, but I don't know if either is a recoverable transaction manager. If that is the case (that neither is a recoverable transaction manager) then you could do the same thing (non-XA 2PC) with 10 or so lines of code, something like:

    rollforward=false
    try{
      postgres.prepare
      sybase.prepare
      postgres.commit
      rollforward=true
    } catch (..) {
    }

    if (rollforward) {
      sybase.commit
    } else {
      sybase.rollback
      postgres.rollback
    }

    Since it's not a true global transaction, you don't have a whole lot of guarantees, but (based on your own description) that's probably acceptable for the application.

    Fernando: After having a look at the User's guide, I've thought it might be possible to just set (using C-JDBC as the JDBC driver) a Datasource of a virtual database and get a JDBC style transaction covering the two writing operations?

    Logically, yes, it can do that. However, as far as potential problems, the same exact problems (potential for inconsistencies etc.) with the above 10 lines of code will exist in C-JDBC too. In fact, you'll probably find very similar code in C-JDBC.

    What C-JDBC does is provide a "controller" which is a database engine. As a database engine, it provides a JDBC driver so that you can connect to it. Of course, when you ask the controller (the C-JDBC database engine) to do anything, it just delegates all of its operations to other database(s) via their JDBC drivers. So it is truly "middleware" in that where your app currently talks directly to your database(s), you can instead have your app talk to the C-JDBC "database" that will then in turn talk to your database(s).

    Unfortunately, the C-JDBC "controller" represents a SPOF (single point of failure) and thus (if you were to produce a predictive model) would tend to reduce application availability unless you currently rely on a single database that has very low availability (thus providing an opportunity to use C-JDBC to replicate a database.) Further, it represents a logical SPOB (single point of bottleneck,) so it would tend to reduce scalable performance, unless your current database fits one of the scenarios that C-JDBC is explicitly designed to address. For example, if using a partitioned approach, C-JDBC should provide much higher throughput for database inserts, up to the extent of what you can pipe through a single C-JDBC controller.

    My advice is to look at the challenges you have now, and try to determine if C-JDBC will solve them without introducing liability that outweighs the original challenge. In your case, I don't think that it makes any sense to use C-JDBC, but that is simply because the problem you described is very simple (you want to commit two different database connections and you don't want to use XA.) I would suggest that people consider C-JDBC for systems that need a massive amount of write (and less importantly, read) throughput with PK-based data models that have low join requirements. A bonus feature is that multiple applications can access the "virtual database image" provided by C-JDBC through the controller. However, if any of the applications require HA, I would not consider C-JDBC until the controller itself provides failover to a second controller.

    Peace,

    Cameron Purdy
    Tangosol, Inc.
    Coherence: Clustered JCache for Grid Computing!
  11. (Emmanuel - FWIW, you should have simply answered "no." That is the simple, honest answer, and that will engender more trust in your project than trying to avoid the question.)


    I don't think I have been dishonest (at least it wasn't my intent). I apologize if I have fooled someone with my approximate english.

    > rollforward=false
    > try{
    > postgres.prepare
    > sybase.prepare
    > postgres.commit
    > rollforward=true
    > } catch (..) {
    > }
    >
    > if (rollforward) {
    > sybase.commit
    > } else {
    > sybase.rollback
    > postgres.rollback
    > }

    What is the difference with this code:
    try{
      postgres.prepare
      sybase.prepare
      // probably some writes here
      postgres.commit
      sybase.commit
    } catch (..) {
      sybase.rollback
      postgres.rollback
    }

    In both cases, if postgres commits but sybase fails to commit you will never be able to rollback a transaction that has commited. Is it right?
     
    > Unfortunately, the C-JDBC "controller" represents a SPOF (single point of failure).

    This is wrong. If you looked at the new features in release candidate 1, there is a full implementation of horizontal scalability that allows you to have transparent failover between multiple controllers.

    >... Further, it represents a logical SPOB (single point of bottleneck,)

    No more. The JDBC URL used by the driver contains a list of controllers (jdbc:cjdbc://node1,node2,node3/myDB) for both fault tolerance and load balancing.

    > I would suggest that people consider C-JDBC for systems that need a massive amount of write (and less importantly, read)

    This is not correct. Performance scalability can only be achieved with read-mostly workloads and C-JDBC will only provide fault tolerance but poor performance with write mostly workloads. C-JDBC uses a read one/write everywhere approach so parallelism only occurs on read queries.

    > However, if any of the applications require HA, I would not consider C-JDBC until the controller itself provides failover to a second controller.

    And now you can even have as many controllers as you want (not only 2). You can look at the horizontal scalability design report in the documentation section about the design and limitations of the approach.
    The user documentation has still to be improved on this point (on the task list in SourceForge) but there has already been discussions on the mailing list about this topic (browse the mailing list archives).

    Sincerely,
    Emmanuel
  12. Emmanuel: I don't think I have been dishonest (at least it wasn't my intent). I apologize if I have fooled someone with my approximate english.

    Personally, I know that it's natural for me to want my own product to handle everything that someone asks about it, and the hardest thing sometimes is to say "sorry, we don't do that" or "we aren't the best solution for that problem." People do appreciate the honesty, though, so be clear on what the limitations are, even if they are only temporary limitations.

    What is the difference with this code:
    try{
      postgres.prepare
      sybase.prepare
      // probably some writes here
      postgres.commit
      sybase.commit
    } catch (..) {
      sybase.rollback
      postgres.rollback
    }


    Maybe no difference in the real world. However, for 2PC, I prefer not to choose to roll everything forward until the first (and hopefully most likely to fail) non-read-only RM has committed (as opposed to all prepared.)

    This is wrong. If you looked at the new features in release candidate 1, there is a full implementation of horizontal scalability that allows you to have transparent failover between multiple controllers.

    That's good news. Does that affect the caching that C-JDBC does? How so?

    Peace,

    Cameron Purdy
    Tangosol, Inc.
    Coherence: Clustered JCache for Grid Computing!
  13. That's good news. Does that affect the caching that C-JDBC does? How so?


    The caching remains the same and is local to each controller. Each write query is multicasted to all controllers so that each controller can invalidate its local cache.
    The cache is populated with the select queries of the local controller. It means that queries executed on controller1 are added in controller1's cache only and are not broadcasted to other caches. We do not check remote controller caches before executing a query. If the local cache fails, we just execute the query locally which always seems to be faster than trying to check remote caches.
    Does that make sense?

    Emmanuel
  14. The caching remains the same and is local to each controller. Each write query is multicasted to all controllers so that each controller can invalidate its local cache. [..] Does that make sense?

    The concept of invalidation-based caching being used in a transactional system scares me, since it provides windows of opportunity for the cached data to be stale. I don't know your implementation, but (for example) if someone specifies a serializable isolation level, does a read that comes from cache and a write based on that value, it seems improbable that data corruption would not occur if more than one client is doing similar work.

    Peace,

    Cameron Purdy
    Tangosol, Inc.
    Coherence: Clustered JCache for Grid Computing!
  15. wouldn't optimistic locking solve the issue of writing stale data to the db? does C-JDBC provide it?
  16. wouldn't optimistic locking solve the issue of writing stale data to the db? does C-JDBC provide it?


    Yes, C-JDBC provides both optimistic and pessimistic locking.
  17. I don't know your implementation, but (for example) if someone specifies a serializable isolation level, does a read that comes from cache and a write based on that value, it seems improbable that data corruption would not occur if more than one client is doing similar work.


    As soon as one transaction performs a write, the corresponding cache values are invalidated. Now, if you have concurrent transactions, we provide both optimistic and pessimitic locking to handle conflicting accesses.
    If you have data that is often written, you probably don't want it to be in the cache. In this case, you can add a rule to the cache to tell that this specific set of queries should not be cached.

    Emmanuel
  18. Failure Support not working ?[ Go to top ]

    Hello,

    we tried the new version of C-JDBC. Installed MySQL on two different machines.
    Installed C-JDBC on a 3rd machine. Our application server is JBoss. While surfing our portal, one of our MySQL machine crashed. Now, what happened ? The portal didnt work anymore. It was stuck. Database Connections were not transfered to the working MySQL server. Does C-JDBC already include failure support ?

    Greets,
    Kai.
  19. Failure Support not working ?[ Go to top ]

    Hi Kai,

    > we tried the new version of C-JDBC. Installed MySQL on two different machines.
    > Installed C-JDBC on a 3rd machine. Our application server is JBoss. While surfing our portal, one of our MySQL machine crashed. Now, what happened ? The portal didnt work anymore. It was stuck. Database Connections were not transfered to the working MySQL server. Does C-JDBC already include failure support ?

    C-JDBC failure support with a single controller has been operational for more than one year now. Connections are opened by the controller on both backends, and if a failure occurs, there is a transparent failover on the other backend (the query is executed again on the other backend and your client does not notice at all that a backend failed).
    This is more likely a problem in the configuration file. Could you post your JBoss setting as well as the C-JDBC virtual database configuration file on the c-jdbc at objectweb dot org mailing list, so that we can figure out what happened?

    Emmanuel
  20. So does C-JDBC replace JDO?[ Go to top ]

    C-JDBC could access to several backend databases -- does this mean it replace JDO?
  21. So does C-JDBC replace JDO?[ Go to top ]

    No, C-JDBC does not replace JDO, it just replaces your JDBC driver. You can consider C-JDBC as a smart JDBC driver that provides database clustering without changing your application.
    The programming interface remains JDBC. But there is no problem to use JDO on top of C-JDBC.

    Emmanuel
  22. First of all, I'd like to say that the features of C-JDBC are potentially very useful for Postgres, which happens to be lacking in important features such as replication and point in time recovery. Hence, I think there's value in implementing checkpoints and logs in the controller rather than using what the database has. Databases which have these features most probably do not need C-JDBC as badly as databases that don't anyway.

    As a very heavy user of Postgres, I've been following C-JDBC's progress, and I've got something that I wish to clarify. After reading the docs, it seems to me that writes to the same table cannot be done concurrently, even if they are on different rows. Is this true? If so, is it possible to support this in the future?

    I don't know about other databases but Postgres can handle concurrent updates to the same table in a very robust manner. Performance will be compromised if we only allow 1 write operation per table at the same time.
  23. As a very heavy user of Postgres, I've been following C-JDBC's progress, and I've got something that I wish to clarify. After reading the docs, it seems to me that writes to the same table cannot be done concurrently, even if they are on different rows. Is this true? If so, is it possible to support this in the future?


    You are right, at the moment, C-JDBC sends write on the same table one by one to the database (even if they are on different rows) to be sure that they execute in the same order at all backends.

    > I don't know about other databases but Postgres can handle concurrent updates to the same table in a very robust manner. Performance will be compromised if we only allow 1 write operation per table at the same time.

    The problem we have when we are operating at the middleware level, is that we have to ensure that all backends execute write statements in the same order. This is also one reason why C-JDBC will not scale with write-heavy workloads.
    There is also an issue with JVM scalability since you need a dedicated thread for each backend to execute a write query (with full replication). If you want to allow more parallelism, you will have to multiply the number of threads at each backend. Unfortunately, JVMs do not give a very good throughput with a very large number of threads.
    We did several experiments with the TPC-W benchmark and we obtained quite good results even with the ordering mix (50% read-50 %write) workload. How would your workload compare to this benchmark?

    Emmanuel
  24. You are right, at the moment, C-JDBC sends write on the same table one by one to the database (even if they are on different rows) to be sure that they execute in the same order at all backends.


    > We did several experiments with the TPC-W benchmark and we obtained quite good results even with the ordering mix (50% read-50 %write) workload. How would your workload compare to this benchmark?

    Thanks Emmanuel, for your good reply. We use JBoss with plenty of Entity Bean Caching under Commit Option A plus some customized caching on the application side for even more aggressive caching. As such, we have reduced reads to a point that writing has become our bottleneck. I don't have the figures off-hand, but disabling some of the writes on our application reaped huge performance gains, suggesting that the writes were slowing down the system.

    I've given it some extra thought, and yes, we do need ensure that writes to the same table are carried out in the same order in the backend, no matter how good the database is at handling concurrent writes. This is a design limitation which can only be overcome with methods that will probably add even more overhead. This hasn't been very clear in the documentation, I would suggest adding this limitation to the documentation perhaps?

    Another point I'd like to raise is that using the databases for the recovery log will add a significant load to the databases. This is because database transactions incur a lot of overheads over plain File I/O, especially if you are using INSERT. Is this something that you have observed as well?

    I read in the documentation that a File based recovery log is not yet ready (User Guide Section 5.6). If my observation is correct, then I believe it is important to get this feature ready, as this would impact the scalability of C-JDBC.

    Thanks
    Ryan
  25. I've given it some extra thought, and yes, we do need ensure that writes to the same table are carried out in the same order in the backend, no matter how good the database is at handling concurrent writes. This is a design limitation which can only be overcome with methods that will probably add even more overhead. This hasn't been very clear in the documentation, I would suggest adding this limitation to the documentation perhaps?


    You are right, we will try to explain this in the documentation. If you have any suggestion, I would be happy to insert it in the documentation.
    >
    > Another point I'd like to raise is that using the databases for the recovery log will add a significant load to the databases. This is because database transactions incur a lot of overheads over plain File I/O, especially if you are using INSERT. Is this something that you have observed as well?

    We did not notice a significant overhead since log updates are done aynchronously (we notify the scheduler of write completion first and update the recovery log in parallel). Therefore, it hides the latency of log updates. Usually we use a separate database for the log.
    >
    > I read in the documentation that a File based recovery log is not yet ready (User Guide Section 5.6). If my observation is correct, then I believe it is important to get this feature ready, as this would impact the scalability of C-JDBC.

    We did not implement File based recovery log yet because there are many optimizations that we cannot do compared to database based recovery log. For example, when a transaction rollbacks, the whole transaction is removed from the log so that we don't have to replay it at recovery time. We cannot do this easily with a file (concurrent read/updates on files are not easy). Also recovering a database (reading the log file) while the system is still doing updates (writing the log file) becomes very tricky with a file.
    Have you experienced bottlenecks with the JDBC recovery log of C-JDBC? Any feedback is welcome.

    Emmanuel
  26. We did not notice a significant overhead since log updates are done aynchronously (we notify the scheduler of write completion first and update the recovery log in parallel). Therefore, it hides the latency of log updates. Usually we use a separate database for the log.


    Thanks Emmanuel,

    Yes, using a separate database and writing to the logs asynchronously would explain why you would not notice the overhead. I have yet to try out C-JDBC so I won't be able to say whether I have experience this overhead. I can only extrapolate from past experience :P

    > We did not implement File based recovery log yet because there are many optimizations that we cannot do compared to database based recovery log. For example, when a transaction rollbacks, the whole transaction is removed from the log so that we don't have to replay it at recovery time. We cannot do this easily with a file (concurrent read/updates on files are not easy). Also recovering a database (reading the log file) while the system is still doing updates (writing the log file) becomes very tricky with a file.

    Agreed. There will be some trade-offs involved.

    I will try out C-JDBC soon and I will contact the C-JDBC team if there is anything I can feedback or contribute. Thank you for taking the time to answer my questions.

    Ryan