MySQL Shell AdminAPI: different ways to call the commands… same effect?

During last Ubuntu Summit in Riga, I got an interesting question from Alex Lutay, engineering manager at Canonical regarding the different ways to perform some Admin API commands in MySQL Shell.

He wanted to know why, in the manual and blogs, we always use separate commands instead of combining them, as he would expect this to have an impact on the connections established with the server.

Let’s illustrate this by looking at the different ways of obtaining the state of a cluster. Here are the four different methods:

Method 1

This is the method most frequently used in the documentation:

$ mysqlsh admin@10.0.0.1
JS> cluster=dba.getCluster()
JS> cluster.status() 

Method 2

This is Alex’s preferred method as he expects to have less round trips with the network:

$ mysqlsh admin@10.0.0.1
JS> dba.getCluster().status()

Method 3

In this method, we force the creation of the cluster object when the connection is made:

$ mysqlsh admin@10.0.0.1 --cluster
JS> cluster.status()

Method 4

The last method consists of using the MySQL Shell line arguments to call the status command directly without entering the MySQL Shell interactive mode:

$ mysqlsh admin@10.0.0.1 -- cluster status

In fact, this shouldn’t matter because, in the background, the MySQL Shell Admin API makes exactly the same calls to the server.

I have captured the network traffic for all four methods and the size of the captured data is almost the same with some slightly noticeable differences :

$ ls -lh cap*tcp
-rw-r--r-- 1 fred fred 22K Nov  5 22:29 cap1.tcp
-rw-r--r-- 1 fred fred 21K Nov  5 22:29 cap2.tcp
-rw-r--r-- 1 fred fred 22K Nov  5 22:29 cap3.tcp
-rw-r--r-- 1 fred fred 20K Nov  5 22:30 cap4.tcp

This difference is mainly due to the quantity of packets captured for each test:

$ for i in $(ls cap*.tcp)
> do
> echo $i $(cat $i | wc -l)
> done
cap1.tcp 139
cap2.tcp 133
cap3.tcp 140
cap4.tcp 127

Why do we have then some difference ?

This is because MySQL Shell in interactive mode runs some statements to set some session variables, and Shell variables (and this could be even more if you have a special prompt).

Here are some examples of instructions captured when connecting to the server with the MySQL Shell

select @@sql_mode;
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
SELECT @@SESSION.session_track_system_variables
SET SESSION session_track_system_variables = 'time_zone,autocommit,
   character_set_client, character_set_results,
   character_set_connection, sql_mode';
select concat(@@version, ' ', @@version_comment)
select schema()

Conclusion

The difference is minimal, but if you really need to optimise your calls, avoiding the MySQL Shell’s interactive mode is the best option.

At least you’re now familiar with the four options for achieving the same result.

Enjoy MySQL Shell and the Admin API !