posted 5 months ago in CUBRID Life category by Esen Sagynov
Last month we announced CUBRID 8.4.4, the latest version of 8.4.x branch, with new SQL Profiling feature and numerous stability improvements. Today I am excited to announce an update to the 9.x branch - all new CUBRID 9.2.0.
Most new features and stability improvements of the latest 8.4.4 version have been applied to CUBRID 9.2.0 as well. However, there are several more new features and performance improvements in 9.2.0 that I will describe below.
You can download the latest version of CUBRID 9.2.0 for Windows and Linux from http://www.cubrid.org/?mid=downloads&item=cubrid&os=detect&cubrid=9.2.0.
Prior to 8.4.4 and 9.2.0 users could obtain SQL profiling information to analyze the query performance only in desktop GUI applications like CUBRID Manager or CUBRID Query Browser. Now CUBRID allows to retrieve SQL profiling information directly from drivers. To enable the SQL profiling one can execute
SET TRACE ON before executing the query, then retrieve the query plan and statistics by executing
SHOW TRACE query. See the following example:
csql> SET TRACE ON; csql> SELECT /*+ RECOMPILE */ o.host_year, o.host_nation, o.host_city, n.name, SUM(p.gold), SUM(p.silver), SUM(p.bronze) FROM OLYMPIC o, PARTICIPANT p, NATION n WHERE o.host_year = p.host_year AND p.nation_code = n.code AND p.gold > 10 GROUP BY o.host_nation; csql> SHOW TRACE; trace ====================== ' Query Plan: SORT (group by) NESTED LOOPS (inner join) NESTED LOOPS (inner join) TABLE SCAN (o) INDEX SCAN (p.fk_participant_host_year) (key range: (o.host_year=p.host_year)) INDEX SCAN (n.pk_nation_code) (key range: p.nation_code=n.code) rewritten query: select o.host_year, o.host_nation, o.host_city, n.[name], sum(p.gold), sum(p.silver), sum(p.bronze) from OLYMPIC o, PARTICIPANT p, NATION n where (o.host_year=p.host_year and p.nation_code=n.code and (p.gold> ?:0 )) group by o.host_nation Trace Statistics: SELECT (time: 1, fetch: 1059, ioread: 2) SCAN (table: olympic), (heap time: 0, fetch: 26, ioread: 0, readrows: 25, rows: 25) SCAN (index: participant.fk_participant_host_year), (btree time: 1, fetch: 945, ioread: 2, readkeys: 5, filteredkeys: 5, rows: 916) (lookup time: 0, rows: 38) SCAN (index: nation.pk_nation_code), (btree time: 0, fetch: 76, ioread: 0, readkeys: 38, filteredkeys: 38, rows: 38) (lookup time: 0, rows: 38) GROUPBY (time: 0, sort: true, page: 0, ioread: 0, rows: 5)
New SQL syntax
In CUBRID 9.2.0 we have added new SQL functions and statements.
- Analytic functions
- Aggregate-analytic functions
NULLS LASTsyntax to the
- Support DROP VIEW IF EXISTS statement.
In CUBRID 9.2.0 there are over 140 stability and usability improvements which include the following:
- Administrative Convenience
- One of the major improvement is the SQL profiling which I've already explained above.
- Sort the output of the tranlist command by the specified column.
- Added an additional event log file to record query status information such as SLOW_QUERY, MANY_IOREADS, LOCK_TIMEOUT, DEADLONG, and TEMP_VOLUME_EXPAND which affect the query performance.
- cubrid_master log file now includes each node information in the output of HA status.
- Added a parameter to adjust the maximum number of replication archive logs.
- Added a system parameter to specify transaction rollback when lock escalation occurs.
- Added CONNECT_ORDER broker parameter which allows to specify the order brokers access the DB host when multiple HA/REPLICA DBs are configured.
- Add a generic_vol_prealloc_size parameter that allows to maintain a certain amount of free space in GENERIC volume to prevent the low performance caused by the sudden increase of a GENERIC volume.
- The in-memory sort optimization is added to process the query by saving the records that match the ORDER BY ... LIMIT condition to the sort buffer.
- Query performance improved by applying SORT-LIMIT optimization while executing the ORDER BY ... LIMIT clause in the join query.
- Query plan is rewritten when the proper data volume is exceeded during the re-executuion of prepare for the same query statement.
- Statistical information of only the added index is updated.
- Fix to use an index when a subquery is given in a START WITH clause as a condition in a hierarchical query
As a result of these improvements, the performance of SELECT operations in CUBRID 9.2.0 has improved by 23%.
Figure 1: Result of SELECT Operation of YCSB Benchmark (Master Server).
For more performance and QA results, refer to CUBRID 9.2.0 QA Completion Report.
Some of the functionality in CUBRID 9.2.0 have been changed.
- When a column without a default value is added by using the ALTER ... ADD COLUMN statement, the PRIMARY KEY or NOT NULL constraint was violated as all values of the added columns became NULL. This problem has been fixed.
- The "cubrid shard" command that controls the SHARD is removed; it is now controlled by the "cubrid broker" command.
- SHARD-related parameters are configured in cubrid_broker.conf instead of shard.conf.
- Some SHARD-related parameter names are changed. Refer to Changing SHARD-related parameter names.
- In JDBC code changed the zero date of TIMESTAMP from '0001-01-01 00:00:00' to '1970-01-01 00:00:00'(GST) when the value of zeroDateTimeBehavior in the connection URL is "round".
- In JDBC and CCI drivers query timeout is now applied to the batch processing function not per query when execution multiple queries at once.
- Locale should be specified when creating a database.
- Now the decimal values output by some utilities in Linux and Windows are same.
- Changed the time and capacity related parameter names to allow to set time unit or capacity unit next to the time or capacity parameter value.
- PHRO is removed from the ACCESS_MODE of the broker.
- Among the system parameters that a user without DBA permission can change dynamically, "client" or the "client/server" parameter can now be changed.
Besides these improvements and behavioral changes, there are many bug fixes and minor improvements in resource management. For the full list refer to CUBRID 9.2.0 Release Notes.
If you would like to migrate from previous version to the latest CUBRID 9.2.0, please refer to Upgrading to CUBRID 9.2.0.
If you have any questions, feel free to leave your comment below.