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