Hive 元数据“waiting for table metadata lock”
来源:互联网 发布:matlab subs 矩阵 编辑:程序博客网 时间:2024/04/30 05:29
最近hive元数据统计时,老出现“waiting for metadata lock”,造成hive查询、统计的sql执行失败。现象:
33692473
hiveadmin 10.5.18.226:5176 dataplatform_hive Query 13316 Waiting for table metadata lock SELECT 'org.apache.hadoop.hive.metastore.model.MTable' AS NUCLEUS_TYPE,`THIS`.`CREATE_TIME`,`THIS`.`
33810095hiveadmin 10.5.18.226:50978 dataplatform_hive Sleep 14357 NULL
33810096hiveadmin 10.5.18.226:50985 dataplatform_hive Query 14207 Waiting for table metadata lock SELECT 'org.apache.hadoop.hive.metastore.model.MTable' AS NUCLEUS_TYPE,`THIS`.`CREATE_TIME`,`THIS`.`
33810124hiveadmin 10.5.18.226:51027 dataplatform_hive Sleep 14334 NULL
33810127hiveadmin 10.5.18.226:51028 dataplatform_hive Query 14093 Waiting for table metadata lock SELECT 'org.apache.hadoop.hive.metastore.model.MTable' AS NUCLEUS_TYPE,`THIS`.`CREATE_TIME`,`THIS`.`
33811831hiveadmin 10.5.18.226:51839 dataplatform_hive Query 13351 Waiting for table metadata lock ALTER TABLE `TBLS` ADD CONSTRAINT `TBLS_FK2` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`)
33811832hiveadmin 10.5.18.226:51843 dataplatform_hive Sleep 13352 NULL
33812847hiveadmin 10.5.18.226:52371 dataplatform_hive Query 12753 Waiting for table metadata lock SELECT 'org.apache.hadoop.hive.metastore.model.MTable' AS NUCLEUS_TYPE,`THIS`.`CREATE_TIME`,`THIS`.`
33813031hiveadmin 10.5.18.226:52404 dataplatform_hive Query 12642 Waiting for table metadata lock ALTER TABLE `TBLS` ADD CONSTRAINT `TBLS_FK2` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`)
33813034hiveadmin 10.5.18.226:52405 dataplatform_hive Sleep 12643 NULL
33814259hiveadmin 10.5.18.226:52891 dataplatform_hive Query 11918 Waiting for table metadata lock ALTER TABLE `TBLS` ADD CONSTRAINT `TBLS_FK2` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`)
33814262hiveadmin 10.5.18.226:52892 dataplatform_hive Sleep 11919 NULL
33814302hiveadmin 10.5.18.226:52907 dataplatform_hive Query 11904 Waiting for table metadata lock ALTER TABLE `TBLS` ADD CONSTRAINT `TBLS_FK2` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`)
33814306hiveadmin 10.5.18.226:52910 dataplatform_hive Sleep 11904 NULL
33814867hiveadmin 10.5.18.226:53478 dataplatform_hive Query 11561 Waiting for table metadata lock ALTER TABLE `TBLS` ADD CONSTRAINT `TBLS_FK2` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`)
33814872hiveadmin 10.5.18.226:53479 dataplatform_hive Sleep 11562 NULL
33815135hiveadmin 10.5.18.226:53589 dataplatform_hive Query 11414 Waiting for table metadata lock ALTER TABLE `TBLS` ADD CONSTRAINT `TBLS_FK2` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`)
33815136hiveadmin 10.5.18.226:53590 dataplatform_hive Sleep 11414 NULL
33816324hiveadmin 10.5.18.226:54193 dataplatform_hive Query 10701 Waiting for table metadata lock ALTER TABLE `TBLS` ADD CONSTRAINT `TBLS_FK2` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`)
33816325hiveadmin 10.5.18.226:54195 dataplatform_hive Query 10701 Waiting for table metadata lock ALTER TABLE `TBLS` ADD CONSTRAINT `TBLS_FK2` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`)
33816333hiveadmin 10.5.18.226:54206 dataplatform_hive Sleep 10701 NULL
33816334hiveadmin 10.5.18.226:54207 dataplatform_hive Sleep 10701 NULL
33816349hiveadmin 10.5.18.226:54218 dataplatform_hive Query 10696 Waiting for table metadata lock ALTER TABLE `TBLS` ADD CONSTRAINT `TBLS_FK2` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`)
33816350hiveadmin 10.5.18.226:54223 dataplatform_hive Sleep 10696 NULL
33816776hiveadmin 10.5.18.226:54507 dataplatform_hive Query 10437 Waiting for table metadata lock ALTER TABLE `TBLS` ADD CONSTRAINT `TBLS_FK2` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`)
33816783hiveadmin 10.5.18.226:54508 dataplatform_hive Sleep 10437 NULL
这个问题困扰了好久,显然,是客户端不断的往mysql提交alter table操作,造成metadata lock,从而出现死锁。
分析步骤:
1,通过mysqlpcap来捕获向mysql数据库提交的sql操作,之前用mysql client端的show full processlist;来捕获,很难捕获到执行的sql。
mysqlpcap源码:https://github.com/hoterran/tcpcollect 将源码下载下载,直接make就可以编译了。
15:24:51:666434
10.9.18.47 330 1 hiveadmin hive_metasto SHOW FULL TABLES FROM `hive_metastore` LIKE 'TBLS'
15:24:51:66832010.9.18.47 193 1 hiveadmin hive_metasto SHOW FULL TABLES FROM `hive_metastore` LIKE 'SDS'
15:24:51:67004010.9.18.47 197 1 hiveadmin hive_metasto SHOW FULL TABLES FROM `hive_metastore` LIKE 'CDS'
15:24:51:67170510.9.18.47 191 1 hiveadmin hive_metasto SHOW FULL TABLES FROM `hive_metastore` LIKE 'COLUMNS_V2'
15:24:51:67339410.9.18.47 300 1 hiveadmin hive_metasto SHOW FULL TABLES FROM `hive_metastore` LIKE 'SERDE_PARAMS'
15:24:51:67520110.9.18.47 188 1 hiveadmin hive_metasto SHOW FULL TABLES FROM `hive_metastore` LIKE 'SD_PARAMS'
15:24:51:67689410.9.18.47 188 1 hiveadmin hive_metasto SHOW FULL TABLES FROM `hive_metastore` LIKE 'PARTITION_KEYS'
15:24:51:67857510.9.18.47 197 1 hiveadmin hive_metasto SHOW FULL TABLES FROM `hive_metastore` LIKE 'SORT_COLS'
15:24:51:68026410.9.18.47 185 1 hiveadmin hive_metasto SHOW FULL TABLES FROM `hive_metastore` LIKE 'BUCKETING_COLS'
15:24:51:68201710.9.18.47 195 1 hiveadmin hive_metasto SHOW FULL TABLES FROM `hive_metastore` LIKE 'TABLE_PARAMS'
15:24:51:68375110.9.18.47