CUBRID Database Management System

This is the second article on SSD and the performance implications of switching to SSD. In the first article, Will the use of SSD increase the speed of DBMS?, we have reviewed how much performance gain we would see if we switched from HDD to SSD for ... [More] an DBMS like CUBRID, and what factors would affect the performance. In this article, I will compare the SSD structure to that of HDD and discuss how software architecture is changing. Overview Last year in July Amazon released a new cloud product - High I/O Quadruple Extra Large. This product uses 2 TB Solid-State Drive (SSD). So now even cloud products have started using SSD. That means the SSD price is becoming low enough to be adopted by cloud products. The operational know-how for cloud services is now established. Then, can we assume that this is the era for using SSDs? A Solid-State Drive uses NAND flash memory and its operation is different from the operation of a Hard Disk Drive. To develop a storage system, including a database, we needed to fully understand the HDD structure for higher performance. Do we also need to fully understand the SSD structure for greater performance? The answer is Yes. As SSDs become popular, software architecture is changing. The architecture designed according to the HDD characteristics is being redesigned considering the SSD characteristics. Introduction to the Experiences of EC2 Users Below I will use some of the reviews posted on Amazon High IO Instance related High Scalability blog. Case #1: Conversocial Conversocial, the social CRM firm, was using MongoDB when the High IO product was released, requiring MongoDB to run on High IO. Compared to the previous HDD, the average response time was reduced by 43% (from 392 ms to 274 ms), the large data for random IO was reduced by 74% (from 877 ms to 504 ms), and the iowait was reduced from 90% to 3%. Case #2: Netflix Netflix, the DVD rental site, has posted a long article related to SSD as well. In the previous configuration, the performance was 100K IOPS or 1 GB/sec. As hi1.4xlarge was used, the response to the average read request was reduced from 10 ms to 2.2 ms and the 99% request response time was reduced from 65 ms to 10 ms. Case #3: MySQL on EC2 Though SSD adoption does not guarantee enhanced performance all the time, on the High Scalability page we can also see the following statement: According to the benchmark, now we can choose EC2 to run MySQL on. Implications If you use HDDs, you should prepare the memory based on the working set. If the size exceeds, increase the number of equipments for horizontal partitioning. On the other hand, SSDs can help to reduce the required memory and can delay introduction of horizontal partitioning. For example, Netflix has removed the memcached layer for 48 IO instances. Then it replaced the layer with 15 instances where no cache was involved. Finally, the DBMS did not need to use additional cache to reduce IO time. Combined with the flexibility of EC2, SSDs can cut costs. For example, for a large capacity index of the genome mapping, the index operation can be performed intensively for a few hours per week. For the rest of the time, there is no need to perform the computation. Without EC2, you must purchase an expensive equipment, use it periodically, and leave it unused for most of the time. With EC2 you can save on the equipment, and pay for only what you use. While the popularization of SSD cuts costs as well as enhances the performance, the cost-cutting effect can be further maximized when SSDs are combined with cloud products. Characteristic Comparisons between SSD and HDD IO An SSD is a non-volatile memory, running in an electronic manner. Compared to HDDs, SSDs provide lower seek times, fewer mechanical delays, and lower fault rates. Therefore, they provide a very fast random read. However, an overhead occurs in random write. The reason is that SSDs cannot overwrite data while writing data. An SSD is divided into many layers in the order of block, page, row, and cell. Data can be written by changing some cells to 0 while all cells in the block have been initialized to 1. To raise 0 to 1, high voltage is required. However, high voltage affects the neighboring cells (write amplification). Therefore, writing data requires two stages of erase and program. Figure 1: SSD Cell Structure (source). An SSD writes by page and the number of writes on the page is limited (write endurance). If the endurance is exceeded, the block fails. To solve this problem, SSD manufacturers use the following methods on their firmware: Wear Leveling Uses blocks equally for lifetime extension. Counts the number of writes per block, and selects and uses the blocks with the fewest counts. Write Gathering If there is an erased empty block, gathers the blocks in use, in order to make the blocks empty. In addition, metadata is updated whenever data is written. As write positions are scattered, metadata update times become longer. Sometimes, the time is almost the same as HDD seek time. Therefore, to reduce write overhead, several write requests are gathered and executed at once. Garbage Collection The write unit is page and the erase unit is block. Among blocks, blocks that have the largest number of unused pages are initialized (erased). It is a type of defragmentation method; first, back up valid pages in a block and then initialize the block to an empty block, then push the valid pages backed up on another block into the empty block. Firmware runs differently by manufacturer. This is the competitive element of an SSD. Manufacturers keep their secrets and provide products by level. There are two levels, the low-end Multi Level Cell (MLC) and the high-end Single Level Cell (SLC). The two show significant differences in reliability and speed. When comparing the performance of an SSD to that of HDD, an SSD is generally 120,000 random reads and 10,000~85,000 random writes, based on IOPS. However, an HDD with 15,000 RPM generally shows performance of 175~210 IOPS. As a result, SSD shows 50~600 times of high performance, compared to an HDD. An SSD has no seek time, so it provides faster IO than an HDD. However, there is overhead in random write, as we have discussed earlier. The write optimization method of each manufacturer cannot be known. So, the exact IO pattern can be determined by testing each product. However, forecasting the write optimization method seems possible to a certain level. The next chapter will show how to optimize IO under these SSD constraints. Trials to Optimize IO on SSD An HDD is significantly slower than the memory. The performance of 10,000 RPM HDD is different from the performance of DDR3-2500 by approximately 800 times. Since HDDs have been used, many methods have been created to improve storage system performance. For example, when the OS and the device driver receive IO requests, they schedule the requests to minimize seek time. When an SSD is used, they do not schedule requests because SSD firmware performs that scheduling. Systems like a DBMS effectively implement the buffer (cache) to minimize IO on HDDs. As a result, when the hit rate is high, replacing an HDD to an SSD may improve the performance but slightly. However, with SSD, there is no need to keep the buffer size large, you may not need to retain a large memory capacity. In addition, a variety of methods are used to write data compactly, minimizing movement of the HDD head. However, these methods are not necessary since the random read of SSD is fast. The operation methods of SSD firmware are not open. So, it is difficult to model the IO pattern at the OS or device driver. However, at the application level, the architecture is being changed considering that the performance of random read and sequential write of SSD is great but random write performance is poor. Let's see how the architecture is changed. Log Structured File System As the memory capacity of a server increases, random read occurs less often than before due to memory cache. Therefore, random write becomes an important performance issue. An HDD performs an in-place write. If the random IO becomes larger, the head must move, causing an increase of seek time and degradation of performance. To compensate for that, there is a way to log the write history only and perform data write occasionally. This method is called journal file system or logging file system. Furthermore, you can avoid the in-place write by writing the data, instead of the history, whenever logging is performed. This is called log-structured file system (LFS). When writing data, the LFS reads the previous version to create the latest version and appends the latest version to the end of a file.   Figure 2: Architecture of Log Structured File System (source). At this time, the previous version is marked as an empty space. The version is always appended at the end of the file whenever it is required, only the sequential write is needed, so you can obtain the good performance at the early stage. However, as empty space increases, data is fragmented and an overhead occurs to gather the fragmented data. LFS writes a file with the serial chunk unit, called segment. In order to reduce the overhead of metadata updates, which occurs per write, several writes are collected and written at once. Since most of segments are partially written, a task (GC) to create an empty segment is performed. It is similar to the efficient write of an SSD where data is gathered and written on sequential spaces with defragmentation for creating an empty space.   If an SSD does not perform wear leveling and write gathering, the file system will be more effective. The Journaling Flash File System (JFFS2), which is frequently used for embedded devices, is an example of the file system. On the performance side, in some cases, LFS can reduce disk usage by 40% compared to general file systems. However, LFS uses most of its time gathering segments. So, performance is lowered because it must process the segments at a certain period. B-Tree Performance and Copy on-write B-Tree B-Tree is a data architecture frequently used by file systems and databases (eg. CUBRID). Will the B-Tree work well on an SSD? The following graph shows the performance result calculated by measuring the B-Tree insert on the X25M SSD with three workloads.  Figure 3: Performance of B-Tree Insert on SSD (source). The red color is the random write at 4 K size, the green color is the sequential write at 4 K size, and the blue color is the random write at 512 K size. At 4 K size, it seems natural that the sequential write shows the highest performance. An interesting thing is that the write performance is significantly lowered at 512 K size when the device capacity (160 G) is exceeded. First, performance is degraded when device capacity is full with the write volume. Why? The reason is that most SSDs have a log structure due to wear leveling and error correction. Originally, a block is 512 K; however, the latest MLC devices allow erasing data with the larger size. Finally, write can erase hundreds of MB with device firmware execution. Anyway, an SSD shows better performance of the sequential write than the random write. Is there a B-Tree that has a method allowing sequential write? Copy on-write (CoW) copies the path of B-Tree. It copies the traversed nodes whenever the nodes of the tree are changed, and then changes the nodes to create a new tree. See the following two sides: Traverses the tree to find a key Rewrites the path In the case of (1) for the CoW, the random IO (random write) occurs (of course, IO may not occur when cache is used); however, case (2) is completely the sequential write. Because the existing nodes are immutable, only the sequential write occurs (append only). This method has already been used in file systems such as ZFS, WAFL, and Btrfs.  Figure 4: CoW B-Tree Structure (source). Then, does the CoW B-Tree work well with the log-structured storage structure of an SSD? Basically, it works well as it is 'append only'. However, there are two issues: space blowup and garbage collection.   CoW B-tree potentially creates a large space blowup. If a 16-byte key/value should be saved in an index with a depth of 3 and the block size is 256 K, data write should perform as much as 256*3 K (768 K). Compared to the small change, larger data should be written. In addition, as the size of space blowup becomes larger, GC should perform more tasks. Since GC runs when there are few IO, performance may be degraded. Fractal Tree At B-Tree, sequential insert is performed quickly because it has optimum locality by changing only specific pages. However, random insert causes high entropy. At the early stage, most pages are loaded on the memory. As time goes by, the possibility that some pages to be accessed in seeking are on the disk (aging) becomes higher. Especially, when terminal pages are scattered and saved on the disk, performance worsens. In the DAM model, IO is performed in the unit of block size between memory and storage. Generally, IO is performed by seeking the optimum block size for tuning. However, B-Tree generally uses one key but loads all blocks on the disk, consuming a lot of IO bandwidth. To optimize IO, the Cache Oblivious model is sometimes used. For this model, the IO size should be decided based on the algorithm applied because it is impossible to estimate optimum block size. In addition, as with LFS, B-Tree requires append to reduce random IO. Fractal Tree is based on them. First, data is inserted as several arrays which are increased exponentially. When insert is performed, the entry is saved in the smallest array. Arrays where data is inserted are merged into a larger array; at this time, the arrays are sorted in the order of keys. This is called Doubling Array. Figure 5: Data Insert Process in Fractal Tree (source). This array performs sequential write whenever the array grows. In this structure, insert is performed quickly. However, if the key should be navigated, binary navigation is performed so it performs more slowly than B-Tree. In order to improve this, the forward pointer is made and the tree is configured with levels. This is called Fractal Cascading. Figure 6: Fractal Cascading. This method is used by TokuDB, one of the MySQL storage engines. TokuDB is storage for write-intensive workloads. Stratified B-Tree Weak points of the CoW B-Tree are space blowup and performance degradation caused by GC. The Stratified B-Tree makes up for the weak points. It is one of the versioned dictionaries. It follows the Cache Oblivious model similar to Fractal Tree. It is a hierarchical tree, which runs by using Doubling Array and has the forward pointer. The difference is that it has the version by key. First, when a key is changed, the existing key version is maintained with the entry {key, version, value_or_pointer}. When Insert comes in, the key value is saved in the memory buffer, arranged, and written in the array with the lowest level when flushed. After that, as the arrays grow, the key versions of arrays with the same level may be duplicated. In this case, the arrays are promoted and then demoted by disjointing them to avoid version duplication. The reason for demoting arrays is a concept, called density. Density is the density degree of a live key in an array based on version v. When the density is low, unnecessary keys should be searched when range query is performed based on version v. On the contrary, when density is high, most keys are under the corresponding condition. However, a great deal of space blowup is made because data should be duplicated to increase the density. Finally, Stratified B-Tree promotes arrays to prevent each array version from being different, and then demotes the arrays with high density in order to improve the range query performance. This tree provides slow point query performance, but is good for analysis query and range query for big data. In addition, Acunu is a storage platform implemented with this method. It is used for NoSQL including Cassandra. Changes in Software Architecture Caused by SSD So far, I have introduced how the software architecture of storage systems is being changed in the era of SSDs. When SSDs are generalized, products leading the marketplace will be software with architecture suitable for SSDs. No one knows whether HDD era winners will hold dominant positions in the SSD era or if a new hero will appear. Successful methods for existing HDDs are not suitable for SSDs. Therefore, it is very interesting to imagine the changes we will meet in this ongoing evolution. By Hyejeong Lee, Senior Software Engineer at Storage System Development Team, NHN Corporation. My other posts: NoSQL Benchmarking Availability and Operational Stability of NoSQL [Less]

