Open sughics opened 2 years ago
Issue potentially related to #35
Appears to be a memory issue on the database when retrieving the LogEvent list . Latest mysql error logs show Out of sort memory
error
These errors correlate with the exact times requests were made to to GET /admin/logEvent
.
I was able to replicate the same error as seen above via CLI sql statements as well. Error is thrown on any attempts to sort the returned data if all columns are selected e.g.
select * from <<table_name>> ORDER BY created DESC LIMIT 1000;
ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size
As suggested by @joe-lipson , indexes have not been added/set properly for the columns which are used to sort the results. Adding index correctly for the column i.e. month
used to filter the default query significantly reduced query times when tested via the CLI but the error still persisted when the /admin/logEvent
path was requested via the UI.
SQL logs were inspected on local db instance to get the actual SQL query made by the application. The query used by grails' scaffolded LogEvent controller implementation actually converts any selected sorting column e.g.month
(default) to uppercase. e.g. select * from log_event ORDER BY uppercase(month) DESC LIMIT 10
. Regardless of any indexes set for the relevant column, this query throws Out of sort memory
error.
Converting the sorting column to uppercase during the query is not explicitly defined anywhere is the codebase or configuration. It seems to be set internally by Grails or Hibernate
Fix
Changes have been made to the application to remove the default sorting specification sort 'month': 'desc'
from LogEvent static mapping.
An index()
method has been added to LogEvent to override the scaffolded index method, the default sorting column is also set here, in turn , preventing the case conversion on sorting columns during /admin/logEvent
query.
Changes to the DB to add correct index to the default sorting column and other sortable columns in the log_event
table have been added in ala-install.
An index()
method has been added to LogDetail controller to override the scaffolded index method, the default sorting column is also set here, in turn , preventing the case conversion on sorting columns during /admin/logDetail
query.
Changes to the DB to add correct index to the default sorting column and other sortable columns in the log_detail
table have been added in ala-install.
Looking into archiving current log_event
and log_detail
per README.md
To test the above in local environment, please enable general sql logs in your local mysql instance as root and tail the generated logs as you make requests to admin/logEvent
or admin/logDetail
. The relevant sql logs should not include the UPPER
function on the order column e.g. order by upper(logevent0_.month) desc limit 10
Please also test explicit sorting of the results using the columns in the UI for either of the pages mentioned above.
To enable SQL logging locally:
SET global general_log = 1;
SET global log_output. = 'file';
SET global general_log_file = '<path_to_file>' ;
Entity Type
,Entity Uid
, andRecord Count
.