How to Automate Tests

I registered a test code to Hudson, Continuous Integration (CI) tool, and set the test code to be executed by using Selenium WebDriver and JUnit whenever source is committed in SVN. It takes about 2 seconds to test a browser. When a problem occurs, the problem is reported within 10 minutes.

Hudson is commonly used in most development departments of NHN. By clicking Build Now, users can view the test progress status. The report system provides a good environment for "Fast Fail, Fast Feedback".

Figure 1: Multi-browser Test with One Set of Test Code.

Automating Firefox Test in Linux

Install xserver package on Linux server with Hudson. Modify run level to 5 and then add the following setting to an account where CI runs in order to execute a browser in a text console.

Install the latest Firefox version under the /usr/lib64 directory and replace the symbolic link. Now you can execute automation tests by running Firefox in Hudson.

• Firefox Test Automation Demo Video (Youtube)

Automating iPhone and iPad Tests in Mac OS X and Simulator

For tests, install Hudson on Mac OS X. It requires the iPhone or iPad Simulator, so you should install Xcode in advance.

• iPhone Test Automation Demo Video by Using Simulator (Youtube)

Automating Internet Explorer and Opera Tests in Microsoft Windows

For more details on how to automate tests in Microsoft Windows, see http://seleniumhq.org/docs/03_webdriver.html.

Why Is Browser Test Automation with Hudson Required?

If tests for several browsers are automatically executed whenever a source code is changed, it would clearly help developers to reduce bugs and keep the source tree that can be released anytime. In addition, QA time can be significantly reduced. There are many benefits of browser tests with Hudson, in addition to quality and cost. The following are the benefits of Hudson.

Keeping a Living Document and Building up Domain Knowledge

Whenever performing a test in Hudson, a document with illustration and description can be automatically generated by using Javadoc package.htm in the test code. This document is called a "living document" which is continuously updated and used by operators, developers and QAers for a variety of purposes such as hand-off of domain knowledge and preservation of revision history.

Figure 2: Javadoc Document Created from Tests.

Visualizing Service Quality by Browser and Domain Knowledge

Anyone who clicks Build Now in Hudson can check the service quality by browser.

Figure 3: Verifying Mobile News Service in Firefox.

For example, in some cases, the news service should be provided differently by day due to the service characteristics. It is very difficult to hand off all domain information to all related staffs. For example, the KOSPI/KOSDAQ indexes are not showed on the news on weekends but after the opening of the market on Monday morning. Anyone who clicks the Build Now button can see the news service operation process by status.

Tests with Hudson help users to hand off domain knowledge and build up the information as well as enhance quality and cut costs.

Best Practices and Examples

We once needed to update the version of Spring Framework used for the mobile news service. We modified the version information in the pom.xml file of Maven and performed the unit test and the integration test for web server source, and the result was no problem. Developers opened some new web pages by running the web server and checked that all of them were fine.

However, there were more than 80 runtime errors. Fortunately, we had the multi-browser test in Hudson test, so we could fix all bugs and problems before releasing the service.

Selenium WebDriver

Why Selenium WebDriver?

We use Selenium WebDriver because of its reliable quality through a version up process for a long time, and it is easy-to-use. The test is performed by running a browser, so it is easy to perform UI test by browser.

The virtual browser test such as HtmlUnit cannot support a variety of browser versions (it is limited to Firefox 3.6 and lower and Internet Explorer 8 and lower). In addition, it does not support mobile browsers. Therefore, I recommend Selenium WebDriver over HtmlUnit, even though Selenium WebDriver requires more effort.

With Java API provided by Selenium WebDriver at the JUnit test code, you can easily produce the browser test code. Code 1 below is now used for the mobile news service test.

Code 1. News Service Test Code.

Benefits of Selenium WebDriver

