The CUBRID Broker Story

1. The client module first checks whether the query meets the CUBRID SQL syntax before processing it. It has a query parser which cuts the SQL statement into the smallest tokens, thus analyzes the statement.
2. After confirming that there are no syntax errors, the client module checks the statement for semantic errors. For example, it checks:
   • whether a foo table actually exists;
   • whether the current user has a SELECT authority;
   • if there is a column called id in the foo table;
   • if the column id is comparable with the numeric values, etc.
3. The more complicated the query is by join or complex WHERE condition, the more syntax errors are checked. Since it is extremely inefficient to send a request to a server every time schema information is needed, schema information is cached in the client.
4. A lock cache is also needed. If a query such as that in the example is executed, then tasks, such as changing the schema for the table, must not be conducted until SELECT on the foo table is completed. Using the lock can be used so that the table is not modified when the table is being searched. In CUBRID, the IS lock is obtained when the table is being SELECTed. Inserting and selecting data can be made on the table when the IS lock is obtained, but altering the table schema, or modifying through a full scan of the table is not allowed until the IS lock has been released. A IS lock on the table to select must be secured in the client module. However the lock must be cached in the client module since it is inefficient to request a lock to server every time, even worse on performance.
5. In the final stage, it optimizes the query and then creates a plan. It checks whether a usable index exists, decides which join method should be used, and sees if the sorting process can be omitted if there is an ORDER BY clause.
6. Then the client module transfers the optimized query plan to the server so to be executed.

We’ve looked at simplified stages of processing a query. In the stages by a client module, it performs pre-execution processes and then sends a request to the server. The client request is handled in the server. Therefore, the client has many modules in order to process the query. The overall structure of CUBRID is explained in the following diagram.

In the above figure you can also notice that the client module provides a native C-interface for JAVA or PHP APIs which applications can use to communicate with the database. To summarize, the 3-tier architecture allows CUBRID to upload the APIs and move the query processing logic to the client module at the same time unloading the database server to do these tasks. Thus, CUBRID APIs are light, and the CUBRID Server is not busy. I believe it became clear why CUBRID adopts a 3-tier architecture.

For a better comprehension, below I am going to compare how the same process would work in a 2-tier architecture which is used in most other relational database systems.

1. One way to process the query is to enable the client module to be called directly by JAVA or PHP applications. One of the biggest problems in this approach is that the application becomes too heavy because of the JDBC. The application consumes too much resources (CPU, Memory, etc.) because the client module performs many stages for query processing.
2. The second method is to separate the architecture that the client module only receives a request from an application and the server performs all of query processes. This is an extremely complicated task since the existing architecture should be re-designed. No matter how difficult the task is, we have to ask a question as “How beneficial will a 2-tier architecture be?” The CUBRID Development Lab came to the conclusion that there is nothing to gain from the modifications. The DBMS server processes most actions that the DBMS is required to do, such as database management, buffer management to change data saved on disks to memory structures, concurrency management, failure restoration management, and this requires a lot of system resources. In most cases, the server's load causes a performance bottleneck therefore to share the server’s loads with the client module assures system resources availability for server processing and maximizes server’s capacity. For this reason, it is beneficial to have the 3-tier architecture.

As we’ve looked through two methods so far, 2-tier architecture is not suitable, therefore a 3-tier structure can be considered as an alternative. Now leave the DBMS server and client module structure as they are, and add a middle layer that can process JAVA and PHP requests. Then application driver conducts relatively simple tasks, and the requests can be handled in the middle layer and in the DBMS server. In CUBRID, this middle layer between a driver and the DBMS server is called, CUBRID BROKER.

CUBRID BROKER

BROKER is not a single process but two processes, which are cub_broker and cub_cas.

The cub_casis a process which handles the query request by parsing and query planning. In other words, it is a client of the DBMS server. An active connection to process a request, among many other connections requested from the driver, corresponds to a single cub_cas.

The other process, cub_broker decides which cub_cas to allocate when the driver requests a new connection, and manages the cub_cas processs accordingly. A single cub_broker process manages multiple cub_cas processes. One cub_broker and an “N” number of cub_cas processes consist of a single BROKER group. Multiple groups can be operated depending on the user setting.

The diagram illustrated in the Figure 1 above shows how processes are interacted when the application requests a connection.

1. When the app sends a connection request, the request is sent to cub_broker.
2. Then the cub_brokerselects available cub_casand sends the application’s connection to cub_cas, thus connecting the driver and the cub_cas.
3. The cub_casaccesses the cub_masterto connect the DB server and requests a connection to the cub_server. A connection between cub_cas and cub_server is created once the cub_master passes the connection from the cub_cas to the cub_server (like the application connection is passed to cub_cas by cub_broker). Thisconnection may last even when the driver is disconnected.

Now we have seen what the BROKER is and how it works in the query processing. Now let’s take a look at the pros and cons of the 3-tier structure.

The Cons of the BROKER

Complexity

One of the first shortcomings that comes to mind when you think of the BROKER is the architecture looks complicated. It gives to users many difficulties to understand additional processes in the server and broker to handle the query.

