MySQL Bugs: #68572: FEDERATED using SHOW TABLE STATUS causes performance problems for InnoDB tables

Bug #68572	FEDERATED using SHOW TABLE STATUS causes performance problems for InnoDB tables
Submitted:	5 Mar 2013 10:57	Modified:	6 Mar 2013 19:46
Reporter:	Przemysław Ołtarzewski	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Federated storage engine	Severity:	S3 (Non-critical)
Version:	5.5.25a, 5.5.31	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	federated, performance, show table status

Description:
FEDERATED storage engine causes severe performance problems for InnoDB tables.

FEDERATED uses SHOW TABLE STATUS before issuing a statement on the target table. This causes the target table statistics to be rebuilt by InnoDB storage engine. This, in turn, creates a bottleneck for every statement accessing the target table - either via FEDERATED or directly.

The result is the exhaustion of database connection pool, as it can take about 1 minute for queries to complete under mild load.

This problem is reported as S3, because described situation renders the database unusable as long as the load continues. The problem is not a performance issue, rather the operation of FEDERATED engine that uses SHOW TABLE STATUS without a good reason.

How to repeat:
1. Use two instances of mysqld nad two databases deployed on them - DB1 and DB2.

2. Create an InnoDB table in DB2. Example table that caused problems:

CREATE TABLE `outpayment_account` (
  `ID` int(11) DEFAULT NULL,
  `SUBSCRIBER_MSISDN` varchar(50) NOT NULL,
  `EXPIRY_DATE` datetime DEFAULT NULL,
  `TARIFF_ID` int(11) DEFAULT NULL,
  `IN_SUBACCOUNT_ID` varchar(50) DEFAULT NULL,
  `LAST_UPDATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `UQ_ACCOUNT_MSISDN_SUBACCOUNT` (`SUBSCRIBER_MSISDN`,`IN_SUBACCOUNT_ID`),
  KEY `IDX_ACCOUNT_EXPIRY_DATE` (`EXPIRY_DATE`),
  KEY `SUBSCRIBER_MSISDN` (`SUBSCRIBER_MSISDN`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

3. Create a FEDERATED table in DB1, linking to target table in DB2:

CREATE TABLE `outpayment_account_offline` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `SUBSCRIBER_MSISDN` varchar(50) NOT NULL,
  `EXPIRY_DATE` datetime DEFAULT NULL,
  `TARIFF_ID` int(11) DEFAULT NULL,
  `IN_SUBACCOUNT_ID` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UQ_ACCOUNT_MSISDN_SUBACCOUNT` (`SUBSCRIBER_MSISDN`,`IN_SUBACCOUNT_ID`),
  KEY `IDX_ACCOUNT_EXPIRY_DATE` (`EXPIRY_DATE`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='DB2/outpayment_account';

Please notice that CONNECTION in the above definition should be tailored to configuration used to repeat the problem.

4. Fill the table on DB2 with sample data - about 25000000 random rows should be enough.

5. Provide mild load on table in DB1 - use many SELECT statements, only a few INSERTs and DELETEs, about 10 queries / second (might need more or less to trigger the behavior - depends on environment).

6. Log to DB2 with mysql console. Display executing queries on DB2, using SHOW FULL PROCESSLIST. Observe the 'SHOW TABLE STATUS' statements issued by FEDERATED and how SELECT statements that follow hang in 'statistics' phase.

7. Enable profiling for your session on DB2. Attempt to issue an identical SELECT statement that the FEDERATED engine uses. Show profile for query. Observe the length of 'statistics' stage.

Suggested fix:
Change behavior of FEDERATED engine. Use different statement to check if target table is available.

If FEDERATED engine is no longer actively developed, mark it explicitly as obsolete and suggest an alternative to migrate existing solutions to.

Setting innodb_stats_on_metadata=0 in my.cnf should help avoid this issue, can you check as a possible workaround?

Sample query profile for observed behavior:

mysql> SELECT `ID`, `SUBSCRIBER_MSISDN`, `EXPIRY_DATE`, `TARIFF_ID`, `IN_SUBACCOUNT_ID` FROM `outpayment_account` WHERE  ( (`SUBSCRIBER_MSISDN` LIKE '48604868674%')  AND  (`IN_SUBACCOUNT_ID` = '3') ) AND ( (1=1)  AND  (1=1) );
Empty set (14.50 sec)

mysql> show profile for query 2;
+------------------------------+-----------+
| Status                       | Duration  |
+------------------------------+-----------+
| starting                     |  0.000043 |
| Waiting for query cache lock |  0.050245 |
| checking permissions         |  0.000023 |
| Opening tables               |  0.000031 |
| System lock                  |  0.000020 |
| Waiting for query cache lock |  0.050171 |
| init                         |  0.000054 |
| optimizing                   |  0.000030 |
| statistics                   | 14.378236 |
| preparing                    |  0.000031 |
| executing                    |  0.000010 |
| Sending data                 |  0.017192 |
| end                          |  0.000017 |
| query end                    |  0.000010 |
| closing tables               |  0.000012 |
| freeing items                |  0.000098 |
| logging slow query           |  0.000016 |
| logging slow query           |  0.000039 |
| cleaning up                  |  0.000011 |
+------------------------------+-----------+
19 rows in set (0.00 sec)

Shane,

Thank you for quick suggestion.

From what I've read about setting innodb_stats_on_metadata=0, this could be a viable workaround. However, I would like to know, what are the consequences of such configuration change.

Will InnoDB statistics be ever rebuilt? Or do we need to execute ANALYZE TABLE manually on regular basis, using an event / job, to keep statistics up to date?

Thank you for the feedback.

You will need to execute ANALYZE TABLE manually on regular basis, using an event / job, to keep statistics up to date.

At the same time bug verified as described using code analysis.

However, this is more feature request for me, because these SHOW TABLE STATUS calls needed to get remote statistics about table.

the innodb_stats_on_metadata=0 parameter should be set in the localhost, remote host or both?