Benefits of Selenium WebDriver are various: it allows developers to produce the test code with a variety of languages (Java, C#, Python, PHP, Ruby, Perl) and provides fast feedback by using the implicit wait function. The following are the benefits of Selenium WebDriver.

Test by Specifying ID/Class/XPath

If considering HTML maintenance, ID is the best choice to specify HTML/CSS elements, the second best is Class, and finally, XPath. Selenium WebDriver supports all three.

Multi-browser Tests with One Set of Test Code

You can test several browsers with one set of test code because you can change the test browser by replacing WebDriver only. As of 2012, WebDriver supports Firefox, Internet Explorer, Chrome, Opera, iPhone Safari, iPad Safari and Android browsers. Code 2 below is a part of code that creates WebDriver.

Code 2. Example of Creating WebDriver.

Conclusion

So far, we have reviewed test automation applied to NAVER services. NHN has already automated tests for many services by using Hudson and Selenium WebDriver. I guess other companies have automated tests in the similar ways. Therefore, I hope this article to be a chance to communicate with others to create a Best Practice, rather than showing it for test methods.

By Hyehwan Ahn, Software Engineer at News Service Development Team, NHN Corporation.

I am a developer who is interested in automation of routine tasks in releasing Alpha/Beta/QA/Distribution phases to improve efficiency.

Big Data, the Web and SQL

In recent years, software companies have started to raise millions up to billions of dollars getting acquired by a big player, such as Google, Facebook, Yahoo! or Microsoft. Very often, the assumed value of such deals lay in the fact that Big Data could be purchased along with such acquisitions. "Social" Big Data was generated by millions of users over the web. It seemed too big to fit in classic relational databases, which is why the purchases also included buying the proprietary, rather short-lived technologies used to maintain Big Data. Most of the new companies thus experimented with NoSQL in one form or another.

SQL, on the other hand, has come a long way. SQL is a very expressive and powerful language used to model queries against any type of data, albeit mostly relational. At the same time, SQL is standardised and quite open. CUBRID is a good example of an object-relational database, which combines the expressiveness of SQL with high availability, sharding, and many other features needed to manage Big Data! In other words, CUBRID is the proof that SQL can be an adequate technology for the modern web.

Querying CUBRID with jOOQ

jOOQ is a Java API modelling SQL as an internal domain-specific language directly in Java. It features a built-in code generator to generate Java classes from your database model. These generated classes can then be used to create typesafe SQL queries directly in Java. A simple example of how this works with CUBRID can be seen in this jOOQ CUBRID tutorial.

The idea of creating fluent APIs in Java is not new. Usually, Martin Fowler takes most credits for his elaborations on the subject. After that, many approaches towards building internal domain-specific languages have surfaced, mostly in unit testing environments (e.g. JMock and Mockito). Apart from jOOQ, there are also a couple of fluent APIs that model SQL as a language in Java. These include:

• JaQu
• OneWebSQL
• Quaere
• QueryDSL
• Squill

Among the above, QueryDSL is the only other API with a comparable traction to jOOQ's. While QueryDSL hides the full SQL expressiveness behind a LINQesque API, jOOQ strongly focuses on SQL only. Unlike any of the above SQL abstraction APIs, jOOQ combines these features:

A BNF defines jOOQ's fluent API

jOOQ uses next generation techniques to implement its fluent API. These techniques involve a formal BNF notation specifying API type and method hierarchies:

With a formal BNF, jOOQ's fluent API is much more robust and typesafe, as it will dictate syntax correctness in a more formal way than ordinary builder APIs.

jOOQ embraces usage of stored procedures

When closely coupling with your favourite relational database, you will likely want to make use of stored procedures and functions, directly in your SQL. jOOQ embraces this fact and allows for typesafe embedding of stored functions.

jOOQ embraces usage of row value expressions

Row value expressions (also called tuples, records) are at the heart of SQL. Few libraries outside of the SQL world will be able to model the fact that the following predicates are type-safe:

jOOQ will leverage the Java compiler to help you check the above:

jOOQ emulates built-in functions and SQL clauses

Providing support for simple SQL clauses is easy: SELECT, DISTINCT, FROM, JOIN, GROUP BY, etc. Implementing "real" SQL is much harder, though. Take the above row value expressions, for instance. They are currently not supported in CUBRID, but you can use them nonetheless with jOOQ. jOOQ emulates missing functions and SQL clauses for you as can be seen in this syndicated blog post.

jOOQ renders specialised SQL for 14 major RDBMS vendors

Instead of generalising and abstracting advanced standard and vendor-specific SQL features, such as JPA and tools built upon JPA, jOOQ sees good things in each vendor-specific syntax element. You know your database well, so you want to leverage it, not abstract it.

jOOQ is a platform

jOOQ is much more than just a SQL library. For example, it features the very useful jOOQ Console, which helps you debug and profile your jOOQ-generated SQL statements in any environment, without the need for expensive third-party tools:

The jOOQ Console also includes on-the-fly SQL editing tools as well as breakpoint capability for advanced debugging.

More feature comparisons

More feature comparisons can be found here, in this blog post.

Getting productive with jOOQ

jOOQ is a vision where SQL matters again to the Java developer. While some have called ORM to be the Vietnam of Computer Science, jOOQ is the Peace Treaty Between SQL and Java. Using the above and many more features, you can be productive again when writing high-performing, specialised SQL against your favourite database directly in Java, typesafely compiled by your Java compiler.

By Lukas Eder, the creator of jOOQ. Follow him on Twitter @JavaOOQ.

I'm a Java and SQL enthusiast developer currently contracting for Adobe Systems in Basel, Switzerland. Originating from the E-Banking field, I have a strong Oracle SQL background. I'm the creator of jOOQ, a comprehensive SQL library for Java.

This is a guest post by Phil Jackson, the creator of ApiAxle open source API management and analytics solution. If you develop or use an open source application and would like to tell the world about it, CUBRID open source database project is accepting guest posts.

ApiAxle is an API management solution which is open source and free. The basic premise is that you build your API, put ApiAxle in front of it and it will handle user authentication, rate limiting, statistics, etc. I represent a business which generates a revenue through support and consultancy, but first and foremost I am a developer who loves APIs and the idea of companies exposing their data to enable people to build some brilliant things. As a company we are enjoying building a great product and watching it gain traction amongst fellow hackers.

The API problem

We noticed a space in the market for an open source, on-premise proxy which did not cost the earth and did not involve sending your data over multiple, high-latency hops out into the cloud.

Where a developer these days can type apt-get install nginx to get a solid webserver, there was not really an equivalent for an API management system. Building out the features we provide can be a time-consuming, monotonous and error-prone process that we really hope people do not keep having to perform.

The solution

That is where we come in. We want to bury our head in security documentation and RFCs so that you can concentrate on making a great API. With a few simple commands you can be up and running within 20 minutes. Within 30 you can be on-boarding customers, authenticating them and getting detailed statistics about their usage. You will also get caching, rate limiting, HTTPS support and a highly configurable logging system.

How it works

There are three components in ApiAxle:

The REPL

You probably want this first. The repl allows you to configure ApiAxle from the command line. Setting up an API and API keys ready for the proxy to work with is easy. It fires up an instance of ApiAxle’s own HTTP API in the background and uses that to modify aspects of the system. Anything you can do in the REPL you can do programmatically with the API too.

Install:

Start:

Configure an API and a new key to use the API with:

The Proxy

The kernel of the system. This goes between the Internet and your API and does the authentication, throttling, caching and statistics collection. It’is fast, secure and easy to setup. You will need either the REPL or the API to configure it first.

It does not matter what your API actually outputs - ApiAxle never modifies the body of a response. With regards to errors (e.g. user over quota) you can tell ApiAxle what format they should be in. We support XML or JSON. If you have Node.js installed, installation is as simple as:

Then start the proxy with:

The API

Bear with me, this gets a bit meta. This is ApiAxle’s own HTTP API which gives you full control over your APIs and the API keys and keyrings used to access them. You can view statistics about individual APIs and API keys from week long granularities right down to near-real-time hits at a single second granularity.

Install:

Start:

Find out which APIs you have configured:

Where we are headed to

We have lots planned, to summarise:

• Client drivers for the API. Demand is high for PHP and Ruby so we will get them done ASAP.
• Now that OAuth2 has been ratified we will be working on getting that in as an authentication method.
• We are working on a dashboard which will give you a way to manage your APIs, keys, keyrings and give you a way to view real-time and historical statistics - this will be a paid-for service.
• We will be pushing out a user registration system soon so that you can on-board and bill customers without any manual intervention.

So the future is exciting. We are really looking forward to meeting more developers that are interested in APIs and the huge ecosystem that is formed around them. Please feel free to get in touch with any questions or just to say hi!

By Phil Jackson, the creator of ApiAxle. Follow him on Twitter @philjackson.

After receiving his computing degree from Teesside University, Phil became a software engineer. As he moved through industry, he found himself becoming increasingly fascinated with APIs and, after helping the BBC write their iPlayer API, founded Qwerly, a company that aggregated social profile information and offered it to companies for insight/marketing purposes. After selling Qwerly to a competitor, taking some time off, Phil wrote the code which would eventually make up ApiAxle. Now he's running ApiAxle full-time and hopes it will become the ubiquitous, open source tool for managing APIs.

Three weeks ago on behalf of the CUBRID team a few of my colleagues and me have attended and given talks at two international conferences. Today I would like to share my impressions of these events. I will write a separate post about various sharding solutions introduced at these conferences. So, stay tuned!

RIT++

The first presentation at RIT++ (Russian Internet Technologies) was held on Monday April 22nd, 2013, in Moscow, Russia. The second one at Percona MySQL Conference & Expo was held on Wednesday the same week on April 24th, 2013, in Santa Clara, CA, US. At both conferences the agenda was the same: "Easy MySQL Database Sharding with CUBRID SHARD". At RIT++, though, the presentation was given in Russian language. Very exciting! The following is a list of resouces related to the talks.

• The presentation abstract in English
• The presentation abstract in Russian
• Slideshare in English
• Slideshare in Russian
• Full recording at Youtube

This was the third time we, the CUBRID team, have attended the conferences organized by Russian Ontico company. Previously we have attended to RIT++ 2012 and HighLoad++ 2012 conferences. This year at RIT++ 2013 there were over 800 attendees, and 13 categories of talks ranging from client-side development to server-side, to database scalability, to project management, to analytics, and so on. Annually after the conference is over the organizers conduct after-event survey and assess the past experience. I think because of users' past feedback this year RIT++ organizers have accepted more talks related to client-side development than usually.

Besides us from Korea, there were presenters from the States, representing Facebook, and Brasil, representing PUC-Rio University of Brasil. My personal impression was that this year there were fewer foreign speakers than last year at RIT++ or HighLoad++.

At my session about MySQL database sharding with CUBRID SHARD there were over 100, close to 150, I guess attendees. The audience welcomed my speech in Russian language very well. Next time I should talk in Russian again. They like it! When the presentation was over, there were slew of questions. I think CUBRID SHARD as an easy sharding middleware for MySQL was received very well.

To my surprise there were many questions unrelated to CUBRID SHARD. The audience asked a lot about CUBRID itself and its HA feature. Later I learned that many attendees listened to my talks about CUBRID open source relational database system from the last year. One from the audience said that he'd been looking into CUBRID for a while already and was considering to use it in production. His most favorite feature in CUBRID was its built-in support for HA and very clever 3-tier architecture. Overall the unofficial Q&A session lasted for over 1 hour 30 minutes.

It was a great experience for me to present CUBRID SHARD at RIT++ this year and a great opportunity to our CUBRID team. The conference lasted two days, but I could not attend the second day as I had to head to Santa Clara, CA, to give a talk at Percona MySQL Conference & Expo.

Percona

It was the first time I have talked at Percona. Previously we have spoken at OSCON 2011 about CUBRID HA, and 2010 MySQL Conference & Expo about CUBRID Database. When compared to OSCON, Percona MySQL conference was a lot more specific (obviously about MySQL). There were more quality talks about scalability and performance tuning. If I was to choose where to go next year, I would definitly select Percona. That interesting it was!

Unlike at RIT++, our session at Percona conference had attracted only about 20 attendees. The presentation went well, but I should accept that the number of listeners plays a big role. There were fewer questions, less enthusiasm. On the other hand, Facebook, two Percona, Continuent, and Tokutek presentations, which were held at the same time at 3:30 PM, attracted hundreds of listeners each. After realizing this I came to a conclusion that it is the brand recognition that plays a significant role in attracting listeners. Even though NHN is very popular in Korea and Asia in general, it is almost unknown in Western countries. In fact, when I asked the audience at Percona if they had ever heard about NHN, their answer was negative. Very pitty. I think NHN has to seriously reconsider its strategy on increasing its worldwide brand recognition. Nevertheless, I am very glad we had this chance to present our open source sharding middleware at a well-known conference like Percona.

Like I mentioned at the beginning of this post, I will write another post covering various sharding solutions presented at Percona conference. It was very interesting to learn about different techniques used by large scale service providers who have developed their own sharding solutions.

After my presentation was over and I had answered all the questions, I headed to one of the lounge rooms where I had made an oppointment to meet with Ryan Walsh, a Corporate Account Executive at Percona. We have discussed about various opportunities for cooperation between Percona and NHN, the company behind CUBRID development.

Percona is a widely-known and reputable MySQL support and consulting company. It is known to be the oldest and largest independent company which provides not only MySQL support, consulting and training but also develops a custom MySQL server, i.e. provides patches, "backport changes to older MySQL versions to obtain a key patch without a full version upgrade".

During our conversation Rayn had introduced his company and told about large scale cases their company has worked on so far. One that I would like to mention today is that some of the services at Amazon Web Services have been actually developed by Percona. Amazon RDS was said to have been developed by Percona team. Percona database tools seem to work with RDS natively.

Also Percona is cooperating with HP to build RedDwarf DaaS as part of the OpenStack open source cloud project. At Percona conference HP engineers have presented how to use RedDwarf APIs to use and administer the features of Percona Server. Such vast knowledge and experience of Percona in bulding cloud database services may be quite benefitial to NHN to develop and provide its own cloud computing service.

Overall, both presentations went well. I have talked to many attendees and answered to quite a lof of their questions about CUBRID SHARD and CUBRID Database. One thing which requires more attention from NHN is its global brand recognition. The more developers will recognize NHN and its services, the more will be eager to listen to and learn from NHN enginneers.

If you have any feedback or suggestions, feel free to comment below. Also you should follow us on twitter here.

With JCA, even developers who do not have specialized knowledge of encryption can successfully implement security-related functions. You don't need to use algorithms like those you had to rack your brain for a long time to understand in computer science classes and cryptology-related classes. JCA allows you to implement the algorithms with a few lines of codes. Of course, utilizing the APIs well will be highly valuable for business. But, it does not mean that you do not need to understand how JCA runs. Understanding how JCA runs internally will be important to using the functions more efficiently.

To be a better software developer and architect, you may need to trace how the result, JCA, was created from the cryptology and security-related algorithms. This article is a summary of JCA architecture that I learned while producing the nClavis (Symmetric-key cryptography) at NHN. Of course, I do not understand all of JCA yet. However, I was so happy to understand JCA at this level that I decided to write this article to share my experience with you.

Design Principles

As I mentioned, JCA is a Java security platform, based on the provider structure, having implementation independence, implementation interoperability, and algorithm extensibility.

An application can utilize the information protection encryption technology just by requesting security services on the Java platform, without implementing security algorithms. JCA-provided security services are implemented by the provider mounted on the Java security platform. An application can introduce a variety of security functions by using several independent providers. The list of providers is described in the jre/lib/security/java.security file. The Java platform includes many providers and installs them by default when JRE is installed.

Code 1: java.security file.

The providers mounted on the Java security platform by default are compatible with all Java applications and so widely used to regard them as trusted. Of course, JCA supports mounting of custom providers for applications which want to introduce the latest security technology that has not been implemented yet.

Architecture

Cryptographic Service Providers

All providers are an implementation of java.security.Provider. This provider implementation includes the list of security algorithm implementations. When an instance of a specific algorithm is necessary, the JCA framework searches the proper implementation class of the corresponding algorithm from the provider repository and creates a class instance. The providers defined in the java.security file are included in the repository by default. In this way, a provider can be statically included. In addition, it can be dynamically added in runtime. When several providers are defined, they may implement an identical encryption algorithm in different ways. In this case, an application can specify the provider or specify the preference in the repository.

To use JCA, an application simply requests a specific object type (such as MessageDigest) and an algorithm or service (e.g., MD5). Then, the application obtains an implementation from one of the installed providers. Of course, it can explicitly request an object of a specific provider.

Code 2: Requesting an Object of a Provider.

Figure 1: Provider Framework of JCA (Source: http://docs.oracle.com/javase/6/docs/technotes/guides/security/crypto/CryptoSpec.html).

Oracle JRE (Sun JDK) has a variety of providers (Sun, SunJSSE, SunJCE, and SUnRsaSign) included by default. The criteria of classifying the providers are made based on the production process; functions or algorithms used by each provider are not so different from each other. JRE except Oracle has no need to include the providers mandatorily. Therefore, it is not recommended to implement an application in the provider-dependent way. All encryption technology implementations required by an application are provided by default and implemented with a fully reliable level. Therefore, developers do not need to pay attention to a provider itself.

Key Management

Two of the most important things in JCA are provider and key management. Java uses a kind of key database, called "keystore", to manage the key/certificate repository. KeyStore can be usefully used in an application that needs information for authentication, encryption, or signature.

An application can access the KeyStore by using the implementation of the java.security.KeyStore class. The default KeyStore implementation is provided by Sun Microsystems (the package name still starts with com.sun even though Oracle acquired it long time ago :D). The KeyStore is created as a file with the naming rule of “jks”. It can also be converted to the type of “jceks” or “pkcs12” in order to suppoprt applications which use another format of KeyStore implemetation.

"jceks" format is in PBE format which uses triple DES, which uses even stronger encryption algorithm than "jks" type, to protect KeyStore.

"pkcs12" format is a standard syntax to exchange personal information, based on RSA. For machines, applications, and browser Internet kiosks that support this standard, users can export, import, or activate the personally identifying information (certificates for identification, pkcs12 format certificates). Safari, Chrome, and IE browsers follow this standard. Therefore, when the pkcs12 certificate file is installed once, it is applied to all browsers. However, Firefox does not follow this standard.

KeyStore is to save and to manage SecretKey (SymmetricKey), Public/Private KeyPair (AsymmetricKey), self-signed certificate, and the certificates signed by trusted CA (Certificate Authority) or the private CA on a file. Here, experienced developers may recall OpenSSL which was used to create a certificate file. Both certificate files have the same purpose but different file format. However their formats are convertible.

Java even provides 'keytool' command-line utility in JDK_HOME/bin directory, which is similar as OpenSSL's utility. Therefore you can handle certificates using a keytool on Windows, unlikely OpeSSL only on Linux. Of course, this tool runs with the KeyStore implementation provided by Oracle JRE. If JDK is installed in the system, a certificate can be created by using the keytool. However, note that the keytool can provide the functions at the same level provided by OpenSSL from JAVA. A KeyStore file created by using the keytool is compatible with the lower Java versions, so you do not need to worry about it.

In-depth 1: Certificate.

Here, I need to address the correct meaning of certificate. In a narrow sense, KeyStore is a kind of certificate. A certificate is used for two purposes; a "lock" required for encrypting the information and a tool for "identification" to identify the opponent technically. The authenticated certificate used for bank transactions is a security technology which utilizes both purposes of a certificate.

The cryptographic meaning of a certificate is an electronic document that uses a digital signature to sign the public key created by the RSA algorithm (asymmetric-key cryptography) with the private key of the certificate authority (CA).

It is popular practice that a pair of keys created by using the RSA algorithm is solid. It had been proven long ago that calculating the other key by using one key within a meaningful time is impossible. So, why is electronic signature required? When "A" and "B" communicate with each other by encrypting their data, A opens its public key and then protects the private key paired with the public key. Then B encrypts the data to be sent to A by using the public key of A. In this case, there is a problem of how B will know it can trust the public key; is it really provided by A? When a malicious attacker "C" disguises its public key as A's to deceive B, the public key cannot be used. To solve this problem, a trusted certificate authority "D" is necessary. The CA has a chain structure from the top root certificate authority to the sub certificate authority. In this structure, the upper layer certifies the lower layer by using signature. As it had been rooted as a worldwide standard so long ago, the signature chain of the certificates around the world includes a few common top root CAs (e.g. VeriSign, Thawte). Countries of which IT communication environment reaches a certain standard have their national root CA (e.g., KISA in Korea). The top root CAs may have a circular structure that allows signatures with each other. In some cases, one CA can sign the public key with its private key.

So, the signature of CA should not be valid limitlessly but updated regularly or irregularly by other security events.

It is possible for an individual or a company to establish a private certificate authority if required. Certificates signed by the private certificate authority have a more complex certification process. You may have seen the following Figure 2 on your browser:

Figure 2: Browser Display when Private Certificate Authority is used.

It is displayed when the website uses the private certificate authority. On the browser (even though it is not recommended), the user can skip this situation by clicking the mouse. However, for server-to-server connection, the opponent's certificate issued by the private certificate authority should be imported to JAVA_HOME/jre/lib/security/cacerts or added to SSLContext when creating the connection of the program code.

The certificates from official certificate authorities are widely granted as trusted CAs and included to the OS or JRE by default. Therefore, the situation illustrated in Figure 2 does not occur. If a private certificate authority can obtain the pkcs12 format certificate, the private certificate authority is considered as a trusted CA. Therefore, it can be easily installed in the system.

Code 2: Certificate Verification Program installed in the system.

@Windows>certmgr.msc
@MAC OS X>Keychain Access
@Linux> keychain

In-depth 2: HTTPS

To understand encryption, you need to understand SSL/TLS, the certificate-based cryptographic protocols, as well as certificate. The purpose of a certificate can be easily misunderstood when encrypting the HTTPS protocol communication section. When a server and a client communicate via HTTPS, if only the communication section is encrypted without identifying the client (setting the clientAuth attribute of HTTPS Connector to false in the server.xml setting of Tomcat), only a simple certification verification is executed. Symmetric-key cryptography is used for encryption of data.

Here, I will describe how a certificate is used through HTTPS.

JCA Structure

JCA structure can be described with Engine class and algorithm. In JCA, an Engine class provides interfaces for all encryption service types regardless of a specific encryption algorithm or provider. The engine class provides one of the following functions:

• Encryption operations (encryption, digital signatures, message digests, etc.)
• Creating and converting the elements (keys and algorithm parameters) required for encryption
• Objects (keystores or certificates) which imply the encryption data or can be used by an object or the upper abstraction layer

Let's take a look at the Engine classes provided by JCA and talk about encryption in detail.

SecureRandom

SecureRandom class is used to create a Pseudo Random number. In Java, random refers to Pseudo Random, as a more accurate expression. If so, are random and pseudo random different? Technically, they are different. There are two random types: True Random and Pseudo Random.

True Random is a random number which cannot be forecasted. You may say that Pseudo Random cannot be forecasted. Pseudo Random is a random progression determined by seed and a mathematical algorithm. It has a sequence which is eventually repeated even if it takes a very long time or its probability is very low. In addition, if you know the seed and the random algorithm, you can forecast the sequence of the Pseudo Random. True Random creates a random number based on atomic physical phenomena, not the mathematical way used by the Pseudo Random. If there is no hardware equipment to measure the atomic physical phenomena, e.g., electromagnetic noises and radioactive element decay, it is impossible to create True Random.

JCA SecureRandom class is an engine class that provides a powerful function to create random numbers. As I described, it is not easy to implement a True Random Number Generator (TRNG). Therefore, many implementations implement Pseudo Random Number Generator (PRNG). As mentioned before, the random level of the pseudo random is incomplete. The popular random class cannot satisfy the minimum level that is required cryptographically. Therefore, the implementation of SecureRandom should be verified that it satisfies the requirements of cryptographic level (CSPRNG).

Figure 3: Classification of Encryption Type (source: http://en.wikipedia.org/wiki/Cipher).

Now, you may ask why creating the random number is considered such an very important thing for encryption.

The core of modern cryptology is the key used for encryption. Previously, cryptology had been based on a conversion table like Base64 or UTF8 encoding. For modern cryptology, that kind of traditional method is not considered as encryption any more. The key is a random sequence generated by the random sequence generator. We naturally think of an encryption algorithm as thinking of cryptology. However, the open symmetric key encryption algorithm can be simplified to XOR operation (or multiplication/division operation) for the input values and key streams. As I said, the core is the key.

If the random level of a random sequence generator is not ensured, the entire outline of the key may be revealed to an attacker when a part of created key or some sequential random sequences are leaked out to the attacker. For modern cryptology, the key used for encryption is the core. So, the random sequence generator is very important.

JCA uses SecureRandom as the random sequence generator. Implementation of an algorithm to create random numbers is provided by a provider, like other encryption algorithms.

MessageDigest

MessageDigest is used to calculate the message digest (hash) of input data.

The purpose of message digest is the integrity check to check whether the original file is reserved as it is. Message digest algorithm processes a variable-length original message into a fixed-length hash output. Message digest algorithm consists of a unidirectional hash function, so it is not possible to draw the original value from the hash value. When A and B are communicating with each other, A sends the original message, message hash value of the original message, and the message digest algorithm to B. B calculates the message hash value by using the algorithm and original message sent from A. When the message hash value calculated by B is identical to the message hash value sent from A, it means that the original message sent from A has not been changed or modified until B receives it via the network.

Figure 4: Example of Using MessageDigest at a Download Site.

You can frequently see Checksums or digital fingerprints at download sites. It is an alternative name for MessageDigest. MD5 or SHA1 is the well-known message digest algorithm.

Signature

Signature is used to sign data and to decide validity of the digital signature with a key received during initialization. Receiving a key means that key-based encryption is executed.

Figure 5: Flow of Actions of Signature Object (source: http://docs.oracle.com/javase/6/docs/technotes/guides/security/crypto/CryptoSpec.html).

In initialization, Signature Object receives the private key and the original data to be signed as parameters and finishes preparation for signing. The sign() method of Signature signs the original data with the private key and returns Signature Bytes. To validate the signed data, the verification signature object is initialized by using the public key paired with the private key used for signing. The object additionally receives the original data, signature output and Signature Bytes, and the verify method checks whether the two parameters are identical to determine the reliability of the original data. Signature can be made only by the person who holds the private key. However, verification is made by using the public key. So, anyone who acquired the public key can perform verification.

Digital Signature vs Cryptography vs MessageDigest

For cryptography, users can select either symmetric key method or asymmetric key method based on the user's request. Digital signature is also a kind of cryptography. However, asymmetric key encryption is a prerequisite for digital signature. In addition, digital signature is a combination of MessageDigest and asymmetric key encryption. Large-capacity data with a variable length is compressed to a fixed-length format which is easy to manage by the MessageDigest and then signed with a private key to create fixed-length signature bytes. When creating a signature instance, you can see the principle of digital signature from the signature algorithm names, such as SHA1withRSA, MD5withRSA, SHA1withDSA; the signature algorithms are sent as a signature.getInstance() parameter and their names are made by combining RSA (asymmetric key encryption algorithm), MessageDigest algorithm, SHA1, and MD5.

Signed Certificate

Let's review certificate and signature, which were described in the previous in depth section, with JCA Signature object mechanism.

The above text box is the KeyStore certificate file created by using the keytool of Java. From Holder and Issuer of Certificate[1], you can see that “CN=NSYMKEY Root CA, OU=NHN NBP, O=NHN INC” has signed the certificate of Holder “O=NHN INC, OU=NHN NBP, CN= NPLAFORM, UID=1” by using its private key. The result of the signature is the Certificate Fingerprint. The length of the Certificate Fingerprint is decided by the MessageDigest algorithm (MD5 or SHA1). As following the Certificate Chain, you can see that the Holder and Issuer of Certificate[2] are identical. It means that “CN=NSYMKEY Root CA, OU=NHN NBP, O=NHN INC” is self-signed by using its private key. As the Certificate[2] has self-signed, the Certificate Chain is ended here.

Cipher Class

Figure 6: Flow of Actions of Cipher Object (source: http://docs.oracle.com/javase/6/docs/technotes/guides/security/crypto/CryptoSpec.html).

Cipher class provides encryption/decryption functions. The encryption/decryption algorithms are variously classified as follows: Symmetric bulk encryption (AES, DES, DESede, Blowfish, IDEA), Stream encryption (RC4), Asymmetric encryption (RSA), and Password-based encryption (PBE).

I will not describe classification of encryption to Symmetric and Asymmetric because the classification is so well-known.

Stream vs. Block Cipher

Symmetric bulk encryption can be classified into Stream and Block Cipher. Block Cipher encodes the data in the fixed-length block unit. Data whose length does not fit the fixed length is padded with dummy values. Bytes padded are removed while decrypting the data. This padding is executed by the padding type (e.g., PKCS5PADDING) which is sent as a parameter while initializing Cipher. On the contrary, Stream Cipher processes input data in the unit of byte or bit. Therefore, it can process variable-length data without padding.

Modes Of Operation

The important concept of Block Cipher you should know is Feedback Modes. Assume a very simple block cipher. If the input data is identical, the encrypted result is identical. From this characteristic, attackers obtain a hint to decrypt the encrypted data with a repeated same pattern.

To avoid security vulnerabilities and make Cipher more complex, Feedback Mode was introduced. Feedback Mode is an operation which combines (XOR operation) the Nth input data block (or the Nth encrypted result data block) and the N-1st input data block (or the N-1st encrypted result data block) at the Nth encryption process. Therefore, when the input data blocks are identical, the result values are different corresponding to the variables used in the previous encryption process. Note one more thing: if N = 1, any variable cannot be acquired from the N-1st encryption process. In this case, Initial Value (IV) takes the role instead of the variable in the previous process. To use Feedback Mode, the IV value should be randomly created and prepared for encryption. The IV value used for encryption should be stored because it is necessary for decryption as well.

The feedback modes provided by JCA are CBC, CFB, and OFB. The mode that no feedback mode is used is called ECB for distinction. More detailed description of each mode will not be provided here.

Figure 7 shows the importance of feedback modes. If the original image data is encrypted without using the feedback mode (ECB MODE), identical input data is used and an identical encryption result is acquired. Therefore, the entire outline is drawn up.

Figure 7: Image Encryption (source: http://en.wikipedia.org/wiki/Modes_of_operation).

Creating Cipher Object

The essential thing for creating a Cipher instance is to specify transformation. Transformation consists of encryption algorithms (/feedback modes/paddings) described before. Only the encryption algorithm values can be specified. But, the default feedback mode/padding (ECB/PKCS5Padding) is internally specified.

The Cipher class instance can be initialized by selecting one from four modes (opmode: Encryption, Decryption, Wrap, Unwrap) for initialization.

• WRAP_MODE: Wraps Java.security.Key to convert it to the byte unit for secured key transmission
• UNWRAP_MODE: Unwraps the wrapped key to the Java.security.Key object

Code 4: Encryption using SealedObject.

//Create Cipher object
Cipher c = Cipher.getInstance("DES");

c.init(Cipher.ENCRYPT_MODE, sKey);

// Create SealedObject: it is an encryped data
SealedObject so = new SealedObject("This is a secret", c);

Code 5 Decryption using SealedObject

//Note: sKey is as same as encryption key
//Note: so is SealedObject which was previously created. 
//Decryption using SealedObject #1
//Decrypt using Cipher object
c.init(Cipher.DECRYPT_MODE, sKey);

try {
	String s = (String)so.getObject(c);
} catch (Exception e) {
	//do something
};

//Decryption using SealedObject #2
//Decrypt using encryption key
try {
	String s = (String)so.getObject(sKey);
} catch (Exception e) {
	//do something
};

Message Authentication Codes(MAC)

MAC is similar to MessageDigest because it creates the hash value; however, it is different from MessageDigest in that it requires SecretKey (symmetric key) for initialization. MessageDigest allows any receiving party to execute integrity check for the received message. However, MAC allows only the party which has the identical SecretKey to execute integrity check for the received message. MAC is used among those who share the SecretKey.

Figure 8. Flow of Actions of MAC (source: http://docs.oracle.com/javase/6/docs/technotes/guides/security/crypto/CryptoSpec.html).

Conclusion

References

• http://docs.oracle.com/javase/6/docs/technotes/guides/security/crypto/CryptoSpec.html
• http://en.wikipedia.org/wiki/Modes_of_operation
• http://en.wikipedia.org/wiki/Cipher
• http://en.wikipedia.org/wiki/Stream_Cipher
• http://luxsci.com/blog/how-does-secure-socket-layer-ssl-or-tls-work.html

Node.js has been widely adopted by individual developers as well as large corporations such as LinkedIn, Yahoo!, Microsoft, and others. It has become so popular that developers have started writing and publishing so called Node Packaged Modules which further extend the functionality of the Node.js platform. In fact, there are over 17,000 registered modules at https://npmjs.org/ which have been downloaded over 12,000,000 times during the last month only. That popular the Node.js platform is.

node-cubrid

To allow Node.js developers to connect and work with CUBRID Database Server, we have developed the node-cubrid module and published it at NPM.

node-cubrid provides a set of APIs to connect to and query CUBRID databases. Besides the database specific APIs, the module also supplies several helper APIs which are useful to sanitize and validate user input values, format and parameterize SQL statements.

Compatibility

node-cubrid has been developed in pure JavaScript, therefore it has no dependency on any external library. This allows users to develop CUBRID Database based Node.js applications on any Node.js compatible platform such as Linux, Mac OS X, and Windows. For the same reason node-cubrid is designed to work with any version of CUBRID RDBMS. However, for the time being it has been tested only with CUBRID 8.4.1.

This is different from other CUBRID drivers such as PHP/PDO, Python, Perl, Ruby, OLEDB, and ODBC which have dynamic dependency on CUBRID C Internface (CCI). Since CUBRID is available only on Linux and Windows OS, these drivers are also limited to these platforms as well as specific CUBRID versions. However, CUBRID’s Node.js as well as ADO.NET drivers do not have any dependency, therefore can be used on any platform where that particular run-time environment is capable of running on.

Installation

Installing and using node-cubrid is easy. To install, one has to initiate npm install command with node-cubrid module name as an argument in the directory where a Node.js application is located.

This will install the latest version available at https://npmjs.org/. Once installed, the module can be accessed by requiring the node-cubrid module:

The node-cubrid module exports the following properties and functions:

• Helpers: an object which provides a set of helper functions.
• Result2Array: an object which provides functions to convert DB result sets into JS arrays.
• createDefaultCUBRIDDemodbConnection(): a function which returns a connection object to work with a local demodb database.
• createCUBRIDConnection(): a function which returns a connection object to work with a user defined CUBRID host and database.

Request flow in node-cubrid

The request flow in node-cubrid module looks as illustrated below.

Because node-cubrid is developed to take the full advantage of JavaScript and Node.js programming, when executing a SQL statement in node-cubrid, developers need to listen for an EVENT_QUERY_DATA_AVAILABLE and EVENT_ERROR events, or provide a callback function which will be called once there is a response from the server.

When the request is sent to the server, CUBRID executes it, and returns the response, which can be either a query result set, or the error code. It is by design that CUBRID does not return any identification about the request sender. In other words, in order to associate the response with a request, the driver has to have only one active request which can be the only owner of this response.

For this reason, if a developer wants to execute several queries, they must execute them one after another, i.e. sequentially, NOT in parallel. This is how the communication between the driver and the server is implemented in CUBRID and many other database systems.

If there is a vital need to run queries in parallel, developers can use connection pooling modules. We will explain this technique in the examples below.

Using node-cubrid

Establishing a connection

First, user establishes a connection with a CUBRID server by providing a host name (default: ‘localhost’), the broker port (default: 33000), database username (default: ‘public’), password (default: empty string), and finally the database name (default: ‘demodb’).

The above code illustrates a callback style when a function is passed as an argument to a connect() API which is called if the connection has been successfully established. Alternatively, developers can write applications based on an event-based coding style. For example, the above code can be rewritten as:

If you prefer the event-based coding style, refer to the Driver Event model wiki page to learn more about other events node-cubrid emits for certain API calls.

Executing queries

Once connected, users can start executing SQL queries. There are several APIs you can use to execute queries in node-cubrid:

1. query(sql, callback);
2. queryWithParams(sql, arrParamsValues, arrDelimiters, callback);
3. execute(sql, callback);
4. executeWithParams(sql, arrParamsValues, arrDelimiters, callback);
5. batchExecuteNoQuery(sqls, callback);

Eventually all of the above APIs execute given SQL queries. The difference is that query* APIs return data records while *execute* APIs do not return any record. So basically, you would use query* with SELECT queries while *execute* with INSERT/UPDATE/DELETE queries.

Executing queries with parameters

queryWithParams() and executeWithParams() APIs allow developers to bind values to parameterized SQL queries. Though “binding” in node-cubrid does not infer a communication with the server, the module merely replaces all ? placeholders with the given arrParamsValues values which are wrapped with arrDelimiters delimeters. Thus, you can bind values as follows:

The same can be done with non-result SQL statements like:

If you need to insert multiple records at once in the form of VALUES (...), (...), ..., you can use helper functions to manually populate ? placeholders with values as shown below.

Fetching more data

Sometimes, when quering a database, it happens that the results set is quite large that it has to be retrieve in multiple steps. Below you can see how to keep fetching more data until all data is retrieved.

The above are the APIs developers will use most of the time.

Using a connection pool manager

node-cubrid does not provide connection pool manager. However, at some point developers may want to execute multiple queries at the same time. In such cases, users can use generic-pool, also known as node-pool, as a pool manager for CUBRID connections.

To install generic-pool type the following in the terminal.

The following example shows how to configure generic-pool to create and destroy CUBRID connections.

Then, the connection pool manager can be used in your application as follows.

Using node-cubrid with async module

node-cubrid module provides ActionQueue helper module which provides the waterfall functionality of async module. You can use ActionQueue as follows:

The above is identical to async’s waterfall function shown below.

Roadmap

At the time of writing this artile node-cubrid version 1.0.1 stable was the latest release. In the future version we plan to improve node-cubrid a lot to make it more convenient for developers to code. For example, developers will be able to bind values with a single object parameter. Its properties and their values will serve as column names and values in the SQL statement. Very convenient which also increases the code readability.

We will also add new APIs to retrieve values set for server configuration parameters. As of version 1.0.1 node-cubrid does not return the number of affected rows after having executed write queries. This will also be implemented. In addition to this, there will be APIs to obtain table schema information which will be very benefitial for ORM developers.

Besides these, in the upcoming version node-cubrid will allow to connect to a CUBRID Server using a connection URL, the same API we already provide in all other drivers. This will allow to pass a list of alternative hosts for broker level failover, specify the query timeout duration, etc.

We plan to add many new functionality to node-cubrid. If you have a specific request, please create an issue in CUBRID JIRA issue tracker, or let us know by IRC, Twitter, or Facebook. We will be glad to review your request. If you have specific questions about CUBRID or node-cubrid module, you can ask at our Q&A site.

These days at NHN we use CUBRID for our services more often than MySQL. This article summarizes the differences between MySQL and CUBRID Database, the knowledge that several departments at NHN have obtained as they have changed their database from MySQL to CUBRID. This document is based on MySQL 5.5 and CUBRID 8.4.1.

The differences can be classified into three types:

1. Column Types
2. SQL Syntax
3. Provided Functions

Differences in Column Types

Case-sensitiveness of Character Types

Basically, MySQL is not case-sensitive for character type values when the query is executed. Therefore, to make the character type be case-sensitive in MySQL, you should add an additional binary keyword when creating a table or a query statement. On the contrary, CUBRID is basically case-sensitive for character type values when the query is executed.

The following example shows how to indicate that a target column is BINARY when creating a table in MySQL.

This example shows how to indicate that a target column is BINARY when executing a SELECT statement in MySQL.

To make CUBRID be case-insensitive, just like MySQL, apply the UPPER() function or the LOWER() function to the target column as shown in the following example.

As shown in the example above, when UPPER() function is not applied the data is returned as they were inserted. When we apply UPPER() function to the ORDER BY column we can obtain case-insensitive results. In this case even if there was an index defined on the name column this index cannot be used by ORDER BY due to the existence of UPPER() function, thus no optimization can be applied. If there was no UPPER() function, ORDER BY would fetch the data in the order of the defined index. In order to optimize ORDER BY, you may consider creating a separate column that is totally upper-cased or lower-cased and configuring index in the column.

The following is an example of separately adding the sorting column name2 which is not case-sensitive.

This coming fall we will release a new version of CUBRID under the code name "Apricot" which will introduce function based indexes. Then, you will no longer need to create a separate column and create an index on it. You will be able to do the following:

Automatic Type Conversion for Date Type

MySQL is very flexible in converting the type. It accepts character string input in the numeric type and vice versa (number input in the character string type). It also accepts numeric input in the date type.

From version 8.4.0, CUBRID supports flexible type conversion, allowing for character string input in the numeric type and number input in the character string type. However, unlike MySQL, CUBRID does not accept number input in the date type.

The following is an example of inputting numbers in the date type dt column in MySQL.

The following is an example of inputting numbers in the date type dt column in CUBRID. You can see that an error is returned as a result value when numbers are input in the date type.

When an error occurs as a result of executing the date function, MySQL returns NULL and CUBRID returns an error by default. To make CUBRID return NULL for such cases, set the value of return_null_on_function_errors system parameter to yes.

The following example shows that NULL is returned when an invalid parameter has been entered in the date function of MySQL.

The following example shows that an error is returned when an invalid parameter has been entered in the date function of CUBRID when the value of system parameter return_null_on_function_error has been set to no which is the default value.

The following example shows that NULL is returned when an invalid parameter has been entered in the date function of CUBRID when the value of system parameter return_null_on_function_errors has been changed to yes.

Result Value Type of Integer-by-Integer Division

When integer-by-integer division is performed, MySQL prints the output value as a DECIMAL (m, n), but CUBRID prints it as a rounded INTEGER. This is because when each operand is of the same type, the result in CUBRID is printed as that same type. In this case to display the result value as a REAL number, apply the CAST() function to any or all operands in the fraction.

The following shows an example of executing integer-by-integer division in MySQL. The result value will be printed as a real number type.

The following shows an example of executing integer-by-integer division in CUBRID. The result value will be printed as an INTEGER type.

The following shows an example of executing integer-by-integer division by using the CAST() function in CUBRID. The result value will be printed as a real number type.

The following shows an example of executing integer-by-real number division in the CUBRID. Since one of the input values is a real number type, the result value will be printed as a real number (DOUBLE) type .

Processing SUM result which is larger than the Maximum Value of Input Value Type

How will the result be printed if the result of SUM is larger than the maximum value of the input value type?

MySQL converts the result of SUM to a pre-defined large DECIMAL number type. However, CUBRID processes the result as an overflow error. It means that in CUBRID the type of the input column decides the result type. Therefore, to avoid overflow errors in CUBRID, you should convert (CAST()) the input column type to a type that can accept the SUM result value before executing operations.

Converting the type when executing the CAST() function incurs some additional cost, so I recommend to decide the column type considering the result value of functions you plan to use.

First, configure the same table in MySQL and the CUBRID as follows.

MySQL successfully prints the value because the result value of executing SUM is smaller than the result type. However, as the print type is decided by the input type in CUBRID, an overflow error occurs because the resulting value of executing SUM is larger than the result type.

The following is an example of converting the column type and then executing SUM in CUBRID. You can see that the value has been successfully printed.

The following is an example of executing SUM after deciding the column type by considering the SUM result value size in the stage of creating a table in the CUBRID. You can see that the value has been successfully printed.

Result Value Type of VARCHAR Type

MySQL and CUBRID both allow for numerical operation when the value of the column that is VARCHAR type is a string consisting of numbers. In this case, the operation result type is DOUBLE for both (however, saving a value that needs numerical operation as a string type is not recommended; it is used just for explanation here).

The following is an example of comparing the query result type of the MySQL to that of CUBRID.

In MySQL, if there is a character (not a number) that exists on the column value, the character is considered as 0 for operation. However, CUBRID prints an error that it cannot convert the character to double type. See the following example.

Difference of SQL Syntax

Supporting START WITH … CONNECT BY

CUBRID supports the START WITH … CONNECT BY syntax which can express the hierarchy that MySQL does not support as a query. This is a part of Oracle SQL compatibility syntax.

As an example, using the following data we will print "managers" and "juniors" by sorting the result values within the same level in the order of "join date". id is the employee ID of a junior staff and mgrid is the employee ID of a manager.

MySQL does not support hierarchy statement. Therefore, to print the result value satisfying the above request, you should execute several query statements in the following order.

1) First, print a "level 1" employees whose mgrid is NULL.

2) Then print the "level 2" employees whose mgrid is 1.

3) Then print the "level 2" employees whose mgrid is 2.

4) Then print the level "3 employee" whose mgrid is 6.

On the contrary, as CUBRID supports the hierarchical queries, a single query statement can be created as follows.

The above code means that a parent node (manager) and the child node (junior staff) should be printed in order of the values of child nodes (junior staffs) with the same level in the order of the id.

Including Disaggregate Item in the SELECT LIST which Includes Aggregate Function

ONLY FULL GROUP BY

When executing the GROUP BY clause, both MySQL and CUBRID basically allow the disaggregate column that is not included in the GROUP BY clause to be included in the SELECT list. However, the disaggregate column not included in the GROUP BY clause selects the record value which is fetched among various values for the first time. Therefore, note that the value may be different according to the fetch orders of MySQL and CUBRID. For the disaggregate column, it is not clear which proper value should it select among several values. Therefore, enable the ONLY FULL GROUP BY function to not expose the column that is not included in the GROUP BY clause to the SELECT list.

To enable the ONLY FULL GROUP BY function, for MySQL set the sql_mode value in the my.conf configuration file to ONLY_FULL_GROUP_BY. For CUBRID, set the only_full_group_by value in the cubrid.conf configuration file to yes.

From version 8.3.0 CUBRID supports the only_full_group_by system parameter. Before 8.3.0, the ONLY FULL GROUP BY function always ran as if it was enabled.

The following example shows the result of executing the GROUP BY clause in MySQL and CUBRID while the ONLY FULL GROUP BY function is enabled.

When There Is No GROUP BY Clause, Is it possible to Query Even if the Disaggregate Item Exists in the SELECT LIST?

MySQL has an item that uses the aggregate function in the SELECT list and executes one value that has been fetched initially when there are several values for another item. However, in this case, CUBRID considers that it cannot decide the value and returns an error.

If there is a disaggregate column by the aggregate function in the SELECT LIST, it means that any value among various values for the column will be randomly selected. Therefore, it is recommended not to execute that kind of query.

Configure identical data in MySQL and CUBRID as shown below.

In this case, the following query can be executed for both MySQL and CUBRID.

In MySQL, when there are several values for column a, the column value of the record which has been fetched for the first time is calculated. In this case, the value of column a is floating so this processing is not proper.

In CUBRID, when there are several values for column a, CUBRID considers that it cannot determine which record column value must be calculated and returns an error.

When an error is returned as shown above, if you want to make it executable, change the a to MAX(a) or MIN(a) in order to get only one value for the INTERVAL a as shown below. If the value of a is always the same, the result of executing this query in MySQL before changing the a will be same as the result in CUBRID after changing the a.

Using a Reserved Word as Column Name and Table Name

Both MySQL and CUBRID do not allow reserved words for column name, table name, and alias. To use reserved words for identifiers such as column name, table name, and alias, the reserved words must be enclosed with quotes (" or `). In CUBRID, square brackets ([ ]) are allowed as well as quotes.

Each DBMS supports different reserved words. For example, MySQL does not use ROWNUM, TYPE, NAMES, FILE, and SIZE as reserved words, however, CUBRID uses them all. For more details on CUBRID reserved words, see the online manual CUBRID SQL Guide > Reserved Words.

The following example is using reserved words as column names in CUBRID.

Functional Differences

Supporting Descending Index

Technically, in MySQL, you can create a descending (DESC) index. However, the descending index is not actually created. However, the descending index is actually created in CUBRID.

The following example shows creating the descending index for the ndate column in the CUBRID.

Note that you can create an index in CUBRID as using the REVERSE keyword. Then the index is created in the same order of having DESC in the column.

The following example shows how to use the REVERSE keyword and the DESC keyword in order to create indexes for the ndate column in CUBRID in descending order.

... which is same as...

In MySQL, creating indexes in descending order is not allowed. Therefore, you can create an ascending index after adding an additional column in order to input values in the reverse order. For example, convert the value of DATE"2012-05-18" to the numeric value -20120518 (negative value) and then input an additional column.

The following example shows creating an ascending index for the reverse_ndate column in MySQL.

1)

2)

3)

4)