The database server and the BROKER operate as a separate component. To the DBMS server, the cub_cas is a single client. To the BROKER, the DBMS server is a target component to connect based on the driver’s request. This relationship may seem hard to understand for those who are unfamiliar with CUBRID. For example, let’s say that the number of connections has been configured in the application but more connections are necessary at this moment. In CUBRID, the user should change the settings in both the BROKER and the DB server respectively. BROKER setting has to be changed to increase the number of cub_cas which can receive the maximum number of requests from the application. The cub_server setting must also be modified to increase the number of cub_server which can receive the maximum number of requests from the cub_cas. It may be confusing as to why two settings have to be modified when the number of connections increases. This is because, as we mentioned above, the BROKER and database server both have different roles. However, we may make changes to this in the future. On the other hand, this separate component enables to have the flexibility and scalability of the configuration.

For this reason, CUBRID has two configuration files; cubrid.conf for the DB server and client library and cubrid_broker.conffor the BROKER.

Performance

Another shortcoming the BROKER has is that its performance decreases due to the increase of hops. The decline in performance may seem natural since the number of processing stages increases. Performance quality can be assessed with response time and throughput. If the process stages increases, the response time naturally increases. The response time when processing a single query can be within a difference of mss, but the gap can widen greatly if the number of repetitions increase.

The increase in the number of communications is the most significant reason behind the increase in response time.

However, unlike a single client environment, in a multi-client environment the increase of response time due to the increase in communications cannot make a big difference. In environments where the server load becomes extremely high, the database server and client module can be separated, which will increase the amount of resource the server can use. The increase in server’s resources means the increase of server’s throughput. Consequently, having an additional layer, BROKER, is not something that you should be concerned about, because it doesn’t have a grave influence on throughput, and can result in better performance. If the performance seriously matters, then you can use connection pooling and statement pooling because it decreases the number of request sent from the driver to the server.

Failure points

The third shortfall is that the failure points increase because of the existing middle layer (BROKER). Fortunately CUBRID has prepared measures for such situations. CUBRID supports HA (High Availability) feature on the DB server, as well as on the BROKER. By providing the connection URL specifying the active and standby BROKER’s IP and port, the failure on the BROKER can be managed by failover to a standby BROKER so as to keep the service alive.

The Pros of the BROKER

Now let’s take a look at the merits of the BROKER.

Sharing resources

One merit provided by the BROKER is that it can share the cub_caswhich handles connections. As was mentioned above, the connection, which handles the actual query, is connected one to one to the cub_cas. In other words, there must be as many cub_cas processes active as concurrent connections.

What if the application creates two connections but there is only one cub_cas?

For convenience sake, let’s call the two connections C1 and C2. C1 is connected to the cub_cas and is processing queries. If the C2 sends a connection request, the cub_broker looks for a cub_cas that can be used. The cub_broker finds one cub_cas process, which is currently processing C1. In such a case, the cub_cas which is currently in service breaks off the connection with C1 and goes to process C2 only when C1 disconnects, or the time between the end of the current transaction and before a new transaction begins. If two cub_cass are active for C1 and C2, then the connection will not be terminated and the queries for C1 and C2 will be processed smoothly.

However, if there is only one cub_cas, C1 and C2 must share a cub_cas and the connection with cub_cas will be terminated every time C1 or C2’s transaction is over. The driver is aware that their connection can be terminated after the transaction, due to the situation before, and will try to reconnect. When the server restarts in the connection pooling environment, connections are restored just as the situation mentioned above, and the application does not have to examine the validity of the connections. The following diagram shows how the processing is done when two connections share a single cub_cas.

However, frequently used connections have to compete with a single cub_cas, which can cause problems in the performance. So the BROKER parameter value should be configured so that the adequate number of cub_cas are running. However, if a majority of the connections are in idle state in the pool, then sharing the connection is more rational. This will be more discussed in the CAS for Oracle/MySQL later on.

Logging and monitoring

The BROKER's second strength is a convenient monitoring and logging features. Since all queries requested by applications, such as JDBC, pass through the BROKER, a part of the processing can be identified through the BROKER’s status. The status can be identified through utilities that monitor the BROKER status. The status information, shown per cub_cas, has the following meanings:

• IDLE – cub_cas is not connected to the driver.
• CLIENT WAIT – in the middle of transaction, the 1st request is processed and is waiting for the 2nd request from the application. For example, a transaction consists of two queries, INSERT and UPDATE and commit. The CLIENT WAIT state is activated after the INSERT execution is completed and before the UPDATE query is requested. If this status continues, then the application is probably not sending the 2nd request.
• CLOSE WAIT – after a transaction in ended, the application has not requested the following query. A new connection request can be handled in cub_cas CLOSE WAIT state only if an IDLE cub_cas is not available.
• BUSY – a query execution request has been sent to the database server. The query statement that is being executed can be checked with the monitoring utility.

Information such as the QPS, the number of errors, the number of slow queries that is being processed in the BROKER is also monitored. Besides monitoring, the information being processed in the BROKER is also recorded in log files, which provides information on connection as well as query executions.

The following shows how a query log is recorded when a simple JDBC application is executed. The log provides information on when the query was executed, what values were bound, execution time, number of tuples, etc.

String connection_url = "jdbc:cubrid:localhost:53300:testdb:public::?";
Connection con =   DriverManager.getConnection(connection_url, null,   null);
String query = "select   * from foo where id = ?";
  
PreparedStatement pstmt = con.prepareStatement(query);
  
pstmt.setInt(1, 1);
  
ResultSet rs = pstmt.executeQuery();
  
while (rs.next())
{
    // fetch result
}
  
rs.close();
pstmt.close();
con.close();