This is the fifth article in the series of "Become a Java GC Expert". In the first issue Understanding Java Garbage Collection we have learned about the processes for different GC algorithms, about how GC works, what Young and Old Generation is, what ... [More] you should know about the 5 types of GC in the new JDK 7, and what the performance implications are for each of these GC types. In the second article How to Monitor Java Garbage Collection we have explained how JVM actually runs the Garbage Collection in the real time, how we can monitor GC, and which tools we can use to make this process faster and more effective. In the third article How to Tune Java Garbage Collection we have shown some of the best options based on real cases as our examples that you can use for GC tuning. Also we have explained how to minimize the number of objects passed to Old Area, decreasing Full GC time, as well as how to set GC type and the memory size. In the fourth article MaxClients in Apache and its effect on Tomcat during Full GC we have explained the importance of MaxClients parameter in Apache that significantly affects the overall system performance when GC occurs. In this fifth article I will explain about the principles of Java application performance tuning. Specificaly, I will explain what is required in order to tune the performance of Java application, the steps you need to perform to identify whether your application needs tuning. I will also explain the problems you may encounter during performance tuning. The article will be finalized with the recommendations you need to follow to make better decisions when tuning Java applications. Overview Not every application requires tuning. If an application performs as well as expected, you don't need to exert additional efforts to enhance its performance. However, it would be difficult to expect an application would reach its target performance as soon as it finishes debugging. This is when tuning is required. Regardless of the implementation language, tuning an application requires high expertise and concentration. Also, you may not use the same method for tuning a certain application to tune another application. This is because each application has its unique action and a different type of resource usage. For this reason, tuning an application requires more basic knowledge compared to the knowledge required to write an application. For example, you need knowledge on virtual machines, operating systems and computer architectures. When you focus on an application domain based on such knowledge, you can successfully tune an application. Sometimes Java application tuning requires only changing JVM options, such as Garbage Collector, but sometimes it requires changing the application source code. Whichever method you choose, you need to monitor the process of executing the Java application first. For this reason, the issues this article will deal with are as follows: How can I monitor a Java application? What JVM options should I give? How can I know if modifying source codes is required or not? Knowledge Required to Tune the Performance of Java Applications Java applications operate inside Java Virtual Machine (JVM). Therefore, to tune a Java application, you need to understand the JVM operation process. I have previously blogged about Understanding JVM Internals where you can find great insights about JVM. The knowledge regarding the process of the operation of JVM in this article mainly refers to the knowledge of Garbage Collection (GC) and Hotspot. Although you may not be able to tune the performance of all kinds of Java applications only with the knowledge on GC or Hotspot, these two factors influence the performance of Java applications in most cases. It is noted that from the perspective of an operating system JVM is also an application process. To make an environment in which a JVM can operate well, you should understand how an OS allocates resources to processes. This means, to tune the performance of Java applications, you should have an understanding of OS or hardware as well as JVM itself. Another aspect is that knowledge of Java language domain is also important. It is also important to understand lock or concurrency and to be familiar with class loading or object creation. When you carry out Java application performance tuning, you should approach it by integrating all this knowledge. The Process of Java Application Performance Tuning  Figure 1 shows a flow chart from the book co-authored by Charlie Hunt and Binu John. This chart shows the process of Java application performance tuning.  Figure 1: The Process of Tuning the Performance of Java Applications. The above process is not a one-time process. You may need to repeat it until the tuning is completed. This also applies to determining an expected performance value. In the process of tuning, sometimes you should lower the expected performance value, and sometimes raise it. JVM distribution model A JVM distribution model is related with making a decision on whether to operate Java applications on a single JVM or to operate them on multiple JVMs. You can decide it according to its availability, responsiveness and maintainability. When operating JVM on multiple servers, you can also decide whether to run multiple JVMs on a single server or to run a single JVM per server. For example, for each server, you can decide whether to run a single JVM using a heap of 8 GB, or to use four JVMs each using a heap of 2 GB. Of course, you can decide the number of JVMs running on a single server depending on the number of cores and the characteristics of the application. When comparing the two settings in terms of responsiveness, it might be more advantageous to use a heap of 2 GB rather than 8 GB for the same application, for it takes shorter to perform a full garbage collection when using a heap of 2 GB. If you use a heap of 8 GB, however, you can reduce the frequency of full GCs. You can also improve responsiveness by increasing the hit rate if the application uses internal cache. Therefore, you can choose a suitable distribution model by taking into account the characteristics of the application and the method to overcome the disadvantage of the model you chose for some advantages. JVM architecture Selecting a JVM means whether to use a 32-bit JVM or a 64-bit JVM. Under the same conditions, you had better choose a 32-bit JVM. This is because a 32-bit JVM performs better than a 64-bit JVM. However, the maximum logical heap size of a 32-bit JVM is 4 GB. (However, actual allocatable size for both 32-bit OS and 64-bit OS is 2-3 GB.) It is appropriate to use a 64-bit JVM when a heap size larger than this is required. Table 1: Performance Comparison (source). BenchmarkTime (sec)Factor C++ Opt 23 1.0x C++ Dbg 197 8.6x Java 64-bit 134 5.8x Java 32-bit 290 12.6x Java 32-bit GC* 106 4.6x Java 32-bit SPEC GC* 89 3.7x Scala 82 3.6x Scala low-level* 67 2.9x Scala low-level GC* 58 2.5x Go 6g 161 7.0x Go Pro* 126 5.5x The next step is to run the application and to measure its performance. This process includes tuning GC, changing OS settings and modifying codes. For these tasks, you can use a system monitoring tool or a profiling tool. It should be noted that tuning for responsiveness and tuning for throughput could be different approaches. Responsiveness will be reduced if stop-the-world occurs from time to time, for example, for a full garbage collection despite a large amount of throughput per unit time. You also need to consider that a trade-off could occur. Such trade-off could occur not only between responsiveness and throughput. You may need to use more CPU resources to reduce memory usage or put up with reduction in responsiveness or throughput. As opposite cases could likewise occur, you need to approach it according to the priority. The flow chart of Figure 1 above shows the performance tuning approach for almost all kinds of Java applications, including Swing applications. However, this chart is somewhat unsuitable for writing a server application for Internet service as our company NHN does. The flow chart in Figure 2 below is a simpler procedure designed based on Figure 1 to be more suitable for NHN. Figure 2: A Recommended Procedure for Tuning NHN's Java Applications. Select JVM in the above flow chart means using a 32-bit JVM as much as possible except when you need to use a 64-bit JVM to maintain cache of several GB. Now, based on the flow chart in Figure 2, you will learn about things to do to execute each of the steps.q JVM Options I will explain how to specify suitable JVM options mainly for a web application server. Despite not being applied to every case, the best GC algorithm, especially for web server applications, is the Concurrent Mark Sweep GC. This is because what matters is low latency. Of course, when using the Concurrent Mark Sweep, sometimes a very long stop-the-world phenomenon could take place due to fractions. Nevertheless, this problem is likely to be resolved by adjusting the new area size or the fraction ratio. Specifying the new area size is as important as specifying the entire heap size. You had better specify the ratio of the new area size to the entire heap size by using –XX:NewRatio or specify the desired new area size by using the –XX:NewSize option. Specifying a new area size is important because most objects cannot survive long. In web applications, most objects, except cache data, are generated when HttpResponse to HttpRequest is created. This time hardly exceeds a second. This means the life of objects does not exceed a second, either. If the new area size is not large, it should be moved to the old area to make space for newly created objects. The cost for GC for the old area is much bigger than that for the new area; therefore, it is good to set the size of the new area sufficiently. If the new area size exceeds a certain level, however, responsiveness will be reduced. This is because the garbage collection for the new area is basically to copy data from one survivor area to another survivor area. Also, the stop-the-world phenomenon will occur even when performing GC for the new area as well as the old area. If the new area becomes bigger, the survivor area size will increase, and thus the size of the data to copy will increase as well. Given such characteristics, it is good to set a suitable new area size by referring to the NewRatio of HotSpot JVM by OS. Table 2: NewRatio by OS and option. OS and optionDefault -XX:NewRatio Sparc -server 2 Sparc -client 8 x86 -server 8 x86 -client 12 If the NewRatio is specified, 1/(NewRatio +1) of the entire heap size becomes the new area size. You will find the NewRatio of Sparc -server is very small. This is because the Sparc system was used for more high-end use than x86 when default values were specified. Now it is common to use the x86 server and its performance has also been improved. Thus it is better to specify 2 or 3, which is the value similar to that of the Sparc -server. You can also specify NewSize and MaxNewSize instead of NewRatio. The new area is created as much as the value specified for NewSize and the size increments as much as the value specified for MaxNewSize. The Eden or Survivor area also increases according to the (specified or default) ratio. As you specify the same size for -Xs and -Xmx, it is a very good choice to specify the same size for MaxSize and MaxNewSize. If you have specified both NewRatio and NewSize, you should use the bigger one. Therefore, when a heap has been created, you can express the initial New area size as follows: min(MaxNewSize, max(NewSize, heap/(NewRatio+1))) However, it is impossible to determine the appropriate entire heap size and New area size in a single attempt. Based on my experience running Web server applications at NHN, I recommend to run Java applications with the following JVM options. After monitoring the performance of the application with these options, you can use a more suitable GC algorithm or options. Table 3: Recommended JVM options. TypeOption Operation mode -sever Entire heap size Specify the same value for -Xms and -Xmx. New area size -XX:NewRatio: value of 2 to 4 -XX:NewSize=? –XX:MaxNewSize=?. Also good to specify NewSize instead of NewRatio. Perm size -XX:PermSize=256 m -XX:MaxPermSize=256 m. Specify the value to an extent not to cause any trouble in the operation because it does not affect the performance. GC log -Xloggc:$CATALINA_BASE/logs/gc.log -XX:+PrintGCDetails -XX:+PrintGCDateStamps. Leaving a GC log does not particularly affect the performance of Java applications. You are recommended to leave a GC log as much as possible. GC algorithm -XX:+UseParNewGC -XX:+CMSParallelRemarkEnabled -XX:+UseConcMarkSweepGC -XX:CMSInitiatingOccupancyFraction=75.This is only a generally recommendable configuration. Other choices could be better depending on the characteristics of the application. Creating a heap dump when an OOM error occurs -XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=$CATALINA_BASE/logs Actions after an OOM occurs -XX:OnOutOfMemoryError=$CATALINA_HOME/bin/stop.sh or -XX:OnOutOfMemoryError=$CATALINA_HOME/bin/restart.sh. After leaving a heap dump, take a proper operation according to a management policy. Measuring the Performance of Applications The information to acquire to grasp the performance of an application is as follows: TPS (OPS): The information required to understand the performance of an application conceptually.  Request Per Second (RPS): Strictly speaking, RPS is different from responsiveness, but you can understand it as responsiveness. Through RPS, you can check the time it takes for the user to see the result.  RPS Standard Deviation: It is necessary to induce even RPS if possible. If a deviation occurs, you need to check GC tuning or interworking systems.   To obtain a more accurate performance result, you should measure it after warming up the application sufficiently. This is because byte code is expected to be compiled by HotSpot JIT. In general, you can measure actual performance values after applying load to a certain feature for at least 10 minutes by using nGrinder load testing tool. Tuning in Earnest You don't need to tune the performance of an application if the result of the execution of nGrinder meets the expectation. If the performance does not meet the expectation, you need to carry out tuning to resolve problems. Now you will see the approach by case. In the event the Stop-the-World takes long Long stop-the-world time could result from inappropriate GC options or incorrect implementation. You can decide the cause according to the result of a profiler or a heap dump. This means you can judge the cause after checking the type and number of objects of a heap. If you find many unnecessary objects, you had better modify source codes. If you find no particular problem in the process of creating objects, you had better simply change GC options. To adjust GC options appropriately, you need to have GC log secured for a sufficient period of time. You need to understand in which situation the stop-the-world takes a long time. For more information on the selection of appropriate GC options, read my colleague's blog about How to Monitor Java Garbage Collection. In the event CPU usage rate is low When blocking time occurs, both TPS and CPU usage rate will decrease. This might result from the problem of interworking systems or concurrency. To analyze this, you can use an analysis on the result of thread dump or a profiler. For more information on thread dump analysis, read How to Analyze Java Thread Dumps. You can conduct a very accurate lock analysis by using a commercial profiler. In most cases, however, you can obtain a satisfactory result with only the CPU analyzer in jvisualvm. In the event CPU usage rate is high If TPS is low but CPU usage rate is high, this is likely to result from inefficient implementation. In this case, you should find out the location of bottlenecks by using a profiler. You can analyze this by using jvisuavm, TPTP of Eclipse or JProbe. Approach for Tuning You are advised to use the following approach to tune applications. First, you should check whether performance tuning is necessary. The process of performance measuring is not easy work. You are also not guaranteed to obtain a satisfactory result all the time. Therefore, if the application already meets its target performance, you don't need to invest additionally in performance. The problem lies in only a single place. All you have to do is to fix it. The Pareto principle applies to performance tuning as well. This does not mean to emphasize that the low performance of a certain feature results necessarily from a single problem. Rather, this emphasizes that we should focus on one factor that has the biggest influence on the performance when approaching performance tuning. Thus, you could handle another problem after fixing the most important one. You are advised to try to fix just one problem at a time. You should consider the balloon effect. You should decide what to give up to get something. You can improve responsiveness by applying cache but if the cache size increases, the time it takes to carry out a full GC will increase as well. In general, if you want a small amount of memory usage, throughput or responsiveness could be deteriorated. Thus, you need to consider what is most important and what is less important. So far, you have read the method for Java application performance tuning. To introduce a concrete procedure for performance measurement, I had to omit some details. Nevertheless, I think this could satisfy most of the cases for tuning Java web server applications. Good luck with performance tuning! By Se Hoon Park, Senior Software Engineer at Web Platform Development Lab, NHN Corporation. [Less]

