Porting and Performance Analysis of InTraBase from PostgreSQL to Oracle

Abstract

Network monitoring is an important task in the Internet. It is also a very complex task. Especially managing the measurement data, since the amount of data involved makes it difficult to organise and store. InTraBase was introduced to solve the data management problem experienced with other network monitoring tools. InTraBase stores all the information inside a database and perform the analysis directly in the database. Our hypothesis is that a system like InTraBase will perform better if implemented in Oracle. We base this on the claims of the database vendors, along with the fact that Oracle has a collection of tuning tools and advisors. These tools are easy to use because they can be accessed through the GUI in Enterprise Manager. ; pgInTraBase is a PostgreSQL-based implementation of InTraBase. During the porting of pgInTraBase we identified a small set of differences in PostgreSQL and Oracle's syntax. For instance Oracle does not have 'LIMIT' or 'OFFSET' clause for SQL. We have found simple ways to work most of these clauses. In most other cases we found equivalent functions in Oracle. In pgInTraBase the queries were concatenated as a string to include tablename and various variables and then the string was given as a parameter to be executed. In oraInTraBase we used a synonym to include tablename, and bind variables for other variables. This was done in order for the parser to be able to reuse the execution plans. We see the process of analysing data as a two phases process: 1) Upload – upload packet headers into the database from a flat file. 2) Analysis – analyse the data to find the root cause for throughput. Systematically tuning of the procedure that populates the database with packet headers, reduced the running time of the Oracle prototype to approximately 20% of the original time when using 1GB trace. Compared to PostgreSQL the execution time of the tuned process is almost cut to half. The Oracle version performs significantly better than the PostgreSQL version in the upload phase. The process of analysing the packet headers was also tuned in a systematic way. After tuning, the analysis of a 50MB trace takes 2% of the time it used before tuning. Compared to PostgreSQL a 50MB trace analysed in Oracle takes only about 42% of the time. Comparing the running time of traces of 10MB, 50MB, 100MB and 1GB show the same results, while using a 10GB trace Oracle needs 182% of the time PostgreSQL does. However, Upload is much faster in Oracle than in PostgreSQL. So much that if we add the time Oracle use for both Upload and Analysis this takes less time than the Upload alone in PostgreSQL. This highlights the very different performances of the two DBMS depending on the task.