Proxies and supporting an exploding number of user-accounts Eric - PowerPoint PPT Presentation

Proxies and supporting an exploding number of user-accounts Eric Herman - Percona Live - 2018-04-24

Eric Herman eric.herman@gmail.com https://github.com/ericherman/ https://twitter.com/eric_herman

Trend of more complex deployments - tools ● More external tooling ○ Orchestrator, gh-ost ○ pt-online-schema-change ○ Percona XtraBackup ○ Sqoop, mysql-time-machine ○ ps-top, query analysis tools ○ binlog parsing/analysis tools (e.g. to kafka)

Trend of more complex deployments - plugins ● More external tooling (e.g. orchestrator) ● More sophisticated use of plugins, e.g. Audit: ○ Prevent CREATE TABLE if no primary key

Trend of more complex deployments - plugins 1 ● More external tooling (e.g. orchestrator) ● More sophisticated use of plugins, e.g. Audit: ○ Prevent CREATE TABLE if no primary key ■ https://github.com/dveeden/mysql-enforcepk

Trend of more complex deployments - plugins 2 ● More external tooling (e.g. orchestrator) ● More sophisticated use of plugins, e.g. Audit: ○ Prevent CREATE TABLE if no primary key ■ https://github.com/dveeden/mysql-enforcepk ○ Prevent DROP TABLE if not named like %_DROP_AFTER_YYYY_MM_DD (and past)

Trend of more complex deployments - plugins 3 ● More external tooling (e.g. orchestrator) ● More sophisticated use of plugins, e.g. Audit: ○ Prevent CREATE TABLE if no primary key ■ https://github.com/dveeden/mysql-enforcepk ○ Prevent DROP TABLE if not named like %_DROP_AFTER_YYYY_MM_DD (and past) ○ Warn if “suspicious” query

Trend of more complex deployments - triggers ● More external tooling (e.g. orchestrator) ● More sophisticated use of plugins ● More sophisticated use of triggers ○ Online schema change

Trend of more complex deployments - triggers 1 ● More external tooling (e.g. orchestrator) ● More sophisticated use of plugins ● More sophisticated use of triggers ○ Online schema change ○ R&D: write additional information into a blackhole table in order to record more information about “who did it” in the binlog

Trend of more complex deployments - proxies ● More external tooling (e.g. orchestrator) ● More sophisticated use of plugins, triggers ● More proxies ○ Vitess ○ ProxySQL ○ MySQL Router ○ MaxScale

Trend of more complex deployments - users ● More external tooling (e.g. orchestrator) ● More sophisticated use of plugins, triggers ● More proxies ● More user accounts per server ○ More applications, services, micro-services ○ More powerful logging, auditing ○ Per-user accounts, fine-grain permissions

Trend of more complex deployments - conflict ● More external tooling (e.g. orchestrator) ● More sophisticated use of plugins, triggers ● More proxies ● More user accounts per server ● Conflict: proxy may not support custom connection attributes, or may not nicely support lots of users with session variables

Wanted: more dynamic connection attributes ● E.g: connection pool with shared app user ○ Set the webpage with each request?

Wanted: more dynamic connection attributes 1 ● E.g: connection pool with shared app user ○ Set the webpage with each request? ● Wish for nicer support at the protocol level ○ But then connector support, too

Wanted: more dynamic connection attributes 2 ● E.g: connection pool with shared app user ○ Set the webpage with each request? ● Wish for nicer support at the protocol level ○ But then connector support, too ● Proxies might use COM_CHANGE_USER ○ Store attributes per proxied connection :-( ○ https://dev.mysql.com/doc/internals/en/com-change-user.html ○ https://mariadb.com/kb/en/library/com_change_user/

Surprise: connect time grows with more users ● Never noticed when a few apps shared a few user accounts ● Noticed slowdown on server with >1000 users ○ Thousands of users is new for me. You?

Surprise: connect time grows with more users 1 ● Never noticed when a few apps shared a few user accounts ● Noticed slowdown on server with >1000 users ○ Thousands of users is new for me. You? ○ https://bugs.mysql.com/bug.php?id=88834 ● Testing showed that slow-down was linear! ○ delay ≅ const_a + (const_b * num_users)

Why a linear slow-down? ● acl_users is a list in RAM ○ This is fast and light for most deployments ○ Has been good-enough for years

Why a linear slow-down? 1 ● acl_users is a list in RAM ○ This is fast and light for most deployments ○ Has been good-enough for years ● But, list is naively traversed in a few places

Why a linear slow-down? 2 ● acl_users is a list in RAM ○ This is fast and light for most deployments ○ Has been good-enough for years ● But, list is naively traversed in a few places ○ 44% time in __strcmp_sse2_unaligned

Why a linear slow-down? 3 ● acl_users is a list in RAM ○ This is fast and light for most deployments ○ Has been good-enough for years ● But, list is naively traversed in a few places ○ 44% time in __strcmp_sse2_unaligned ● Maybe we could use a hashtable?

Hacking a prototype easy, “for real” was tricky ● Subtleties in ACL_USER sorting, candidates ○ Anonymous users ○ Wild-cards in hostnames

Hacking a prototype easy, “for real” was tricky 1 ● Subtleties in ACL_USER sorting, candidates ○ Anonymous users ○ Wild-cards in hostnames ● unorderd_map<string, list<ACL_USER *>> ○ Custom Memory Allocator for PS ○ Tip: avoid allocator constructor arguments

Hacking a prototype easy, “for real” was tricky 2 ● Subtleties in ACL_USER sorting, candidates ○ Anonymous users ○ Wild-cards in hostnames ● unorderd_map<string, list<ACL_USER *>> ○ Custom Memory Allocator for PS ○ Tip: avoid allocator constructor arguments ● https://github.com/mysql/mysql-server/pull/203

Together, let’s make the server better! ● Devs from the whole ecosystem are here: ○ Oracle MySQL ○ MariaDB Server ○ Percona Server ○ Proxies: Vitess, ProxySQL, Spider ○ Individual contributors ● What are your needs? Let’s talk!

Questions?

Thank you