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

["Leonid Vasiliev" <lvasiliev@tarantool.org>]

[-- Attachment #1: Type: text/plain, Size: 3860 bytes --]




Hi! 
>Понедельник, 28 декабря 2020, 15:13 +03:00 от Nikita Pettik <korablev@tarantool.org>:
> 
>On 28 Dec 11:54, Leonid Vasiliev wrote:
>> 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?
>
>We have tpc-h/tpc-c bench at least as I know:
>https://github.com/tarantool/tpcc
>https://github.com/tarantool/tpch
>
>Please contact QA team to get instructions how to lauch them.
> 
 
As I understood after conversation with Timur, Oleg can help me.
So, I'll add him to the mailing list.
Oleg, can you help me with the benchmarks?
 
>> > 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."
>
>Ok, fine, at least it won't affect users which don't use SQL sub-system.
>So then we should only make sure that cache increase only improves
>SQL performance on standard benches.
>
>> > > 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
>> > > > > 
 
 
--
Leonid Vasiliev
 

[-- Attachment #2: Type: text/html, Size: 6000 bytes --]