We are very pleased to announce django_cubrid, an official Django backend for CUBRID Database. Now Python users who create apps based on Django Web Framework can use CUBRID as a backend database server for their apps. Requirements Being a Python ... [More] Web Framework, Django requires Python version 2.5 to 2.7. Django is not yet compatible with Python version 3.0. django_cubrid has been tested with Django 1.4, the latest official version. Since django_cubrid is embedded into the CUBRID Python driver, it requires the latest stable release of the driver (CUBRID-Python 8.4.3). Installation django_cubrid is embedded into the CUBRID Python driver which means all you need to do is install the driver via pip, easy_install, or from its source, and you will have django_cubrid installed. pip install CUBRID-Python For other ways of installation, refer to CUBRID Python Driver Installation Instructions. Configure Django app In your application configuration file setting.py configure the database section as follows: DATABASES = {     'default': {         'ENGINE': 'django_cubrid',       # The backend name: django_cubrid         'NAME': 'demodb',                  # CUBRID database name: eg. demodb         'USER': 'public',                      # a database user: eg. public         'PASSWORD': '',                     # a database user password: eg. an empty password         'HOST': '',                            # Set to empty string for localhost.         'PORT': '33000',                 # Set to empty string for default 33000.     } } Done! Now you can use CUBRID as your backend database server. For more information on django_cubrid, refer to How to use Django with CUBRID. To get started with CUBRID database, refer to the following tutorials: Important Facts to Know about CUBRID Getting started with demodb (CUBRID Demo Database) Create a sample CUBRID Database from the command line How to ... using CUBRID Tutorials If you have questions, feel free to ask at CUBRID Q&A site, Twitter, or Facebook. [Less]

This is the tenth article on CUBRID Internals. In the previous articles, I have discussed about Data Types, Domains, and Inheritance in CUBRID, how data is stored in CUBRID, etc. Today I will explain about two very important aspects of CUBRID ... [More] Database - the execution mode and the client-server architecture. These are the most vague but interesting parts in understanding the internal structure of CUBRID. There are two execution modes in CUBRID: Client-server mode. Standalone mode. To understand them better, I need to explain about the client-server architecture of CUBRID. The Client-Server Architecture in CUBRIDFor a general database, the client-server architecture reminds many people of a DB client application and the DBMS server as shown on the left part of the following Figure 1. In other words in the common client-server architecture, the client application uses the database client library to connect to a database server and execute queries. In this case, the query processing is handled by the database server, not the client application. This is the common architecture. Figure 1: Concept of CUBRID Client and Server. However, for the internal structure of CUBRID, the terms client and server are slightly different. In CUBRID, the client part is thicker than usually. It is not just an application that requests data processing from the DB server, but it has some DBMS functions like the DB object and query processing functions (e.g. query syntax analysis, optimization, and execution plan generation) as well as a simple network communication function. On the other hand, the server part is classified into the storage system function layer that manages the database on a disk and the query processing function layer that executes SQL statements against this data on the disk. Thus, CUBRID is based on the client-server DBMS architecture which has a different meaning than in a general DBMS. This client-server DBMS architecture allows CUBRID to enhance the query processing speed by distributing DBMS functions to multiple client hosts. The client hosts perform much of the heavy work such as query syntax analysis and optimization, execution plan generation, and caching the database objects. Eventually, the server is left with only executing the statement against the data volumes stored on the disk which allows to respond to more requests than otherwise would be possible. Thus, this client-server architecture provides significant performance increase. Difference between client-server and standalone modesNow, let's discuss the client-server mode and the standalone mode in CUBRID. Single vs. multiuser access The difference between these two modes is how the client and the server execute. In one mode, a process where the database volume is mounted runs as a server and client processes access this process. In the other mode, one process includes both the client function and the server function. In other words, in the standalone mode only one user can access the database while in the client-server mode multiple users can access the database. Libraries The source code of CUBRID creates three libraries, libcubrid.so (the CUBRID server library), libcubridcs.so (the CUBRID client library) and libcubridsa.so (the standalone mode combining the server and the client code). The Makefile.am builds all these libraries. All of source code included in the libcubrid.so library and the libcubridcs.so library are included in the libcubridsa.so library which means that the libcubridsa.so source code includes both parts: the part that acts as a server and the part that acts as a client. In addition, this libcubridsa.so library for standalone mode is one library linking with the two codes. In the CUBRID source code, you can frequently find the condition compile macro definitions such as #if defined(SA_MODE), #if defined(CS_MODE), and #if defined(SERVER_MODE). This configuration implements both the client-server mode and the standalone mode with one CUBRID code. Single-threaded vs. Multi-threaded As you know, using CUBRID in client-server mode means that multiple users simultaneously use one database. Therefore, the CUBRID server should support this mode and be configured to run in multiple threads. However, the client-side code is configured to run with a single thread. In addition, the standalone mode where one process runs as both a client and a server runs with a single thread. It is not usual for other DMBSs to support the standalone mode. CUBRID supports this mode to make it easy to implement some database management utilities. Among the utilities which should mount the database volume for modification, some of them should not be used by multiple users. In this case, the utilities are designed to run in the standalone mode. The most popular example of these utilities is compactdb which adjusts the location of objects in the database volume to reduce wasted space. The addvoldb utility, which creates the initial data volume, runs in the standalone mode, too. Another interesting thing is that the CSQL command line tool can run as the standalone mode as well. This is because the standalone mode includes all CUBRID functions, from the database volume file function to the SQL processing function, so it works as a DBMS by itself. The CUBRID Broker program was developed by using only the client library. It serves as a bridge for various database drivers such as JDBC or ODBC to access the CUBRID server. Therefore, at the CUBRID server process side, the Broker is an application that uses the C API included in the libcubridcs.so library. When CSQL runs as the client-server mode, it becomes similar to the Broker. So, when you use utilities that show the internal information of the CUBRID server, such as killtran or lockdb, most of the clients in the list are shown as cub_cas or csql. At the cub_server server process, the the cub_cs program, not the Java application, is a client. Figure 2: Client-Server Mode and Standalone Mode. Conclusion Now you know about two different execution modes in CUBRID: the client-server mode and the standalone mode. In this article you have also learned that the client-server architecture of CUBRID is slightly different than in a common DBMS. In CUBRID, the client part is thicker and includes some of the DBMS functionality such as object and query processing which allows CUBRID to distribute these functionality to multiple client hosts to enhance the query processing speed. In the next article I will continue my previous talk about Two-phase Locking mechanism in CUBRID. I will explain about how the Lock Manager works in CUBRID. [Less]

Unlike other RDBMS, CUBRID has a middle layer called a BROKER. For better understanding of CUBRID let’s take a look at why the BROKER is needed, what are its pros and cons. Developers who use CUBRID know that its features a 3-tier structure, "API – ... [More] BROKER – DB Server". The most frequently asked question by those who are new to CUBRID is: "Why is CUBRID built in a 3-tier structure?" Another commonly asked question is: "The BROKER port is required in the connection string. What does the BROKER do?” This article aims at providing developers with answers as to why the BROKER is needed, what features it has, and what the pros and cons of the BROKER is. CUBRID Client Module There is a database, which is a collection of data. Then there is a server, which controls the data. And a client that sends requests to the server. This is the general pattern that CUBRID shares in its architecture. Now take a look at CUBRID's Process Structure illustrated in the figure below. You can see that the process that acts as the database server is called cub_server (the lower part). Also the only client module that can send a request to this cub_server process is libcubridcs.so (a part of the cub_cas module). This library is written in C-language. The native application that uses libcubridcs.so will look like the following. And below you can see what these two client and server processes do when, for instance, a SELECT statement is executed in the native application. The figure shows an abbreviated form of the query processing stages. The processing stage may change depending on whether a plan cache was used, on the isolation level that was set, or depending on the form of query. This schema above shows that CUBRID’s client module does not just send to the server the request received from the application program, but also it is responsible for other tasks. The client module: parses the query; optimizes it; creates a query plan; and sends the plan to the server. The server then searches and manipulates the data according to the plan sent by the client. To create a query plan a lot of information is needed for the client module. Let’s say the following query is executed. SELECT * from foo where id = 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. 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. 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. 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. 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. 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. 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. 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_cas is 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. When the app sends a connection request, the request is sent to cub_broker. Then the cub_broker selects available cub_cas and sends the application’s connection to cub_cas, thus connecting the driver and the cub_cas. The cub_cas accesses the cub_master to 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). This connection 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.conf for 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_cas which 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(); 03/21 18:51:36.467 (0) CLIENT IP 127.0.0.1 03/21 18:51:36.473 (0) connect db cgkdb user public url jdbc:cubrid:localhost:53300:cgkdb:public::? 03/21 18:51:36.476 (1) prepare 0 select * from foo where id = ? 03/21 18:51:36.477 (1) prepare srv_h_id 1 03/21 18:51:36.491 (1) execute srv_h_id 1 select * from foo where id = ? 03/21 18:51:36.491 (1) bind 1 : INT 1 03/21 18:51:36.529 (1) execute 0 tuple 1 time 0.055 03/21 18:51:36.529 (0) auto_commit 03/21 18:51:36.529 (0) auto_commit 0 03/21 18:51:36.529 (0) *** elapsed time 0.052 03/21 18:51:36.537 (0) con_close 03/21 18:51:36.537 (0) disconnect Flexibility The third advantage of BROKER lies in its various modes. As briefly mentioned before, CUBRID supports HA. Database server has active-standby structure, and data in an active server gets duplicated to a standby server, thereby enabling a continuous service when a failure occurs on the active server through automatical failover to the standby server. While write operations (INSERT/DELETE/UPDATE) are required to be handled by the active server, read operations (SELECT) can be processed by both active and standby servers. According to operations the application executes, a mode to select to which server the application should access is needed. There are three modes for BROKER. Read-write mode is for accessing the active server with read and write operations; Read-only mode is for accessing the standby server only for read. In case of connection failure, the broker can redirect the requests to the active server. Slave-only mode is also for accessing the standby server only for read, but in case of connection failure, the broker will not redirect the requests to the active server but return the error. When the BROKER is set in read-only mode and the standby server is accessible, then the broker connects to a standby server. In case of connection failure, the BROKER connects to the active server. Even if the database server has changed its role (i.e. changed from being active to being standby) due to a failure, the application shall not consider which server is active because the BROKER automatically connects to a active/standby server according to its read-write or read-only mode. In slave-only mode the BROKER can access only a standby server. In a case when a failure occurs on the standby server and there is only an active server, all the requested connections to slave-only BROKER return an error. This mode is useful when a standby server is only allowed to execute queries in order to analyze the inserted data in a real production service however those queries should be blocked from reaching the active server because they may decrease the performance in service. However if the failure on the standby server lasts too long and the application is needed to connect to the active server, then a DBA can change this slave-only BROKER to read-only. This is very convenient, as there is no need to make code modification on the application level; no application developer is required to be involved. Only DBA is involved and is capable of switching the modes according to their needs. Below is the image of server connection according to BROKER mode. For more detailed examples of the BROKER in different modes, you can refer to the presentation we gave at OSCON 2011: CUBRID HA - Guaranteed Way to Never-Die Web Services. So, now we have briefly looked into advantages and disadvantages of the BROKER model. Let me show you some examples of possible BROKER development. CAS for Oracle/MySQL CAS for Oracle/MySQL is a middleware connection pool manager for ORACLE and MySQL server based on CUBRID BROKER and JDBC driver. The main purpose of CAS for Oracle/MySQL is to be able to use the advantages of CUBRID Broker's connection management also in Oracle/MySQL. Let’s say there are two open connections in a single Web server which are in idle state in the connection pool. If there are 10 Web servers in idle state, this means there are 20 connections in total which are mostly idle. If the number of Web servers increases as a service grows in traffic, the number of idle connections will also increase accordingly. In this case, if application connections can be shared in the BROKER layer, we can avoid the increase of idle connections. The connection loads on the DBMS server can be reduced if cub_cas is set to operate accordingly not by total connections set in Web servers, but also by actual load of DBMS. The following image is simplified structure of CAS for Oracle/MySQL, which you can also see in the above mentioned OSCON presentation. DB sharding As database grows in volume, there are some cases where you need to manage the data larger than manageable capacity that each device can handle. In such cases, the data needs to be allocated to several databases with the same schema. Generally, when processed in application, you are to select a sharding key and a rule for deciding a shard based on a key value and then to pick up a database and query to it. For example, as for forum database, forum ID shall be a shard key and a shard will be determined through hash function in the application. If such a sharding method is supported in the BROKER, then the application has to transfer only a shard key with a query and the BROKER will determine a shard by the shard key and properly execute the query to the shard. In this case, there will be no need for implementing logic for deciding a shard in application. Only changing the BROKER’s configuration will be necessary when more shards add up. We wrote a separate blog about CUBRID Database Sharding. Refer to it for more explanation and examples. Load balancing In a case of increased read load on the DB, one of the possible ways to handle the load is to extend the replication server, then distribute read requests. CUBRID also supports 1:N active standby configurations in HA and allows to have additional replicas for read balancing. In BROKER, however, loads shall be balanced dynamically deciding how much load is applied to each replicated server. For example, let’s say in a case where there are ten connections, five of them are connected to the replicated server 1 while the other give - to the replicated server 2. It is impossible to forecast how much connections will be used at that moment when each of 5 connections is made. If the connection pool operates in First-In-First-Out, throughput per a connection could vary greatly one from another and so could throughput per a replicated server. However, if connection can be changed after evaluating its throughput dynamically, then it can balance each of throughputs and distribute server loads automatically by only changing BROKER’s configuration even when additional replicated server is added. What DB sharding and load balancing have in common is that by the time the request is handled, a target DB should be selected. And the good news is that the 3-tier structure is capable of responding to such requests in a more flexible manner. CAS for Oracle/MySQL is being supported in CUBRID version 8.4.0, while the Database Sharding and load balancing featues will be provided in the upcoming versions. Conclusion So far, we have looked into CUBRID and its middleware BROKER. Although there are some disadvantages of the 3-tier structure, I believe that there are many more advantages in it which allow more flexibility to connection management as the server environment gets more complex. Taking advantage of its unique structural advantage, starting from CAS for Oracle/MySQL, the CUBRID BROKER shall evolve to become an independent middleware. By Chol Gyu Kang, CUBRID Dev. Lab Leader, NHN Corporation. [Less]

It is not a new attempt at all to use main memory as a storage area instead of a disk. You can find in your daily life many cases in which Main Memory DBMS (MMDB) is used to execute much faster than a disk. One example is when you use a mobile ... [More] phone. When you SMS or call your friend, most mobile service providers use MMDB to get the information on your friend as soon as possible.  In Memory Data Grid (IMDG) is the same as MMDB in that it stores data in main memory, but it has a totally different architecture. The features of IMDG can be summarized as follows:  Data is distributed and stored in multiple servers. Each server operates in the active mode. A data model is usually object-oriented (serialized) and non-relational. According to the necessity, you often need to add or reduce servers. In other words, IMDG is designed to store data in main memory, ensure scalability and store an object itself. IMDG products, whether open source or commercial product, include:   Hazelcast Terracotta Enterprise Suite VMware Gemfire Oracle Coherence Gigaspaces XAP Elastic Caching Edition IBM eXtreme Scale JBoss Infinispan This article does not aim to compare the features and performance of these products. Instead we will look into the architecture of IMDG and discuss how NHN can utilize it. Why Memory? As of June 2012, an SSD using SATA interface performs approximately 500 MB/s, while an SSD using expensive PCI Express records approximately 3,000 MB/s. As the performance of 10,000 RPM SATA HDD is approximately 150 MB/s, an SSD is 4-20 times faster than an HDD. However, the performance of DDR3-2500 reaches 20,000 MB/s. The processing performance of main memory is 800 times faster than an HDD, 40 times faster than an SSD and seven times faster than the fastest SDD. Moreover, the latest x86 server supports main memory of hundreds of GB per server. Michael Stonebraker says the typical Online Transaction Processing (OLTP) data capacity is approximately 1 TB and that the OLTP processing data capacity would not increment well. If servers using main memory of 1 TB or larger become more commonly used, you will be able to conduct operations with the entire data placed in main memory, at least in the field of OLTP. In the history of computing, "Make it faster" has always been the best virtue everyone should pursue. As the capacity of main memory incremented, it was necessary for platforms actively using main memory as a storage area instead of permanent storage to appear. IMDG Architecture To use main memory as a storage area, you should overcome two weak points: limited capacity and reliability. You need to handle data that exceeds the maximum capacity of the main memory of the server and also make no data loss take place when a failure occurs. IMDG overcomes the limit of capacity by ensuring horizontal scalability using a distributed architecture, and resolves the issue of reliability through a replication system. Despite some differences in details for each product, you can generalize an IMDG architecture as in Figure 1:   Figure 1: IMDG Architecture. An application server has a client library provided by IMDG and it accesses IMDG by using this library. Many IMDG products provide the feature of synchronizing data to RDBMS. However, you don't need to necessarily establish a separate permanent storage system (e.g., RDBMS). In general, IMDB enables objects to be stored through serialization. Some products provide the feature of storing objects that implement serializable interface, while some IMDGs provide an independent serialization method. Of course, it is very convenient to use them thanks to the schemaless structure. It can be understood as the concept of In Memory Key-Value Database to store and retrieve objects. The data model used in IMDG is Key-Value. Therefore, data can be distributed and stored by using this key. There are a variety of methods ranging from using a consistency hash model as in Arcus (NHN's Memcached Cloud) to using a simple modulo method as in Hazelcast. When storing data in this way, at least one node is used as a replication system to respond to any failure. There are a variety of interfaces by products. Some products provide an SQL-like syntax for access via JDBC, and some provide API implementing Java's Collection where you can use HashMap or HashSet for multiple nodes. IMDG has a different usage and purpose compared to cache systems such as Arcus. Figure 2 shows a simplified structure of Arcus Architecture. Figure 2: Cache System Architecture Such as Arcus. Cache systems such as Arcus also use main memory as a storage area and secure horizontal scalability. In this sense, they are the same as IMDG. However, the use method and purposes shown in Figure 1 and 2 are significantly different. It is necessary to use a permanent storage area in cache systems such as Arcus, but it is optional in IMDG. Table 1: Comparison of Read/Write Performance between IMDG and Cache System. Cache System IMDG Read If data is in a cache, it will not be read from the database. If data is not in a cache, it will be read from the database. It is always read from IMDG main memory, therefore it's fast. Write As data is written in permanent storage, applying the cache system is not related with the enhancement of writing performance. Even when configured to synchronize data to permanent storage, some products support non-synchronized write. In this case, you can expect a very high write performance. The cache system and IMDG also differ in the respect of whether data migration is available, whether reliability is ensured and whether a replication feature is provided. Features of IMDG A list of the features of HazelCast, one of IMDG products, is as follows. As HazelCast holds a double license policy, you must purchase a commercial license to use some features, such as ElasticMemory. You can find use reference information easily because many features of the product can be used freely as open source. Although it is difficult to say the features of HazelCast are the common ones provided by all the other IMDG products, I decided to introduce it here because I considered HazelCast a good example to understand the features of IMDG. DistributedMap & DistributedMultiMap It is a class which implements Map, ?>. Map data is distributed and allocated to multiple IMDG nodes. As a table of RDBMS can be expressed as Map>, you can get the data distribution effect similar to when using sharded RDBMS. HazelCast also supports SQL-like features in DistributedMap. When inspecting values in a map, you can use SQL-like clauses, such as WHERE clause, LIKE, IN and BETWEEN. HazelCast provides not only the feature of storing all data in memory but also provides the feature of storing it in permanent storage. When storing data in permanent storage, you can configure it to be used as a cache system. You can also store only necessary data in memory and put the remaining less frequently used data in permanent storage by selecting the LRU or LFU algorithms. In addition, you can use MultiMap in a distributed environment. If you retrieve a certain key, you can get a value list in the form of Collection. Distributed Collections You can use DistributedSet, DistributedList or DistributedQueue. Data in such a Distributed Collection object is stored not in a single IMDG node, but distributed and stored in multiple nodes. For this reason, it is possible to maintain a single list object or set object stored in multiple nodes.  DistributedTopic & DistributedEvent HazelCast provides the feature of topic reading that guarantees the order of messages being published. This means you can use it as a distributed message queue system.  DistributedLock This is literally a distributed lock. You can conduct a synchronization in multiple distributed systems by using a single lock.  Transactions You can use transactions for DistributedMap and DistributedQueue. With the feature of commit/rollback, you can use IMDG even in an environment where operations should be executed more carefully. Using Large Capacity Memory and GC  Most of the products introduced above use Java as an implementation language. As a heap of tens of GB should be used, it could take quite a long time to complete a full GC. For this reason, IMDG provides a method to overcome this limit. The method is to use an off-heap memory (direct buffer). When JVM receives a direct buffer creation request, it allocates memory to the space out of a heap and uses it. Objects are stored in this allocated space. Because a direct buffer is not a space for GC any more, no full GC problem will occur. In general, accessing a direct buffer takes longer than it takes to access a heap buffer. Nevertheless, if you use a direct buffer, you can allocate large space and reduce the burden of a full GC. Therefore you can have the advantage to secure a certain amount of processing time by eliminating the time for a full GC when using large capacity memory space. Figure 3: Comparison of a Heap, a Direct Buffer and a Disk (Source). However, it requires expertise to store and to retrieve objects by using a direct buffer. You need to have the expertise required to make a memory allocator. For this reason, this feature of storing objects by using off-heap memory is provided only in commercial IMDG products. If Integrity Constraint is Provided Currently IMDG is used mainly as a cache system. However, an IMDG is a platform that is likely to develop into a main storage area. In many cases, a distributed map can reliably replace a table of RDBMS. Some products provide a distributed lock and if the integrity constraint feature can be provided based on such distributed lock, it is possible to replace RDBMS with IMDG. In this case, you will be able to respond to statistics processing by using RDBMS as a back-end system. This means the role of RDBMS in Internet services can become assistive. If the integrity constraint feature is provided, it is possible to provide a pleasant user experience based on fast speed as well as other features that were difficult to provide in the past. I think, given such possibilities, it is necessary to seriously examine the values of using an IMDG as the main storage in NHN Internet services. By Ki Sun Song, Software Engineer at Web Platform Development Lab, NHN Corporation. [Less]

Yes, it has been quite a while since Yii and Propel users have been requesting to provide CUBRID Database support. And today I am very happy to announce that we have sent Pull Requests to both of these projects. Both projects rely on CUBRID's PDO ... [More] driver which is available on PECL. Yii supports CUBRID 9.0 on top of CUBRID PDO 9.0. Propel2 supports CUBRID 8.4.1+ on top of CUBRID PDO 8.4.0+ (PDO driver 8.4.0 is compatible with CUBRID 8.4.1). Until the patches are officially integrated to the upstream, you can download the patched version of either packages from the following Gihub repos and start using them right away. We will be very glad to receive your feedback and observations. Yii: https://github.com/kadishmal/yii/tree/cubrid-database-support with a patch over Yii 1.1.12 (the latest stable release as of Dec 27, 2012). Propel2: https://github.com/kadishmal/Propel2 with a patch over the latest Propel2. If you like what we have done, you can go to the following PR we have sent and up vote them. This may push the upstream to review the submitted code quicker. Yii: https://github.com/yiisoft/yii/pull/1893 Propel2: https://github.com/propelorm/Propel2/pull/328 Alternatively, you can discuss this topic in the comments below. Let us know if you have questions or feedback. [Less]

Welcome to the 2nd part of the CUBRID Node.js introduction tutorial! We have published the first tutorial in October 2012. If you have not read it yet, we strongly recommend that you do before continuing with this next part. Remember: All the ... [More] CUBRID Node.js tutorials can be found at node-cubrid Wiki page. In this tutorial, we will go through some more advanced topics: Using a connection pool Queries with parameters Some driver usage tips But before we start, let’s take a quick look at what has happened since the first CUBRID Node.js 1.0 driver was released in October this year. The big news is that we have just released a new node-cubrid 1.1 driver and the most important update for this version is that it adds compatibility with two new recent CUBRID engine releases: CUBRID 8.4.3 with Database Sharding and API level Load Balancing support CUBRID 9.0 beta with 3x Performance Increase and Database Sharding support At the same time, the driver keeps full backward compatibility with the previous 8.4.1 engine release – so you can use any of the latest engine releases while using a single driver version! And if you wonder what are the particular differences in the TCP/IP protocol between the various versions and how we implemented support for the different protocols, please take a look at the driver code, and in particular, at the TCP/IP communication packets definitions. Using a connection pool Connection pooling is a fundamental concept in software applications development, especially when combined with a database server environment (Figure 1). Using a connection pool provides many advantages to a consumer application, for example: Control the server resources usage Speed up things (connecting to a database is usually a “heavy” long-time operation) Allow for “parallel” tasks execution  Figure 1: Database Connection Pooling. One special thing to note is that the TCP/IP database communication protocol is synchronous and not parallel-execution aware/capable. This means that if you want to execute two or more queries truly in parallel, you will need to use multiple database connections and the best solution is definitely to go on with a connection pool implementation. The current release of the CUBRID Node.js driver does not feature (yet) a built-in connections pool, but it supports the best Node.js connection pool application available today – the node-pool. You can find code example on using node-pool with the driver in Common uses of CUBRID Node.js API with examples blog. In this blog you can learn how to use node-pool to: Reuse a database connection Limit the number of concurrent connections In this blog you will find not only a node-pool usage example, but much more! Also, you will find many code examples which will get you started very fast with the CUBRID Node.js driver! For this tutorial, let’s use a different Node.js connection pool implementation – the node-pooling. And for this example, let’s focus on different goals: Limited the number of concurrent connections Not keep a connection opened indefinitely; open a connection only when needed. So how do we use node-pooling with node-cubrid? First of all – let’s define a pool with: A maximum of 2 “active” clients A maximum idle time of 30 sec. An interval to check for idle clients of 1 sec. create and destroy functions which generate a client ID and logs these events var conn_idx = 1; var pool = pooling.createPool({    checkInterval: 1 * 1000,    max: 2,    maxIdleTime: 30*1000,    name : 'my pool',    create : function create(callback) {        var client = new EventEmitter();        client.id = conn_idx ;        Helpers.logInfo('Creating pool client id: '   client.id);                return callback(null, client);    },    destroy : function destroy(client) {        Helpers.logInfo('Destroyed pool client id: '   client.id);        client.was = client.id;        client.id = -1;    }}); Notice that the pool definition doesn't mention anything about CUBRID database connection. Since it is our goal to open a connection only when necessary, we will establish a connection in the actual code as shown below. Now we will request 3 client connections to the CUBRID demodb database, each one requesting a query execution to a different table: code, nation, game, and, for each one, display the number of rows returned in the result set. Because we do not want to keep the connection open, the code will close the database connection when the query execution is completed. For simplicity, we will list here the code for just one of these client requests – the other two are absolutely similar: pool.acquire(function (err, client) {     var CUBRIDClient = new CUBRIDConnection('localhost', 33000, 'public', '', 'demodb');     CUBRIDClient.connect(function (err) {         if (err === null) {             Helpers.logInfo('Database connection acquired for pool client id: '   client.id);             Helpers.logInfo('Executing query: select * from code');             CUBRIDClient.query("select * from code", function (err, result, queryHandle) {                 if (err) {                     Helpers.logError(err);                 }                 else {                     Helpers.logInfo('Query results rows count for pool client id: '   client.id   ' is: '   Result2Array.TotalRowsCount(result));                     CUBRIDClient.closeQuery(queryHandle, function (err) {                         if (err) {                             Helpers.logError(err);                         }                         else {                             Helpers.logInfo('Query closed for pool client id: '   client.id);                             CUBRIDClient.close(function (err) {                                 if (err) {                                     Helpers.logError(err);                                 }                                 else {                                     Helpers.logInfo('Connection closed for pool client id: '   client.id);                                     Helpers.logInfo('Waiting some time before releasing the pool client id: '   client.id   '...');                                     setTimeout(function () {                                         Helpers.logInfo('Releasing pool client id: '   client.id);                                         pool.release(client);                                     }, 10 * 1000);                                 }                             });                         }                     });                 }             });         }     }); }); And this is the execution result output, which shows how only 2 clients are initially created, and when the first client job is done, it is “reused” to execute the third query: Figure 2: Connection pooling using node-pooling. As you can see, it’s not difficult at all to combine node-cubrid with node-pooling (or node-pool) in order to achieve a connection pool implementation working with a CUBRID database application backend. One more thing to mention – we are considering implementing a built-in connection pool in the future driver releases to simplify even more the CUBRID Node.js driver usage. Please let us know your feedback and suggestions! Queries with parameters One of the important features a driver needs to implement is giving the users the ability to run queries using parameters. Please note that using parameters not only helps to support flexible coding, but also helps with the server side queries execution optimization (see Queries Execution Plan). The node-cubrid driver implements two functions that support parameters: Function Description executeWithParams(sql, arrParamsValues, arrDelimiters, callback) Execute a SQL statement which does not return recordset results. queryWithParams(sql, arrParamsValues, arrDelimiters, callback) Execute a SQL query statement, which returns recordset results (rows data). Usually, when we discuss about query parameters, we refer only to parameters specified in query (WHERE) conditions – for example: SELECT * FROM code WHERE s_name = ? OR f_name LIKE ? CUBRID Node.js driver takes query parameters concept one step further! You can specify as parameters anything you want, including table and column names. For example, you can do this: SELECT * FROM ? WHERE ? = ? OR ? LIKE ? Obviously, so much flexibility comes with a price, and the “price” is that you will need to specify the correct delimiters for each parameter, because the SQL delimiters can be different, from one data type to another: Parameter data type Standard delimiter(s) string ' (single quote) integer none table name ` (backtick) column name ` (backtick) or none For example, let’s consider the following very generic query with parameters: SELECT * FROM ? WHERE ? LIKE ? AND LENGTH(?) > ? And let’s suppose what we want to execute is: Figure 3: A sample SQL and query results in CUBRID Manager. Then, the parameters delimiters we need to use are: Parameter index/position Parameter "scope" Delimiter 1 Table name ` (backtick) 2 Column name none 3 Condition (string type) ' (single quote) 4 Column name none 5 Condition (number type) none And this means that this is how we will call the node-cubrid queryWithParams function: var sql = 'SELECT * FROM ? WHERE ? LIKE ? AND LENGTH(?) > ?'; var arrValues = ['nation', 'code', 'A%', 'capital', '5']; var arrDelimiters = ['`', '', '\'', '', '']; function errorHandler(err) {     throw err.message; } CUBRIDClient.connect(function (err) {     if (err) {         errorHandler(err);     }     else {         CUBRIDClient.queryWithParams(sql, arrValues, arrDelimiters, function (err, result, queryHandle) {             if (err) {                 errorHandler(err);             }             else {                 assert(Result2Array.TotalRowsCount(result) === 12);                 CUBRIDClient.closeQuery(queryHandle, function (err) {                     ... The execution result will be: Figure 4: Results of executing a query with parameters in node-cubrid. Remember – When using SQL statements with parameters, you need to: Use ? for each parameter placeholder Specify the delimiters for each parameter as a JavaScript array in sequential order. As a side note, you can also specify delimiters as hard-coded in the SQL query definitions – for example: SELECT * FROM `?` WHERE `?` LIKE ‘?’ AND LENGTH(?) > ? If you choose to do this, then the parameters you need to provide will obviously be always empty string values. Some driver usage tips Querying database schema All CUBRID drivers provide, in various degrees, support for querying a database schema, which is giving the client the ability to get information about the CUBRID database objects: Tables Views Stored procedures Indexes Etc. In node-cubrid, so far we are providing a built-in support for: Tables Views And this is how you do it: CUBRIDClient.getSchema(CUBRIDClient.SCHEMA_TABLE, callback); CUBRIDClient.getSchema(CUBRIDClient.SCHEMA_VIEW, callback); The results are: Figure 5: SCHEMA_TABLE results. Figure 6: SCHEMA_VIEW results. However, you can easily extend the built-in support by simply querying the schema catalog tables and views! The schema catalog will “tell” you about columns, users etc. All you need to do is to write appropriate SELECT queries, and the CUBRID ADO.NET Driver implementation will show you how to do it! Tip: There is a nice open-source tool available for CUBRID – CUBRID Database Schema – which will show you, in a very friendly way, a CUBRID database schema content. An online demo is available at http://cubdbsch.cubrid.org/. Stay tuned for the next driver release – 2.0 – which will feature extended schema support: Tables and Views columns/attributes schema Users and users’ access rights Indexes and foreign keys etc. Querying without explicit connect Did you know that you can do this? var CUBRIDConnection = require('./node_modules/node-cubrid/src/CUBRIDConnection'); var CUBRIDClient = new CUBRIDConnection(); CUBRIDClient.query('select * from nation', function (err, result, queryHandle) { .... As you can see, there is no connect() call – just direct query. And it works! Why? The reason is that the driver automatically opens a connection for you in the background with default parameters, if you have not explicitly done that yourself. This means that by simply tweaking the default connect values (just edit the node-cubrid/src/CUBRIDConnection.js file) you can skip the connect() prerequisite and directly execute queries (and not only queries, but batch execute also works!). With default configurations it will connect to demodb database on localhost with dba user and an empty password. BTW, if you call connection close() before closing the open queries using closeQuery(), don’t worry! The driver automatically takes care of closing all open query handles. Getting the LAST INSERT ID One of the most common patterns in database applications is to get the LAST INSERT ID, after performing database INSERT statement(s). Depending on the driver used, there are various ways to support this functionality. For example, if you used CUBRID PHP Driver, you would use the function called cubrid_insert_id(). But how do you do this in node-cubrid? node-cubrid 1.0 version does not provide yet a dedicated support for retrieving the LAST INSERT ID value(s). However, there is a quite simple workaround – use the built-in SQL function - LAST_INSERT_ID(). Let’s see how to do that: CUBRIDClient.batchExecuteNoQuery([     'drop table if exists node_test',     'create table node_test(id INT AUTO_INCREMENT NOT NULL PRIMARY KEY)' ], function (err) {     CUBRIDClient.batchExecuteNoQuery([         'insert into node_test values(NULL)'     ], function (err) {         CUBRIDClient.query('select LAST_INSERT_ID()', function (err, result, queryHandle) {             ... Links & Resources CUBRID Node.js driver home page node-cubrid documentation node-cubrid Online Wiki node-cubrid tutorials and examples General Node.js resources http://howtonode.org/ http://nodejs.org/community/ http://en.wikipedia.org/wiki/Node.js CUBRID API Blog And this was the second part of CUBRID Node.js introduction tutorial. Please let us know your feedback – as always, we highly appreciate your suggestions and comments - and remember to periodically check the CUBRID web site (www.cubrid.org) for more CUBRID tutorials and resources. Remember, you can ask questions and suggest driver improvements using the CUBRID forum or the dedicated Q&A site section. [Less]

I am very glad to announce the immediate availability of CUBRID Node.js driver version 1.1. You can download node-cubrid from NPM. For more details, see check out the official repository at https://github.com/CUBRID/node-cubrid. What's New In ... [More] this new release we have improved many aspects of the driver. We now follow "One driver to rule them all" concept. In other words, you can use node-cubrid with any version of CUBRID Database including 8.4.1, 8.4.3, and 9.0.0 (beta). The new driver comes with many code fixes, bugs fixes, and design changes. Rich database support: Connect, Query, Fetch, Execute, Commit, Rollback, DB Schema etc. Out of the box driver events model. 10.000+ LOC, including the driver test code and demos New test cases added. Now 50+. We have significantly refactored the code using JSHint/JSLint code analysis. New documentation. Created new tutorials that we will publish later. TestingThe 1.1 release was successfully tested, using the driver test suite, on 2 OS x 3 CUBRID engine = 6 different test environments. What's nextThe upcoming new version, 2.0, will feature many new features and improvements. For example: CUBRID 9.1 stable release compatibility. Improved queries support. In particular, implementing queries queuing support similar to MySQL. Extended database schema support ... and many more. If you have questions, ask at our CUBRID Q&A site, or at #cubrid IRC. We will be glad to answer you! [Less]

ENUM is a new enumerated data type introduced in CUBRID 9.0. Like in all programming languages, the ENUM type is a data type composed of a set of static, ordered values. Users can define numeric and string values for ENUM columns. Working with ... [More] ENUM types Creating an ENUM column is done by specifying a static list of possible values: CREATE TABLE person(     name VARCHAR(255),     gender ENUM('Male', 'Female') ); CUBRID understands the ENUM type as an ordered set of constants which, in the above example, is a set of {NULL: NULL, 1: 'Male', 2: 'Female”}. To assign a value to the gender column, users may either use the index of the value ({NULL, 1, 2}) or the actual constant literal ({NULL}, {'Male'}, {'Female'}). CUBRID restricts the values that can be assigned to this column to only values from this set + NULL. Moreover, ENUM column is case-sensitive, i.e. it will raise an error if you try to enter 'female' in lower case. Also, an empty string is allowed if it is defined as one of the elements of the ENUM column. In our examples, it is not allowed. csql> INSERT INTO person(name, gender) VALUES('Eugene', 'Male'); 1 row affected. 1 command(s) successfully processed. csql> INSERT INTO person(name, gender) VALUES('Anne', 2); 1 row affected. 1 command(s) successfully processed. csql> SELECT * FROM person; ===  ===   name                  gender ============================================   'Anne'                'Female'   'Eugene'              'Male' 2 rows selected. Any attempt to insert a value outside of the defined set will result in a coercion error. In the below case, trying to insert an empty string raises an error because it is not in the set of allowed values defined in the person table. csql> INSERT INTO person(name, gender) VALUES('John', 'N/A'); IN line 1, COLUMN 44, ERROR: before ' ); ' Cannot coerce 'N/A' TO type enum. 0 command(s) successfully processed. csql> INSERT INTO person(name, gender) VALUES('John', 4); IN line 1, COLUMN 45, ERROR: before ' ); ' Cannot coerce 4 TO type enum. 0 command(s) successfully processed. csql> INSERT INTO person(name, gender) VALUES('John', ''); IN line 1, COLUMN 44, ERROR: before ' ); ' Cannot coerce '' TO type enum. 0 command(s) successfully processed. Why you should use the ENUM type There are three important reasons for which you should consider using the ENUM type: Reduce storage space. Reduce join complexity. Create cheap values constraints. Storage Space CUBRID uses only 1 byte per tuple when 255 or less ENUM elements are defined or 2 bytes for 256~65535 elements. This is because, rather that storing the constant literal of the value, CUBRID stores the index in the ordered set of that value. For very large tables, this might prove to be a significant storage space save. Take, for example, a table with 1,000,000,000 records which has an ENUM column defined as ('Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'). If you use a VARCHAR type instead of the ENUM type to store these values, the column would require anywhere between 5GB and 9GB of storage space. Using the ENUM type, you can reduce the required space to 2 bytes per tuple, adding up to a total of 2GB. Reduce join complexity JOIN way The same effect of the ENUM type can be achieved by creating a one to many relationship on two or more tables. Considering the example above, you can store values for days of the week like this: CREATE TABLE days_of_week(     id SHORT PRIMARY KEY,     name VARCHAR(9) ); CREATE TABLE opening_hours(     week_day SHORT,     opening_time TIME,     closing_time TIME,     FOREIGN KEY fk_dow (week_day) REFERENCES days_of_week(id) ); Then, when you wish to display the name of the week day, you would execute a query like: SELECT d.name day_name, o.opening_time, o.closing_time FROM days_of_week d, opening_hours o WHERE d.id = o.week_day ORDER BY d.id; ===  ===   day_name              opening_time  closing_time ==================================================   'Monday'              09:00:00 AM   06:00:00 PM   'Tuesday'             09:00:00 AM   06:00:00 PM   'Wednesday'           09:00:00 AM   06:00:00 PM   ... ENUM way You can achieve the same effect using an ENUM column: CREATE TABLE opening_hours(     week_day ENUM ('Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'),     opening_time TIME,     closing_time TIME ); And there’s no JOIN required to select opening hours: SELECT week_day, opening_time, closing_time FROM opening_hours ORDER BY week_day; ===  ===   week_day              opening_time  closing_time ==================================================   'Monday'              09:00:00 AM   06:00:00 PM   'Tuesday'             09:00:00 AM   06:00:00 PM   'Wednesday'           09:00:00 AM   06:00:00 PM   ... This can prove to be very useful, especially if your queries join several tables. Value constraints ENUM columns behave like foreign key relationships in the sense that values from an ENUM column are restricted to the values specified in the column definition. For a short list of values, this is more efficient than creating foreign key relationships. While foreign key relationships use index scans to enforce the restriction, ENUM columns just have to go through a list of predefined values which is faster even for small indexes. Why/When you should NOT use the ENUM type Even though ENUM is a great feature, there are cases when you’d better not use it. For example: When ENUM type is not fixed When ENUM type has a long list of values When your application does not know the list of ENUM values ENUM type is not reusable Portability is a concern When ENUM type is not fixed If you’re not sure if the ENUM type holds all possible values for that column, you should consider using a one to many relationship instead. The only way in which an ENUM column can be changed to handle more values is by using an ALTER statement. This is a very expensive operation in any RDBMS and requires administrator rights. Also, ALTER statements are maintenance operations and should, as much as possible, be performed offline. When ENUM type has a long list of values ENUM types should not be used if you cannot limit a set of possible values to a few elements. When your application does not know the list of ENUM values There are only two ways of getting a list of values you have defined for an ENUM type: parsing the output of SHOW CREATE TABLE statement: csql> SHOW CREATE TABLE opening_hours; ===  ===   TABLE                 CREATE TABLE ============================================ 'opening_hours'       'CREATE TABLE [opening_hours] ([week_day] ENUM('Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'), [opening_time] TIME, [closing_time] TIME) selecting information from CUBRID system tables: csql> SELECT d.enumeration FROM _db_domain d, _db_attribute a WHERE a.attr_name = 'week_day' AND a.class_of.class_name = 'opening_hours' AND d IN a.domains; ===  === enumeration ====================== {'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'} Both might require complex coding and selecting from system tables requires administrator privileges. ENUM type is not reusable If you have several tables which require the names of week days, you will have to create an ENUM type for each of them. If you create a table to hold week days names, you can join this table with whichever other table that requires this information. Portability is a concern The ENUM type is only supported by a few RDBMSs and each one has its own idea as to how ENUM type is supposed to work. Below is a list of a few notable differences between CUBRID, MySQL and PostgreSQL: CUBRID PostgreSQL MySQL Inserting out of range value Throws error Throws error Inserts special value index 0 Comparing to char literals Compare as strings Compare as ENUM elements Compare as strings Comparing to values outside of the ENUM domain Compare as strings Throws error Compare as strings These subtle differences will most probably break your application in interesting and hard to understand ways. If you’re migrating from PostgreSQL to CUBRID for example, and you expect comparisons with char literals to be performed as ENUM comparisons, you’ll have a hard time understanding why your query returns weird results. [Less]