High CPU usage and low network utilization when processing a huge result set

We test by loading the example employee database from MySQL (https://dev.mysql.com/doc/employee/en/) to MariaDB server, then run the test on the client machine with MariaDB and MySQL ODBC driver.

We found that the following query takes extremely long time to execute:

SELECT s.emp_no, s.salary, e.emp_no, e.first_name, e.last_name, e.gender FROM salaries s, employees e WHERE s.emp_no = e.emp_no;

Using MariaDB ODBC driver, the query above takes 123.7 seconds to complete, compared to MySQL ODBC driver, which does so in only 15.8 seconds.

Details on the connection string and execution time:

>> Connection String : Driver={MySQL ODBC 5.3 Unicode Driver};Server=192.168.xxx.xxx;Port=3306;Database=employees;UID=root;PWD=123;

>> Query String : SELECT s.emp_no, s.salary, e.emp_no, e.first_name, e.last_name, e.gender FROM salaries s, employees e WHERE s.emp_no = e.emp_no;

>> Number of Loops : 1

>> Make Connection...  SUCCESSFUL.

>> Begin querying 1 time(s)... 

15834, 

>> Close Connection...  SUCCESSFUL.

>> Connection String : Driver={MariaDB ODBC 1.0 Driver};Server=192.168.xxx.xxx;Port=3306;Database=employees;UID=root;PWD=123;

>> Query String : SELECT s.emp_no, s.salary, e.emp_no, e.first_name, e.last_name, e.gender FROM salaries s, employees e WHERE s.emp_no = e.emp_no;

>> Number of Loops : 1

>> Make Connection...  SUCCESSFUL.

>> Begin querying 1 time(s)... 

123740, 

>> Close Connection...  SUCCESSFUL.

Although it only shows one run of the query, we can reproduce this issue any time.

Checking the performance on the client machine, the test program runs at 100% of a single core (our machine has 4 cores, so it shows up as 25%), and the network utilization drops to only 1-2 percent of the 100 Mbps link. In contrast, MySQL ODBC driver utilizes around 60% of the link, with the same backend.