AEROSPIKE VS MYSQL

Comparative Analysis Of Legacy Database(Mysql) & Nosql Database Technology For Streaming Data Storage & Retrieval

From decades traditional databases are mainly used for data storage, retrieval and management of data. However, due to increasing need for scalability and performance, alternative systems have emerged, namely NoSQL technology. The rising interest in NoSQL technology like Cassandra and Aerospike as well as the growth in the number of use case scenarios, over the last few years resulted in an increasing number of evaluations and comparisons among competing technologies.

We were assigned the task to store and retrieve streaming data generated from sensors in MySQL and Aerospike and to compare database performance. MySQL is and open source relational database management system based on structured query language (SQL), whereas Aerospike offers and open source ,flash optimized NoSQL data base that demonstrate 10X better performance and better reliability than other NoSQL databases like Cassandra, Couchbase etc. Some of the key differences between MySQL and Aerospike technology are below.

• Aerospike is flash-optimized in-memory NoSQL Database, whereas MySQL is widely used open source RDBMS.
• Aerospike use key value database model, MySQL use relational RDBMS.
• Aerospike use Linux as server operating system, MySQL runs on windows, linux, solaris and OS X.
• Aerospike is schema free while MySQL use a rigid schema.
• Both Aerospike and MySQL use secondary indexes.
• Aerospike use sharding as portioning method whereas MySQL use horizontal partitioning, sharding with MySQL Cluster or MySQL Fabric.
• Aerospike use selectable replication factor while MySQL use Master-master replication & Master-slave replication.
• Both Aerospike and MySQL are concurrent and durable.
• DB engines ranking of Aerospike and MySQL is shown in below figure

    Ref: http://db-engines.com/en/system/Aerospike%3BMySQL

It is clear from above figure that MySQL ranking is same from decades whereas Aerospike ranking is increasing gradually. 

Key Performance Measures:

Some of the key performance measures selected for our experiment are,

Database Memory Usage: To keep track of database utilization, we need to monitor the total memory used at any point in time if we run out of space in your database, users will be unable to update or add information to the database.

Measurement of Read/Write Operation: This means how many kilobytes of data are being read out and written in. Monitoring these stats will enable us to measure the workload on your database server.

Usage of Disk and Cache: We also need to be aware of the transactions that utilize the disk and cache resources.

Client Access License (CAL): This monitoring will help us to keep a close eye on number out connection count.

Connection Status: Monitors and keeps track of opened and failed/aborted connections.

Database Availability: Monitors uptime statistics.

Transaction Logs: Monitoring our databases and transaction logs is important to ensure that we have adequate free space inside the database for new data to be added.

Streaming data Generation & Performance Comparison:

The ideology of analysis is based on streaming data coming from various sources which is to be written and read on database simultaneously. For comparative analysis latest trending database Aerospike was analyzed in contrast of Legacy MySql database. The Streaming data was generated by android platform by using sensors which is read by Android application and sent to database over TCP/IP network. The database is hosted on system which utilizes a socket application to acquire and perform CURD. For performance analysis of both databases following tools were used.

MySQL Workbench: MySQL Workbench is a graphical tool for working with MySQL Servers and databases. It provides a suite of tools to improve the performance of MySQL applications. Database administrators can quickly view key performance indicators using the Performance Dashboard. 

AMC: The Aerospike Management Console (AMC) is a web-based tool to monitor/manage an Aerospike cluster. It provides live updates to the current status of a cluster. It includes features to let you see at a glance the throughput, storage usage, and configuration of a cluster.

MySQL workbench was configured at port 3036 whereas AMC was configured to port 8081. Android application can connect with only server at time so data was first acquired and saved to MySQL and then Aerospike database. Screen shots of both tools are shown in figure 1 and 2. 

 

Figure 1: MySQL Workbench

Figure 2: Aerospike Management Console

From Figure 1 and 2 it is clear that we can check all the key performance measures of both databases from above dash boards like server status, traffic information, connection statuses, database memory usage and measurement of read and write operations.

Traffic Status:

Traffic status of both databases are shown in figure 3 &4.

   

Figure 3: Traffic Status MySQL

Figure 4: Traffic Status AMC

Reads per Second:

Reads operation per second are shown in figure 5 and 6.

Figure 5: Reads Per Sec MySQL

                                                   

Figure 6: Reads Per Sec AMC        

Conclusions:

Following conclusion can be drawn from above comparative analysis.

1. Aerospike queries are distributed and processed in parallel across the cluster and results on each node can be filtered, transformed, aggregated via user defined functions. This enables developers to enhance key value workloads with a few queries and some in-database processing.
2. Aerospike use In-memory for high-speed transaction and NoSQL for scaling purpose.
3. Aerospike cluster are self-healed and autonomous that they don't require to manual assistance for performance and optimization.
4. Aerospike has web-based client connectivity for transaction.

In a boarded context it can be said that legacy database provides high concurrency and consistency as compared to currently trending NoSQL technologies. But the upcoming cloud of big-Data requires performance with optimized speed and accuracy which cannot be fulfilled by legacy tools so development and evaluation for NoSQL technologies has to incorporate in future application to ensure a healthy comparison with market.

Performed By:

Dr. Tariq Mahmood  (Associate Professor PAF KIET)

Anwar-ul-Haque         (MSCS student PAF KIET)

Syeda Midhat Zaidi     (MSCS student PAF KIET)