However, when you create a descending index in the ndate column, or when you create the reverse_ndate column with reverse order values and then create an ascending index, if one is UPDATE or DELETE and the other is SELECT, UPDATE, or DELETE and both of indexes are simultaneously scanned, the index scan will be made in reverse to each other. This case increases the possibility of a deadlock so you must note this while creating a descending index.

Both MySQL and CUBRID have a bi-directional link between index nodes. Therefore, the ascending index can be used when the query planner determines that the cost of reverse scan using an ascending index will be lower than the sequential scan using the descending index even if ORDER BY DESC is performed in the query, The reverse scan takes the advantage only when the number of records to be scanned is relatively small.

The following example shows using an ascending index when ordering in the descending order in CUBRID.

Supporting ROWNUM

ROWNUM is a function to number the result rows of the SELECT query from 1 in the ascending order and used as a column of a table. With ROWNUM, you can add a serial number for printed records and limit the number of records of the query result by using the conditions of the WHERE clause.

CUBRID supports ROWNUM while MySQL does not. So if you want to add a serial number to the records in MySQL, you should use a session variable. Both MySQL and CUBRID support LIMIT … OFFSET in order to limit the number of records of the query result. However, the following discussion will address ROWNUM.

The following two examples are used to print serial numbers for the records of the query result.

In MySQL, process ROWNUM by using a session variable.

In CUBRID, execute ROWNUM.

Oracle also supports ROWNUM. However, in Oracle, ROWNUM is executed first in the syntax including the GROUP BY clause and the ORDER BY clause, and then the GROUP BY clause and the ORDER BY clause are executed later. Therefore, the printed ROWNUM> is not in the sorting order. So, if you want to execute the query including the GROUP BY clause or the ORDER BY clause first, make the query as a subquery of the FROM clause and execute ROWNUM for that.

The following example shows printing ROWNUM in the order of ORDER BY in Oracle.

CUBRID supports the GROUPBY_NUM() function and the ORDERBY_NUM() function which produces the result of GROUP BY and ORDER BY printed in the order.

In the example of MySQL, since there was no ROWNUM and a session variable @rownum has been used, the final order result was used to print the result even though there were the GROUP BY clause and the ORDER BY clause. However, since CUBRID uses ROWNUM, you should use the GROUPBY_NUM() function and the ORDERBY_NUM() function if you want to keep the order of printed numbers without using a subquery.

The following two examples show the difference between two cases; one uses GROUPBY_NUM() function while the other does not. Also it shows limiting the number of result records by using ROWNUM and then executing the GROUP BY order in the corresponding records.

The following example shows limiting the number of result records by using GROUPBY_NUM() for the record set ordered by using GROUP BY.

The following two examples show the difference between two cases; one uses the FOR ORDERBY_NUM() function while the other does not. Also it shows limiting the number of result records by using ROWNUM and then executing the ORDER BY order in the corresponding records.

The following example shows limiting the number of the result records by using ORDERBY_NUM() for the record set ordered by using ORDER BY.

Difference of AUTO_INCREMENT

What should we do if we must know the primary key created after executing INSERT?

The LAST_INSERT_ID() function is used to get the last value which has been INSERTED to the column after INSERTING a value to the AUTO_INCREMENT attribute column within a program connected to the database. For example, you can use it when you want to INSERT a value in the table where the primary key is included and then insert a foreign key value from another table in the table as the value of the LAST_INSERT_ID() function. Both MySQL and CUBRID support this function. However, an error may occur in some specific cases, so I recommend you to directly get the AUTO_INCREMENT value rather than using the LAST_INSERT_ID() function in CUBRID. Below I will explain these specific cases.

In MySQL or CUBRID, when a record is INSERTED to the table which includes the AUTO_INCREMENT attribute column, the value of the column is automatically increased by 1.

After executing INSERT to Table A, if you are trying to INSERT a foreign key value to Table B (which refers to Table A) by using the LAST_INSERT_ID() function value and an application or a server is unexpectedly and abnormally terminated, what will happen? (on the assumption that the procedure is one transaction, of course)

In MySQL, the procedure, including all values increased by the AUTO_INCREMENT, is rolled back. If the AUTO_INCREMENT value of the record to execute INSERT newly was 3, the AUTO_INCREMENT value becomes 3 again after transaction rollback. However, in CUBRID, the increase of AUTO_INCREMENT value is not affected by transaction rollback. Therefore, the existing AUTO_INCREMENT value of the record was 3, the AUTO_INCREMENT value becomes 4 after transaction rollback.

In MySQL, you can get the value entered in the column specified as the AUTO_INCREMENT by using the LAST_INSERT_ID() function only. However, in CUBRID, you can directly get the AUTO_INCREMENT value without using the LAST_INSERT_ID() function. It is just like using the SERIAL object of CUBRID because AUTO_INCREMENT has been implemented as a SERIAL object in CUBRID. When an AUTO_INCREMENT column is created in CUBRID, the name of the SERIAL object of the column is specified as "<table name>_ai_<column name>" internally.

The following example shows calculating the next value and the current value on the AUTO_INCREMENT column in CUBRID.

In the current CUBRID version, the LAST_INSERT_ID() function may malfunction in the following specific cases. Therefore, I recommend that you directly get the AUTO_INCREMENT value rather than using the LAST_INSERT_ID() function. The LAST_INSERT_ID() is one of the session variables that manage the values in each database connection unit. If a broker is switched in CUBRID or if a failover occurs because of master node switch in the HA environment, it may malfunction so the desired value may not be printed. It will be modified later.

In the following example, a session variable has been used as an intermediate storage for getting the next AUTO_INCREMENT value. For an application, use the variable in the program as an intermediate storage. Note that the SERIAL in CUBRID has the same function as the SEQUENCE in Oracle.

The following example shows executing INSERT the primary key and the foreign key respectively after getting the NEXT_VALUE value of the SERIAL.

At this time, the name of SERIAL created by the tblPK AUTO_INCREMENT is tblpk_ai_col. You can check that by executing:

Directly insert the tblPK AUTO_INCREMENT value in the tblPK primary key and the tblPK foreign key. You don't need to save the tblFK colfk in an additional variable since it is not referred to by the other tables.

The following example shows how to write the code that returns the primary key value when a value executes INSERT in the tblPK in the iBatis sqlmaps. For the tblFK foreign key, use the returned value from the "insert" object.

Length Difference of String Functions Such as CHAR_LENGTH

For a string function, MySQL calculates the length based on the number of characters. However, CUBRID calculates it based on the character byte length. This difference depends on the database support of the character set. MySQL supports the character set and calculates the length based on the character set. However, CUBRID does not support the character set and considers all data as bytes, so it calculates the length based on the byte length.

For UTF-8 character set, the byte length of one Korean character is 3 bytes. Therefore, the return value of the CHAR_LENGTH will be three times the number of characters in CUBRID in case of Korean characters. In CUBRID, the functions related to the character length, such as POSITION, RPAD and SUBSTR, use the byte length as the input parameter, not the character length. When specifying the length of CHAR or VARCHAR also as creating a table, the byte length is used.

In the second half of this year we will release a new version of CUBRID under the code name "Apricot" with full Unicode support where the calculation will be made based on the character length, not the byte length.

The following example shows the result of executing CHAR_LENGTH in the CUBRID.

Cursor Holdability After Commit while the Record Set Fetch

Cursor holdability is that an application holds the record set of the query result to fetch the next record even after performing an explicit commit or an automatic commit. In this connection, the JDBC specification supports ResultSet.HOLD_CURSORS_OVER_COMMIT and ResultSet.CLOSE_CURSORS_AT_COMMIT. Both MySQL and CUBRID ignore the configuration that uses the conn.setHoldability() function. MySQL always runs as HOLD_CURSORS_OVER_COMMIT, and CUBRID always runs as CLOSE_CURSORS_AT_COMMIT.

In MySQL, when conn.getHoldability() is called, the CLOSE_CURSORS_AT_COMMIT value is returned, which is opposite to the current operation. Refer to the source code of mysql-connector-java-5.1.20.tar.gz of http://www.mysql.com/downloads/connector/j/.

In other words, MySQL holds the cursor even if a commit occurs during fetch, and CUBRID closes the cursor when a commit occurs during fetch. Therefore, to hold the cursor while fetching the record set SELECT in CUBRID 8.4.x or lower versions, set the auto commit mode to FALSE and fetch the record set while not committing a transaction. (In the new version of CUBRID "Apricot" to be released in the second half of this year, the cursor will be held regardless of commit unless the record set is closed.)

The following schema is used below to demonstrate how cursor holdability works.

The following example shows a program that there are four data rows on Table tbl1 while the auto commit is set to true (autocommit=true) and SELECT and INSERT the data to Table tbl2. After executing that, MySQL holds the cursor even when auto commit is executed, so four data will be INSERT in Table tbl2. On the contrary, CUBRID closes the cursor because of auto commit when INSERT is executed. Therefore, no more fetch is executed and only one data will be INSERT in Table tbl2.

Conclusion

In this article I have reviewed many (but not all) differences between MySQL and CUBRID, which I recommend users to know before they switch from MySQL to CUBRID. It is important to fully understand these differences to successfully apply CUBRID to services.

By Donghyeon Lee, NHN Business Platform DBMS Development Lab

From the author:

"Is your baby a boy?" I am a father who is asked this question even though my little baby is a girl. I will do my best until the day CUBRID becomes a DBMS that is very familiar to users.

References

1. MySQL 5.5 Reference Manual: http://dev.mysql.com/doc/refman/5.5/en/create-index.html
2. MySQL Performance Blog: http://www.mysqlperformanceblog.com/2006/05/09/descending-indexing-and-loose-index-scan/
3. Interface ResultSet (Java Platform SE 7): http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html

In the middle of this year a news report announced that Facebook is planning to support Google's SPDY protocol in large scale, and that they are already implementing SPDY/v2. Here is the official response from Facebook that I have found on this topic. Among various efforts devised and suggested by Google to make the Web faster, I think SPDY will be the one to become a new industry standard, and it will be included in HTTP/2.0.

As an acronym of SPeeDy, SPDY is a new protocol Google has suggested as a part of its efforts to "make the Web faster." It was suggested as a protocol to use the present and future Internet environment more efficiently by addressing the disadvantages of HTTP devised in the early Internet environment.

In this article, I will provide a brief introduction to the features and merits of SPDY. I will explain about the state of SPDY support, and what to do and what to consider when it is introduced.

When was the latest version of HTTP released?

HTTP version 0.9 was first announced in 1991, and HTTP 1.0 and 1.1 were released in 1996 and 1999, respectively, and since then nothing has been changed in HTTP for the last 10 years. These days, however, a webpage has a size 20 times bigger with 20 times more HTTP requests than a webpage in the 1990s. The Table 1 below shows the data quoted from Google I/O 2012.

The capacity of the Yahoo! main page in 1996 was 34 KB. That is only 1/30 of the mean webpage capacity in 2012. There is a significant gap even between 2010 and 2012, let alone between the 1990s and 2012. This is because the mean page size and number of requests are ever increasing as User UX is becoming more and more sophisticated along with the dissemination of high-speed Internet.

The characteristics of today's webpage have changed from that in the past as follows:

• Consists of much more resources.
• Uses multiple domains.
• Operates more dynamically.
• Emphasizes security more.

Considering how today's web environment is different from the past, Google has announced the SPDY protocol, which complemented the disadvantages of HTTP. SPDY focuses especially on resolving the problem of load latency.

Features of SPDY

The Figure 1 below shows the layers of SPDY compared to the traditional TCP/IP layer model.

Figure 1: HTTP vs. SPDY.

The features of SPDY can be summarized as follows:

• Always operates on Transport Layer Security (TLS).
  • Transport Layer Security (TLS) is the next version of Secure Sockets Layer (SSL). TLS and SSL are sometimes used to refer to the same protocol because they are the name for two different versions of the same protocol, and this also applies to this article.
• Therefore, SPDY applies only to websites written with HTTPS.

HTTP Header compression

As HTTP headers have many redundant contents whenever a request is made, you can improve the performance significantly just by compressing headers. According to a report by Google, it is possible to reduce the size by 10-35% by compressing HTTP headers even in the initial request, and reduce the size of headers by 80-97% when requests are made several times (long-lived connection). Also, when the upload bandwidth is relatively small, as in mobile devices, this HTTP header compression is more useful. These days, as the HTTP header is 2 KB on average and is growing bigger, the merit of compressing HTTP headers is expected to grow in the future.

Binary protocol

As it uses binary framing rather than text-based framing, it provides faster parsing and is less sensitive to errors.

Multiplexing

SPDY handles multiple independent streams in a single connection concurrently. For this reason, unlike HTTP which handles one request at a time in a single connection with response to requests made consecutively, SPDY handles multiple requests and responses concurrently with a small number of connections. In addition, unlike HTTP pipelining in which if one response is delayed, the others are all delayed, SPDY handles each request and response independently as it uses a FIFO queue.

Full-duplex interleaving and stream prioritization

As SPDY allows interleaving in which one stream in a process can be interleaved with another and stream prioritization, data of higher priority can jump into the process of transportation of data of lower priority and can be transported earlier.

Server push

Servers can push content without client requests unlike Comet and Long-polling. Unlike methods such as inlining, SPDY supports resource caching and uses the same bandwidth as that of inlining or a smaller bandwidth. When implementing server push, however, you need to implement additional Web server application logic.

No need to re-write a website

Except for some features that require additional implementation, such as server push, you don't need to change a website itself to apply SPDY. However, the browser and the server should support SPDY. SPDY can be applied completely and transparently to browser users. In other words, there is no protocol scheme like "spdy://." The browser also displays nothing with regard to the use of the SPDY protocol.

Based on such characteristics of SPDY, the following table shows the difference between HTTP and SPDY.

Therefore, it can be said that SPDY is a protocol designed to use TCP connection more efficiently by improving the data transfer format and connection management of HTTP. As a result of such efforts, in a test with the top 25 websites, SPDY worked 39-55% faster compared to HTTP + SSL.

Why does SPDY need TLS?

Why does SPDY use TLS even though using TLS causes latency due to encryption/decryption? Google's SPDY Whitepaper states the following answer for this question: 

"In the long term, the importance of web security will be emphasized more and more, and thus we want to get a better security in the future by specifying TLS as a sub protocol of SPDY. We need TLS for compatibility with the current network infrastructure. In other words, we need it to prevent any compatibility issue with the communication going through the existing proxy.”

Despite this reason, when looking into the implementation of the actual SPDY, you can see that SPDY depends much on TLS's Next Protocol Negotiation (NPN) extension mechanism. This TLS NPN extension mechanism determines whether a request coming from Port 443 is SPDY or not and identifies the version of SPDY used by the request, to decide whether to use SPDY to handle the next communication. Without TLS NPN, you will need to get additional RTT to use SPDY.

Efforts for Standardization 

SPDY is being developed as an open networking protocol and has been suggested to IETF as a method of HTTP/2.0. SPDY is a sub-project of the Google Chromium project, and thus Chromium client implementations and server tools are all being developed with open sources.

Future of SPDY

Most recently SPDY Draft 3 was released, and SPDY Draft 4 is under development. The features likely to be added in Draft 4 are as follows: 

• Name resolution push
• Certificate data push
• Explicit proxy support

The final goal of SPDY is to provide a page within "a single connection setup time + bytes/bandwidth time".

Browsers, Servers, Libraries and Web Services Supporting SPDY

Currently a variety of browsers and servers support SPDY, and Google, who originally suggested SPDY, already provides almost all of its services with SPDY. The browsers, servers, libraries and services supporting SPDY are as follows.

Browsers Supporting SPDY

As of July 2012, the following is a list of browsers which support the SPDY protocol.

