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 |