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

[Leonid Vasiliev <lvasiliev@tarantool.org>]

Hi!

On 24.12.2020 19:57, Nikita Pettik wrote:
> On 11 Dec 18:29, Leonid Vasiliev wrote:
> 
> IMHO before pushing performance aimed patches, we should carefully
> review them. Could you run some SQL benchmarks to verify there's
> no degradation on any case?

Can you tell me where we have benchmarks for SQL and how to run them?

> Also, does this change affect non-SQL
> users? I mean does SQL tmp cache is lazy initialized or it allocates
> all 20mb right on start? For sure, 18mb is not a lot memory in 2020,
> but still it may turn out to be surprise for some users..

The memory allocation is done on per-request basis:
"The default page cache implemention does not allocate the full amount
of cache memory all at once. Cache memory is allocated in smaller chunks
on an as-needed basis."

>   
>> 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
>>>>