Google Chrome/Chromium

Chrome and Chromium have supported SPDY from their initial version. If you enter the following URI, you can inspect SPDY sessions in Chrome/Chromium: chrome://net-internals/#events&q=type:SPDY_SESSION%20is:active. For example, if you visit www.gmail.com, in the chrome:// tab you can see multiple SPDY sessions being created. Android mobile Chrome also supports SPDY.

Firefox 11 and later versions

While added in version 11, SPDY was not enabled by default until Firefox version 13. If you enter about:config in FF, which is the URI for Firefox settings, and see network.http.spdy.enabled, you can check whether support for SPDY has been enabled or not. Android mobile Firefox 14 also supports SPDY.

Amazon Silk

The Silk browser equipped in Kindle Fire, an Android-based e-book reader from Amazon, also supports SPDY. It communicates with Amazon EC2 service by using SPDY.

Default browser of Android 3.0 and higher

The default browser of Android 3.0 (Honeycomb) and 4.0 (Ice Cream Sandwich) supports SPDY.

For more information about which browsers support SPDY, see http://caniuse.com/spdy.

Servers and Libraries Supporting SPDY

Support for SPDY is being vitalized mainly by major web servers and application servers, and a variety of libraries that implement SPDY are also being developed.

Nginx

Nginx released a beta version of SPDY module on June 15, 2012, and has continuously provided patches. See SPDY: 146% faster Slideshare presentation by Nginx team to learn more about it.

Jetty

Jetty also provides the SPDY module.

Apache

The SPDY module for Apache 2.2 is also being developed.

Libraries

In addition, SPDY implementation structures for Python, Ruby and node.js servers have already been developed or are currently being developed. There are a variety of versions for SPDY C library, including libspdy, spindly and spdylay. A library to use SPDY on iOS is also being developed.

Netty

Netty began to provide SPDY package from its 3.3.1 version released in 2012.

Tomcat

SPDY support in Tomcat is currently under development and it should come in Tomcat version 8.

Services Using SPDY

As mentioned earlier, Google has already converted almost all of its services, including search, Gmail and Google plus, into HTTPS, and provides them through SPDY. In addition, when Google App Engine uses HTTPS, it also supports SPDY. Twitter also uses SPDY when providing service via HTTPS.

However, among numerous Web sites on the Internet, only a few websites use SPDY. According to a survey by Netcraft conducted in May 2012, of a total of 660 million websites only 339 websites are currently using SPDY. In other words, except for Google and Twitter, there is hardly any major website using SPDY.

When SPDY is Not Very Efficient

SPDY is not always fast. Sometimes you may not get any performance improvement with SPDY. Such situations are as follows.

When using only HTTP

As SPDY always requires SSL, you need additional SSL handshake time. Therefore, when you convert an HTTP site into HTTPS to support SPDY, you may not obtain distinct performance improvement due to SSL handshake.

When there are too many domains

SPDY operates by domain. This means that it requires as many connections as the number of domains, and that request multiplexing is available only in a single domain. Moreover, as it is difficult to make all domains support SPDY, you may not get the merits of SPDY when there are too many domains. Especially when a CDN does not support SPDY, you may not expect the performance improvement with SPDY.

When HTTP is not the bottleneck

For most pages, HTTP is not the bottleneck. For example, in cases when a resource can be downloaded only after another resource is downloaded, SPDY will not be that effective.

When Round-Trip-Time (RTT) is low

SPDY is more efficient when RTT is high. When RTT is very low, for example, in communications between servers within IDC, SPDY has few merits.

When resource in a page is very small

For pages with six or fewer resources, SPDY has few merits because the value of reusing connection is not significant.

Things to Do to Introduce SPDY 

When you introduce SPDY, you need to carry out the following tasks to apply it most efficiently.

Application Level

Use only one connection

For better SPDY performance and more efficient use of Internet resources, you need to use as few connections as possible. If you use a small number of connections when using SPDY, you can see the benefits such as putting data into packets in a better way, getting better header compression efficiency, reducing the frequency of checking the status of connection and reducing the frequency of handshake. Also, in terms of Internet resources, with a small number of connections, you can also have a more efficient TCP and reduce Bufferbloat.

Bufferbloat is a phenomenon in which excess buffering of packets at a router or a switch causes high latency and reduced throughput. With the idea of avoiding packet discards as much as possible and low memory prices, the buffer size of a router or a switch is continuously increasing. The packets that should have been discarded earlier could survive longer and as a result these packets hinder TCP congestion avoidance algorithm, deteriorating the overall network performance.

Avoid domain sharding

Domain sharding is a kind of expedient used to avoid the restriction on the number of concurrent downloads (in general, six downloads per hostname in modern browsers) in web applications. If you use SPDY and comply with "use a single connection" recommendation, you don't need to use domain sharding. To make it worse, domain sharding causes additional DNS queries and makes applications more complex.

Use server push instead of inlining

Inlining stylesheets or scripts is often used to reduce the number of HTTP requests, thus RTT, in web applications. However, inlining makes Web pages less cacheable and increases the size of webpages due to base64 encoding. If you use the SPDY server push feature used to push content, you can avoid these problems.

Use request prioritization

You can enable the client to inform the server of the relative priority of resources by using the request prioritization feature of SPDY. The simple common heuristic prioritization could be html > js, css > *.

Choose the proper size of a SPDY frame

Although SPDY spec allows large frames, sometimes a small frame is more desirable. This is because a small frame allows interleaving to work better.

SSL Level

Use a smaller, full certificate chain

The size of a certificate chain makes a huge influence on the performance of the initialization of a connection. If there are more certificates in a certificate chain, it will take more time to verify the validity of certificates, and more space will be occupied in initcwnd.

initcwnd: the initial value used in TCP initial congestion window and TCP congestion control algorithm. The congestion window is a sender-side window to control the size according to TCP congestion control algorithm, which is different from the TCP window, which is a receiver-side limit on the size. 

In addition, if a server does not provide a full certificate chain, the client will use additional RTT to get certificates in the middle. Therefore, if a large, incomplete certificate chain is used, it will take longer time for an application to use the connection.

Use a wildcard certificate (e.g., *.naver.com) if possible

If you use wildcard certificates, you can reduce the number of connections and use the connection sharing of SPDY. As a wildcard certificate is provided by a certification institute, however, you may need to discuss with the institute and pay extra costs.

Do not set the size of SSL write buffer too large

If SSL write buffer is too large, TLS application record will be put on multiple packets. As an application can process only after the entire TLS application record is completed, the record put on multiple packets will cause additional latency. Google servers use 2 KB buffers.

TCP Level

Set the initcwnd of the server to at least 10

Initcwnd is the main bottleneck that affects the initial loading time of a page. If you use only HTTP, you can avoid this problem by attaining the initial congestion window size of n × initcwnd by opening multiple connections concurrently. As a single connection is advantageous in SPDY, however, it is better to set initcwnd to a large value from the beginning. This value in old Linux kernels is fixed to 2-3, and the method to adjust this value is not provided. As this value was determined according to the reliability and bandwidth of TCP network when it was first considered, it is not suitable to today's TCP network with higher stability and bandwidth. The method to adjust this value was added in Linux Kernel 3.0, and the latest Linux kernels already use the default value of 10 or higher.

Disable tcp_slow_start_after_idle

The tcp_slow_start_after_idle on Linux is set to 1. This causes a congestion window to return to the size of initcwnd when the connection goes idle, and makes TCP Slow Start restart.

TCP Slow Start is an algorithm that works by sending packets to the congestion window of the initcwnd size and increasing the TCP congestion window up to the maximum value allowed by the network or up to the TCP window of the receiver side. If the initcwnd value is small, it takes more round-trips until the window reaches the maximum size allowed by the network, and as a result, the initial page loading time will increase.

As this will eliminate the advantage of a single connection of SPDY, this setting should be disabled. You can change the setting by using the sysctl command. It is also advantageous to disable this setting when using HTTP keepalive.

If SPDY is Really Introduced …

In conclusion, to actually introduce SPDY, you need to consider a variety of matters and modify applications and servers. You cannot ignore the costs of introducing the protocol, either. For a Web application written for Tomcat, which does not yet support SPDY, you should consider the cost required to change the Web application server, as well as the cost required to implement the server push functionality.

Except for the costs required to introduce SPDY, what should we take into account first when we introduce SPDY in a real service? I chose three matters to consider.

The service should be the one that already uses HTTPS

You cannot get many advantages when introducing SPDY for services using only HTTP. You should pay the costs for introducing SSL as well.

You should be able to change the Linux kernel

Even CentOS 6.3, the latest version released on July 9, 2012, still uses the kernel 2.6.32. Adjustment of initcwnd is supported only from the kernel 3.0, and you need to change the kernel if possible because the performance improvement you can get by adjusting initcwnd is very significant.

Consider the ratio of users of SPDY supported browsers

In Korea, many users still use IE which does not support SPDY. On mobile devices, iOS does not yet support SPDY, while Android 3.0 or higher supports SPDY. Therefore, until there are sufficient users of SPDY-supported browsers, you should carefully compare the advantages you can get from the performance improvement derived from SPDY with the costs required to introduce the protocol.

By Sehoon Park, Senior Engineer at Web Platform Development Lab, NHN Corporation.

At CUBRID we know how much important and difficult at the same time it is to find the right audience who would listen to what your open source project has to offer. Due to its nature, most OS projects, especially those driven by a community of likeminded enthusiasts, often do not possess the budget to reach out to target users. If you are a member of such community and would like to tell the world about it, read on. We have good news for you!

Last year we announced the CUBRID Affiliates Program through which we have already donated 3000 USD to 14 open source projects. Some of these projects provide very useful features to CUBRID Database users. This time we want to periodically introduce to our CUBRID community members one or two handpicked open source projects that we think are also potential and worth talking about. Starting from today we will accept guest posts to our CUBRID Blog from open source communities where they can introduce their software to our readers. We will handle the promotion.

Step 1: Pitch it!

We are looking for great software that will make our and our readers' eyes sparkle. Before your post gets published, we would like to read an overview of your project. Send us a brief email introducing your project: site links, introductory videos, presentations if available, what it does, what benefits it can provide to our readers. We will get back to you in no time.

Step 2: Write it!

If we accept your pitch, we will ask you to write a full post that should cover your software. You can include images and even video. In your introduction you need to tell the readers what problem your software solves, where and how a reader can take off, and, very importantly, how it differs from other existing solutions (I am sure there is at least one product you can compare with). Together with your article, send us your bio and Gravatar enabled email address.

Step 3: Get published!

We will create an account for you on our CUBRID community site and associate your email address it with. Then we will publish your post under your name so that you can receive notifications for comments as the post author. Once published, we will share your story on CUBRID Facebook, Twitter, Google+ pages as well as other networking sites like the popular DZone. On top of that we will place a 223x170 pixel banner on the top right of CUBRID Blog site, if you provide one, as well as pay at least $5 (about 11K impressions) to promote your post on Facebook timeline.

Like it? Then go on and contact us!

Last month a colleague of mine has already covered Vert.x, a relatively new Java application framework which provides noticable performance advantage over competing technologies and features multi programming language support. The previous article has explained us about the philosophy of Vert.x, performance comparison with Node.js, internal structure of Vert.x, and many more. Today, I would like to continue this conversation and talk more about Vert.x architecture.

Considerations Used to Develop Vert.x

Polyglot is the feature making Vert.x stand out from other server frameworks. In the past, server frameworks could not support multiple languages. Supporting several languages does more than expand the range of users. More important thing is that services using different languages in a distributed environment can intercommunicate with ease. Of course, supporting a variety of languages is not sufficient for supporting a distributed environment. Essential functions of greater priority for a distributed environment include address system or message bus. Vert.x framework provides these functions. As Vert.x provides these functions as well as Polyglot, the benefits of Vert.x should be considered for a distributed environment.

As Vert.x supports a universal server framework, a variety of workloads should be considered. We should consider unusal cases different from Nginx, which is typically used as a Web server, or Node.js. It is to build a universal server application that processes a variety of protocols except HTTP (i.e., not a Web server which executes simple operations, considering scalability in a 3-tier environment). In order to accomplish this, Vert.x provides an additional thread pool while using the Run Loop method.

We will discuss Vert.x architecture starting from the thread pool and a consideration for a distributed environment.

Run Loop and Thread Pool

Vert.x and asynchronous server applications (or frameworks), include Ngin.x and Node.js, use the Run Loop method. Vert.x uses the term 'Event Loop' instead of 'Run Loop'. However, as Run Loop is the more popular term among some developers. I use this term, Run Loop, here. Run Loop, as you will guess from the name, is a method for checking whether there is a new event in the infinite loop, and calling an event handler when an event has been received. As such, the asynchronous server application and the event-based server application are different terms indicating an identical target, similar to ‘enharmonic' for music. To use the Run Loop method, all I/Os should be managed as events.

For example, imagine a general Web server application that creates a query for a database to respond to an HTTP request from a Web browser. The CPU of the Web server is used when one thread analyzes the HTTP request to execute proper business logic, and creates a query statement. However, the CPU is not used while the thread sends the query to the database and waits for a response. However, when the thread to be created equals the number of HTTP requests (Thread per Connection), another thread may be processing a task requiring the web server CPU, while one thread is waiting for response from the database. Finally, the web server CPU is used to process HTTP requests. As you know, the weakness of Thread per Connection is the cost for context switching at the kernel level since many threads must be created. This can be called waste. 

The asynchronous event handling method can overcome this weakness (figuratively speaking, 'asynchronous event handling' is the 'purpose' and ‘Run Loop’ is the 'means'). If ‘HTTP request itself’ and ‘receiving a response from the database’ are created as an event, and the Run Loop calls the corresponding event handler whenever an event is received, the execution performance of the application can be enhanced by avoiding unnecessary context switching. In this fashion, to utilize a CPU efficiently, the number of Run Loops required equals the number of cores (i.e., thread should be created equaling the number of cores and each thread should run the Run Loop).

However, there is another problem creating threads equaling the number of cores, which is preventing as much context switching as possible. If a handler, using server resources, takes a long time to handle an event, other events received while the handler is being executed are not managed in a timely manner. A popular example is file searching on the server disk. In this case, it is better to create a separate thread for searching files. 

Therefore, to build a universal server framework with asynchronous event handling, the framework should have a function for managing a thread pool. This is the aim of Vert.x. Thread pool management is the biggest difference between Vert.x and Node.js, except for polyglot. Vert.x creates Run Loops (Event Loops) equaling the number of cores and provides thread pool-related function to handle tasks using server resources requiring long periods for event handling.

Why is Hazelcast Used?

Vert.x uses Hazelcast, an In-Memory Data Grid (IMDG). Hazelcast API is not directly revealed to users but is used in Vert.x. When Vert.x is started, Hazelcast is started as an embedded element.

Hazelcast is a type of distributed storage. When storage is embedded and used in a server framework, we can obtain expected effects from a distributed environment.

The most popular case is session data processing. Vert.x calls it Shared Data. It allows multiple Vert.x instances to share the same data. Of course, additional RDBMS, instead of Hazelcast, will bring the same effect from the functional side. It is natural that embedded memory storage can consistently provide results faster than remote RDBMS. Therefore, users who need sessions for e-commerce or chatting servers can build a system with a simple configuration by using only Vert.x.

Hazelcast allows a message queue use without additional costs or investments (without server costs or monitoring of message queue instances). As mentioned before, Hazelcast is a distributed storage. It can duplicate a storage for reliability. By using this distributed storage as a queue, the server application implemented by using Vert.x becomes a message processing server application and a distributed queue.

These benefits make Vert.x a strong framework in a distributed environment.

Understanding Vert.x Components

Figure 1: Vert.x Architecture (Component) Diagram.

Figure 1 above shows a diagram of Vert.x components. As shown in the figure, in all Vert.x instances (these can be understood as a JVM), a Hazelcast is embedded and runs. The embedded Hazelcast is connected to Hazelcast in other Vert.x instances. Event Bus uses functions of Hazelcast. Hazelcast itself provides a certain level of reliability (because of WAL records and data duplication). So, events can be forwarded with a certain level of reliability.

HTTP Server and Net Server

HTTP Server and Net Server control network events and event handlers. A Net Server is for events and handlers private protocol, and an HTTP Server allows registering a handler to an HTTP event such as GET or POST. The reason for preparing an HTTP Server is eliminating the need to add event types, as well as the universality of HTTP itself. HTTP Server supports WebSocket as well as HTTP.

Figure 2: Event and Handler of HTTP Server.

Vert.x Thread Pool

Vert.x has three types of thread pools:

1. Acceptor: A thread to accept a socket. One thread is created for one port.
2. Event Loops: (same with Run Loop) equals the number of cores. When an event occurs, it executes a corresponding handler. When execution is performed, it repeats reading another event.
3. Background: Used when Event Loop executes a handler and an additional thread is required. Users can specify the number of threads in vertx.backgroundPoolSize, an environmental variable. The default is 20. Using too many threads causes an increase in context switching costs, so be cautious.

Event Loops can be described as follows in a detailed way. Event Loops use Netty NioWorkder as it is. All handlers specified by verticles run on Event Loops. Each verticle instance has its specified NioWorker. As such, it is guaranteed that a verticle instance is always executed on an identical thread. Therefore, verticles can be written in a thread-safe manner.

Conclusion

So far, I have briefly described Vert.x architecture. Since Vert.x framework is not widely used, I believe it would be better to detail the concept of designing Vert.x than detail each Vert.x component. Even if you have no interest in network server frameworks, it is helpful to review new products and determine differences between new and existing products. Doing so helps in understanding the evolution and direction of software products that are flooding today's market.

Therefore, it is important to review Vert.x: which network environment it considers as well as how it delivers high performance. So, I think it will be valuable to pay sufficient time examining Vert.x structure.

Philosophy of Vert.x

Vert.x is a project affected by Node.js. Like Node.js, Vert.x is a server framework providing an event-based programming model. Therefore, Vert.x API is very similar to that of Node.js. That's because both of these models provide asynchronous API.

Node.js is created by using JavaScript, but Vert.x is created by using Java. However, it is too much to understand Vert.x as a Java version of Node.js. Vert.x has been affected by Node.js; but Vert.x has its own unique philosophy, different from Node.js.

The most typical design philosophy of Vert.x is summarized as follows:

• Polyglot - supports several languages
  Vert.x itself is built in Java. However, Java is not required to use Vert.x.
  As well as languages based on JVM operation, such as Java or Groovy, Vert.x can be used with Ruby, Python, and even JavaScript. If you need to build a server application by using JavaScript, there is an alternative to Node.js. In addition, Scala and Closure are planned to be supported.
• Super Simple Concurrency model
  When building a server application by using Vert.x, users can write code as a single thread application. That means that the multi-thread programming effect can be achieved without synchronization, lock, or volatility.
  In Node.js, the JavaScript execution engine does not support multi-thread. However, to utilize all CPU cores, several same JavaScript programs have to be executed.
  However, Vert.x allows to create multiple threads based on the number of CPU cores whlie only one process is executed. It handle the multi-threading so users can focus on implementing business logic.
• Provides Event Bus
  As described in the introduction, the goal of Vert.x is not only to produce a ‘one server process DAEMON'. Vert.x aims to make a variety of Vert.x-built server programs communicate well with each other. For this, Vert.x provides Event Bus. Therefore, MQ functions such as Point to Point or Pub/Sub can be used (to provide Event Bus function, Vert.x uses Hazelcastm, an In-Memory Data Grid).
  With this Event Bus, a server application built with different languages can easily communicate with each other.
• Module System & Public Module Repository
  Vert.x has a module system. This module system can be understood as a type of component. That means the Vert.x-built server application project itself is modularized. It aims at reusability. This module can be registered to Public Module Repository. Through the Public Module Repository, the module can be shared.

What is the relationship and defference between Netty and Vert.x?

 Before discussing the Vert.x performance, we should summarize the relationship between Netty and Vert.x. Vert.x uses Netty. In other words, it processes all IOs by using Netty. Therefore, it is meaningless to verify differences of the performance between Vert.x and Netty.

Vert.x is a server framework that provides API and functions different and independent from Netty, designed with different purpose from Netty.

Netty is a framework that can process the low-level IO and Vert.x can process the higher-level IO than Netty.

Comparison of Performance with Node.js

