MySQL [SQL DB] Evaluation

[ppyordanov]

The SQL database management system is going to be evaluated based on a number of different criteria. Results will be compared to the performance of a NoSQL alternative(for example, MongoDB or CouchDB). THe following tests are going to be carried out:

• Data Write:
  • insert around 60 000 records in the key DB table (DataReading)
  • single instance of type data reading insertion
  • updating a single instance of type data reading
• Data Read:
  • retrieve all records in DataReading table
  • retrieve a single instance of type data reading from the table
• Additional Tests:
  • delete a single record from the table

NoSQL (MongoDB) Evaluation details: #30

DBMS choice information: #27

[ppyordanov]

• Data deserialization from sample_data.json file now works. 5000 readings were inserted in the table for the first test:

• Execution time is around 30-40 seconds, not bad at all , around _6 ms per record_:

• Example output in the controller view on front end (again, some data has been removed for improved readability):

• For the complete benchmarking, 60, 000 data readings were inserted in the DataReading table using the Hibernate session manager and the data reading DAO (Data Access Object):

• This was even faster on average, taking _4 min, 44 sec_ in total, which is _4,73 milliseconds_ per insertion:

284 000 millisec / 60 000 insertions = 4,7333(33..) milliseconds

[ppyordanov]

_OUTPUT /some data removed for readability/_

Test DB
Records inserted in the database: 5000

DATA:

2014-10-13 10:45:55.0 2014-10-13 10:44:59.0 2014-10-13 10:42:56.0 2014-10-13 10:41:53.0 2014-10-13 10:40:53.0 2014-10-13 10:39:53.0 2014-10-13 10:38:54.0 2014-10-13 10:37:53.0 2014-10-13 10:36:52.0 2014-10-13 10:35:52.0 2014-10-13 10:34:52.0 2014-10-13 10:33:56.0 2014-10-13 10:33:00.0 2014-10-13 10:31:53.0 2014-10-13 10:29:01.0 2014-10-13 10:27:51.0 2014-10-13 10:26:59.0 2014-10-13 10:25:51.0 2014-10-13 10:23:52.0 2014-10-13 10:22:50.0 2014-10-13 10:21:51.0 2014-10-13 10:20:56.0 2014-10-13 10:19:52.0 2014-10-13 10:18:50.0 2014-10-13 10:17:58.0 2014-10-13 10:16:50.0 2014-10-13 10:15:50.0 2014-10-13 10:14:54.0 2014-10-13 10:13:50.0 2014-10-13 10:12:50.0 2014-10-13 10:11:51.0 2014-10-13 10:10:49.0 2014-10-13 10:09:50.0 2014-10-13 10:07:52.0 2014-10-13 10:06:46.0 2014-10-13 10:05:45.0 2014-10-13 10:04:44.0 2014-10-13 10:01:45.0 2014-10-13 10:00:43.0 2014-10-13 09:59:47.0

RESULT:Execution time: 0 min, 30 sec, 615 millisec```

[ppyordanov]

_FINAL RESULTS /NO INDICES/_

Two consecutive tests were carried out to ensure that the information is accurate as performance depends on system resources.

• FULL TEST 1:

Test DB

Records inserted in the database:

DATA: skipped

RESULT:

Insert single record:
Single insert in tables DEVICE and DATA_READING: Execution time: 0 min, 0 sec, 260 millisec

Insert real data (SIM):
Execution time: 4 min, 43 sec, 240134 millisec Data size:
12 * 5000

Update single record:
Execution time: 0 min, 0 sec, 70 millisec

Get single record:
Execution time: 0 min, 0 sec, 46 millisec

Delete single record:
Execution time: 0 min, 0 sec, 30 millisec

Get all records:
Execution time: 0 min, 5 sec, 72 millisec

TOTAL:

Execution time: 4 min, 48 sec, 240612 millisec

• FULL TEST 2:

Test MySQL DB

DATA: skipped

RESULT:

Insert single record:
Single insert in tables DEVICE and DATA_READING: Execution time: 0 min, 0 sec, 246 millisec

Insert real data (SIM):
Execution time: 4 min, 9 sec, 240310 millisec Data size:
12 * 5000

Update single record:
Execution time: 0 min, 0 sec, 54 millisec

Get single record:
Execution time: 0 min, 0 sec, 25 millisec

Delete single record:
Execution time: 0 min, 0 sec, 17 millisec

Get all records:
Execution time: 0 min, 3 sec, 448 millisec

TOTAL:

Execution time: 4 min, 13 sec, 240101 millisec

[ppyordanov]

_RESULTS_

_AVG_TOTAL_ = (288 + 253 sec)/2 = 270,5 sec

_TOTAL_OPERATIONS_ ≈ 60 010

_AVG_PER_OPERATION_ = AVG_TOTAL/TOTAL_OPERATIONS = 270 500 millisec / 60 010 op = _4.507582 millisec/op_

[ppyordanov]

• FULL TEST 3: 5000 INSERTIONS

Test MySQL DB
MySQL

DATA: skipped

RESULT:

Insert single record:
Single insert in tables DEVICE and DATA_READING: Execution time: 0 min, 0 sec, 222 millisec

Insert real data (SIM):
Execution time: 0 min, 37 sec, 115 millisec Data size:
1 * 5000

Update single record:
Execution time: 0 min, 0 sec, 84 millisec

Get single record:
Execution time: 0 min, 0 sec, 31 millisec

Delete single record:
Execution time: 0 min, 0 sec, 90 millisec

Get all records:
Execution time: 0 min, 0 sec, 989 millisec

TOTAL:

Execution time: 0 min, 38 sec, 531 millisec

_FULL TEST 4: 200 000 INSERTIONS_

Test MySQL DB
MySQL

DATA: skipped

RESULT:

Insert single record:
Single insert in tables DEVICE and DATA_READING: Execution time: 0 min, 0 sec, 267 millisec

Insert real data (SIM):
Execution time: 11 min, 39 sec, 643 millisec Data size:
40 * 5000

Update single record:
Execution time: 0 min, 0 sec, 74 millisec

Get single record:
Execution time: 0 min, 0 sec, 52 millisec

Delete single record:
Execution time: 0 min, 0 sec, 20 millisec

Get all records:
Execution time: 0 min, 13 sec, 615 millisec

TOTAL:

Execution time: 11 min, 53 sec, 674 millisec

• MySQL Admin page:

AVG_TIME/ OP = 713 674 millisec/ 200 010 op ≈ 3.5681 millisec

[ppyordanov]

_Conclusions_

• Retrieving all records might be a bit slow in the case of extreme data size manipulation (200 000 records in the DATA_READING table):

  
  Test MySQL DB
  MySQL
  
  DATA: skipped
  
  RESULT:
  
  Insert single record:
  
  Insert real data (SIM):
  Data size:
  40 * 5000
  
  Update single record:
  
  Get single record:
  
  Delete single record:
  
  Get all records:
  Execution time: 0 min, 12 sec, 894 millisec
  
  TOTAL:
  
  Execution time: 0 min, 12 sec, 894 millisec
  

Each consecutive retrieval in the same session reduces the time by half approximately (session caching).

And for 60 000 entries complete retrieval is around 3-5 seconds:

Test MySQL DB
MySQL

DATA: skipped

RESULT:

Insert single record:

Insert real data (SIM):
Data size:
12 * 5000

Update single record:

Get single record:

Delete single record:

Get all records:
Execution time: 0 min, 4 sec, 31 millisec

TOTAL:

Execution time: 0 min, 4 sec, 31 millisec