posted last year in CUBRID Life category by CUBRID
Today I am excited to announce an updated 9.x branch - CUBRID 9.3.0 has been released. CUBRID 9.3 is the version which stabilized and improved CUBRID 9.2.
CUBRID 9.3's DB volume is compatible with 9.2's. CUBRID 9.1 and previous version should migrate database.
There are a number of new features and performance improvements in 9.3.0 that I will describe below.
You can download the latest version of CUBRID 9.3.0 for Windows and Linux from http://www.cubrid.org/?mid=downloads&item=cubrid&os=any&cubrid=9.3.0
The new features of CUBRID 9.3.0 are involved with several database aspects : SQL, HA, Administrative Convenience, Utility and Driver.
SQL New Features
Support SELECT FOR UPDATE Statement
The FOR UPDATE clause can be used in SELECT statements for locking rows returned by the statement for a later UPDATE/DELETE.
SELECT * FROM t1 INNER JOIN t2 ON t1.i=t2.i ORDER BY 1 FOR UPDATE OF t1, t2;
Support Schema Lock
The following two locks for schema are added.
- SCH-S: Schema stability lock. This lock is obtained while query compile is being executed and guarantees that the schema included in the query is not modified by other transactions.
- SCH-M: Schema modification lock. This lock is obtained while DDL (ALTER/CREATE/DROP) is being executed and prevents the modified schema from being accessed by other transactions.
As the schema lock is applied, the deadlock problem which has occurred when multiple DELETEs on the table without indexes were executed is solved.
Support various SHOW syntaxes for checking the internal database information
- Add the
SHOW ACCESS STATUSstatement
The SHOW ACCESS STATUS statement is added to display the login information of the database account. This statement can only be used by a "DBA" account
- Add the
SHOW VOLUME HEADERstatement
The "SHOW VOLUME HEADER" is added to display the specific volume header's information.
- Add the
SHOW HEAP HEADER/CAPICITYstatement
The "SHOW HEAP HEADER" statement displays the header page of the specified heap. The "SHOW HEAP CAPACITY" statement displays the capacity of the specified heap.
- Add the
SHOW INDEX HEADER/CAPACITYstatement
The "SHOW INDEX HEADER" statement displays the header page of the specified index. The "SHOW INDEX CAPACITY" statement displays the capacity of the specified index.
- Add the
SHOW SLOTTED PAGE HEADER/SLOTSstatement
The "SHOW SLOTTED PAGE HEADER OF VOLUME=vol_num AND PAGE=page_num" statement displays the header information of the specified slot page. The "SHOW SLOTTED PAGE SLOTS OF VOLUME=vol_num AND PAGE=page_num" statement displays all slot information of the specified slot page.
Support SHA-1, SHA-2, TO_BASE64, and FROM_BASE64 functions.
- Add SHA-1 and SHA-2 functions
- Add the TO_BASE64 function and the FROM_BASE64 function.
- Support the "IF EXISTS" clause for the DROP SERIAL statement.
- Add a hint, SKIP_UPDATE_NULL to allow a constraint of a certain column from NULL to NOT NULL without data verification.
HA New Features
CUBRID 9.3.0 contains following improvements in HA environment.
CUBRID allows a broker to connect only to replica
- Add a parameter that sets the broker to access only to the replica.
CSQL can do write operations to slave or replica if it has a special option
- Add an option to write on the slave DB or the replica DB to CSQL.
- The statistics information can be updated on the slave node or on the replica node with CSQL.
A utility for shrinking the replicating time is added
Other HA features
- Add a functionality to try to reconnect to another node when replication between the CAS and the DB server is delayed.
- Add a replication latency time to the result of "cubrid statdump".
- Add the logging functionality for the SQL received by the applylogdb process.
- Add the functionality which detects a log flush delay caused by a copylogdb delay and writes it on the database server log file.
Improvement of Administrative Convenience
Internal status information of SHARD proxy is strengthened
The SHARD proxy status information items are added and the number of shard proxy errors can be identified.
A number of options are added or changed in the "cubrid broker status" command.
A command to test whether the query specified in the DB is successfully executed or not, is added
A command to test query execution to the DB and to display whether the execution is successful or has failed has been added. After a query is executed, the transaction is rolled back. If the SHARD is set, query can be executed to all SHARD DBs. With this command, connection status to the DB, user's query permission, and hashing result of the SHARD DB can be checked.
- Add the standalone section to cubrid.conf.
- Add the parameter to define the number of replication-delayed hosts among the hosts defined as db-host in the databases.txt that will try to connect.
- Add the cubrid_replay utility to replay the SQL log in order to reconfirm the query execution time and query plan.
- Add the --check-prev-link option and the --repair-prev-link option to the "cubrid checkdb".
- Add functionalities of displaying the last connection time by the client with each IP registered in the ACL list of the broker and displaying the number of ACL REJECTs.
- Add a broker parameter which enables turning off trigger operation and CSQL option.
- Add the query plan cache information when displaying the "cubrid statdump" command.
- Add the logging functionality about the result of cubrid utility execution.
- [JDBC] Add the functionality to wait without broker connection while a connection object is created.
- [CCI] Add a function that changes the login timeout value and a function that changes the datasource property.
- [CCI] Add cci_bind_param_ex(), which can bind even when '\0' exists in the middle of a string.
Improvement of Performance
SQL Performance Improvement
- Sampling method is adopted to update the statistics information for shorter UPDATE time.
- Improve the performance of INSERT operations.
- Improve the performance of SELECT and SCAN operations by improving page replacement policy of buffer manager.
- Support loose index scan.
- Improve the performance of a query which includes aggregate or analytic functions.
- Improve the performance of a LIKE query.
- Improve the problem that the INSERT operations take a long time after many DELETE operations.
- Improve the performance for the insertion of TIMESTAMP type data.
- Reduce the spent time for generating multiple connections at once.
- Improve the performance of replicating UPDATE statements.
- Reduce the spent time for generating indexes in a standalone mode. (--SA-mode)
- Reduce the spent time for executing many FULL SCAN operations on the same table at the same time.
- Improve the phenomenon for an application with queries to be relatively slower in Windows than in Linux.
Sharding Performance Improvment
- Improve performance when the statement is reused in the SHARD statement pool.
- Reduce the time required to create several connections in the application at once.
- When a query is executed in an application in the Windows environment, the speed is relatively slower than in the Linux environment.
Some of the functionality in CUBRID 9.3.0 have been changed.
- Change the operation when the column in the expression of the SELECT list and the alias of the expression are identical with the name of the column of GROUP BY clause.
- Modify not to create sort merge join execution plan.
- Modify to "reload" only the node information when "cubrid heartbeat reload" command is executed.
- Add the "cubrid heartbeat replication start/stop <node_name>" command.
- [JDBC][CCI] Changed the range of application and the default values of CCI loginTimeout and JDBC connectTimeout.
- [JDBC][CCI] Modify the system so that queries, that are executed without specifying commit or rollback when the server process is restarted during a transaction or when a failover occurs in the HA environment, are treated as errors.
- [JDBC] Modify that the JDBC connection object follows the URL's user or password when the user name and the password are entered as "" in the JDBC connection object.
- Modify not to apply corresponding broker parameters if the values of broker parameters (LONG_QUERY_TIME and LONG_TRANSACTION_TIME) are 0.
- Modify the print information and related parameters of the broker access log (ACCESS_LOG) file.
- The minimum value of log_buffer_size of cubrid.conf is changed from 48KB (3*1page, 16KB=1page) to 2MB (128*1page, 16KB=1page).
- Displyed values are truncated when "-b" or "-f" option is used in "cubrid broker status" command.
- Replace error messages and utilities messages in Vietnamese and Khmer with that in English.
Besides these new features, performance improvements and behavioral changes, there are many bug fixes and minor improvements in resource management. For the full list refer to CUBRID 9.3.0 Release Notes.
If you would like to migrate from previous version to the latest CUBRID 9.3.0, please refer to Upgrading to CUBRID 9.3.0.
If you have any questions, feel free to leave your comment below.