Bug #93201 reconsider the number of SHOW [SESSION] VARIABLES commands run by connector/C++

• Submitted: 15 Nov 2018 6:12
• Modified: 3 Feb 2021 15:09
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Closed
• Category: Connector / C++
• Severity: S5 (Performance)
• Version: 8.0.13
• OS: Any
• CPU Architecture: Any

[15 Nov 2018 6:12] Shane Bester

Description:
We're debugging a performance problem where server seems to be running
too many queries that pile up e.g.

SHOW SESSION VARIABLES LIKE 'lower_case_table_names';
SHOW SESSION VARIABLES LIKE 'sql_mode';
SHOW VARIABLES;
SHOW GLOBAL VARIABLES;

The reason they pileup is due to taking locks:

https://bugs.mysql.com/bug.php?id=75590
(SHOW VARIABLES WHERE ... materializes all variables into bigass temptable)

This is a bug about the connector/c++ running the SHOW VARIABLE statements.

How to repeat:
If you look at the 8.0.13 Connector/C++ source code you'll see it calls this:

/* {{{ MySQL_Connection::getSessionVariable() -I- */
sql::SQLString
MySQL_Connection::getSessionVariable(const sql::SQLString & varname)
{
  CPP_ENTER_WL(intern->logger, "MySQL_Connection::getSessionVariable");
  checkClosed();

  if (intern->cache_sql_mode && intern->sql_mode_set == true && !varname.compare("sql_mode")) {
    CPP_INFO_FMT("sql_mode=%s", intern->sql_mode.c_str());
    return intern->sql_mode;
  }
  sql::SQLString q("SHOW SESSION VARIABLES LIKE '");
  q.append(varname).append("'");
<cut>

Search for "getSessionVariable" we can see the calls :

lower_case_table_names = connection->getSessionVariable("lower_case_table_names");
	Line 2500:     sql::SQLString sql_mode(connection->getSessionVariable("SQL_MODE"));
	Line 3081:   return atoi(connection->getSessionVariable("max_connections").c_str());
	Line 3141:   return atoi(connection->getSessionVariable("max_allowed_packet").c_str()) - 4;
sql::SQLString value= connection->getSessionVariable("max_statement_time");
 return sql::SQLString(getSessionVariable("character_set_results"));

Suggested fix:
Can we please avoid SHOW commands here, since they're extremely unscalable (locks taken).

Suggest:

SELECT @@GLOBAL.lower_case_table_names;
SELECT @@GLOBAL.max_connections;
SELECT @@GLOBAL.max_statement_time;
etc etc

Then deal with the server versions that return error:
ERROR 1193 (HY000): Unknown system variable 'max_statement_time'

[15 Nov 2018 7:06] MySQL Verification Team

Here's the request for connector/net https://bugs.mysql.com/bug.php?id=93202

[15 Nov 2018 9:35] MySQL Verification Team

Hello Shane,

Thank you for the report.

Regards,
Umesh

[3 Feb 2021 15:09] Paul DuBois

Posted by developer:
 
Fixed in 8.0.24.

Upon connecting to the server, Connector/C++ executed a number of
SHOW [SESSION] VARIABLES statements to retrieve system variable
values. Such statements involve locking in the server, so they are
now avoided in favor of SELECT @@var_name statements.

Additionally, Connector/C++ was trying to fetch the value of the
max_statement_time system variable, which has been renamed to
max_execution_time. Connector/C++ now uses the correct variable name,
with the result that getQueryTimeout() and setQueryTimeout() now work
properly for both Statement and Prepared Statement objects.