Investigate and reduce amount of 'CACHE' SQL for Projects::NotesController#create
Summary
As listed in epic: &3873 (closed), we detected several end-points that have a big amount of CACHE
SQL.
Projects::NotesController#create
is one of them.
It seems that if new Note contains a lot of reference links (to labels, snippets, merge_requests, users, issues), we have N+1 'CACHE' queries, which can lead in some cases up to 23,306
CACHED queries.
json.path.keyword | 50th percentile of json.db_cached_count | 75th percentile of json.db_cached_count | 95th percentile of json.db_cached_count | Average json.db_count | Average json.db_write_count | Average json.db_duration_s | Count | |
---|---|---|---|---|---|---|---|---|
notes | 23306 | 23306 | 23306 | 23347 | 7 | 0.785030007362366 | 1 |
The cached queries help with reducing DB load, but they still:
- consume memory
- require as to re-instantiate each AR object
- require as to re-instantiate each relation of the object
- makes us spend additional CPU-cycles to look into a list of cached queries.
Improvements
We should detect those N+1 CACHE
queries and try to make them independent on the number of references (constant).
We should treat the CACHE
the same as N+1
queries.
They are cheaper, but they are not cheap at all from ~memory perspective.
Involved components
Potentially fixing Banzai::ReferenceFilters and Banzai::ReferenceParser, will improve all the places where we use markup, such as:
- Blobs
- File preview
- Issue Preview
- Notes Preview
Testing
I run memory profiler, to compare memory consumption, before and after the change for Projects::NotesController#create
action
I tested with a note that contains 165 label
and snippet
references:
- ~nik $51
- ~nik $51
Memory consumption
master | 228626-reduce-cached-queries-for-banzai-filters | |
---|---|---|
Total allocated | 82.34 mb | 45.44 mb |
Total retained | 11.67 mb | 4.15 mb |
allocated memory by file - app/models/label.rb | 504784 | 467107 |
allocated objects by file - app/models/label.rb | 2314 | 1999 |
retained memory by file - app/models/snippet.rb | 67364 | - |
Number of calls
master | 228626-reduce-cached-queries-for-banzai-filters | |
---|---|---|
db_count | 372 | 44 |
db_write_count | 5 | 5 |
db_cached_count | 332 | 4 |
duration_s | 9.81588 | 2.25808 |