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:
This is the method most frequently used in the documentation:
$ mysqlsh email@example.com JS> cluster=dba.getCluster() JS> cluster.status()
This is Alex’s preferred method as he expects to have less round trips with the network:
$ mysqlsh firstname.lastname@example.org JS> dba.getCluster().status()
In this method, we force the creation of the cluster object when the connection is made:
$ mysqlsh email@example.com --cluster JS> cluster.status()
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 firstname.lastname@example.org -- 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()
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 !