PMM, Federated Tables, Table Stats, and Lots of Connections!
source link: https://www.percona.com/blog/pmm-federated-tables-table-stats-and-lots-of-connections/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
PMM, Federated Tables, Table Stats, and Lots of Connections!
Earlier in the year, I was working on an issue where one of my clients had reported a massive influx in connection on their hosts after enabling Percona Monitoring and Management (PMM). This was something I had not seen before and after researching for a couple of days I discovered that if you monitor a MySQL instance with PMM configured to collect table statistics, and if the tables that it’s gathering statistics from are Federated, it will generate a connection on the remote host for the Federated tables, one for each Federated table in the instance. Let’s go over the details and provide some examples so we can understand this a bit better.
First, I’ll offer a reminder that a Federated table is simply a table that you can put in your MySQL instance that is empty locally and uses a network connection to get the data from another MySQL host when the table is queried. For example, if I have a normal table called peter_data on host mysql1, I can set up a Federated table on mysql2 that points to mysql1. Each time that mysql2 has a query on the peter_data table, it connects to mysql1, gets the data, and then returns it locally. This feature is a lot less common now than it once was given how MySQL replication has improved over time, but as you can see here in the MySQL reference guide, it’s still supported.
So how does this impact our issue where PMM was establishing so many connections? Let’s set this up in my lab and have a look!
Lab setup
Let’s start by setting up my first host centos7-1 as the “remote host”. This is the host that has the actual data on it.
[root@centos7-1 ~]# mysql ..... mysql> use ftest; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show create table t1 \G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `c1` int(11) NOT NULL, `c2` int(11) DEFAULT NULL, `c3` int(11) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> select * from t1; +----+------+------+ | c1 | c2 | c3 | +----+------+------+ | 1 | 1 | 1 | | 2 | 2 | 2 | | 3 | 3 | 3 | +----+------+------+ 3 rows in set (0.00 sec) |
Now that’s done, I’ll set up my second host centos7-2 to act as the host that has the Federated table.
[root@centos7-2 ~]# mysql -u root -ppassword mysql> select * from mysql.servers; +-------------+-----------+-------+----------+----------+------+--------+---------+-------+ | Server_name | Host | Db | Username | Password | Port | Socket | Wrapper | Owner | +-------------+-----------+-------+----------+----------+------+--------+---------+-------+ | fedlink | 10.0.2.12 | ftest | root | password | 3306 | | mysql | | +-------------+-----------+-------+----------+----------+------+--------+---------+-------+ 1 row in set (0.00 sec) mysql> use ftest; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `c1` int(11) NOT NULL, `c2` int(11) DEFAULT NULL, `c3` int(11) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='fedlink/t1' 1 row in set (0.00 sec) mysql> select * from t1; +----+------+------+ | c1 | c2 | c3 | +----+------+------+ | 1 | 1 | 1 | | 2 | 2 | 2 | | 3 | 3 | 3 | +----+------+------+ 3 rows in set (0.01 sec) |
Recreating the issue
Now that we have our Federated table set up. Let’s test and see how querying table metadata on centos7-2, the instance with the Federated table, impacts connections on centos7-1, the remote host. What I did was connect to centos7-2, query the information_schema.tables table much in the same way that PMM does, disconnected, and then connected a second time running the same query.
[root@centos7-2 ~]# mysql -u root -ppassword mysql> select * from information_schema.tables where table_schema = 'ftest'; +---------------+--------------+------------+------------+-----------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+---------------------+------------+-------------------+----------+----------------+---------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | +---------------+--------------+------------+------------+-----------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+---------------------+------------+-------------------+----------+----------------+---------------+ | def | ftest | t1 | BASE TABLE | FEDERATED | 10 | Fixed | 3 | 5461 | 16383 | 0 | 0 | 0 | NULL | NULL | 1969-12-31 19:33:42 | NULL | latin1_swedish_ci | NULL | | | +---------------+--------------+------------+------------+-----------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+---------------------+------------+-------------------+----------+----------------+---------------+ 1 row in set (0.01 sec) mysql> exit [root@centos7-2 ~]# mysql -u root -ppassword mysql> select * from information_schema.tables where table_schema = 'ftest'; +---------------+--------------+------------+------------+-----------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+---------------------+------------+-------------------+----------+----------------+---------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | +---------------+--------------+------------+------------+-----------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+---------------------+------------+-------------------+----------+----------------+---------------+ | def | ftest | t1 | BASE TABLE | FEDERATED | 10 | Fixed | 3 | 5461 | 16383 | 0 | 0 | 0 | NULL | NULL | 1969-12-31 19:33:42 | NULL | latin1_swedish_ci | NULL | | | +---------------+--------------+------------+------------+-----------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+---------------------+------------+-------------------+----------+----------------+---------------+ 1 row in set (0.01 sec) mysql> exit |
As you can see below, this resulted in two connections on centos7-1 that did not drop despite disconnecting and reconnecting on centos7-2.
mysql> show processlist; +----+------+-----------------+-------+---------+------+----------+------------------+-----------+---------------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | +----+------+-----------------+-------+---------+------+----------+------------------+-----------+---------------+ | 23 | root | localhost | NULL | Query | 0 | starting | show processlist | 0 | 0 | | 25 | root | 10.0.2.13:33232 | ftest | Sleep | 112 | | NULL | 1 | 1 | | 27 | root | 10.0.2.13:33236 | ftest | Sleep | 71 | | NULL | 1 | 1 | +----+------+-----------------+-------+---------+------+----------+------------------+-----------+---------------+ 3 rows in set (0.00 sec) |
This doesn’t sound like that big of a deal, especially considering that PMM usually remains connected to the host. So if you have one Federated table in your system and if PMM is monitoring table stats, it will only add one connection on the remote host right? That’s true, but in my lab, I expanded this to create 145 Federated tables, and the result of this despite only querying the information_schema.tables table, 145 connections were created on centos7-1.
[root@centos7-2 ~]# mysql -u root -ppassword mysql> select * from information_schema.tables where table_schema = 'ftest'; <strong>145 rows in set (0.08 sec)</strong> [root@centos7-1 ~]# mysql -u root -ppassword mysql> select * from information_schema.processlist where substring_index(host,':',1) = '10.0.2.13' and user = 'root'; +------+------+------------------+-------+---------+------+-------+------+---------+-----------+---------------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | TIME_MS | ROWS_SENT | ROWS_EXAMINED | +------+------+------------------+-------+---------+------+-------+------+---------+-----------+---------------+ | 2120 | root | 10.0.2.13:60728 | ftest | Sleep | 7 | | NULL | 6477 | 1 | 1 | | 2106 | root | 10.0.2.13:60700 | ftest | Sleep | 7 | | NULL | 6701 | 1 | 1 | | 2117 | root | 10.0.2.13:60722 | ftest | Sleep | 7 | | NULL | 6528 | 1 | 1 | | 2118 | root | 10.0.2.13:60724 | ftest | Sleep | 7 | | NULL | 6512 | 1 | 1 | +------+------+------------------+-------+---------+------+-------+------+---------+-----------+---------------+ <strong>145 rows in set (0.00 sec)</strong> |
This can be a big problem if you have a host that doesn’t support a lot of connections and you need those connections to be available for your app!
Conclusion
Based on the lab testing above, we can see how PMM queries against the information_schema.tables table can cause issues with a lot of connections being created on a Federated remote host. This probably will not be a problem for most MySQL users considering that Federated tables aren’t that common, but if you have Federated tables and if you’re considering adding PMM monitoring, or any other monitoring that collects table statistics, be warned! The maintenance of Federated connections on a remote host is not a bug, this is how it’s supposed to behave.
If you have Federated tables and if you want to avoid this problem, you can ensure that you use the flag –disable-tablestats when adding MySQL to your local PMM client using the “pmm-admin add mysql” command.
Percona Monitoring and Management is a best-of-breed open source database monitoring solution. It helps you reduce complexity, optimize performance, and improve the security of your business-critical database environments, no matter where they are located or deployed.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK