feature: Cache disk files for intermediate results of aggregate operations to avoid OOM

[adofsauron]

Abstract:

TIANMU Engine - Aggregate operation intermediate results cache disk files to avoid OOM- requirements analysis

Related ISSUE: #21

Note of context:

The results of the current aggregate operation are cached in the in-memory HASH. Once the amount of data exceeds the RAM, the OOM occurs.

If the data volume exceeds RAM, convert it to disk file storage to avoid OOM

Functional Requirements:

1. The HASH result of the aggregation operation is cached in the disk file and properly participates in the operation

When the intermediate result exceeds RAM

Performance requirements:

1. The compression ratio of the cache file size to the original data content, and the ratio of disk space to memory

Influence compression algorithm

2. Disk cache file write speed and read speed

Standard disk hardware

It affects the speed of aggregation operations and the rules for writing and reading disk cache files

3. The impact on the performance of aggregation operations

Development cycle:

TODO:

[adofsauron]

Aggregate Cache disks - Development process

1. Funny layer

Step 1 Include

1. Idiots who can't read code

2. A variety of power jokers with managerial titles

Step 2: Strategy

1. Straight to death

2. Not qualified to participate in any development process

3. Requirement layer

Step 1 Include

1. Product side

2. Test side

Step 2: Strategy

1. Rigorous needs analysis to define boundaries

2. Leave clear evidence of any needs

3. In case of transgression

4. Kill immediately

5. Leave clear evidence to inform all

6. Remember the past as a guide to the future

7. Working layer

Step 1 Include

1. Someone who can read code

2. Someone who can go beyond existing code

Step 2: Strategy

1. Demonstrate the rationality of each detail design in detail

2. Cross validation

3. Development planning

4. Demand determination and counter-propaganda

5. One to two days

6. Programme pre-study

7. One to two days

8. Detailed scheme design

9. Two to three days

10. Code development

11. Three to five days

12. Write unit tests /MTR

13. Two to three days

[adofsauron]

Aggregate cache hard drives - Requirements analysis

1. Functional requirements

2. If the aggregate cache exceeds RAM, use hard disk files

3. Write the disk only when the RAM exceeds

4. Limit the size of cache files

5. The aggregation result must be the same before and after the change

6. Ensure that the query results are consistent only before and after the modification

7. INNODB does not guarantee the consistency of query results

8. You can set parameters to disable the feature dynamically

9. The aggregation operation in progress is not affected

10. Users decide whether to enable this feature

11. After the query is complete, the disk cache file is automatically cleared

12. Consider asynchrony

13. Disk cache clearing does not affect aggregate queries

14. Performance requirements

15. Disk data starts to be written after the RAM usage reaches the threshold

16. 80%

17. Reserve memory for other modules to work

18. Intermediate result Indicates the disk cache file write speed threshold

19. Compression ratio of the cache file to the original data

20. Compression speed of the cache file compression algorithm

21. Write disk I/OS synchronously or asynchronously

22. Check whether the block cache needs to flush disk I/OS in batches to improve performance

23. Indicates the speed threshold for reading cache files

24. The decompressing speed of the cache file compression algorithm

25. What can I do if the memory is insufficient when I read the cached file

26. Whether to use elimination and replacement mechanism

27. If there is no replacement

28. Terminate the aggregate query

29. Give a clear error message

30. Clear the disk cache

31. Impact of cache file reading on aggregate scanning

32. Aggregate scan sequential traversal

33. Append the intermediate result

34. Writing to the cached file will involve disk I/O

35. Determine whether to create a resident memory pool for cached files

36. Replace the BLOCK with LRU

37. Local data caching reduces repeated I/OS

38. Impact on aggregate query

39. Intermediate results are all in memory

40. Some intermediate results are cached on the disk

41. 30%

42. 50%

43. 80%

44. All intermediate results are cached on disk

45. Stability needs

46. mysqld dump occurs when data is being written to the disk cache

47. How do I clean residual files after mysqld restarts

48. Disk I/OS are abnormal when data is being written to the disk cache

49. The write fails

50. How do I verify the correctness of cached files

51. CRC

52. Other verification algorithms

53. Read data fails

54. Terminate the aggregate query

