Proxysql: ProxySQL handle enourmous number of users

[ton31337]

It would be awesome to have such functionality, like to avoid LOAD MYSQL USERS TO RUNTIME, because it's to heavy with >100k users.

Would be nice if it worked with just inserting new records into disk.mysql_users or main.mysql_users

@renecannao

[skirmis]

👍 for this long awaited feature

[renecannao]

Hi @skirmis . Can you please give more context on why this feature is so important for you?
I already know the specific use case from @ton31337 .
Thanks.

[renecannao]

perf report with 100k users highlight a very clear bottleneck:

    70.99%  proxysql  proxysql             [.] PtrArray::remove_fast(void*)
     2.91%  proxysql  proxysql             [.] je_arena_ptr_small_binind_get
     2.30%  proxysql  proxysql             [.] je_arena_bitselm_get_mutable
     1.53%  proxysql  proxysql             [.] je_arena_mapbitsp_read
     1.29%  proxysql  proxysql             [.] je_arena_mapbitsp_get_const
     1.21%  proxysql  proxysql             [.] je_arena_mapbitsp_get_mutable
     1.20%  proxysql  proxysql             [.] je_arena_mapbits_get
     0.94%  proxysql  proxysql             [.] je_arena_miscelm_to_pageind
     0.86%  proxysql  libc-2.19.so         [.] vfprintf
     0.76%  proxysql  proxysql             [.] je_arena_salloc
     0.53%  proxysql  proxysql             [.] je_index2size_compute
     0.53%  proxysql  proxysql             [.] je_arena_mapbits_allocated_get
     0.53%  proxysql  proxysql             [.] je_arena_mapbits_large_get
     0.44%  proxysql  proxysql             [.] je_witness_assert_lockless
     0.42%  proxysql  proxysql             [.] je_arena_mapbits_small_runind_get
     0.39%  proxysql  proxysql             [.] je_arena_miscelm_get_mutable
     0.38%  proxysql  proxysql             [.] je_arena_mapbits_binind_get
     0.33%  proxysql  proxysql             [.] je_tsdn_null
     0.29%  proxysql  proxysql             [.] je_index2size_lookup
     0.27%  proxysql  libc-2.19.so         [.] memset
     0.27%  proxysql  proxysql             [.] je_tcache_dalloc_small
     0.26%  proxysql  proxysql             [.] je_arena_miscelm_to_rpages
     0.25%  proxysql  proxysql             [.] je_tcache_alloc_small
     0.24%  proxysql  proxysql             [.] je_tsdn_tsd
     0.24%  proxysql  proxysql             [.] je_bitmap_get
     0.23%  proxysql  libcrypto.so.1.0.0   [.] 0x000000000006ab6c

mysql> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (34.12 sec)

mysql> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (32.31 sec)

[ton31337]

@renecannao we are working with @skirmis for the same company

[renecannao]

The last 2 commits allows to execute 12x faster LOAD MYSQL USERS TO RUNTIME for 100k users

[ton31337]

@renecannao amazing! any plans to ship this to 1.3.0?

[skirmis]

thanks @renecannao 👍

[renecannao]

Yes, this can be backported to 1.3.x

[ton31337]

@renecannao any time frame when this could be backported to 1.3.x?

[renecannao]

After nearly 3 months, this was never backported to 1.3.x .
Instead, I will try to accelerate the release of 1.4