Re: [Tarantool-patches] [PATCH] sql: increase default cache size

[Leonid Vasiliev <lvasiliev@tarantool.org>]

On 11.12.2020 15:09, Nikita Pettik wrote:
> On 11 Dec 01:37, Leonid Vasiliev wrote:
>> Increase the maximum number of in-memory pages to use
>> for temporary tables.
>> (https://www.sqlite.org/compile.html#default_cache_size)
>>
>> Part of #5609
>> ---
>>
>> Hi SQL team. It is a simplest part of #5609.
>> This patch increases the default SQL cache size by 10 times to 20 MB.
>> A similar experiment shows a 10% performance increase for some datasets.
>> (https://github.com/tarantool/tarantool/issues/5593#issuecomment-740044007).
>> Mons approved these changes. I haven't done any research to determine the
>> optimal value. So feel free to throw this patch away.
> 
> Hi, why not inroduce separate handler for user to change this setting?
> Since SQLite is embedded database, almost all settings are set at
> compile time. In constrast, we can move it to the box.
> 

If I understood you correctly, the settings should be implemented when 
working on # 5609 (this path doesn't close the task). This is the
simplest improvement to update the default.

>> https://github.com/tarantool/tarantool/issues/5609
>> https://github.com/tarantool/tarantool/tree/lvasiliev/gh-5609-increase-default-sql-sort-cache-size
>>
>>   src/box/CMakeLists.txt | 4 ++++
>>   1 file changed, 4 insertions(+)
>>
>> diff --git a/src/box/CMakeLists.txt b/src/box/CMakeLists.txt
>> index 19203f7..7372179 100644
>> --- a/src/box/CMakeLists.txt
>> +++ b/src/box/CMakeLists.txt
>> @@ -218,6 +218,10 @@ if(CMAKE_BUILD_TYPE STREQUAL "Debug")
>>     add_definitions(-DSQL_DEBUG=1)
>>   endif()
>>   add_definitions(-DSQL_TEST=1)
>> +# Set the maximum number of in-memory pages to use for temporary tables.
>> +# 20000 * 1024 = 20480000 bytes.
>> +# (https://www.sqlite.org/compile.html#default_cache_size)
>> +add_definitions(-DSQL_DEFAULT_CACHE_SIZE=-20000)
>>   
>>   set(EXT_SRC_DIR ${CMAKE_SOURCE_DIR}/extra)
>>   set(EXT_BIN_DIR ${CMAKE_BINARY_DIR}/extra)
>> -- 
>> 2.7.4
>>