Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
Description
When server starts it sets my_default_record_cache_size to the value of global variable read_buffer_size.
The user cannot change my_default_record_cache_size after server restart.
The default value for read_buffer_size is 131072.
When creating a heap/memory table, the value of my_default_record_cache is used as the
maximum block size used when allocation data for records.
This causes performance issues in the case where the record length is big (> 1000 bytes) and the my_default_record_cache is small.
To fix this, two things should be done:
- Update my_default_record_cache_size if the user changes global.read_buffer_size.
- Use the session value of read_buffer_size when creating heap tables
For some test that I have been running, which is using record length=633, the speed of the query doubled when doing
set @@read_buffer_size=1024*1024;
This enabled the heap table to put 165 rows in a block instead of just 21 and do 7 times less malloc calls.
Attachments
Issue Links
- relates to
-
MDEV-35700 Consumes more and more memory until OOM. Consistently
-
- Open
-
-
-
- Open
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Fix Version/s | 10.6.21 [ 29953 ] |
| Description |
When server starts it sets my_default_record_cache_size to the value of global variable read_buffer_size.
The user cannot change my_default_record_cache_size after server restart. The default value for read_buffer_size is 131072. When creating a heap/memory table, the value of my_default_record_cache is used as the maximum block size used when allocation data for records. This causes performance issues in the case where the record length is big (> 1000 bytes) and the my_default_record_cache is small. To fix this, two things should be done: - Update my_default_record_cache_size if the user changes global.read_buffer_size. - Use the session value of read_buffer_size when creating heap tables |
When server starts it sets my_default_record_cache_size to the value of global variable read_buffer_size.
The user cannot change my_default_record_cache_size after server restart. The default value for read_buffer_size is 131072. When creating a heap/memory table, the value of my_default_record_cache is used as the maximum block size used when allocation data for records. This causes performance issues in the case where the record length is big (> 1000 bytes) and the my_default_record_cache is small. To fix this, two things should be done: - Update my_default_record_cache_size if the user changes global.read_buffer_size. - Use the session value of read_buffer_size when creating heap tables For some test that I have been running, which is using record length=633, the speed of the query doubled when doing set @@read_buffer_size=1024*1024; This enabled the heap table to put 165 rows in a block instead of just 21 and do 7 times less malloc calls. |
| Assignee | Sergei Golubchik [ serg ] |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.6.21 [ 29953 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Summary | Heap tables are not using THD:read_buffer_size | Heap tables are calling mallocs to often |
7901c086b84 is ok to push
after fixing my_default_record_cache_size assignment as discussed on slack
| Assignee | Sergei Golubchik [ serg ] | Michael Widenius [ monty ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Link | This issue relates to MDEV-35700 [ MDEV-35700 ] |
| Link | This issue relates to MDEV-35750 [ MDEV-35750 ] |
| Fix Version/s | 10.6.21 [ 29953 ] | |
| Fix Version/s | 10.6 [ 24028 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Component/s | Storage Engine - Memory [ 10300 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
Commit message:
Heap tables are allocated blocks to store rows according to my_default_record_cache (mapped to the server global variable read_buffer_size).
This causes performance issues when the record length is big (> 1000 bytes) and the my_default_record_cache is small.
Changed to instead split the default heap allocation to 1/16 of the allowed space and not use my_default_record_cache anymore when creating the heap. The allocation is also aligned to be just under a power of 2.
For some test that I have been running, which was using record length=633, the speed of the query doubled thanks to this change.
After discussions with Sergei, we decided that instead of doing allocations based on read_buffer_size, lets change heap so that instead of using record_buffer in heap, we just split the allocations in 10 segments. This will automatically increase the allocation size if max_heap_table_size & tmp_memory_table_size are changed.