Scenario:

Got below error from application end.

SQL state [HY001]; error code [1038]; (conn=15945) Out of sort memory, consider increasing server sort buffer size; nested exception is java.sql.SQLException: (conn=15945) Out of sort memory, consider increasing server sort buffer size
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89)

Investigation:

Detailed dig down on the MariaDB error log found the below error.

[ERROR] mariadbd: Out of sort memory, consider increasing server sort buffer size

With reference of the above error it found that the sort_merge_passes global status variable has a high value over 600 on Galera node1. Thus it is definitive that the value of sort_buffer_size global system variable value needs to be increased.

Solution:

Increase the value of sort_buffer_size.

Note:

There are no specific formula of how much to increase the value of sort_buffer_size but start with a small portion depending on the nature of your application sorting data size. You can increase with an increment of 2M, 4M, 16M, 32M, 128M at a time depending on your available physical memory and monitor the impact. Putting this variable at a high value GLOBALLY may leads to performance degradation as larger values may significantly slow down memory allocation, then set the value at SESSION level not GLOBALLY.