Utilizing Database Performance Using Column Store - Literature review Example

?Utilizing Database Performance Using Column Store s) s) s) Affiliation(s) Email This paper seeks to discuss how databases can perform with DB column storage techniques. Over the past few years, databases systems running on column stores have been discussed and so much attention paid to them. In retrospect, column stores are used to store each and every database table column on its own in isolation. Every column in a database table is stored separately. In this system of database storage, the attribute values in each column are stored in a contiguous manner, they are compressed, and then densely packed; very much unlike traditional systems where databases would store entire records or rows of data, one row, after the other. This technique of data storage has its benefits but again several questions still exist on the same matter. For instance, how row based systems be able to be customized to achieve performances associated with column stores? This is the kind of question whose answers we seek to discuss in this document. 1. Introduction The paper seeks to show how database performance can be increases using database column storage techniques. The paper will be divided into sections which include a brief description about the database column storage, an explanation on how column store can utilize the performance of databases, how database performance will differ by using column store and not row storage. Additionally, areas where column storage count will also be discussed; these are areas of application of the technique will also be discussed. Finally, recommendations on the enhancements of column stores will come at the end of the paper. 2. Database Column Storage Column store database systems can be traced to the 1970’s; this was the first time when transposed files were being studied; Then followed the investigations into the vertical partitioning technique of clustering table attributes on a database. The mid 80’s witnessed the experience of the advantages of decomposed model of storage (DSM). This was the predecessor to column storage technique. It was considered better than the old row based system of storage. Nonetheless, row based database systems still went on to maintain dominance of the markets as a result of market needs, as well as non-favorable trends in technology to implement the column based systems of storage. This was despite the fact that the DSM technique was very suitable and had potential for better analytical queries. However, the 2000s had good tidings for research on column storage systems. Commercial systems of the same took off instantly. In this paper, we look at the technology and the application trends which have led to the renaissance of commercialization of the column stores. In comparison to the row-oriented stores of data, the column oriented, database systems were read optimized; this means that the when a query is sent, access is granted to the required fields only, and a reduction in disk input output processes and time is registered. student_Id Firstname Lastname Grade 1 James Smith A 2 Cathy Jones A- 3 Elizabeth Queen C Table 1: Sample Database Table In a computer, the database information has to be converted and bytes for storage in the hard drive or to be written onto the RAM. For row-based storages, the data in the database is serialized according to the values in each of the rows; then follows the data in the next row. The data is arranged as follows, in the row based model: 1, James, Smith, A; 2, Cathy, Jones, A-; 3, Elizabeth, Queen, C; On the other hand, the column based storage system would arrange the data in the following format for storage: 1, 2, 3; James, Cathy, Elizabeth; Smith, Jones, Queen; A, A-, C; Research on column stores indicates that, with compression, row-stores perform less effectively than column oriented systems. More formally, column storage systems store their data tables in the form of columns of data unlike the row based systems which store data in the form of rows of data; as seen in most relational database management systems. This system of storage; the column store method of storage is mainly best for systems like data warehouses, in addition to the customer relationship management systems, and finally, ad-hoc systems of inquiry, and library card catalogs. In these areas, large numbers of the same data items are used to compute aggregates on the data. Column oriented storage systems serialize the values in a column together; then follows values of the next column and so on, and so forth. 3. How Column Store can utilize the Performance of Databases Implementations of column stores work best for large data repositories which are read intensive and are read multiple times in unit time. This system or technique is applied in systems which read, only the most relevant data in a system. Column stores can be used to make better performance databases which only get the needed columns for queries made to the database. This technique results in better cache effects, in a system of storage of data or information. Also, column stores result in better compression of data in storage. Despite all the better results column stores may have for databases and performances for database systems, some applications may register reduced speeds in performance. In the group of the slower applications is the OLTP applications which have very many rows in data storage models. Presently, database systems are mainly in the traditional row based storage models. This is not as fast as the column based storage systems. Therefore, in an attempt to boost these speeds, developers and technologists involved should encourage people to adopt the column based database management systems. Already manufacturers have come up with some of these database systems. It is up to customers to switch to them for better performance. Ways in which column stores can be implemented in commercially row oriented database management systems include vertical partitioning, index-only plans, and finally, materialized views. These ways form the different design types which can be used towards the purpose. Vertical Partitioning involves the connection of fields that are from a row together. The reason for this is that column storage systems match up records in an implicit manner since the columns meant for storage are kept in the same order. This kind of optimization is not found in row-based database systems. This approach to database storage comes in handy because it only requires adding a column for integer positions for every table in the database. Doing this, results in a design of databases which performs much better than using primary keys in databases. Figure 1: Vertical Partitioning Primary keys of databases are sometimes large and even composite on certain occasions. Index-only plans are devised because the vertical partitioning way of implementing column stores has its fair share of limitations. Among these problems is the fact that if the approach is implemented on a database, there is a need for the position attribute which has to be kept for each column. This is a disadvantage because space is wasted and also disk bandwidth is used up. Additionally, row stores of data have extra headers of on each tuple which also takes up space. The other strategy is the materialized view which creates optimal sets of views meant for each query flight of the workloads. In this sense, optimization of the columns in each flight results in only the columns required for answering flight queries. 4. Column Oriented Execution This section of database execution involves ways of optimization meant to improve the performance of database systems of the column store architecture. The first optimization technique is compression. When data is compressed using the column oriented algorithms of compression, and let to remain in the format when being operated on results to an increase in performance of queries by up to four times magnitude. Also, the data that is stored in columns are much easier to compress than the data that is stored in rows. Other techniques include late materialization, invisible joined, and finally block iteration methods. Late materialization often improves the performance in databases by magnitudes of up to three. Invisible join, on the other hand, results on the improvement of performance of up to 50-75%. Block processing, on the other hand, results to increase in performance to factors of 5-50%. APPLICATION AREAS include data warehousing, data mining, business intelligence applications. Other uses include the scientific management of data. Available commercial column stores in these applications are Kdb, Vector Wise, and Sybase IQ. RECOMMENDATIONS From the discussions and illustrations above, we may conclude that column oriented systems are efficient especially when an aggregate is to be computed over many rows. However, this is usually only for a small subset of the columns of data – the reason for this is that it is quite faster to read smaller data sub sets than it is to read all the data. This is an advantage of the column stores. Other benefits of column stores include the fact that these systems tend to be efficient when new column values are supplied at once for all rows – this is because of the efficiency that is associated with writing of column data, which in effect replaces the old data in the column while not even touching any other columns in the rows concerned. In order to continue getting such benefits in column stores, database systems need to switch towards this direction – this is because even attempting to implement hybrid database systems does not achieve more favorable results than simply using entirely column oriented database systems. In order to improve compression, there are quite a number of implementations like Vertica which need to sort the rows. An example of doing this is the use of low cardinality columns in compression as the first keys in sorting. For instance, if given a table that has columns age, sex, and name, it is best that first we sort the values on sex (it has a cardinality of two), and then age follows (it has a cardinality of less than 150), and finally name. 5. Works Cited Abadi, D. J. "Column-stores vs. Row-stores: how different are they really?" SIGMOD (2008): 967-980. Print. —. "Integrating Compression and Execution in Column Oriented Database Systems." 2006 ACM SIGMOD International Conference on Management of Data (2006): 671-682. Print. —. Query Execution in Column Oriented Database Systems. PhD Dissertation. Massachussets: MIT, 2008. Print. Abadi, Danieli J. Column-oriented Database Systems. New Haven: Yale University, 2006. Print. Copeland, G. P. A Decomposition Storage Model. SIGMOD, 1985. Print. Dai, Xiaolei. The Application of Materialization Strategies on OLAP in Column Oriented Database Systems. New York: SIGMOND, 2006. Print. Ding, Xiangwu and Wenbing Yu. An Adaptive Projection Strategy and Its Implementation in Column Stores. New York: IEEE, 2011. Print. Read More