55. An error message is displayed

56. Clear the disk cache

57. Impact on other modules

58. Aggregate the consistency of computing module interfaces

59. Interface compatibility of the read/write module for the aggregated cache result

60. Pluggable and replaceable policy interfaces

61. Learn from the filesort module

[adofsauron]

Abstract:

Cache the disk file for the intermediate results of the aggregate operation to avoid the OOM summary design as a starting point for the next detailed design.

And play a role in communicating design ideas to other developers only.

Requirement analysis: 2022-11-17 mysql column storage Engine - Aggregate operation intermediate results cache disk files to avoid OOM- Requirement Analysis _ Zunwu World's blog -CSDN blog

Cache RAM analysis of current aggregated intermediate results:

Static structure:

Dynamic structure:

[adofsauron]

Summary design for adding disk cache:

Design Idea:

Maintain compatibility with the upper layer module interfaces of existing aggregation operations

Avoid excessive disk I/OS without causing OOM

Design Strategy:

Add DiskCache instead of BlockedRowMemStorage, but keep the interface consistent

DiskCache uses LRU internally to cache disk blocks

When the LRU reaches the upper limit to eliminate blocks, the Block must be flushed to disk and the occupied RAM space must be released

[adofsauron]

Architecture Design:

Static structure:

Dynamic structure:

[adofsauron]

Refer to the AIO used by mysql for asynchronous IO

https://dev.mysql.com/doc/refman/5.7/en/innodb-linux-native-aio.html

[adofsauron]

The data flow that interacts with the file system

[adofsauron]

More flexible memory control and disk swapping mechanisms are needed to make more efficient use of memory

[adofsauron]

buffer Settings for mysql5.7.36

mysql> show variables like  '%buffer%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| bulk_insert_buffer_size             | 8388608        |
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 40             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 536870912      |
| innodb_change_buffer_max_size       | 25             |
| innodb_change_buffering             | all            |
| innodb_log_buffer_size              | 1048576        |
| innodb_sort_buffer_size             | 1048576        |
| join_buffer_size                    | 262144         |
| key_buffer_size                     | 536870912      |
| myisam_sort_buffer_size             | 8388608        |
| net_buffer_length                   | 16384          |
| preload_buffer_size                 | 32768          |
| read_buffer_size                    | 4194304        |
| read_rnd_buffer_size                | 16777216       |
| sort_buffer_size                    | 4194304        |
| sql_buffer_result                   | OFF            |
| tianmu_insert_buffer_size           | 512            |
| tianmu_insert_max_buffered          | 65536          |
| tianmu_sync_buffers                 | 0              |
+-------------------------------------+----------------+
27 rows in set (0.00 sec)

mysql> show global status like '%innodb_buffer_pool%';
+---------------------------------------+--------------------------------------------------+
| Variable_name                         | Value                                            |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status        | Dumping of buffer pool not started               |
| Innodb_buffer_pool_load_status        | Buffer pool(s) load completed at 230208  7:42:46 |
| Innodb_buffer_pool_resize_status      |                                                  |
| Innodb_buffer_pool_pages_data         | 252                                              |
| Innodb_buffer_pool_bytes_data         | 4128768                                          |
| Innodb_buffer_pool_pages_dirty        | 0                                                |
| Innodb_buffer_pool_bytes_dirty        | 0                                                |
| Innodb_buffer_pool_pages_flushed      | 36                                               |
| Innodb_buffer_pool_pages_free         | 32512                                            |
| Innodb_buffer_pool_pages_misc         | 0                                                |
| Innodb_buffer_pool_pages_total        | 32764                                            |
| Innodb_buffer_pool_read_ahead_rnd     | 0                                                |
| Innodb_buffer_pool_read_ahead         | 0                                                |
| Innodb_buffer_pool_read_ahead_evicted | 0                                                |
| Innodb_buffer_pool_read_requests      | 1055                                             |
| Innodb_buffer_pool_reads              | 219                                              |
| Innodb_buffer_pool_wait_free          | 0                                                |
| Innodb_buffer_pool_write_requests     | 325                                              |
+---------------------------------------+--------------------------------------------------+
18 rows in set (0.00 sec)