This situation is bound to occur every time the mysqld is restarted，and There are a large number of sub tables in the database ds.

The environment built is a center with 6 distributed nodes, capturing slow queries for more than 0.2 seconds. None of the center nodes are present (Center Server first run), while the other 6 distributed nodes are all very slow(Concurrent Access Center Server).

Can we avoid it by optimizing what parameters? Or can upgrading the mariadb version solve this problem?

CREATE TABLE `adm_history` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT ,
`CHANNEL_ID` varchar(50) NOT NULL ,
`CAPTURE_TIME` bigint(20) NOT NULL,
`PLATE_IMAGE_URL` varchar(200) DEFAULT NULL ,
`VEHICLE_URL` varchar(200) DEFAULT '' ,
`PICTURE_URL` varchar(200) DEFAULT NULL,
`FEATURE_VECTOR` blob DEFAULT NULL ,
`FEATURE_VERSION` varchar(64) DEFAULT NULL ,
`CODE` varchar(50) DEFAULT NULL ,
PRIMARY KEY (`ID`),
KEY `IDX_VEHICLE_FEATURE_TIME` (`CAPTURE_TIME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

CREATE TABLE `c_pic` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT ,
`DEV_ID` varchar(50) NOT NULL ,
`DEV_CHNNUM` int(11) NOT NULL,
`DEV_NAME` varchar(50) NOT NULL ,
`DEV_CHNNAME` varchar(50) NOT NULL ,
`CAR_NUM` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`EXT_CAR_NUM` varchar(100) DEFAULT NULL,
`CAR_NUM_LENGTH` varchar(100) DEFAULT NULL,
`CAR_NUM_AREA_CODE` varchar(100) DEFAULT NULL ,
`ISSUE_PLACE` varchar(100) DEFAULT NULL,
`CAR_NUMTYPE` int(11) NOT NULL ,
`CAR_NUMCOLOR` int(11) NOT NULL ,
`CAR_SPEED` int(11) DEFAULT NULL ,
`CAR_TYPE` int(11) NOT NULL ,
`CAR_COLOR` int(11) NOT NULL ,
`CAR_LENGTH` int(11) NOT NULL ,
`CAR_DIRECT` varchar(8) NOT NULL ,
`CAR_WAY_CODE` varchar(8) NOT NULL ,
`CAP_TIME` int(11) DEFAULT NULL ,
`CAP_DATE` datetime NOT NULL ,
`INF_NOTE` varchar(255) DEFAULT NULL ,
`MAX_SPEED` int(11) DEFAULT NULL ,
`MIN_SPEED` int(11) DEFAULT NULL ,
`CAR_IMG_URL` varchar(255) DEFAULT NULL ,
`CAR_IMG1_URL` varchar(255) DEFAULT NULL,
`CAR_IMG2_URL` varchar(255) DEFAULT NULL ,
`CAR_IMG3_URL` varchar(255) DEFAULT NULL ,
`CAR_IMG4_URL` varchar(255) DEFAULT NULL ,
`CAR_IMG5_URL` varchar(255) DEFAULT NULL ,
`REC_STAT` tinyint(4) DEFAULT 0 ,
`DEV_CHNID` varchar(50) NOT NULL ,
`CAR_IMG_COUNT` int(11) DEFAULT NULL ,
`SAVE_FLAG` tinyint(4) DEFAULT 0 ,
`DC_CLEANFLAG` tinyint(4) DEFAULT 0 ,
`PIC_ID` int(11) DEFAULT NULL ,
`CAR_IMG_PLATE_TOP` int(11) DEFAULT NULL ,
`CAR_IMG_PLATE_LEFT` int(11) DEFAULT NULL ,
`CAR_IMG_PLATE_BOTTOM` int(11) DEFAULT NULL ,
`CAR_IMG_PLATE_RIGHT` int(11) DEFAULT NULL,
`CAR_BRAND` int(11) DEFAULT NULL ,
`ISSAFETYBELT` tinyint(4) DEFAULT 0 ,
`ISVISOR` tinyint(4) DEFAULT 0 ,
`BIND_STAT` tinyint(4) DEFAULT 0 ,
`CAR_NUM_PIC` varchar(255) DEFAULT NULL ,
`COMBINED_PIC_URL` varchar(255) DEFAULT NULL ,
`VERIFY_MEMO` varchar(255) DEFAULT NULL ,
`CAR_WIDTH` int(11) DEFAULT NULL ,
`CAR_HEIGHT` int(11) DEFAULT NULL ,
`SERVICE_CODE` varchar(20) NOT NULL DEFAULT '0'
`REC_TYPE` int(11) DEFAULT NULL,
`PIC_RECORD_IDX` varchar(60) DEFAULT NULL,
`CROSSROAD_ID` varchar(30) DEFAULT NULL,
`CHAN_CODE` varchar(30) DEFAULT NULL,
`DATA_SOURCE` tinyint(4) DEFAULT NULL ,
`CREATE_TIME` timestamp NOT NULL DEFAULT current_timestamp() ,
`REC_CODE` varchar(32) DEFAULT NULL ,
`VIDEO_URL` varchar(255) DEFAULT NULL,
`CAP_DAY` date DEFAULT NULL ,
`CAP_DATE_UTC` bigint(20) DEFAULT NULL ,
`IN_VEHICLE_GROUP` tinyint(2) NOT NULL DEFAULT 0,
PRIMARY KEY (`ID`),
KEY `IDX_CP_COMBINE_UTC` (`CAR_NUM`,`CAP_DATE_UTC`),
KEY `IDX_PICRECORD_DEVCHNID_UTC` (`DEV_CHNID`,`CAP_DATE_UTC`),
KEY `IDX_CP_CAP_DATE_UTC` (`CAP_DATE_UTC`),
KEY `IDX_CPIC_BRAND_CD_UTC` (`CAR_BRAND`,`CAP_DATE_UTC`),
KEY `IDX_CPIC_BRAND_COLOR_UTC` (`CAR_BRAND`,`CAR_COLOR`,`CAP_DATE_UTC`),
KEY `IDX_CPIC_BRAND_DEV` (`CAR_BRAND`,`DEV_CHNID`),
KEY `IDX_CPIC_BRAND_COLOR_DEV` (`CAR_BRAND`,`CAR_COLOR`,`DEV_CHNID`),
KEY `IDX_CPIC_COLOR_DEV_UTC` (`CAR_COLOR`,`DEV_CHNID`,`CAP_DATE_UTC`),
KEY `IDX_CAR_TYPE_CAP_DATE_UTC` (`CAR_TYPE`,`CAP_DATE_UTC`),
KEY `IDX_IN_GROUP_CAP_DATE_UTC` (`IN_VEHICLE_GROUP`,`CAP_DATE_UTC`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

kennyliao, is this query coming from MariaDB's JDBC driver? DatabaseMetadata.getPrimaryKeys()?

Can you execute it yourself, replacing

A.TABLE_NAME LIKE 'adm_history' AND B.TABLE_NAME LIKE 'adm_history' with
A.TABLE_NAME = 'adm_history' AND B.TABLE_NAME = 'adm_history'

(basically replacing LIKE with '=')
and then see if it helps?

I believe there is a bug in JDBC driver logic, documentation indicates there should not be wildcard search here, so no "LIKE". It possibly has an implication on how many tables are checked (and you got many)

Vladislav Vaintroub，after ‘like’ replace to ‘=’ ，The distributed nodes has been shortened from 5 minutes to 2 minutes, and the effect is very obvious.

kennyliao There was indeed a problem in mariadb connector. After discussing it with wlad, consist of 2 parts:

• query has been corrected to use "=" in place of "like" following JDBC spec
• DatabaseMetaData.getPrimaryKeys now use only IS.STATISTICS table

(correction done connector side in CONJ-1129)

benchmark result show huge improvement: 12251.3 operations per second in place of 11.5 in testing environment, using 10k tables

Correction is for now only as a snapshot version for testing, until 3.3.2 release.
Snapshot can be used adding snapshot repository:

<repositories>

	<repository>

		<id>sonatype-nexus-snapshots</id>

		<name>Sonatype Nexus Snapshots</name>

		<url>https://oss.sonatype.org/content/repositories/snapshots</url>

	</repository>

</repositories>

<dependencies>

	<dependency>

		<groupId>org.mariadb.jdbc</groupId>

		<artifactId>mariadb-java-client</artifactId>

		<version>3.3.2-SNAPSHOT</version>

	</dependency>

</dependencies>

I moved it to Connector/J , as the fix is done inside the connector. I do not know about the blocker, only that it worked like this for a long time, or even since inception of the Connector. It is likely that only not very usual setups with many thousands of tables are affected

That was released some days ago in Connector/J 3.3.2 ( https://mariadb.com/kb/en/mariadb-connector-j-3-3-2-release-notes/ )