Why:
Created script post SingleStore v8+ update for our system to take advantage of JSON seekable performance enhancement
What:
Scans all schemas looking for tables that have a JSON variable type so can rebuild for SingleStore v8+ JSON seekable format performance enhancement
Based On:
Columnstore Seekablity · SingleStore Documentation
- Order of processing differs as making a priority to getting table productionally available again
- Assumption that use_seekable_json = ON by default
Miscellaneous Notes:
- Code is set in a ‘anonymous code block’ due to my running in MySQL Workbench having a limit to outputted windows that are generated by ‘OPTIMIZE TABLE’
- Rebuilds only ‘true’ tables (BASE TABLE) that have JSON variable types
- Will update / re-sync rebuild tables containing AUTO_INCRMENT to leaves
- Makes accommodations for computed columns as part of the rebuild
- Added a comment for each schema for adding backup of tables (suggest doing backup before any table change)
Script:
WITH Dta AS (
SELECT
T.TABLE_SCHEMA
,T.`TABLE_NAME`
,IF(T.`AUTO_INCREMENT` IS NOT NULL,1,NULL):>TINYINT HasIncrement
,ROW_NUMBER() OVER(PARTITION BY T.TABLE_SCHEMA ORDER BY T.TABLE_SCHEMA,T.`TABLE_NAME`) AS RowNum -- Used to backup schema
,GROUP_CONCAT(IF(C.EXTRA <> 'computed',CONCAT('`',`COLUMN_NAME`,'`'),NULL) ORDER BY ORDINAL_POSITION) DataColumns
,MAX(IF(C.EXTRA = 'computed',1,0)) HasComputed
FROM INFORMATION_SCHEMA.`TABLES` T
JOIN INFORMATION_SCHEMA.`COLUMNS` C ON T.TABLE_SCHEMA = C.TABLE_SCHEMA AND T.`TABLE_NAME` = C.`TABLE_NAME`
WHERE T.TABLE_TYPE = 'BASE TABLE' AND T.STORAGE_TYPE = 'COLUMNSTORE'
GROUP BY T.TABLE_SCHEMA, T.`TABLE_NAME`
HAVING MAX(IF(C.DATA_TYPE = 'JSON',1,0)) = 1 -- Must have JSON type in table
), Bck AS (
-- Our backup processing allows single schema with a table list
SELECT
TABLE_SCHEMA
,CONCAT("-- Add Backup Tables for `",TABLE_SCHEMA,"`: \"DBTables\": \"",GROUP_CONCAT(`TABLE_NAME`),"\";\n") BckUp
FROM Dta
GROUP BY TABLE_SCHEMA
)
SELECT
REPLACE(CONCAT(
"-- SET RESOURCE_POOL = <EXCEPT_POOL>; -- Optional; Based on system. Large data sets may take a long time to copy!
DELIMITER //\nDO DECLARE\nBEGIN\n"
,GROUP_CONCAT(CONCAT(
IF(Dta.RowNum = 1,Bck.BckUp,'') -- Backup if required
,"DROP TABLE IF EXISTS `",Dta.TABLE_SCHEMA,"`.`",`TABLE_NAME`,"_Rebuild`;
CREATE TABLE `",Dta.TABLE_SCHEMA,"`.`",`TABLE_NAME`,"_Rebuild` LIKE `",Dta.TABLE_SCHEMA,"`.`",Dta.`TABLE_NAME`,"`;
INSERT INTO `",Dta.TABLE_SCHEMA,"`.`",`TABLE_NAME`,"_Rebuild` ",IF(Dta.HasComputed = 1,CONCAT("(",Dta.DataColumns,") "),""),"SELECT ",IF(Dta.HasComputed = 1,Dta.DataColumns,"*")," FROM `",Dta.TABLE_SCHEMA,"`.`",Dta.`TABLE_NAME`,"`;
ALTER TABLE `",Dta.TABLE_SCHEMA,"`.`",`TABLE_NAME`,"` RENAME TO `",Dta.TABLE_SCHEMA,"`.`",Dta.`TABLE_NAME`,"_OLD`;
ALTER TABLE `",Dta.TABLE_SCHEMA,"`.`",`TABLE_NAME`,"_Rebuild` RENAME TO `",Dta.TABLE_SCHEMA,"`.`",Dta.`TABLE_NAME`,"`;\n"
,IF(Dta.HasIncrement = 1,CONCAT("AGGREGATOR SYNC AUTO_INCREMENT ON `",Dta.TABLE_SCHEMA,"`.`",Dta.`TABLE_NAME`,"`;\n"),'')
,"OPTIMIZE TABLE `",Dta.TABLE_SCHEMA,"`.`",Dta.`TABLE_NAME`,"` FULL;
DROP TABLE `",Dta.TABLE_SCHEMA,"`.`",`TABLE_NAME`,"_OLD`;
") SEPARATOR "\n")
,"END //\nDELIMITER ;"
),'\t','') RebuildJSONTables
FROM Dta
JOIN Bck ON Dta.TABLE_SCHEMA = Bck.TABLE_SCHEMA;
Example single table output snippet
(Example table - T_Table_Info - has computed columns)
DROP TABLE IF EXISTS `test`.`T_Table_Info_Rebuild`;
CREATE TABLE `test`.`T_Table_Info_Rebuild` LIKE `test`.`T_Table_Info`;
INSERT INTO `test`.`T_Table_Info_Rebuild` (`TABLE_SCHEMA`,`TABLE_NAME`,`ModifiedDate`,`TableDetails`,`Statistics`) SELECT `TABLE_SCHEMA`,`TABLE_NAME`,`ModifiedDate`,`TableDetails`,`Statistics` FROM `test`.`T_Table_Info`;
ALTER TABLE `test`.`T_Table_Info` RENAME TO `test`.`T_Table_Info_OLD`;
ALTER TABLE `test`.`T_Table_Info_Rebuild` RENAME TO `test`.`T_Table_Info`;
OPTIMIZE TABLE `test`.`T_Table_Info` FULL;
DROP TABLE `test`.`T_Table_Info_OLD`;