[MXS-3892] schema router flood information schemas with queries Created: 2021-11-23 Updated: 2022-09-01 Resolved: 2022-01-04 |
|
| Status: | Closed |
| Project: | MariaDB MaxScale |
| Component/s: | schemarouter |
| Affects Version/s: | 2.4 |
| Fix Version/s: | 2.4.19 |
| Type: | Bug | Priority: | Major |
| Reporter: | Massimo | Assignee: | markus makela |
| Resolution: | Fixed | Votes: | 1 |
| Labels: | None | ||
| Sprint: | MXS-SPRINT-147, MXS-SPRINT-148 |
| Description |
|
As we discuss with MAxscale team, schema router create tons of queries to information_schema:
in a large databases this is going to be a HUGE flood problem in performance, the queries getting slower and slower and the response of database is getting stack there due the queries that maxscale keep going. Information schema is based on views . The customer has just more the 400 tables . The massive performance issue is killing the Column Store instance |
| Comments |
| Comment by markus makela [ 2021-12-13 ] |
|
Tested the query with 1500 empty InnoDB tables in 10.5 and the query returned in less than a second: 1693 rows in set (0.019 sec) |
| Comment by Massimo [ 2021-12-13 ] |
|
Hi Markus, thanks for the test. the point i guess is that the tables are not empty and keep getting instert/update that required all time to get the information schema lookup to give back the information and the right count. |
| Comment by markus makela [ 2021-12-13 ] |
|
OK, so it's the locking that causes the problem. |
| Comment by Massimo [ 2021-12-15 ] |
|
when you query information schemas for big tables it take time, usually the view is a read, so no locking , but still update and look for stats for big tables take long time and is a performance issue always |
| Comment by Massimo [ 2021-12-16 ] |
|
markus makela is the feedback ok or do you need anything else |
| Comment by Todd Stoffel (Inactive) [ 2021-12-20 ] |
|
The information gathered by MaxScale is required for the schema router. This kind of performance problem probably needs to be addressed by the server team. The manifestation here in MaxScale is likely just a symptom. |
| Comment by Massimo [ 2021-12-21 ] |
|
toddstoffel is also the number of requested maxscale does by schema router, which involve maxscale directly |
| Comment by markus makela [ 2022-01-04 ] |
|
Changed the schemarouter to send only one query at a time when multiple users need to update their cache. This should reduce the number of concurrent queries to one per user. |