Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
6.4.0
-
None
Description
Tested on Maxscale 6.4.0
Customer has requested that maxscale schema router be configured so that they can shard schemas by moving them to separate Mariadb instances.
Current customer configuration: single instance, 300 schemas, each with approximately 75 tables. Approximately 1,000 user accounts. Each account with varied granted privileges.
Desired customer configuration: Move entire schemas to new instances and use schema router as an entry-point for all connections.
To test this scenario, I created the following:
SCHEMAS TABLES USERS Select_priv SCHEMA_PRIVILEGES TABLE_PRIVILEGES
|
f1.edw.ee - replicates to --> f2.edw.ee 87 6732 1103 22 3841 40269
|
f3.edw.ee - replicates to --> f4.edw.ee 93 9011 1103 100 5029 405
|
f5.edw.ee 102 7258 1103 14 29320 288609
|
f6.edw.ee 88 7093 1103 24 45512 3847
|
mx.edw.ee maxscale as schema router
|
Select_priv = grant select on *.* to
SCHEMA_PRIVILEGES = grant select on my_schema.* to
TABLE_PRIVILEGES = grant select on my_schema.my_table to
I created scripts that would connect repeatedly and select, insert, update, delete. Each query a new connection from a separate account. Example:
mariadb -uofficer_h_dreadhiss -ppassword -h192.168.20.125 -P3306 -ABNe 'select * from hatchettine_is_half.damp_we_units_cynomolgus;'
|
mariadb -ug_yarona -ppassword -h192.168.20.125 -P3306 -ABNe 'select * from campy_by_thaw.hospitable_so_problems_unvitreousness;'
|
mariadb -ucounselor_s_frannk -ppassword -h192.168.20.125 -P3306 -ABNe 'select * from television_be_bite.tremendous_do_childs_thromboembolic;'
|
mariadb -udean_m_signe -ppassword -h192.168.20.125 -P3306 -ABNe 'select * from crizzling_or_stab.erratic_by_passengers_ovigerm;'
|
mariadb -uo_ellil -ppassword -h192.168.20.125 -P3306 -ABNe 'select * from ceratin_am_snub.rotten_of_informations_ecthyma;'
|
mariadb -ud_hector -ppassword -h192.168.20.125 -P3306 -ABNe 'select * from humpbacks_as_hook.exciting_up_extents_subucula;'
|
Running a typical script with 605 lines connecting directly to the database completes in 10 to 15 seconds.
Running a typical script with 605 lines connecting through maxscale schema router completes in more than 10 minutes.