MongoDB Bi connector not able to update max_num_global_tables system variable

Question

I have a MongoDB installed in the ec2 machine, which has around 50 databases. I have set up a Bi connector in order to connect to the tableau.

But it is only mapping up to 4000 tables, and because of that it's not mapping all the dbs.

Here's the mongosqld.conf file

systemLog:
  path: '/logs/mongosqld.log'
security:
  enabled: true
mongodb:
  net:
    uri: "mongodb://127.0.0.1:27017/"
    auth:
      username: <USERNAME>
      password: <PASSWORD>
net:
  bindIp: 0.0.0.0
  port: 3307
  ssl:
      mode: "allowSSL"
      PEMKeyFile: '<PATH>'

Here's the logs:

2020-11-26T12:33:44.645+0000 I SCHEMA     [sampler] max num global tables (4000) reached: not mapping any more tables
2020-11-26T12:33:44.645+0000 I SCHEMA     [sampler] max num global tables (4000) reached: not mapping any more tables
2020-11-26T12:33:44.646+0000 I SCHEMA     [sampler] max num global tables (4000) reached: not mapping any more tables
2020-11-26T12:33:44.646+0000 I SCHEMA     [sampler] max num global tables (4000) reached: not mapping any more tables
2020-11-26T12:33:44.646+0000 I SCHEMA     [sampler] max num global tables (4000) reached: not mapping any more tables
2020-11-26T12:33:44.646+0000 I SCHEMA     [sampler] mapped schema for 424 namespaces: "cust" (12):<NAMESPACES>]
2020-11-26T12:37:55.295+0000 I NETWORK    [conn1] connection accepted from <IPADDRESS>:60554 #1 (1 connection now open)
2020-11-26T12:37:55.298+0000 I NETWORK    [conn1] end connection <IPADDRESS>:60554 (0 connections now open)
2020-11-26T12:44:02.262+0000 I NETWORK    [conn2] connection accepted from <IPADDRESS>:60568 #2 (1 connection now open)
2020-11-26T12:49:31.462+0000 I NETWORK    [conn3] connection accepted from <IPADDRESS>:60574 #3 (2 connections now open)

I found max num global tables (4000) reached is the problem, and I found in here how to set the variables. I tried it using mysql client and when I changed its changing in mysql "show varaiables;" command, but when I restart BI connector and then check mysql client, its again showing 4000.

How to change global system variable max_num_global_tables in BI connector?

@D.SMtheres no logs to add. Bi connector is working fine for the first ~40 Db's and the rest of it is not even recognizing. I added the mongosqld.conf file — Jerin A Mathews, Nov 25 '20 at 09:21
If you say you checked the logs, include the logs in the question. How did you check logs that do not exist? — D. SM, Nov 25 '20 at 13:58

D. SM · Answer 1 · 2020-11-28T06:36:09.290

0

In theory this should be achievable by running

mongosqld --setParameter "max_num_global_tables=10000"

Or via the config file:

setParameter:
  max_num_global_tables: 10000

https://docs.mongodb.com/bi-connector/master/reference/mongosqld#std-option-mongosqld.--setParameter

https://docs.mongodb.com/bi-connector/master/reference/system-variables#std-label-system-environment-variables

However, it appears that mongosqld doesn't map this parameter out of the config file (and I assume out of the command line either).

The parameter can still be set at runtime and resample initiated as follows:


MySQL [(none)]> SET GLOBAL max_num_global_tables=10000;
Query OK, 0 rows affected (0.001 sec)

MySQL [(none)]> flush sample;
Query OK, 0 rows affected (14.561 sec)

You may consider requesting the parameter support via the official support channels if you are a paying customer or via https://feedback.mongodb.com/ otherwise.

edited Nov 28 '20 at 06:36

answered Nov 26 '20 at 15:57

D. SM

13,584
3
12
21

Its not working, i tried both of them. This is the error am geting `failed to start due to configuration error: invalid setParameter key: max_num_global_tables try 'mongosqld --help' for more information` – Jerin A Mathews Nov 28 '20 at 05:42
Update to latest version. – D. SM Nov 28 '20 at 05:47
Already using the tatest version: `mongosqld v2.14.0` – Jerin A Mathews Nov 28 '20 at 06:00
This setting appears to be only settable at runtime. – D. SM Nov 28 '20 at 06:37

score 0 · Answer 2 · answered Mar 16 '21 at 22:20

Been searching and this is the only thread that had this issue.

Could only resolve it during runtime.

Start the mongosqld. Mine was started with port 3307.
Use an sqlclient. Tested with mysqlclient on a linux machine.
Connect to mongosqld: mysql -h xxxyyzzz.com -P 3307
In sql shell: SET GLOBAL max_num_global_tables=10000; As explained above.
SHOW GLOBAL Variables; --> You should see the change
flush sample;

// This is the solution above, but it has to be rerun when mongosqld restarts

After using the above, I got the data but the PowerBI tool was slow due to the shear amount of data, which became unusable.

// If one does not need all the tables for their BI tools, I would recommend to create a view that encompasses most of the tables/mongo keys that are needed.

https://docs.mongodb.com/manual/reference/method/db.createView/

// Then run your mongosqld on the smaller view/collection. This will only have the data that you need for your specific task.

// Other tasks will also need their own views.

MongoDB Bi connector not able to update max_num_global_tables system variable

2 Answers2

// This is the solution above, but it has to be rerun when mongosqld restarts