Even if functions provided by Vert.x are different from those of Node.js, comparing the performance between them is a significant matter. Figure 1 and Figure 2 below show the performance of Vert.x (Java, Ruby, Groovy) and Node.js. (Source: http://vertxproject.wordpress.com/2012/05/09/vert-x-vs-node-js-simple-http-benchmarks/).

Figure 1 shows a comparison of performance when an HTTP server is built and only a 200/OK response has been returned. Figure 2 shows the comparison of performance when a 72-byte static HTML file is returned as a response.

Figure 1: Comparison of Performance When Only 200/OK Response Has Been Returned.

Figure 2: Comparison of Performance When a 72-byte Static HTML File is Returned.

This performance is proclaimed by Vert.x developers but the test has not been made under a strict environment. Just look the relative differences in performance.

Also, a notable point is that the performance of Vert.x-JavaScript is better than Node.js. However, even if the performance result is very reliable, it may be difficult to say that Vert.x is better than Node.js. That's because Node.js provides great models such as Socket.io and has lots of references.

Vert.x Terminology

Vert.x defines its unique terms and redefines general terms for Vert.x itself. Therefore, to understand Vert.x, it is necessary to understand the Vert.x-defined terms. The followings are popular terms used in Vert.x:

Verticle

For Java, it is the class with a main method. Verticle can also include other scripts referred to by the main method. It can also include the jars files or resources. An application may consist of one Verticle or several Verticles, which communicate through Event Bus. Alongside Java, it can be understood as an independently executable class or a jar file.

Vert.x Instance

A Verticle is executed within a Vert.x instance and the Vert.x instance is executed in its JVM instance. So there will be a lot of Verticles which are simultaneously executed in a single Vert.x instance. Each Verticle can have its own unique class loader. In this manner, direct interactions between Verticles, made through static members and global variables, can be prevented. A lot of Verticles can be simultaneously executed in several hosts on the network and the Vert.x instances can be clustered through Event Bus.

Concurrency

The Verticle instance guarantees it is always executed on an identical thread. As all codes can be developed as a single thread operation type, developers who use environment where Vert.x can be easily developed. In addition, race condition or deadlock can be prevented.

Event-based Programming Model

Like the Node.js framework, Vert.x provides an event-based programming model. When programming a server by using Vert.x, most codes for development are related to event handlers. For example, an event handler should be set to receive data from a TCP socket or an event handler, which will be called when data is received, should be created. In addition, event handlers should be created to receive alarms 'when Event Bus receives a message,' 'when HTTP messages are received,' 'when a connection has been disconnected,' and 'when a timer is timeout.'

Event Loops

Vert.x instance internally manages the thread pool. Vert.x matches the number of thread pools to the number of CPU cores as closely as possible.

Each thread executes Event Loop. Event Loop verifies the events as rounding the loop. For example, verifying whether there is data to read in the socket or on which timer an event has occurred. If there is an event to process on the loop, Vert.x calls the corresponding handler (of course, additional work is necessary if the handler-processing period is too long or there is a blocking I/O).

Message Passing

Verticles use Event Bus for communication. If a Verticle is assumed as an actor, Message Passing is similar to an actor model, which was famous in Erlang programming languages. The Vert.x server instance has a lot of Verticle instances and allows message passing among the instances. Therefore, the system can be extended according to the usable cores without executing the Verticle code through multi-thread.

Shared data

Message passing is very useful. However, it is not always the best approach in all types of application concurrency situations. Cache is one of the most popular examples. If only one vertical has a certain cache, it is very inefficient. If other Verticles need the cache, each Verticle should manage the same cache data.

Therefore, Vert.x provides a method for global access. It is the Shared Map. Verticles share immutable data only.

Vert.x Core

As named, this is the core function of Vert.x. Functions that the Verticle can directly call are included in the core. Therefore, the core can be accessed by each programming language API supported by Vert.x.

Vert.x Architecture

The simple architecture of Vert.x is shown in the following Figure 3.

Figure 3: Vert.x Architecture (source: http://www.javacodegeeks.com/2012/07/osgi-case-study-modular-vertx.html)

The default execution unit of Vert.x is Verticle and several Verticles can be simultaneously executed on one Vert.x instance. The Verticles are executed on the event-loop thread. Several Vert.x instances can be executed on several hosts, as well as on one host on the network. At this time, the Verticles or modules communicate by using the event bus.

To sum up, the vert.x application consists of combinations of Verticles or modules and communication among those is made by using Event Bus.

vert.x Project Structure

The following is the Vert.x project structure viewed from eclipse when clonning the source code from the Vert.x Github page.

Figure 4: Vert.x source tree.

The overall configuration is as follows:

• vertx-core is the core library.
• vertx-platform manages distribution and lifecycle.
• vert-lang used to expose the Core Java API to another language.

Gradle is used as its project build system. It has the advantages of ant and maven.

Installing Vert.x and Executing Simple Examples

To use Vert.x, JDK7 is required because Vert.x uses the invokeDynamic in JDK7.

Vert.x can easily be installed.

1. Download the compressed installation file from http://vertx.io/downloads.html to a desired location.
2. Decompress the file and add the bin directory to the PATH environment variables.

This is all about the installation of Vert.x. In the command window, execute the vertx version. If the version information successfully prints out, the installation is completed.

Example 1

Now, let's build and execute a simple Web server with JavaScript which prints out "hello world". After writing the following codes, save it in server.js file. It is almost identical to the Node.js code.

Execute the created server.js application by using the vertx command as follows:

Open a browser and connect to http://localhost:8080. If you can see the 'Hello World!' message, you have succeeded.

Example 2

Let's see another example built other languages. The following code is written in Java. It shows a Web server that reads a static file and returns it as an HTTP response.

Vertx vertx = Vertx.newVertx();
vertx.createHttpServer().requestHandler(new Handler() {
    public void handle(HttpServerRequest req) {
        String file = req.path.equals("/") ? "index.html" : req.path;
        req.response.sendFile("webroot/" + file);
    }
}).listen(8080);

The following code is written in Groovy and provide the same functionality:

Future of Vert.x and NHN

At NHN we have been observing Vert.x development since its preofficial release. We think highly of Vert.x. We have been communicating with the main developer, Tim Fox, since June 2012 to discuss ways to improve Vert.x. For example, Socket.io on Vert.x. Socket.io is available on Node.js only. So we have ported it to Java and sent a Pull Request https://github.com/vert-x/vert.x/pull/320 to Vert.x repository on Gitub. It's now merged to Vert.x-mod project.

Our effort, socket.io vert.x module, will be used for RTCS 2.0 version (vert.x + Socket.io) which is under-developing in NHN.

Node.js could remain very popular because of Socket.io. If Vert.x can use Socket.io, Vert.x may have many use cases. Furthermore, if this socket.io vertx module is used as an embedded library, it will be meaningful to use socket.io in Java based applications.

What is RTCS?

RTCS (Real Time Communication System) is a Real Time Web Development Platform created by NHN. It helps to transfer messages between a Browser and a Server in real time. RTCS has been deployed for NHN Web services such as Baseball 9, Me2Day Chatting, BAND Chatting and so on.

Wrap-up

The first version of Vert.x was released in May 2012. Compared to Node.js where the first version was released in 2009, the history of Vert.x is very short. Therefore, Vert.x does not have many references yet. However, Vert.x is supported by VMware and can run on CloudFoundry. So, we expect that many references will soon be obtained.

By Seongmin Woo, Software Engineer at Web Platform Development Lab, NHN Corporation.

References

• "Main Manual" http://vertx.io/manual.html
• "Installation Guide" http://vertx.io/install.html
• "The C10K problem" http://www.kegel.com/c10k.html
• Gim Seongbak, Song Jihun "Java I/O & NIO Network Programming”, Hanbit Media 2004.

Why You Should Know about PostgreSQL

PostgreSQL is an RDBMS, which is popular mainly in North America and Japan. It is not used much in Korea yet, but as it is a very excellent RDBMS in terms of functionality and performance, it is worth learning about what kind of database PostgreSQL is.

PostgreSQL (pronounced as [Post-Gres-Q-L]) is an object-relational database system (ORDBMS), and is an open-source DBMS that provides the enterprise-level DBMS functionalities and many other functionalities you can find only in advanced DBMS. PostgreSQL is also known as an open-source DBMS that Oracle users can adapt themselves to the most easily, as it has many functionalities similar to those of Oracle.

History

There were many ancestors of PostgreSQL, and of them, Ingres (INteractive Graphics REtrieval System) can be said to be the progenitor of PostgreSQL. Ingres was a project launched by Michael Stonebraker (Picture 1), a great master in the area of databases who is still working hard even today.

Picture 1: Michael Stonebraker started Ingres project.

The Ingres project was launched at Berkeley University in the US in 1977. After Ingres Michael Stonebraker had started another project called Postgres (Post-Ingres). As Postgres version 3 was released in 1991, its user base grew to be quite large. But as the burden of providing support to users became too high, the project was terminated in 1993 (Postgres is known to have had a huge influence on the current Informix product, even after the end of the project. Illustra, a commercial version of POSTGRES, was taken over by Informix in 1997, and then by IBM in 2001.).

Figure 1: Product History.

Despite the project having ended, Postgres users and students continued its development and finally created Postgres95, which achieved 40% better performance than Postgres by supporting SQL and improving its structure.

When Postgre95 became an open-source system in 1996, it was given the name PostgreSQL, its current name, to reflect the fact that it succeeded Postgres and supports SQL (Postgres supported a language called QUEL instead of SQL). In 1997, PostgreSQL was finally released after determining its first version as 6.0.

Since then, PostgreSQL has been actively developed to this day through an open-source community, and the latest release is 9.2, as of May 2013. In addition, due to its open license (like the BSD or MIT license, PostgreSQL allows commercial use and modification, but it also clarifies that the original developers are not liable for any problem that may occur in its use), there have been more than 20 various forks, some of which have had an influence on PostgreSQL and some of which have disappeared.

PostgreSQL's logo is an elephant named 'Slonik' (a baby elephant in Russian language). The true reason why an elephant was used for the logo is not known, but it has been said that just after it became an open-source system, one of its users was inspired by Agatha Christie's novel "Elephants Can Remember" and suggested it. Since then, the elephant logo has been visible at every official PostgreSQL event.

As elephants are thought of as large, strong, reliable and have a good memory, Hadoop and Evernote also use an elephant as their official logo.

Functionalities and Limitations

PostgreSQL supports transaction and ACID, which are the basic functionalities of a relational DBMS. Moreover, PostgreSQL also has many progressive functionalities or expanded functionalities for academic research as well as for basic reliability and stability. Even a general list of PostgreSQL functionalities includes a large number of functionalities.

• Nested transactions (savepoints)
• Point in time recovery
• Online/hot backups, Parallel restore
• Rules system (query rewrite system)
• B-tree, R-tree, hash, GiST method indexes
• Multi-Version Concurrency Control (MVCC)
• Tablespaces
• Procedural Language
• Information Schema
• I18N, L10N
• Database & Column level collation
• Array, XML, UUID type
• Auto-increment (sequences), 
• Asynchronous replication
• LIMIT/OFFSET
• Full text search
• SSL, IPv6
• Key/Value storage
• Table inheritance

In addition to these, it features a variety of functionalities and new functionalities of enterprise-level DBMS.

In general, PostgreSQL has the following limits:

Roadmap

As of May 2013, the latest release is 9.2. Figure 2 provides some brief information on the progress of PostgreSQL by year.

Figure 2: Progress of PostgreSQL by Year.

The main functionalities of each version are as follows:

The next PostgreSQL release under development is PostgreSQL 9.3, which is due to be released in the third quarter of 2013. This release features many functionalities, including an enhanced management functionality, parallel query, MERGE/UPSERT, multi-master replication, materialized view, and enhanced multi-language support.

Internal Structure

The following shows the process structure:

Figure 3: Process Structure.

If the client requests connection with the server through the (1) interface library (variety of Interfaces Including libpg, JDBC and ODBC), the Postmaster process relays connection with the server (2). Then, the client executes a query through connection with the allocated server (Figure 3).

The following shows the process of query execution in the server:

Figure 4: Query Execution Procedure.

If it receives a query request from the client, the system creates a parse tree through the syntax analytics process (1), starts a new transaction through the semantic checking process (2) and creates a query tree.

Next, a query tree is re-generated according to the rules defined in the server (3), and of the many available execution plans, the most optimized plan tree is created (4). The server executes this (5) and sends the result of the requested query to the client.

While the server executes a query, a system catalog in the database is frequently used. In the system catalog, users can directly define the type of functions and data, as well as index access methods and rules. In PostgreSQL, therefore, a system catalog is utilized as an important point in adding or expanding its functionalities.

A file that stores data consists of multiple pages, and a single page has a scalable slotted page structure (Figures 5 and 6).

Figure 5: Data Page Structure.

Figure 6: Index Page Structure.

Development Process

The development process model of PostgreSQL can be explained by the following sentence:

‘A community-based open-source project led by a few.’

Like the Linux, Apache and Eclipse projects, the PostgreSQL project is also composed only of a few administrators, a variety of developers and a large number of users. The small administrator group (Core Team) collects requests and feedback (the group sometimes takes a vote to determine priorities at http://postgresql.uservoice.com) from a large number of users, determines the direction of the product, has final approval right for the code and exerts its right for release. This is a different model from corporate management development processes such as MySQL and JBoss.

The developer group consists of code committers and code developers/contributors. They are located in many countries, including the U.S., Japan and Europe.

Figure 7: Distribution of PostgreSQL Developers by Region.

Codes developed by a variety of developers go through a variety of review processes (Submission Review, Usability Review, Feature Test, Performance Review, Coding Review, Architecture Review, Review Review), and are reflected in the product after approval by the Core Team. The mailing list that has been used by the community for a long time is usually used, and a variety of documents, including manuals, are well maintained through the official website.

Products in Competition

PostgreSQL wants to be compared with enterprise-level commercial DBs, but it has been compared mainly with popular open-source DBMSs. The following are the catchphrases of these open-source DBMSs, each of which reflects its features:

• PostgreSQL: The world's most advanced open source database
• MySQL: The world's most popular open source database
• CUBRID: Open Source Database Highly Optimized for Web Applications
• Firebird: The true open source database
• SQLite: self-contained library, serverless, zero-configuration, transactional SQL database engine

It is not easy to compare these products using their catchphrases alone, but you can see that PostgreSQL seeks progressiveness and openness.

The following is brief comparison of PostgreSQL and its competitiors:

For a long time, the PostgreSQL community has made attempts to enter the enterprise DBMS market. In 2004, EnterpriseDB, a company using PostgreSQL, was established, and it is striving to strengthen its position in the enterprise DBMS market. One of the company's main products is Postgres Plus Advanced Server. Postgres Plus Advanced Server was developed by adding Oracle-compatible functionalities (PL/SQL, SQL statements, functions, DB Links, OCI library, etc.) to the open-source PostgreSQL, featuring easy data and application migration and a cost reduction of 20% compared to Oracle (Figure 7).

Figure 8: Cost Reduction Compared to Oracle.

In addition, Postgres Plus Advanced Server provides differentiated services, including a training, consulting and migration, and technical support service from PostgreSQL experts. Through approximately 300 reference sites in a variety of areas, the product is promoted as a database for all industries, with a growing base of users across the world.

Present Status and Trend

As you can see from most posts on PostgreSQL, most PostgreSQL users have a developer-like tendency, and are very loyal to the product.

In fact, they have a good reason for their loyalty. PostgreSQL provides sufficient functionalities and conservative performance compared to other products, and one of its advantages is that it has good enough conditions for beginners to attract new developers.

These good conditions include a well-written manual on the project page, related documents, over 300 reference publications, and over 10 seminars and conferences held in a variety of countries every year. More recently, a PostgreSQL magazine has even appeared. And these are the results of the active PostgreSQL community.

The representative features that PostgreSQL users identify as being important are as follows:

• Reliability is the top priority of the product
• ACID and transaction
• A variety of indexing techniques
• Flexible full-text search
• MVCC for better concurrency performance
• Diverse and flexible replication methods
• A variety of procedures (PL/pgSQL, Perl, Python, Ruby, TCL, etc.)/Interface (JDBC, ODBC, C/C++, .Net, Perl, Python, etc.) languages
• Excellent community and commercial support
• Well-made documents and a thorough manual

A variety of expansion functionalities and ease of development of such functionalities are also advantages of PostgreSQL. The following are the differentiated expansion functionalities of PostgreSQL:

• GIS add-on (PostGIS)
• Key-Value store expansion (HStore)
• DBLink
• Support for a variety of functions and types, including Crypto and UUID

There are many other practical and experimental expansion functionalities as well.

Of these, you will see a brief account of GIS (Geographic Information System), which has recently become a hot topic. PostGIS is a middleware expansion functionality that enables PostgreSQL to conform to the OpenGIS standard and support geographic objects (Figure 9).

Figure 9: PostGIS Structure.

PostGIS began to be developed from 2001, and with many functionality and performance improvements, it currently has the most users among the open-source products. There are some commercial products, such as Oracle Spatial, DB2 and MS SQL Server, but the commercial products have not been as well-received in terms of price-performance ratio.

In addition, you can easily find benchmark data that shows that the functionalities and performance of PostGIS/PostgreSQL are worthy of comparison to Oracle.

According to the recent trend, PostgreSQL is also much talked about in relation to cloud as well as GIS. With the recent increase in the number of companies providing DBaaS (Database as a Service), the demand for PostgreSQL, which has advantages in terms of costs and license, has increased, and as such EnterpriseDB has released Postgres Plus Cloud Database in the cloud market, with the following features:

• Simple setup & web-based management
• Automatic scaling, load balancing and failover
• Automated online backup
• Database Cloning

It is used in many web services, including Amazon EC2, Eucalyptus cloud, and Red Hat Openshift development platform cloud. Other cloud service providers such as Heroku and dotCloud also provide services using PostgreSQL.

Conclusion

As Sun, which had acquired MySQL, was acquired by Oracle in 2009, MySQL began to be developed as a more closed corporate project, and many MySQL developers left the community around the same time. Afraid of this change, MySQL users are paying attention not only to the forks (MariaDB, Drizzle, Percona, etc.) of MySQL to which they can easily migrate, but also to the migration to PostgreSQL.

Looking at the trend of help-wanted ads related to PostgreSQL and MySQL in the most popular job finding portal http://www.indeed.com (Figure 9), we can see the increase in help-wanted ads related to MySQL is slowing down, while help-wanted ads related to PostgreSQL continue to increase.

Figure 10: Trend of Help-wanted Ads.

According to the trend of search frequency in search sites (Figure 10), MySQL shows a continued downtrend, while PostgreSQL seems to have almost no change. In Korea, however, the search frequency for PostgreSQL has shown an upward trend since mid 2010.

Figure 11: Search Frequency Trend (source).

Of course, the popularity and usage of MySQL is still much higher than PostgreSQL. Although you may not be able to determine the true status or prospects of these products from the above data alone, you could infer that if the popularity of MySQL declines, the popularity of PostgreSQL will increase.

PostgreSQL is not yet powerful enough to surpass MySQL in popularity, but the PostgreSQL open source project community continues to make the following efforts: 

• Improvement of the reliability of basic DBMS functionalities
• Provision of progressive and differentiated functionality expansion
• Continuous attraction of more open source developers

In addition, EnterpriseDB, which has stronger business purposes, is also striving to achieve the following objectives:

• Expansion of its share in the enterprise market
• Expansion of its share in the cloud market
• Efforts to replace Oracle and MySQL

By Kim Sung Kyu, Senior Software Engineer at CUBRID DBMS Lab, NHN Corporation.

In this article we will reveal the characteristics of Cassandra, HBase and MongoDB identified through multiple workloads.

Why NoSQL?

The interest in NoSQL continues to rise because the amount of data to process continues to increase. International internet companies, including Google and Facebook, have their own NoSQL solution designed to process the exploding amount of data.

Why are they using NoSQL instead of RDBMS?

Twitter is still using MySQL. Although smaller than Facebook, the international SNS company MySpace uses MS SQL Server as their main storage.

RDBMS is known to experience burden when processing tera or peta unit large sized data. However, this can be resolved through sharding. For instance, NHN’s Café, Blog and News services use RDBMS through sharding.

There is no single correct answer in processing bulk data. Since every situation is different, the company must select the solution that best fits their situation and apply that for seamless service.

Out of the RDBMSs, Oracle is an exception since Oracle’s performance and functions, such as mass data processing or data synchronization, are far more superior to other RDBMS. However, the high cost can be a problem. Depending on the size, it may be more economical to develop a NoSQL than purchasing an Oracle license.

Even if you have enough expertise on how to process bulk data with RDBMS, we will need to have continuous interest and training in NoSQL. NoSQL is gaining popularity because of its ability to process mass data, but it still has many technical (or functional) limitations compared to RDBMS. However, this will be resolved as time passes.

NoSQL provides a non-relational, and in the long run, schema-free data model, allowing the horizontal extension of the system. Instead, it is less structured than RDBMS and does not guarantee ACID. Therefore, after the INSERT operation is completed and SELECT conducted, a different value can be acquired. And after the NoSQL demo has failed and restored, the stored value may be different than before. Operations such as transactions or join cannot be conducted.

While ACID has been abandoned, the flexibility, scalability and usability of data storage have increased. Therefore, it is more suitable for explosive amounts of data processing.

Now let’s see if the NoSQL products really play the expected part for the internet services you are to develop. Why don’t we take a look at the characteristics of the most widely used NoSQL products (Cassandra, HBase, MongoDB) by looking into each of its architectures and benchmark tests that were conducted.

Benchmarking Tests using YCSB

YCSB (Yahoo Cloud Servicing Benchmark) is a test framework developed by Yahoo. It allows to conduct benchmark tests on storages by creating "work loads". Through this benchmark, the storage most suitable for the service that is to be developed can be selected.

Basic operations are Insert, Update, Read, and Scan. There are basic workload sets that combine the basic operations, but new additional workloads can also be created.

YCBS currently supports Cassandra, HBase, MongoDB, Voldemort and JDBC. If tests on other storages are needed, then use YCBS interfaces to test during the development process.

This article contains tests conducted on the following products and versions.

• Cassandra-0.7.4
  Although Cassandra’s latest version is 0.8.0, we have decided to use the previous version known to be stable. Because when testing with the 0.8 version, the gossip protocol between nodes malfunctioned and the node up/down information was incorrect.
• HBase-0.90.2 (Hadoop-0.20-append)
  The HBase-0.90.2 (Hadoop-0.20-append) was selected because, if not the Hadoop-append version, there may be problems on decreased durability in HDFS.
• MongoDB-1.8.1

The test workload is as follows.

• Insert Only
  Enter 50 million 1K-sized records to the empty DB.
• Read Only
  Search the key in the Zipfian Distribution for a one hour period on the DB that contains 50 million 1K-sized records.
• Read & Update
  Conduct read and update one-on-one instead of read under the identical conditions of Read Only.

There are three testing equipments with the same specifications:

• Nehalem 6 Core x 2 CPU, 16GB Memory.

Each conduct replicates and distributes three copies. However, with MongoDB the performance result was abnormally high when both replications and distribution compositions were organized, so the test was conducted only with the replica set. The parameter for each product is shown in the following listings. Other items were set to default.

• Cassandra

• HBase

• MongoDB

The results are as shown in the following figure.

1. All three products showed better throughput in INSERT.
2. Cassandra showed outstanding throughput in INSERT-only with 20,000 ops.
3. HBase also presented relatively good performance in INSERT-only.
4. The products did not show large differences with READ-only than INSERT-only.
5. HBase’s performance was better than Cassandra in READ-only.
6. Cassandra’s performance in READ-and-UPDATE was better than the other two products. Cassandra’s READ-and-UPDATE might have been higher than READ-only due to Cassandra’s excellent insert throughout.
7. MongoDB’s throughout in all three conditions was the lowest of the three products. MongoDB, which uses the Memory Mapped File (mmap), probably showed poor performance because the large data size exceeded the physical memory size.

Cassandra and HBase Architecture

Both Cassandra and HBase are influenced by Google’s BigTable. (Although Cassandra was directly influenced by Amazon’s Dynamo.) BigTable is a Column-Oriented DB that stores data in a Multidimensional Sorted Map format and has the <row key, column family, column key> data structure. Column family is a basic unit that stores data with column groups that are related to each other.

In BigTable, data is written basically with the append method. In other words, when modifying data, the updates are appended to a file, rather than an in-place update in the stored file. The figure below shows BigTable’s data read/insert path.

When a write operation is inserted, it is first placed in a memory space called memtable. If the memtable is full, then the whole data is stored in a file called SSTable (Sorted String Table). Data may be lost if the server collapses before the memtable data is moved to SSTable, so to provide durability it is necessary to save the history of commit logs every time before writing to the memtable. When conducting the read operation, first find the pertaining key in the memtable. If it is not in the memtable, search for it in the SSTable. You may have to search multiple SSTables.

There are advantages in the writing operation if this architecture is used. This is because the ‘writing’ operation is only recorded in the memory and moved to the actual disk only after a certain amount has been accumulated. Therefore, concurrent I/O can be avoided. However, when "reading", if "reading" is done in the SSTable and not in the memtable, then the performance will relatively decrease. Both Cassandra and HBase use bloom filter to quickly judge whether the key exists in the SSTable and creates an index for use. However, if there are many SSTables, then a lot of I/O will be created during the reading operation. Therefore, compaction is done to improve the reading performance. Compaction is where two SSTables merge and sort to become one SSTable, which decreases the number of SSTables. The reading and writing performance improved as more compactions are done.

For these reasons READ and READ-and-UPDATE are much slower than INSERT operations in these NoSQL solutions.

Cassandra

Additionally, Cassandra uses consistent hashing. The HBase and Casandra may have their similarities, there are differences as well. Cassandra prefers AP (Availability & Partition tolerance) while HBase prefers CP (Consistency & Partition tolerance). CAP theorem is a theory in Distributed Computing.

The theory claims that there is no system that provides all three Consistency, Availability and Partition tolerance. For example, replications must be made in multiple nodes to increase usability, and the adjustability between replicated data must be met. However, in order to make operation possible, even during network partitioning, it becomes difficult to guarantee the adjustability between replications or data. Therefore, only a part of the CAP can be provided.

As mentioned before, Amazon’s Dynamo has a direct influence on Cassandra. Dynamo uses consistent hashing to disperse data to the key-value store, and provides high adjustability. Consistent hashing sequences the value (slot) that hashed the key and placed it in a ring format. The multiple nodes that create the cluster processes certain ranges of the ring. Therefore, every time a node in the cluster falls out or comes in, the closed node on the ring can take over the concerned range or distribute the range without rehashing.

Cassandra also raises the usability level with the concept of consistency level. This concept is related to replication. Confirmation of the number of replications and the completion of replication can be adjusted with the system parameter. For example, if three replications are maintained and a write operation is inserted, then the operation is only considered to be successful if the three replications are completed.

However, Cassandra allows only N (under 3) number of executions to be checked and immediately returns to value. Therefore, write can be conducted successfully even with failures in the replication node, which raises usability. Histories of failed write operations are recorded on a different node, and the operation can be retried at a later date (this is called “hinted handoff”). Since the success of replicated writing is not guaranteed, the data suitability is checked in the reading stage. Generally, if there are multiple replications, it is collected into one when reading. However, Cassandra keeps in mind that not all replications match, and reads the data from all three replications, checks whether it is identical, and restores the latest data if it is not suitable (this is called “read repair”).

This is why Cassandra is slower in reading than writing.

HBase

Now let’s look into HBase. HBase has the same structure as BigTable. While BigTable operates on the tablet unit, HBase disperses and replicates with the region unit. Key is arranged according to ranges, and the location of the region, where the key is stored in, is saved as meta data (this is called meta region). Therefore, if a key is inserted, first find the region, and later the client will cache this information. HBase places the writing operation in a single region, without distributing and splits the region if it becomes too large. Adjustments, such as creating regions in advance, must be made for the action above.

HDFS is in charge of data storage and replication. When writing a file with Hadoop’s distribution file system, HDFS writes multiple replications (synchronous replication) and only reads one of them when reading. This guarantees data integrity and lowers reading overhead. However, since HBase and HDFS operates separately, the location of the node that contains memtable and SSTable may change, which may cause additional network I/O.

What happens if there is a failure in the node? If the region server collapses, other nodes take over the tasks which comprised the region. In other words, the commit log that pertains to the region is replayed and memtable is created. If the failed node contains HDFS’s data, then chunk that pertains to the node is migrated to a different node.

Thus, we have looked at the similarities and differences of Cassandra and HBase.

Then why did the two products show a difference in writing during the comparison test?

1. First of all, there is a difference in commit log. Cassandra writes the commit log once in the local file system. However, HBase enter the commit log in the HDFS and HDFS conducts the replication. Data locality is not put into consideration, so the file write request will probably be sent to a different physical node in the network.
2. Second, Cassandra receives the write request on all three nodes, but HBase only ‘writes’ on a single region in the beginning, and receives requests on only one node.

Then why is there a difference in reading performance?

There are various differences, but for one READ request Cassandra reads the data three times while HBase only reads the data once. This places an influence on the throughput.

MongoDB Architecture

MongoDB is unlike Cassandra or HBase eplained earlier. There are too many functional groups that can be classified as NoSQL, which makes it difficult to compare with Cassandra and HBase on the same level.

Cassandra and HBase is for processing full-scale large amounts of data, while MongoDB can be used quickly, schema-free when using a certain amount of data.

MongoDB adopts a documented-oriented format, so it is more similar to RDBMS than a key-value or column oriented format.

MongoDB operates on a memory base and places high performance above data scalability. If reading and writing is conducted within the usable memory, then high-performance is possible. However, performance is not guaranteed if operations exceed the given memory. Therefore, MongoDB should be used as a medium or small-sized storage system.

Configure a test condition similar to the original test on a lower specification server and test 300 thousand records instead of 50 million, and MongoDB will record greater performance than Cassandra or HBase.

Mongo DB uses BSON for data storage. (As known as Binary JSON, but is similar to Hessian than JSON in data encoding format and expressive datatype.)

The logical unit that is expressed using BSON is called document. (When using NPC as an example, it is the same as when the NPC data is recorded on the disk and searched.) Document is a concept that corresponds to a row in RDBMS. The difference is that a document in MongoDB does not have a schema.

Accumulated documents are called collections. A collection corresponds to a table in RDBMS.

Basic query method is to search the collection to find documents that meet the conditions. Each document has a field called _id, which acts as the OID (Object Identifier), and all query results are returned with this _id.

Index can also be created for collections. An index can be prepared for the fields in the document. This index is implemented in B-Tree, similar to that of RDBM’s index. Each collection creates an index for _id by default. One of the strengths of MongoDB is that its functions are similar to RDBMS, in that it supports indexing and JOIN.

Further, we will now look at the structure of MongoDB.

MongoDB Structure

MongoDB is configurable in replication and distribution configuration. The system configuration recommended by MongoDB is three shard servers and three replica sets for each server (in other words, nine nodes). Replications are made possible by Master/Slave or a replica set. Replica set supports automatic failover. It has one master and multiple slaves, and write can only be sent to the master. If the master is terminated, service is resumed after one of the slaves are appointed as a master.

The distribution method provided by MongoDB is sharding. Each collection can shard, and a field in the Collection can designate a shard key. Like HBase, sharding is operated with the Order-preserving partitioning method. Chunk is included in the <collection, minkey, maxkey>, a continuous data range within shard, and can be as big as 64M. Chunks that have exceeded the maximum size can split into two chunks, and the divided chunks can migrate to different shard servers. The following figure contains a picture of MongoDB, which is composed of shards and replica sets.

The client first contacts a process called mongos, which is in charge of routing and coordination. Coordination is done over multiple nodes through requests and the results are collected and returned. The config server retains meta information on shards and chunks.

Now let’s look into MongoDB's internal structure. MongoDB uses the asynchronous method for replication. When a “write” operation is inserted, master stores the history in Oplog. Oplog is a fixed sized, FlfO method collection called capped collection. Logs accumulated in the Oplog are regularly polled by the slave, read, then implemented. If the slave failed to replay the log while the Oplog fills up, then the slave must suspend log-based replications and synchronizes by resyncing the master’s data. One of Oplog’s other distinctive characteristics is that it can conduct the saved operations idempotently when replaying the log. Operation logs are saved in the order it was executed in, so logs can be replayed after that period. Ops such as increment are changed to set ops when logged for this purpose.

MongoDB’s replication and failover method and query power is similar to RDBMS. The most significant difference is that flexibility in data storage is provided to the application while schema-free data is stored.

RDBMS Architecture

Unlike NoSQL, RDBMS is "read" operation oriented. The detailed method differs for each product, but generally NoSQL products are implemented so that mass data can be ‘written’ quickly. On the other hand, RDBMS, which is ‘read’ oriented, not only enables quick reading, but also provides many functions and inquiry features. DBMS can quickly inquire values using indexes, but at the same time indexes slow down ‘writing’. Since an index must be configured for every ‘writing’ operation, the more indexes there are, the more time is needed to configure the indexes.

The difference of RDBMS and NoSQL can be compared with ACID and BASE, but comparisons can also be made with reading and writing.

If the business you are to build requires more reading than writing, and needs complex reading methods, as well as basic aggregate functions such as SUM() or AVG(), then RDBMS might be a better choice than NoSQL.

Summary

We have looked at benchmark tests done on Cassandra, HBase, MongoDB and the differences between the products. Cassandra and HBase show excellent writing abilities, but its reading performance did not meet expectations. This was because unlike existing RDBMSs, the two products were optimized for writing, and this resulted in a lot of concurrent I/O when reading.

MongoDB has similar structures to that of RDBMS and shows great flexibility in data modeling especially for medium and small-sized businesses.

By Lee Hye Jeong, Storage System Development Team, NHN Corporation.

These days many websites (in fact, millions) are implemented by the use of popular open source content management systems (CMS). I would say, for everything you would imagine your site doing, there is one or another CMS which can do that. Thus, the reason for choosing a particular one depends on the tasks you want to accomplish. For blogs there is a common sense of using WordPress blog management system, for large scale websites Drupal CMS is perceived to be a better fit, while Joomla CMS is easier to learn for newbies. .NET developers prefer alternative DonNetNuke or umbraco CMS. Besides these, CMS Made Simple and Liferay are amongst the most popular content management systems based on the number of the number of downloads. Elgg and MODx are amongst the rising stars.

I will not tell you what a CMS is. If you want to learn, see the CMS Wikipedia page. In this blog I will give a comprehensive overview of top 14 content management systems based on the number of weekly downloads, installations, and brand familiarity. The full statistical data can be found in the 2010 OPEN SOURCE CMS MARKET SHARE REPORT by water&stone (2010). The report is distributed under the Creative Commons Attribution-Noncommercial License (3.0).

Today we will cover Drupal, Joomla, MODx, WordPress, DotNetNuke, umbraco, Liferay, TYPO3, CMS Made Simple, MOVABLE TYPE, Plone, eZ Publish, concrete5, and Alfresco content management systems. You might already be familiar with some of these, while some can be new to you. I bet you will be surprised to learn that some unknown for you systems suit your requirements much better than those you have got to use so far (my personal experience). So, let's learn about each of them, and do not forget to leave your comments below on what other CMS should have been included in this list and why.

Note: the order of the CMS does not represent its quality, performance, or superiority over the other. They are numbered to give a better perception to readers. The users feedback and opinions are retrieved from Stack Overflow, thus can be subjective as they are based on personal views and preferences.

# 1: Drupal

Official site: http://drupal.org/

1% of all websites on the Internet are based on this platform. An estimated 7.2 million sites were powered by Drupal as of July 2010. Drupal started out in 2001. In one year from May 2007 to April 2008, Drupal was downloaded from their official site more than 1.4 million times, an increase of approximately 125% from the previous year.

Weekly downloads: 33,671 (ranked #3 after Joomla and before DotNetNuke).

Installations: 575 according to the survey (#3 after WordPres and before DotNetNuke), but 1.4% (#3 after Joomla and before Typo3) of Alexa Top 1 million sites.

Brand Familiarity: Drupal is known to be the #3 most familiar content management system.

Major Features:

• You can manage multiple sites with Drupal in multiple languages. You can use it for blogging site, corporate site, personal site, gallery, briefly, whatever you imagine.
• You can easily manage your site users, providing standard registration, including OpenID support. You can set various access control rules to limit the activity of your site users.
• Provides multiple-level menu system, template customization, advanced search, RSS feed aggregator.
• Officially Drupal supports several databases including MySQL, PostgreSQL, MariaDB, and SQLite.
• To increase its performance, you can use caching. At the same time it provides high security with notifications about the new update releases.
• Provides Search Engine Friendly descriptive URLs.
• Powered by jQuery JavaScript framework.

Distinguished Clients:

• http://www.whitehouse.gov/
• http://data.gov.uk/
• http://www.ubuntu.com/
• http://www.alrc.gov.au/
• http://www.nysenate.gov/
• http://london.gov.uk/
• http://www.rutgers.edu/
• http://www.economist.com/
• http://wfp.org/

• More difficult to master especially for newbies. It's more for advanced users. Though the new Drupal 7 claims to provide significantly improved usability (maybe more toward WordPress style). To achieve this, they hired web designers to specifically address the UX problems it had in previous versions.
• New major releases are not quite backward compatible. More focus on new features and functionality. External module developers should take care of compatibility themselves, except for data representation, which Drupal is intended to keep same.
• Drupal is often compared with Joomla and is perceived to be a bit slower. Though this depends on the type of website you develop. However, leveraging its caching and gzipping technologies, it is possible to achieve quite impressive results.
• jQuery, the default JavaScript framework in Drupal, allows to use alternative Mootools framework at the same time.

# 2: Joomla

Official site: http://www.joomla.org/

Joomla is the result of a fork of Mambo CMS on August 17, 2005. Within its first year of release, Joomla had been downloaded 2.5 million times.

Weekly downloads: 113,836 (ranked #2 after WordPress and before Drupal).

Installations: 1,297 according to the survey (#1 before WordPress), but 2.5% (#2 after WordPress and before Drupal) of Alexa Top 1 million sites.

Brand Familiarity: Joomla is known to be the #1 most familiar content management system.

Major Features:

• You can manage multiple site with Joomla in multiple languages natively (since Joomla 1.6). You can use it for blogging site, corporate site, personal site, gallery, briefly, whatever you imagine.
• You can easily manage your site users, providing standard registration, including Google OpenID support. Full support for Access Control List.
• Provides Multiple-level menu and content category system, template customization, advanced search, RSS feed aggregator.
• Officially supports only MySQL.
• Page cashing for increased performance.
• Provides moderate descriptive URLs (still not fully customizable as you can do in WordPress).
• Powered by MooTools JavaScript framework.

Distinguished Clients:

• http://www.linux.com/
• http://www.itwire.com/
• http://www.quizilla.com
• http://www.ihop.com
• http://gsas.harvard.edu
• Citibank (Financial institution intranet) - Not publicly accessible
• http://www.greenmaven.com
• http://www.outdoorphotographer.com
• http://www.playshakespeare.com
• http://www.sensointeriors.co.za

• More intuitive and easy to use than Drupal, though still not like WordPress.
• Powerful. Fully-fledged content management system, so you can create whatever site you want.
• Really strong security. If security problems found, immediately fixed.
• The new Joomla 1.6 release is expected to be faster, more convenient, with more features.
• Its strong dependency on Mootools JavaScript framework sometimes bothers users as Joomla does not give easy workaround to disable it and use jQuery instead.
• Support of only one database, limits Joomla a lot in terms of the number of users. However, this is a compromise for high optimizations for MySQL, thus increased overall performance.
• Does not allow to fully customize URLs - a must feature for CMS.

#3: modx

Official site: http://www.modxcms.com/

modx is not just an open source CMS but also a web application framework. Raymond Irving and Ryan Thrash began the MODx CMS project in 2004 as a fork of Etomite. In 2008 MODx users created a new logo and branding for the project. Now MODx allows for full segregation of content (plain HTML), appearance and behavior (standards compliant CSS and JavaScript) and logic (PHP, snippets).

Weekly downloads: 4,500 (ranked #11 after umbraco and before Tiki).

Installations: 58 according to the survey (#12 after eZ Publish and before umbraco), has less than 0.1% among the Alexa Top 1 million sites.

Brand Familiarity: #14 (before Liferay and after eZ Publish).

Major Features:

• As with Joomla, modx officially supports only MySQL database.
• Not just CMS but a PHP framework for Web.
• Freedom to choose jQuery, Mootools, ExtJS, Prototype or any other JavaScript library.
• Supports PHP 4.3.11 and above.
• Complete control of all metadata and URL structure for SEO (Search Engine Optimization).
• Unlimited hierarchical page depth.
• Can create custom fields and widgets for templates.
• Role-based permissions for the Manager.
• Ability to customize the Manager on a per-deployment basis.
• Ecommerce integration via Foxy Cart.

Distinguished Clients:

• http://www.pippatoledo.com/
• http://www.aquevix.com/
• http://www.not1bug.com/
• http://everlight-uva.com/
• http://www.tritopora.ru/
• http://www.strategische-webloesungen.de/

• Good to have a choice for favorite JavaScript framework.
• Light CMS solution (but not necessarily the fastest).
• PHP 4 support for developers mean that a lot of compromises had to be made in terms of OOP (Object Oriented Programming) in order to offer PHP 4 support .
• Nice to have freedom to set custom URL.

#4: WordPress

Official site: http://wordpress.org/

WordPress was first released on May 27, 2003, by Matt Mullenweg as a fork of b2/cafelog. As of August 2010, version 3.0 had been downloaded over 12.5 million times. Nowadays, known as the #1 CMS for blogging.

Weekly downloads: 983,625 (ranked #1 before Joomla).

Installations: 1,012 according to the survey (#2 after Joomla and before Drupal), but 12.9% of the Alexa Top 1 million sites (#1 before Joomla).

Brand Familiarity: #2 (after Joomla and before Drupal).

Major Features:

• Highly optimized for blogging.
• Custom and easy to switch themes.
• Users can re-arrange widgets without editing PHP or HTML code.
• Official support for only MySQL.
• Custom URL, clean permalink structure, excellent for SEO.
• Nested, multiple categories to articles.
• Support for tagging. Advanced search by tags.
• Highly intuitive UI (User Interface).
• jQuery JavaScript framework.
• Supports the Trackback and Pingback standards for displaying links to other sites that have themselves linked to a post or article.
• Rich plugin architecture which allows users and developers to extend its functionality beyond the features that come as part of the base install.

Extensions: 12,780 plugins and 1,315 themes.

Distinguished Clients:

• http://www.nytimes.com/interactive/blogs/directory.html
• http://tmagazine.blogs.nytimes.com/
• http://crowdfavorite.com/
• http://blog.broadband.gov/
• http://blogs.america.gov/
• http://www.number10.gov.uk/
• http://www.speaker.gov/
• http://allthingsd.com/
• http://politicalticker.blogs.cnn.com/
• http://stylenews.peoplestylewatch.com/

• Perhaps, the most convenient, easy to use and intuitive CMS (or BMS) in the world. Perfect for blog sites. But if you need to develop a dynamic site with various components, perhaps, other CMS would fit better, though, WordPress provides enough plugins to accomplish almost all tasks.
• I would avoid Wordpress as a CMS in a professional environment. As stated earlier, it's a great blogging platform, but doesn't generally offer the robustness that most professional environments require.
• Availability of jQuery makes the plugin development a lot easier for external developers and site owners.
• Endless themes - no need to worry about the new design for your site, unless you really need something special.

#5: DotNetNuke

Official site: http://www.dotnetnuke.com/

DotNetNuke is an open source platform for building web sites based on Microsoft .NET technology. It is written in VB.NET and distributed under both a Community Edition BSD-style license and a commercial proprietary license. The Community Edition is a popular web content management (WCM) system and application development framework for ASP.NET, with over 6 million downloads and 600,000 production web sites as of October 2010. More than 8,000 DotNetNuke apps are available for purchase on Snowcovered.com. DotNetNuke.com has over 800,000 registered members as of October 2010.

Weekly downloads: 13,000 (ranked #4 after Drupal and before CMS Made Simple).

Installations: 402 according to the survey (#4 after Drupal and before Liferay), but 0.2% of the Alexa Top 1 million sites (#4 after Typo3 and before MOVABLE TYPE).

Brand Familiarity: #4 (after Drupal and before Typo3).

Major Features:

• Distinguishes between community (common features) and enterprise (full set of features) editions.
• Various modules, and data providers.
• Provides language packs for about 60 languages.
• Customizable through skins and templates.

• http://www.chamberlain.edu/
• http://magenic.com/
• http://www.graphiksolutions.ca/
• http://www.marly.com/
• http://www.dotcomsoftwaresolutions.com
• http://www.cityplaceascent.com/
• http://sites.kiwanis.org/kiwanis/en/home.aspx
• http://www.zonediet.com/

• A little bit difficult to create modules.
• It is perceived as a little bit bulky CMS.
• Does not provide extensive documentation and user guides.
• Similar to most CMS, does not provide full backward compatibility in its new major releases.
• Unlike its enterprise edition, the community edition is not tested and certified by the DotNetNuke Corporation.
• Even if it provides the language packs, the sites cannot be created to support multiple languages. Third party module should be used to enable this feature.
• Auto-upgrade, Advanced Site Search, Page Cashing, and many must-have features are not included in the Community edition, only in Professional or Enterprise editions.

#6: Umbraco

Official site: http://umbraco.org/

Umbraco is also an open source content management system. It was developed by Niels Hartvig in 2000 and released as open source software in 2004. It is written in C# and can be deployed on Microsoft based infrastructure. In 2010, with about 1,000 downloads a day, Umbraco was in the Top 5 most popular downloads via the Microsoft Web Platform Installer, two places below its main rival DotNetNuke.

Weekly downloads: 5,420 (ranked #10 after Alfresco and before MODx).

Installations: 57 according to the survey (#13 after MODx and before e107), but less than 0.1% of the Alexa Top 1 million sites.

Brand Familiarity: #16 (after Liferay and before e107).

Major Features:

• Can be deployed with several databases, including MySQL, SQL Server, and VistaDB.
• SEO-friendly URLs.

What users say:

• Limited number of extensions.
• Official support for Windows OS only.
• Umbraco is oriented to small low-cost sites.
• Video trainings have to be purchased.
• Many must-have features have to purchased.

#7: Liferay

Official site: http://www.liferay.com/

Liferay Portal is a free and open source enterprise portal written in Java and distributed under the GNU Lesser General Public License. It allows users to set up features common to websites. It is fundamentally constructed of functional units called portlets. Liferay is sometimes described as a content management framework or a web application framework. It comes with certain portlets preinstalled. These comprise the core functionality of the portal system.

Weekly downloads: 9,435 (ranked #6 after CMS Made Simple and before TYPO3).

Installations: 154 according to the survey (#5 after DotNetNuke and before TYPO3), but less than 0.1% of the Alexa Top 1 million sites.

Brand Familiarity: #15 (after MODx and before Umbraco).

Major Features/ Portlets:

• Can tag and categorize contents.
• Document Library Manager, Recent Documents.
• Alfresco, Documentum, and other document library integration.
• User management based on various roles and groups (ACL).
• WebDAV Integration (Web-based Distributed Authoring and Versioning which allows users to collaboratively edit and manage files on remote web servers).
• Nested Portlets
• User Directory
• LDAP Integration
• Microsoft Office Integration
• Calendar/Chat/Mail/Message Boards/Polls
• Wiki (supports Creole as well as MediaWiki syntax)
• Alerts and Announcements
• Knowledge Base
• Social Equity
• Can create multi-language sites.
• Asset Publisher to publish many contents, tagged by a specific term, at once.

Distinguished Clients: It is primarily used to power corporate business sites.

• http://developer.cisco.com
• http://www.t-mobile.cz
• http://www.betavine.net
• http://www.foxchannel.de/
• http://www.ixarm.com

• It is mostly used by enterprise companies rather than for powering personal or community sites, though it has social and collaboration features.
• More professional developers driven project (backed by Liferay Inc.) rather than the community driven.
• Provide paid Enterprise edition.
• All features are available in Community edition (except for support and customization related).

#8: Typo3

Official site: http://typo3.org/

TYPO3 is a free and open source CMS released under the GNU General Public License oriented to small to mid size enterprise-class users. TemplaVoila is an alternative template engine extension for TYPO3. A graphical mapping tool for creating templates is included, an alternative page module, the ability to create flexible content elements and an API for developers. New content element types can be created without programming. TemplaVoila facilitates more flexibility for maintaining web pages than TYPO3's standard templating, while making it possible to enforce a strict corporate design and allowing editors to work with content more intuitively.

Weekly downloads: 7,461 (ranked #7 after Liferay and before eZ Publish).

Installations: 122 according to the survey (#6 after Liferay and before Tiki), 0.6% of the Alexa Top 1 million sites (#4 after Drupal and before DotNetNuke).

Brand Familiarity: #5 (after DotNetNuke and before OpenCMS).

Major Features:

• Supports MySQL, Oracle, MS-SQL, PostgreSQL, ODBC, LDAP - virtually any external data source.
• You can undo any change you make on the site.
• Can create multiple sites with multiple domains for each.
• Can have multiple template per site.
• User management.
• Able to switch from administrator user to general user to check permissions.
• Sandbox: administrators can set up a section within the system to test new features without disturbing the main site.
• Versioning of content pages.
• Advanced caching: template, navigation or page level.
• Link management.
• Multi-language content.
• Search Engine friendly URLs.

Distinguished clients:

• http://www.volkswagen-ir.de
• http://www.eadssecureuk.com
• http://www.geveriwise-eu.com
• http://www.rewe-xxl.de

• The administrator's UI (User Interface) is not user-friendly, though intuitive and functional.
• It is extremely powerful which provide more enterprise level features, but the learning curve is incredibly steep.
• Great ease of multi-lingual site management.
• Difficult to customize templates. Need to learn TypoScript and TemplaVoila, two TYPO3-specific systems.

#9: CMS Made Simple

Official site: http://www.cmsmadesimple.org/

CMS Made Simple is an open source cms built using PHP with support for MySQL and PostgreSQL. The template system is driven using the Smarty Template Engine.

Weekly downloads: 9,948 (ranked #5 after DotNetNuke and before Liferay).

Installations: 72 according to the survey (#7 after Tiki and before Alfresco), 0.1% of the Alexa Top 1 million sites (#8 after Xoops and before eZ Publish).

Brand Familiarity: #12 (after Xoop and before Ez Publish).

Major Features:

• Officially supports MySQL and PostgreSQL.
• Search Engine Friendly URLs.
• Users management and group based permissions system.
• Content tagging.
• Site localization is available in 20 languages.

What users say:

• It is oriented more toward professional users.
• It does not provide many templates, so coding knowledge is a must.

#10: Movable Type

Official site: http://movabletype.org/

Movable Type is a weblog publishing system, similar to WordPress, developed by the Six Apart company in Perl programming language. At various times, this company maintained three other publishing systems - TypePad, Vox, and LiveJournal. Movable Type was publicly announced on September 3, 2001. Version 1.0 was publicly released on October 8, 2001, thus it is a blogging system older than WordPress. On 12 December 2007, Movable Type was relicensed as free software under the GNU General Public License. Based on the list of its customers, Movable Type is quite credible CMS.

Weekly downloads: unavailable.

Installations: 30 according to the survey (#17 after Silverstripe and before OpenCMS), 0.1% of the Alexa Top 1 million sites (#6 after DotNetNuke and before Xoops).

Brand Familiarity: #8 (after Plone and before Alfresco).

Major Features:

• Convenient blogging system with social community features.
• Since version 5 officially supports only MySQL. PostgreSQL and SQLite can be used via plugins. Databases such as Oracle can be integrated with Movable Type Enterprise edition.
• Manage user roles. OpenID support.
• Multiple site hosting.
• Easily customizable templates.
• Revision history.
• Can add custom fields.
• Content tags and categories.
• Feeds and trackback links.
• Available localization and internationalization.
• Can generate static pages (updated whenever the content of the site is changed).

Distinguished clients:

• http://www.britneyspears.com/
• http://www.barackobama.com/
• http://boeingblogs.com/
• http://blogs.oracle.com/
• http://gehealthcare.typepad.com/
• http://www.spd.org/
• http://blog.ted.com/

• Users often deploy Movable Type for their blog sites, newspaper or other type of publishing sites.
• A powerful alternative for WordPress.

#11: Plone

Official site: http://plone.org/

Plone, a free and open source CMS, started in 1999 by Alexander Limi, Alan Runyan, and Vidar Andersen. It was made as a usability layer on top of the Zope content management framework, thus Plone is written in Python. The first version was released in 2001. In 2004, Plone 2.0 was released. This release brought more customizable features to Plone, and enhanced the add-on functions. In 2007, Plone 3 was released. This new release brought inline editing, an upgraded visual editor, and strengthened security, among many other enhancements. Recently in 2010, Plone 4 was released with major improvements in performance.

Weekly downloads: unavailabe.

Installations: 34 according to the survey (#15 after e107 and before Silverstripe), 0.1% of the Alexa Top 1 million sites (#10 after eZ Publish).

Brand Familiarity: #7 (after OpenCMS and before Movable Type).

Major Features:

• Inline editing - no need to reload the page for editing.
• Localized into 40 languages.
• Plone can be integrated with Active Directory, Salesforce, LDAP, SQL, Web Services, and Oracle.
• Working Copy support - keep the old version of a content published until you publish a new version.
• Cut/copy/paste operations on content.
• Link and reference integrity checking - no more broken links within your site.
• Can create workflows - useful for organizations.
• LiveSearch - instant site search powered by AJAX.
• Full-text indexing of Word and PDF documents.
• Wiki support
• Collaboration and sharing
• Automatic locking and unlocking
• Versioning, history and reverting content
• Authentication back-end
• Collections
• Multilingual content management
• Automatic previous/next navigation
• Human-readable URLs
• Caching proxy integration
• Drag and drop reordering of content
• Adjustable templates on content
• RSS feed support
• Automatic image scaling and thumbnail generation
• Comment capabilities on any content
• WebDAV and FTP support
• Hot -backup support

Distinguished clients:

• http://www.amnesty.ch/en
• http://www.brasil.gov.br/
• http://www.chicagohistory.org/
• http://ccnmtl.columbia.edu/
• http://cnx.org/
• http://discovermagazine.com/
• http://www.ece.rice.edu/
• http://www.engagemedia.org/
• http://science.nasa.gov/

• It has a lot of features (several times more than listed above) built-in.
• To become a Plone expert is long and expensive. It has a steep learning curve.
• Plone is backed by Zope framework which is very powerful with support for caching, rollback, etc. - everything what your organization might need.
• Plone is really complex to deeply tweak.
• Plone is known for its high security.
• Plone runs slow if you don't know how to optimize it.

#12: eZ Publish

Official site: http://ez.no/

eZ Publish is an open source enterprise CMS developed by the Norwegian company eZ Systems in 1999 using PHP programming language. eZ Publish is freely available under the GPL licence, as well as under proprietary licenses that include commercial support. eZ Publish supports the development of customized web applications. Typical applications range from a personal homepage to a multilingual corporate website, which include role-based multi-user access, e-commerce functions and online communities.

Weekly downloads: 7,031 (ranked #8 after TYPO3 and before Alfresco).

Installations: 60 according to the survey (#11 after Concrete5 and before MODx), 0.1% of the Alexa Top 1 million sites (#9 after CMS Made Simple and before Plone).

Brand Familiarity: #13 (after CMS Made Simple and before MODx).

Major Features:

• Advanced Search feature is available in only Enterprise Edition.
• Online image editor.
• Can create building blocks for the site and later reuse them in several pages.

• http://www.schemexpert.com/
• http://www.ticotimes.net/
• http://jp.wsj.com/
• http://www.laborange.fr/
• http://canalstreet.canalplus.fr/
• http://www.hks.harvard.edu/
• http://www.vogue.com.au/

• The documentation is a bit limited especially there is almost no documentation for module development.

#13: Concrete 5

Official site: http://www.concrete5.org/

Concrete5 is an open source CMS started in 2003 as a rapid-design approach to building the now-defunct LewisAndClark200.org, the official site for the Ad Council's National Council for the Lewis & Clark Bicentennial. Concrete5 is developed in PHP and is distributed under MIT software license.

Weekly downloads: unavailable.

Installations: 62 according to the survey (#10 after Alfresco and before eZ Publish), has less than 0.1% of the Alexa Top 1 million sites.

Brand Familiarity: #20 (after TextPattern).

Major Features:

• Integrated server side caching.
• Support for only MySQL.
• Inline content editing.
• Image editing tool.
• Editable areas are defined in concrete5 templates which allow editors to insert 'blocks' of content. Additional blocks are available as add-ons.
• Automatic upgrade is available.
• Advanced Permissions to track content versions.

• http://www.genco.com
• http://www.cottonfrombluetogreen.org/
• http://www.cs.uh.edu/
• http://www.signals.ca/
• http://www.paulraymondgregory.com

• It's PHP based and quite new but it's quite a nice layout and it's really natural for new CMS users. You can go from a paper-based sitemap and PSD to a full site structure, ready for data entry, within a day, two at a push.
• Concrete5 is simple, suitable for creating sites quickly.
• Creating template is very easy with Concrete5.

#14: Alfresco

Official site: http://www.alfresco.com/

Alfresco is an open source enterprise content management system for Microsoft Windows and Unix-like operating systems. Alfresco includes a content repository, an out-of-the-box web portal framework for managing and using standard portal content, a CIFS interface that provides file system compatibility on Microsoft Windows and Unix-like operating systems, a web content management system capable of virtualizing web apps and static sites via Apache Tomcat, Lucene indexing, and jBPM workflow. The Alfresco system is developed using Java technology. John Newton (co-founder of Documentum) and John Powell (a former COO of Business Objects) founded Alfresco Software, Inc. in 2005.

Weekly downloads: 7,000 (ranked #9 after Ez Publish and before Umbraco).

Installations: 70 according to the survey (#9 after CMS Made Simple and before Concrete5) Alexa ranking is not available.

Brand Familiarity: #9 (after Movable Type and before Tiki).

Major Features:

• Document Management.
• Web Content Management (including full webapp & session virtualization).
• Repository-level versioning (similar to Subversion).
• Records Management, including 5015.2 certification.
• Repository access via CIFS /SMB, FTP, Web DAV, NFS and CMIS.
• j BPM workflow.
• Advanced search with Lucene.
• Multi-language support.
• Officially runs on Windows, Linux and Solaris.
• User Interface official supports Internet Explorer and Firefox.
• Desktop integration with Microsoft Office and OpenOffice.org.
• Clustering support.
• Pluggable authentication: NTLM, LDAP, Kerberos, CAS.

• France Air Force
• Harvard Business School Publishing
• Toyota
• Sony Pictures
• Fox
• National Academy of Sciences
• Cisco

• Alfresco is mostly for enterprises rather than for personal sites.
• Simple to install and use, flexible and open-ended.
• Alfresco is a solution with the broadest range of technical capabilities and the best feedback from users. In addition to demonstrating a promising roadmap for collaboration tools, Alfresco was highly attractive from a cost perspective, compared to the proprietary products offered by other ECM vendors.
• All in one solution for enterprises.

See also

Besides, the official home pages, you can refer to Wikipedia for general information on these content management systems. Also Stack Overflow provides very informative users feedbacks on each of these CMS.

1. http://en.wikipedia.org/wiki/Drupal
2. http://en.wikipedia.org/wiki/Joomla
3. http://en.wikipedia.org/wiki/Modx
4. http://en.wikipedia.org/wiki/Wordpress
5. http://en.wikipedia.org/wiki/DotNetNuke
6. http://en.wikipedia.org/wiki/Umbraco
7. http://en.wikipedia.org/wiki/Liferay
8. http://en.wikipedia.org/wiki/Typo3
9. http://en.wikipedia.org/wiki/CMS_Made_Simple
10. http://en.wikipedia.org/wiki/Movable_Type
11. http://en.wikipedia.org/wiki/Plone_(software)
12. http://en.wikipedia.org/wiki/EZ_Publish
13. http://en.wikipedia.org/wiki/Concrete5
14. http://en.wikipedia.org/wiki/Alfresco_(software)
15. http://www.waterandstone.com/sites/default/files/2010%20OSCMS%20Report.pdf
16. http://php.opensourcecms.com/
17. http://stackoverflow.com/

ElasticSearch is a distributed search engine based on Lucene developed by Shay Banon. Shay and his team have recently released the long awaited version 0.90. Here is a link to a one-hour recorded webinar where Clinton Gormley, one of the core ElasticSearch developers, explains what's new in ElasticSearch 0.90.

If you are developing a system which requires a search functionality, I would recommend ElasticSearch as its installation and server expansion are very easy. Since it is a distributed system, ElasticSearch can easily cope with an increase in the volume of search targets. At NHN all logs coming into NELO2 are stored and indexed by ElasticSearch for faster near real-time search results.

Features of ElasticSearch

Let's get started with familiarizing ourselves with the terms widely used in ElasticSearch. For those who are familiar with relational database systems, the following table compares the terms used in relational databases with the terms used in ElasticSearch.

JSON-based Schemaless Storage

ElasticSearch is a search engine but can be used like NoSQL. Since a data model is represented in JSON, both requests and responses are exchanged as JSON documents. Moreover, sources are also stored in JSON. Although schema is not defined in advance, JSON documents are automatically indexed when they are transferred. Number and date types are automatically mapped.

Multitenancy

ElasticSearch supports multitenancy. Multiple indexes can be stored in a single ElasticSearch server, and data of multiple indexes can be searched with a single query. NELO2 separates indexes by date and stores logs. When executing a search, NELO requests indexes of dates within the scope of search with a single query.

Code 1: Multitenancy Example Query.

Scalability and Flexibility

ElasticSearch provides excellent scalability and flexibility. It enables the expansion of functionality through plug-ins, which was further improved in recent 0.90 release. For example, by using Thrift or Jetty plugin, you can change transfer protocol. If you install BigDesk or Head, which is a required plugin, you can use the functionality of ElasticSearch monitoring. As shown in the following Code 2, you can also adjust the number of replicas dynamically. The number of shards is not changeable as it is fixed for each index, so an appropriate number of shards should be allocated in the first time by taking the number of nodes and future server expansion into account.

Code 2: Dynamic Configuration Change Query.

Distributed Storage

ElasticSearch is a distributed search engine. It distributes data by configuring multiple shards according to keys. An index is configured for each shard. Each shard has 0 or more replicas. Moreover, ElasticSearch supports clustering, and when a cluster runs, one of many nodes is selected as the master node to manage metadata. If the master node fails, another node in the cluster automatically becomes the master. It is also very easy to add nodes. When a node is added to the same network, the added node will automatically find the cluster through multicast and add itself to the cluster. If the same network is not used, the master node address should be specified through unicast (see a related video: http://youtu.be/l4ReamjCxHo).

Installing

Quick Start

ElasticSearch supports zero configuration installation. As shown in the following code snippets, all you have to do for execution is download a file from the official homepage and unzip it.

1. Download
   
   ~$ wget http://download.ElasticSearch.org/ElasticSearch/ElasticSearch/ElasticSearch-0.20.1.tar.gz   
   ~$ tar xvzf ElasticSearch-0.20.1.tar.gz
2. Executing Server
   
   ~$ bin/ElasticSearch -f

Installing Plugins

You can easily expand the functionality of ElasticSearch through plugins. You can add management functionalities, change the analyzer of Lucene, and change the basic transfer module from Netty to Jetty. The following is a command we use to install plugins for NELO2. Head and bigdesk, which are found in the first and second lines, are the plugins required for ElasticSearch monitoring. It is strongly recommended to install them and check their functionalities. After installing them, visit http://localhost:9200/plugin/head/ and http://localhost:9200/plugin/bigdesk/, and you can see the status of ElasticSearch in your Web browser.

Main Configurations

You don't need to change configurations when conducting a simple functionality test. When you carry out a performance test or apply it to production services, then you should change some default configurations. See the following snippet and try to find for yourself the configurations which should be changed from the initial configuration file.

Code 5: Main Configurations (config/ElasticSearch.yml).

Using REST API

ElasticSearch provides a REST API as shown below. It provides most of its functionalities through REST API, including the creation and deletion of indexes, mappings, as well as search and change of settings. In addition to REST API, it also provides various client APIs for Java, Python and Ruby.

Code 6: REST API Format in ES.

As mentioned earlier, NELO2 classifies indexes (databases in RDBMS terms) by date, and type (table) is separated by project. Code 7 below shows the process of creating logs that came into the hadoop project on December 27, 2012, in the unit of document by using a REST API.

Code 7: An Example of Using ElasticSearch REST API.

Creating Documents and Indexes

As shown in the following Code 8, when the request is sent, ElasticSearch creates the log-2012-12-27 index and hadoop type automatically without any pre-defined index or type. If you want to create them specifically instead of using auto creation, you should specify the setting of action.auto_create_index and index.mapper.dynamic as false in the configuration file.

Code 8: Creating Documents.

As shown in Code 9 below, you can make a request after including type in a document.

Code 9: A Query Including Type.

If an id value is omitted as in Code 10, an id will be created automatically when a document is created. Note that thePOST method was used instead of PUT when a request was made.

Code 10: A Query Creating a Document without an ID.

Deleting a Document

Code 11 below shows how to delete a document (a record in RDBMS terms) in type (a table). You can delete a hadoop type document with id=1 of the log-2012-12-27 index by using the DELETE method.

Code 11: A Query to Delete a Document.

Getting a Document

You can get a hadoop type document with id=1 of the log-2012-12-27 index by using the GET method as shown in Code 12.

Code 12: A Query to Get a Document.

Search

When the Search API is called, ElasticSearch executes the Search API and returns the search results that match the content of the query. Code 13 shows an example of using Search API.

Code 13: An Example Query of Using Search API.

Search API by Using URI Request

If you use URI, you can search easily by using parameters in Table 2 and a query string. As it does not provide all search options, it is useful when used for tests.

Code 14: Search Query by Using URI Request.

Search API by Using Request Body

When HTTP body is used, perform a search by using query DSL. As query DSL has a large amount of contents, you are advised to refer to a guide from the official website.

Code 15: Search by Using Query DSL.

Mapping

Put Mapping API

To add a mapping to a specific type, you can define a mapping in the form shown in Code 16.

Code 16: Query to Register a Mapping.

Get Mapping API

To get defined mapping information, you can use a query in the form shown in Code 17.

Code 17: Query to Get a Mapping.

Delete Mapping API

Code 18 shows an example of deleting a defined mapping.

Code 18: Query to Delete a Mapping.

How to Optimize Performance

Memory and the Number of Open Files

If the amount of data to search increases, you will need more memory. When you run ElasticSearch, you will encounter many problems due to the use of memory. In an operating method recommended by an ElasticSearch community, when you run a server exclusively for ElasticSearch, you are advised to allocate only half of the memory capacity to ElasticSearch, and to allow the OS to use the other half for system cache. You can set the memory size by setting the ES_HEAP_SIZE environmental variable or by using -Xms and -Xmx of JVM.

Code 19: Execution by Specifying Heap Size.

When using ElasticSearch, you will see OutOfMemory errors frequently. This error occurs when the field cache exceeds the maximum heap size. If you change the setting for index.cache.field.type from resident (default) to soft, soft reference will be used and the cache area will be preferentially GC, and this problem can be resolved.

Code 20: Configuring Field Cache Type.

If the amount of data increases, the number of index files also increases. This is because Lucene, which is used by ElasticSearch, manages indexes in the unit of segments. Sometimes the number will even exceed the number of MAX_OPEN files. For this reason, you need to change the maximum open file limit by using the ulimit command. The recommended value is 32000-64000, but sometimes you may need to set a larger value depending on the size of the system or data.

Index Optimization

NELO2 manages indexes by date. If indexes are managed by date, you can delete old logs that don't need to be managed easily and quickly, as shown in Code 21. In this case, the overhead imposed on the system is smaller than when deleting logs by specifying the TTL value for each document.

Code 21: Deleting an Index.

If index optimization is performed, segments are incorporated. Using this method, you can enhance search performance. As index optimization can impose a burden on the system, it is better to perform it when the system is being used less.

Code 22: Index Optimization.

Shards and Replicas

You can't change the number of shards after setting it. For this reason, you need to decide this value carefully by taking the current number of nodes in the system and the number of nodes expected to be added in the future into account. For example, if there are 5 nodes and the number is expected to reach 10 in the future, it is recommended to set the number of shards as 10 from the beginning. If you set it as 5 in the beginning, you can add 5 more nodes later, but you won't be able to use the added 5 nodes. If you set the number of replicas to 1, of course, you can utilize the added 5 nodes as nodes exclusively for replication.

If the number of shards increases, it is more advantageous to process a large amount of data because queries are distributed as much as the number of shards. But you need to set this value appropriately, because the performance could be deteriorated due to increasing traffic if the value is too high.

Configuring Cluster Topologies

The content of the configuration file of ElasticSearch is shown in Code 23 below. There are three types of nodes:

1. data node
   This does not act as the master, and only stores data. When it receives a request from a client, it searches data from shards or creates an index. 
2. master node
   It functions to maintain a cluster, and requests indexing or search to data nodes.  
3. search balancer node
   If it receives a search request, it requests data, gathers data and delivers the result.

You can have one node which will function both like a master and a data node. But if you use the three types of node separately, you can reduce the burden of the data node. In addition, if you configure the master node separately, you can improve the stability of a cluster. Also, you can reduce operation costs by using low-spec. server equipment for the master and search node.

Code 23: Settings Related to Topology.

Figure 1 below shows the configuration of NELO2 topologies that use ElasticSearch. The efficiency of equipment use and the stability of the entire cluster has been improved as follows: only ElasticSearch runs on the 20 data nodes (server) so that they can achieve sufficient performance, while other daemon server processes in addition to ElasticSearch run on the 4 master nodes and 3 search nodes.

Figure 1: NELO2 ElasticSearch Topologies.

Configuring Routing

When a large amount of data needs to be indexed, increasing the number of shards will improve the overall performance. On the other hand, if the number of shards increases, the traffic among nodes will also go up. For example, when there are 100 shards, if it receives a single search request, it sends the request to all the 100 shards and aggregates data, and this imposes a burden on the entire cluster.

If you use routing, data will be stored only in a specific shard. Even if the number of shards increases, the application will still send a request only to a single shard, and consequently the traffic can be reduced dramatically. Figure 2, 3, and 4 are excerpted from the slides Rafal Kuc presented at Berlin Buzzwords 2012. If you don't use routing, as shown in Figure 2, the application will send a request to all the shards. But if you use routing, it will send a request only to a specific shard, as shown in Figure 3.

According to the material cited, in Figure 4 when there are 200 shards, the response time is over 10 times faster with routing than without routing. If routing is applied, the number of threads will increase by 10 to 20 times compared to when it is not applied, but the CPU usage is much smaller. In some cases, however, the performance will be better when routing is not applied. For a search query whose result should be collected from multiple shards, it could be more advantageous in terms of performance to send the request to multiple shards. To complement this, NELO2 determines the use of routing depending on the log usage of the project.

Figure 2: Before Using Routing.

Figure 3: After Using Routing.

Figure 4: Performance Comparison before and after Using Routing.

Conclusion

The number of users of ElasticSearch is increasing rapidly, thanks to its easy installation and high scalability. It was several days only since the release of the latest ElasticSearch version 0.90. Its functionality is improving very quickly thanks to its active community. In addition, more and more companies are beginning to use ElasticSearch for their services. Recently, some committers, including the developer Shay Banon, gathered together and established ElasticSearch.com, which provides consulting and training services.

In this article I have explained the basic information on the installation of ElasticSearch, how to use it, and do performance tuning. We have started testing the latest 0.90 release and soon will migrate the current 0.20.1 ES deployment. In the next post I will continue this topic and tell you about our experience with 0.90 as well as the critical split-brain problem we have previously experienced. Due to the scarcity of solutions for this problem, I believe it will be very useful for our readers.

By Lee Jae Ik, Senior Software Engineer at Global Platform Development Lab, NHN Corporation.

References

• Official guide: http://www.ElasticSearch.org/guide/
• Introduction to ElasticSearch and comparison of the terms of ElasticSearch and RDB: http://www.slideshare.net/clintongormley/cool-bonsai-cool-an-introduction-to-ElasticSearch
• About ElasticSearch: http://www.slideshare.net/dadoonet/ElasticSearch-devoxx-france-2012-english-version
• Shay Banon's articles: http://2011.berlinbuzzwords.de/sites/2011.berlinbuzzwords.de/files/ElasticSearch-bbuzz2011.pdf
• Using ElasticSearch for logs: http://www.ElasticSearch.org/tutorials/2012/05/19/ElasticSearch-for-logging.html
• Concept of multitenancy: http://en.wikipedia.org/wiki/Multitenancy
• Shay Banon's ElasticSearch optimization: https://github.com/logstash/logstash/wiki/ElasticSearch-Storage-Optimization
• Rafal Kuc's article on performance tuning presented at Berlin Buzzwords 2012: http://www.slideshare.net/kucrafal/scaling-massive-elastic-search-clusters-rafa-ku-sematext

We are very glad to announce the immediate availability of CUBRID ALL-IN-ONE Windows Downloader version 1.0 beta. You can download CUBRID ALL-IN-ONE Windows Downloader from http://www.cubrid.org/wiki_tools/entry/cubrid-all-in-one-windows-downloader. The source code is available at http://svn.cubrid.org/cubridtools/cubrid-downloader/ which is open sourced under BSD license just like all other CUBRID Tools.

CUBRID ALL-IN-ONE Windows Downloader is an application that allows our users to easily download CUBRID components including the server engine, drivers and GUI tools. All you have to do is to select the components you want to download on your local Windows machine and the Downloader will download them for you, one by one, without any other actions required.

Application key features:

• The application can auto-update itself, anytime a new version is available (it uses the ClickOnce technology).
• Retrieves all the components information from a remote CUBRID online location, so it is always up-to- date with the latest application releases.
• Detects local machine specifics - CUBRID version, OS architecture – and automatically selects the appropriate list of components.
• Can handle software pre-prerequisites dependencies and download them as well.
• Supports both HTTP and FTP protocols for downloads.
• Provides additional information to users like links to online resources.
• Handles download errors and auto-retries in case of failures.
• Supports alternate download locations to try in case of failures.
• Saves the user preferences and re-uses them next time.
• Provides a comprehensive operations log information.
• Supports UI localization.

Here is a mute video which shows how to use CUBRID ALL-IN-ONE Downloader.

If you have questions or suggestions, leave your comments below.

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 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.

Testing

The 1.1 release was successfully tested, using the driver test suite, on 2 OS x 3 CUBRID engine = 6 different test environments.

What's next

The 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!

Database Expansion

To store and search a volume of data which is so big that it cannot be handled by one database, you must find a way to use multiple databases. Although there are some databases made for distributed environments, such as Cassandra or Dynamo, these have many functional constraints, such as weakness in terms of search range or inability to use the JOIN operations. In order to expand data while using a relatively feature-rich functionality, it is recommended to use RDBMS by sharding the databases.

In the past, the Sharding logic was implemented directly on the application layer, but now there is an increasing number of examples which introduce Sharding platforms which allow to move the Sharding logic from the application layer to database or middleware layers. At a core level, Sharding platforms must respond effectively to ever-increasing data without failure, and handle different data characteristics and models depending on services.

In this article I will compare Spock Proxy, the Sharding platform based on MySQL Proxy, Gizzard, created by Twitter, and CUBRID SHARD, native database sharding feature in CUBRID which is set to launch in the first half of 2012. A table of other solutions was previous posted at Database Sharding with CUBRID.

Horizontal Partitioning and Sharding

Horizontal partitioning is a design that divides and stores data, such as a schema, into two or more tables within one database. For example, to handle large data of user messages, such schema may be created so that messages by users from city A are stored in one table, while messages by users from city B are stored in another table. This allows to reduce the size of the index and increase the concurrency of operations. The key point in this approach is that the data is partitioned between tables within a single database.

Sharding, on the other hand, is the distributed approach where data is horizontally partitioned between tables created in physically different databases.  Thus, Sharding is a method to store the messages by users from city A in database A and messages by users from city B in database B. Here each database is called a Shard.

As you have to work on multiple databases, there could be functional limitations based on circumstances and also drawbacks in terms of consistency and replication, including JOIN operations. In many cases, Sharding used to be implemented at the application server level. There have been many attempts to provide this at the platform level. These can be classified into a pattern that operates in the application server, such as Hibernate Shards, a middle tier pattern such as CUBRID SHARD, Spock Proxy, or Gizzard, and a pattern that provides the Sharding functionality from a database itself, e.g. nStore or MongoDB.

Middle Tier Sharding Platform

By default, a Sharding platform should consider the following items:

• Database location abstraction
• Scalability
• Monitoring/Ease of operations

Database location abstraction and scalability are different from each other but connected. Database location abstraction ensures that on the application layer you do not need to know which data (which Row) is located in which database. The application is connected only with the Sharding platform. Connecting to a database is what the Sharding platform should do. In addition, Sharding platform should carry out the task to add a replicated storage to a specific Shard (one of the partitioned databases) in order to migrate database for replacement without restarting or changing the application code. When the Sharding platform comes to a stop, it is obvious that an application server will not be able to access the database. For this reason, the Sharding platform should provide redundancy.

For monitoring, the Sharding platform should be able to provide a number of requests and error information according to Shard keys (a standard to determine which data is stored in which database).

Comparison between Spock Proxy and CUBRID SHARD

The key function of the Sharding platform is the CRUD operation (CREATE, READ, UPDATE, DELETE) which chooses one database among many according to 'a standard set by a developer', in other words the Sharding strategy. We will compare Spock Proxy, a typical Sharding platform of MySQL, with the CUBRID SHARD platform developed by NHN.

Spock Proxy

Spock Proxy is a Sharding platform designed based on MySQL Proxy. In MySQL Proxy, you can execute the Lau scrip code, which is written by a developer before and after performing SQL. The primary purpose of using MySQL Proxy is to analyze and modify SQL. To use Spock Proxy, you need to create a MySQL database to manage the information about shards and how the data should be distributed. The row is a rule for Sharding.

Figure 1: Specifying Sharding rules in Spock Proxy.

CUBRID SHARD

CUBRID SHARD is the Sharding platform for CUBRID. The uniqueness of CUBRID SHARD is that it can also be used with MySQL as well as Oracle. It is set to be launched in the first half of 2012, and is planned to be used for processing the meta information database system of NDrive service, the cloud storage system developed by NHN.

The following table shows a summary of a comparison between Spock Proxy and CUBRID SHARD.

Table 1 Comparison between Spock Proxy and CUBRID SHARD

Spock Proxy stores Sharding rules in the table in MySQL universal_db database. The SQL received from an application server is parsed and checked whether the query has shard keys. If shard keys are provided, the MySQL instance will be identified according to the standard recorded in universal_db, then SQL will be relayed to that MySQL instance.

When using this method, you do not need to describe information related to shard keys in SQL, unlike in CUBRID SHARD. Therefore, if you did not use Sharding before for your coding, but recently had to use due to the data increase, you may use Spock Proxy which will work without requiring you to change the SQL in your application. Note that this is limited to cases where there is no need to change the schema for Sharding, or when Sharding can be applied without changing the SQL that you use. However, the method used to find the Shard after parsing SQL, as used by Spock Proxy, has weaknesses in terms of performance. It could lead to unnecessary work, as SQL should be parsed twice: once by Spock Proxy to determine the MySQL instance, then by MySQL itself.

In CUBRID SHARD, SQL HINT is used which allows to avoid parsing SQL twice. Suppose there is a table as follows:

To use the student_no column data as a shard key, an application server sends the following prepared SQL to CUBRID SHARD.

CUBRID SHARD checks if a HINT displays /*+ SHARD_KEY */ in SQL, in which case the column data with the corresponding HINT will be used as a Shard key. It then reads the student_no value, which follows the hint, and identifies RDBMS based on the configuration file and transmits the corresponding query. As such, the benefit of using HINT is that you can improve processing efficiency by avoiding parsing SQL twice, and react to various RDBMS without violating the database location abstract.

1. CUBRID SHARDING provides various HINTs, in addition to /*+ SHARD_KEY */.
2. Typically, there is /*+ SHARD_ID(__id__) */, a HINT that allows you to find a special shard.
3. The /*+ SHARD_VAL(__shard_key_val__) */ HINT can also play a role in finding a special shard for tables which have no shard keys. While this HINT is the same in that it searches for tables that have no specific shard keys, it configures a value for shard key column directly without choosing a shard, and selects the shard according to the internal rules of the middleware.

Unlike Spock Proxy, where Sharding rules are inserted into a database table, in CUBRID SHARD Sharding rules are specified in the configuration file. If there are three access addresses for actual RDBMS storage, you should specify DB addresses to CUBRID SHARD as follows.

If you want to use the Modulo method, write the corresponding value in the configuration file as below.

In addition, specify [MIN..MAX] according to the value generated from SHARD_KEY_MODULAR, and describe which shard to send the corresponding query to.

If you need a more subtle method than Modulo, you can create program code of your own that calculates the Shard ID for Shard keys.

As shown in the above example, you can register your own library which will calculate the Shard ID logic.

The common weakness of both Spock Proxy and CUBRID SHARD is that they both require additional network IO time for each additional hop because they are implemented as a middle tier.

The following figure displays a general process of internal execution in CUBRID SHARD, which is performed when a developer executes a query. When a developer executes a query, the query is analyzed by the DB shard middleware, which determines to which shard it will be sent. Then the query is transmitted to the selected shard, and finally the middleware delivers the response to the client.

Figure 2: CUBRID SHARD process.

Gizzard

Gizzard is a Sharding platform developed by Twitter. It is also a middle tier just like Spock Proxy and CUBRID SHARD. However, its usage and architecture are quite different from Spock Proxy or CUBRID SHARD.

The following figure shows how the data can be shared between several databases deploying two units of Gizzard.

Figure 3: Gizzard deployment diagram.

In this case, the interface being used by an application server is Thrift, an RPC protocol, not JDBC. Therefore, it is similar to DBGW (CUBRID SHARD) platform developed by NHN. When the schema of a storage changes, Gizzard may also need modifications. While it can be a constraint, it could also open up access not only to RDBMS, but also to various other databases (e.g. Lucene).

Gizzard is written based on Scala, and you can expand its functions by adding Scala codes as needed. Instead of viewing it as a complete product, we recommend that you download the source and modify it to suit your needs.

The biggest advantage of Gizzard is that you can perform hotspot response and database migration in the middle tier platform level. For example, Gizzard provides the direct replication feature, as shown in the following figure.

Figure 4: The replication feature of Gizzard.

Spock Proxy or CUBRID SHARD do not offer this feature, as they do not need it. CUBRID, MySQL, and Oracle have their own replication feature, thus they do not need to offer the feature from the middle tier.

The replication feature provided by Gizzard is very simple - send each request to several replicas - but cannot handle variety of requests that may occur while running the replication feature. However, it can be useful when using a database without the replication feature.

Figure 5: The migration feature of Gizzard.

As shown in the figure above, it offers the replication feature which does not require a service halt. When migrating a certain database, the feature replicates the details of a database storage before replication configuration, after allowing the new data to be written in two locations by configuring the replication. This approach, of course, cannot be used on RDBMS due to consistency issue.

Limitations of Sharding

Sharding, on the other hand, also has its own limitations. Typical constraints of Sharding are as follows:

• It cannot perform JOIN operations for two or more Shardings.
• auto_increment (serial) values can vary depending on Sharding.
• last_insert_id() value is no more valid.
• shard key column value must not be updated; requires delete, then insert.
• does not allow to access two or more Shards from one transaction.

When using Sharding, therefore, it is important that you perform right data modeling and schema design to prevent the above constraint issues.

To learn more about Sharding and other features of CUBRID I suggest you to see the Slideshare presentation at:

• http://www.slideshare.net/cubrid/growing-in-the-wild-the-story-by-cubrid-database-developers.
• http://www.cubrid.org/blog/cubrid-life/database-sharding-the-right-way-easy-reliable-open-source/

By Jeon Won Hee, Senior Software Engineer at CUBRID DBMS Development Lab, NHN Corporation.

Figure 1 below illustrates the basic operation flow in CUBRID.

• First, when a user enters the cubrid service start command, CUBRID gets started.
• Then, the cub_master and cub_broker processes are started. At this time, a number of cub_cas processes will be started which corresponds to the value of MIN_NUM_APPL_SERVER parameter in the cubrid_broker.conf file.
• Then, a user enters the cubrid server start demodb command which creates the cub_server process that mounts the demodb database volume. As described in CUBRID Processes, the cub_master process connects the cub_server process with the cub_cas process, which sends the requests, or the csql program.

Figure 1: CUBRID Operation Procedure.

As shown in Figure 1, if a JDBC application is connected to the server process which mounts the database volume file, the SQL statement can be executed in CUBRID. Two most popular methods to use CUBRID is to execute SQL manually using the command line CSQL Interpreter program or to write a program which uses various APIs like JDBC, PHP, Node.js, etc.

Note:
As we have discussed, the JDBC program connects to the database server through a broker while CSQL Interpreter directly connects to the database server bypassing the broker. This is an important difference between APIs and CSQL.

The CUBRID Manager database administration tool is developed on top of the JDBC driver. The SQL statement executed in CUBRID Manager are passed to the server through the JDBC API. Other database management functions that are available in CUBRID Manager (database creating/deleting, etc.) are executed through a management utility called CUBRID Manager Server which runs as a separate manager server daemon outside of the database.

Now, let's take a look at CUBRID Manager as an example to see how an SQL statement is executed in CUBRID. Refer to Figure 2 for this example.

1. The SQL statement entered in the query editor of the CUBRID Manager (for example, SELECT * FROM olympic WHERE host_year > 1988 LIMIT 4;) is sent to CUBRID JDBC driver. This assumes that a database has already been selected in CUBRID Manager before executing this SQL, and the JDBC connection has been established.
2. In the JDBC connection process, the connection is made to the port of the host specified in the JDBC connection information (JDBC Connection URL).
3. The cub_broker process receives the connection and allocates a cub_cas process for the session to the connection.
4. Then, it sends the socket connection to the cub_cas process so the connection between the JDBC driver and the cub_cas process is established.
5. Back to the SQL statement execution, the JDBC driver included in the CUBRID Manager sends the SQL statement of the query editor to the connected broker, the cub_cas process.

The broker sequentially calls db_open_buffer(), db_compile_statement() and db_execute_statement() among C APIs provided by the client library, to execute the received SQL statement. The db_open_buffer() function parses the SQL statement, the db_compile_statement() function compiles the execution plan, and the db_execute_statement() function executes SQL by sending XASL (eXtended Access Spec Language) to the server.

Figure 2: SQL Statement Execution Procedure in CUBRID.

As shown in Figure 2, the qmgr_execute_query() function is executed in the cub_cas, and the xqmgr_execute_query() function is executed in the cub_server process. The qmgr_execute_query() function in the client is the xqmgr_execute_query() function in the server. As shown above, CUBRID implements a communication interface of Remote Procedure Call (RPC) between a client and a server. In the server, the qexec_execute_query() and qexec_execute_mainblock() functions of the query processing module are used to execute XASL.

If you are interesting how queries are processed in CUBRID, I recommend you to read CUBRID Query Processing which provides the detailed step by step explanation using real examples.

By Park Kieun, Senior Software Engineer and Architect at Service Platform Development Center & IT Service Center, NHN Corporation.

We released CUBRID 9.0 beta version in October last year. Since then we have been working hard on stabilizing the beta features, fixing bugs, and improving the overall engine performance. Today I am excited to announce the immediate availability of the CUBRID 9.1 stable release. You can download CUBRID Database Server from http://www.cubrid.org/?mid=downloads&item=cubrid&os=detect&cubrid=9.1.0.

I would also like to announce that we will give a talk about CUBRID Database Sharding at Percona MySQL Conference on April 24, 2013, in Santa Clara, CA. Join us there to meet CUBRID Engineers and get the first-hand insight into the new CUBRID 9.1.

Below I will provide an overview of the latest changes and improvements in CUBRID 9.1.

Overview

CUBRID 9.1 is an upgraded and stabilized version of CUBRID 9.0 Beta. To learn more about the biggest features introduced in 9.x family, refer to 9.0 official announcement. Issues found in the 9.0 Beta version have been fixed and stabilized in this new 9.1 stable release. With a variety of query-related functionalities, CUBRID 9.1 offers improved query processing performance as well as improved query optimization. In addition, its multi-language related functionalities have been further improved. This new 9.1 release is accompanied by new CUBRID Tools and Drivers releases.

Backward Compatibility

Database compatibility

As a database volume of CUBRID 9.1 is not compatible with the database of CUBRID 9.0 Beta, users of CUBRID 9.0 Beta or previous versions should migrate their database. We have created a migration instructions which you can find in Upgrade section of the Release Notes.

Driver compatibility

The JDBC and CCI driver of CUBRID 9.1 are compatible with CUBRID 9.0 Beta and CUBRID 2008 R4.x version. Some features that are fixed and improved for 9.1 are not supported when 9.1 drivers connect to the previous versions.

Major enhancements

New SQL functions and index hints

• New SQL analytics functions like NTILE, LEAD and LAG have been introduced in CUBRID 9.1.
• WIDTH_BUCKET new SQL numeric function is also introduced.
• TRUNC and ROUND functions now also accept the date types.
• New SQL Hints:
  • Support a new index hint clause.
  • SQL hints for Multi UPDATE and DELETE statement.
  • SQL hints for MERGE statement.

Performance improvements and optimizations

• The performance of data replication in HA environment has been significantly improved in CUBRID 9.1.
  
• Improved multi-key range optimization.
• Enhanced optimization of ORDER BY and GROUP BY clause.
• Improved analytic function performance.
• Improved performance of INSERT ON DUPLICATE KEY UPDATE and REPLACE statement.
• Improved search and delete performance for non-unique indexes with many duplicate keys.
• Improved delete performance when insert and delete operations are repeated.
• The overall performance of SELECT operations has been improved by nearly 20%.
   
• Based on the results obtained from the basic performance test, we have found that the performance of INSERT, DELETE, and UPDATE operations are almost same as that of 9.0 Beta.

Multi-language support

• In CUBRID 9.1 we now support collation for tables.
• SHOW COLLATION statement and new CHARSET, COLLATION, and COERCIBILITY functions are now supported.
• Support collation with expansion which sorts French with backward accent order.
• Improved and fixed restrictions and issues of 9.0 Beta version.

CUBRID SHARD

• We have added cubrid shard getid command to verify the shard ID of the shard key.
• CUBRID SHARD is now available on Windows as well.

Administration utility

• cubrid applyinfo utility now also shows information about the replication delay.
• cubrid killtran utility now has the ability to show the query execution information of each transaction as well as the ability to remove transactions which executes a designated SQL.
• When a query timeout occurs, added a functionality to log the query execution information to the server error log and the CAS log files.

Behavioral Changes

• CUBRID_LANG environment variable is no longer used.
• CUBRID_CHARSET environment variable which sets the database charset instead of CUBRID_LANG and the CUBRID_MSG_LANG environment variable which sets the charset for utility and error messages.
• Change array execution functions such as cci_execute_array, cci_execute_batch function and Statement.executeBatch and PreparedStatement.executeBatch method of JDBC to commit whenever it executes an individual query under auto commit mode, while the previous versions commit once for entire execution.
• Change the behavior of cci_execute_array, cci_execute_batch and cci_execute_result function when an error occurs while they are executing multiple statements. These functions now continue to execute the entire given queries while the previous versions stop execution and return an error. Users can access the results and identify the errors with CCI_QUERY_RESULT_* macros.
• OFF is no longer supported for KEEP_CONNECTION broker parameter.
• SELECT_AUTO_COMMIT broker parameter is no longer supported.
• Change the allowed value range of a broker parameter APPL_SERVER_MAX_SIZE_HARD_LIMIT to 1 - 2,097,151.
• Change the default value of a broker parameter SQL_LOG_MAX_SIZE from 100 MB to 10 MB.
• Change the behavior of the call_stack_dump_activation_list parameter.

Numerous Improvements and Bug Fixes

• Fix many critical issues of the previous versions.
• Improve of fix many issues of stability, SQL, partitioning, HA, Sharding, utilities, and drivers.

For more details on changes, see the Release Notes in English or Korean.

So far CUBRID 9.1 is our biggest release which we would like you to try. In fact we have released new improved drivers for Node.js, PHP, PDO, Python, Perl, JDBC, ODBC, OLEDB, ADO.NET, and C. So you should definitely try the new, more performant and stable CUBRID 9.1 Database.

If you have any questions, feel free to leave your comment below.