An integration approach of hybrid databases based on SQL in cloud computing environment

#Database #integration #NoSQL #SQL #ReadPaper

Contents

  • What is IOT？
  • SQL Versus NoSQL
  • Experiment
  • SQL and NoSQL Integration
  • Conclusion

What is IoT ？

  • IoT refers to any object/thing around us which is connected to internet.
  • This object is also able to communicate through internet.

Like temperature sensor connected to oven, fridge or just to measure a room temperature. IoT is network of such things which may communicate with each other or with user.

IoT is having application in various domains like smart city, industrial, medical services, etc.

SQL Versus NoSQL

Experiment

Garden water sprinkler application

Temperature, humidity, soil moisture sensors are used in the hardware along with the water level detection.

Sprinkler motor is turned ON depending on the inputs from soil moisture and water level in the tank. If the soil moisture level drops below the threshold value and if water level is TRUE, then sprinkler motor turns ON.

System Architecture

System Architecture

The data from these sensors is collected and is sent to database server and user email ID. The aim is to further utilize this data for analysis using data processing tools.

Data is stored in both MySQL and MongoDB database systems for carrying out the comparison study.

Database Setup

MySQL

Db.collection.insert({
	sensor_id: <sensor ID number>,
	Humidity: <humidity sensor reading>, 
	Soil_moisture: <moisture sensor reading>, 
	Temperature: <temperature sensor reading>, 
	CreateDate: new Date(), 
	Userid: <User ID> 
})

Experiment Results

  1. SELECT query with varying number of threads

Here is the summary of the first two experiments from the document comparing MySQL and MongoDB databases for an IoT application:

1) SELECT Query with Varying Number of Threads:

Measure the response time of SELECT queries with varying numbers of threads.

  • Setup: The number of threads was varied from 1 to 10. Each SELECT query fetched 1000 records from a database containing 10,000 records.
  • Results:
    • For up to 4 threads, the response times for MySQL and MongoDB were similar.
    • At 10 threads, MongoDB showed a significant increase in response time compared to MySQL.
    • Observation: Too many threads in MongoDB can overwhelm the system, leading to increased latency and CPU starvation.

2) INSERT Query with Varying Number of Threads:

  • Objective: Measure the response time for INSERT queries with varying numbers of threads.
  • Setup: Similar to the SELECT query test, the number of threads was varied from 1 to 10.
  • Results:
    • The response time for MySQL increased at a faster rate compared to MongoDB.
    • MongoDB demonstrated faster performance for INSERT operations.
    • Observation: MongoDB outperformed MySQL in write latency, making it a better choice for applications with heavy write operations.

These experiments highlight the differences in performance characteristics between MySQL and MongoDB, with MongoDB showing advantages in handling write operations under load, while MySQL maintained more stable read performance with higher thread counts.

  1. INSERT query with varying number of threads
  2. SELECT query with varying number of records
  3. INSERT query with varying number of records

Each database has its own advantages and disadvantages.

  • MongoDB showed less response time in some scenarios compared to MySQL.
  • MySQL responses were more stable compared to MongoDB.

SQL and NoSQL Integration

  • SQL + X[NoSQL]
    • Redis+MySQL As a Caching Layer for MySQL
  • Hybrid Database
    • MSI Architecture

MySQL + Redis Caching Layer

Scenario: Improve read performance and reduce database load.

Redis

  • MySQL: Serves as the primary persistent data storage, handling complex queries and transactions.
  • Redis: Acts as a caching layer, storing frequently accessed or common data such as user sessions and popular product information.

Example:
When a user requests data, the application first checks the Redis cache. If the data is found in the cache, it is returned immediately. If not, the data is fetched from MySQL, then stored in Redis for future requests. This approach reduces the load on MySQL and improves the overall response time of the application.

缓存层

场景：提高读性能，减少数据库负载。

  • MySQL：作为主要的持久化数据存储，处理复杂的查询和事务。
  • Redis：作为缓存层，存储常用或频繁访问的数据，如用户会话、热门商品信息等。

Hybrid Database

MSI mainly includes eight components

  • API dispatcher
  • SQL parser
  • SQL optimizer
  • SQL router
  • SQL executor
  • DBMS adapter
  • Result merger
  • Meta-data management

MSI Architecture

  • The MSI's input and the result of the SQL process is the MSI's output.

Conclusion

  • SQL and NoSQL databases offer unique sets of capabilities and advantages.
  • The key to successful integration lies in understanding the intrinsic properties of each database type and crafting a strategy tailored to the specific needs of the organization.

Reference

Table Of Contents
An integration approach of hybrid databases based on SQL in cloud computing environment
Contents
What is IoT ？
SQL Versus NoSQL
Experiment
Garden water sprinkler application
System Architecture
Database Setup
Experiment Results
1. SELECT Query with Varying Number of Threads:
2. INSERT Query with Varying Number of Threads:
SQL and NoSQL Integration
MySQL + Redis Caching Layer
Hybrid Database
Conclusion
Reference