SQLite format 3@ - ?-Aindexsqlite_autoindex_content_1content$++tableproperty_masterproperty_masterCREATE TABLE [property_master] ( [property_id] INTEGER NOT NULL PRIMARY KEY, [property_name] VARCHAR(255) NOT NULL)!!Qtableudo_masterudo_masterCREATE TABLE [udo_master] ( [id] INTEGER NOT NULL PRIMARY KEY, [name] VARCHAR(255) NOT NULL)P))[tableschema_versionschema_versionCREATE TABLE [schema_version] ( [schema_desc] TEXT, [schema_major_version] TEXT, [schema_minor_version] TEXT, PRIMARY KEY ([schema_major_version], [schema_minor_version]));O)indexsqlite_autoindex_schema_version_1schema_version  4.60460 460 ~hQ?+uuuuu !Data_Types)FullText_IndexH{{y7Percona_Active_Tables5Cluster_RedoLogspace +Users_Connected 'Cluster_Nodes 1Cluster_RedoBuffer /Cluster_Data_Free 9Percona_Unused_Indexes/Primary_Key_Ratio#Tables_Size)Storage_Engine'Database_Size !WorstIndex DiskInfo +purgingInterval !keyColumns serverssql1collectionInterval enabled  tjXQ>8 { 0 CCY-Aindexsqlite_autoindex_content_1content$P))[tableschema_versionschema_versionCREATE TABLE [schema_version] ( [schema_desc] TEXT, [schema_major_version] TEXT, [schema_minor_version] TEXT, PRIMARY KEY ([schema_major_version], [schema_minor_version]));O)indexsqlite_autoindex_schema_version_1schema_version!!Qtableudo_masterudo_masterCREATE TABLE [udo_master] ( [id] INTEGER NOT NULL PRIMARY KEY, [name] VARCHAR(255) NOT NULL)$++tableproperty_masterproperty_masterCREATE TABLE [property_master] ( [property_id] INTEGER NOT NULL PRIMARY KEY, [property_name] VARCHAR(255) NOT NULL) v++#indexunique_propertyproperty_master CREATE UNIQUE INDEX [unique_property] ON [property_master]([property_name])D_tablecontentcontentCREATE TABLE [content] ( [udo_id] INTEGER NOT NULL, [property_id] INTEGER NOT NULL, [value] VARCHAR(255) NOT NULL, PRIMARY KEY([udo_id], [property_id]), FOREIGN KEY ([udo_id]) REFERENCES [udo_master]([id]), FOREIGN KEY ([property_id]) REFERENCES [property_master]([property_id]))-Aindexsqlite_autoindex_content_1content +purgingInterval!keyColumns serverssql1collectionInterval enabled \ /H Q`G/?, 604800\00 all_dG/* Requirement : MySQL v5.0+ This query will return TableName and number of records it has. */ SELECT TABLE_SCHEMA AS `Database`, TABLE_NAME AS `Table`, TABLE_ROWS AS `Rows` FROM information_schema.TABLES ;+Database, Table~ 300 300 I /* Requirement : MySQL v5.0+ SQL script to grab the top 10 worst performing indexes */ SELECT t.TABLE_SCHEMA AS `Db`, t.TABLE_NAME AS `Table`, s.INDEX_NAME AS `Index_Name`, s.COLUMN_NAME AS `Field_Name`, s.SEQ_IN_INDEX `Seq_In_Index`, s2.max_columns AS `Max_Cols`, s.CARDINALITY AS `Card`, t.TABLE_ROWS AS `Est_rows`, ROUND( ( ( s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01) ) * 100 ), 2 ) AS `Sel %` F  ~604800};TABLE_SCHEMA,TABLE_NAMEG| /* Requirement : Percona based MySQL server v5.5+ and userstat=ON This query returns top 5 active tables based on rows read and rows changed */ SELECT TABLE_SCHEMA, TABLE_NAME, ROWS_READ, ROWS_CHANGED, ROWS_CHANGED_X_INDEXES FROM INFORMATION_SCHEMA.TABLE_STATISTICS ORDER BY ROWS_CHANGED DESC LIMIT 5 ; h ]yE= y/* Requirement : MySQL v5.0+ This query will return Top 5 databases on server w.r.t. Size */ SELECT TABLE_SCHEMA AS `Database`, SUM( (data_length + index_length) / (1024 * 1024) ) AS `Database_Size` FROM information_schema.TABLES GROUP BY table_schema ORDER BY `Database_Size` DESC LIMIT 5 ; all%#|, 3Db,Table,Index_Name00 300 300#7# ## 604800 604800gH g \ i^  ;.   allDatabase  S/* Requirement : MySQL v5.0+ This query will return Number of tables w.r.t. Engine_Type */ SELECT ENGINE AS `Engine`, COUNT(*) AS `Tables`, CONCAT( ROUND(SUM(table_rows) / 1000000, 2), 'M' ) AS `Rows`, CONCAT( ROUND( SUM(data_length) / (1024 * 1024 * 1024), 2 ), 'G' ) AS `Data`, CONCAT( ROUND( SUM(index_length) / (1024 * 1024 * 1024), 2 ), 'G' ) AS `Index`, CONCAT( ROUND( SUM(data_length + index_length) / (1024 * 1024 * 1024), 2 ), 'G' ) AS `Total_Size`, ROUND( SUM(index_length) / SUM(data_length), 2 ) AS `Index_Frac` FROM information_schema.TABLES WHERE ENGINE != 'NULL' GROUP BY ENGINE ORDER BY SUM(data_length + index_length) DESC ; # 604800D Tables Engine/* Requirement : MySQL v5.0+ This query will return Total number of tables and size */ SELECT COUNT(*) AS `Tables`, CONCAT( ROUND(SUM(table_rows) / 1000000, 2), 'M' ) AS `Rows`, CONCAT( ROUND( SUM(data_length) / (1024 * 1024 * 1024), 2 ), 'G' ) AS `Data`, CONCAT( ROUND( SUM(index_length) / (1024 * 1024 * 1024), 2 ), 'G' ) AS `Index`, CONCAT( ROUND( SUM(data_length + index_length) / (1024 * 1024 * 1024), 2 ), 'G' ) AS `Total_Size`, ROUND( SUM(index_length) / SUM(data_length), 2 ) AS `Index_Frac` FROM information_schema.TABLES ; all 604800 all f+f))STable_Schema,Table_Name,Column_Name 830070 0604800/Data_Type .all+-U/* Requirement : MySQL v5.0+ This query will return Number of tables w.r.t. Data_Type */ SELECT DATA_TYPE AS `Data_Type`, COUNT(*) AS `Number` FROM information_schema.COLUMNS WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql') GROUP BY `Data_Type` ORDER BY `Number` DESC ; ,300+0 *604800 (all*'S/* Requirement : MySQL v5.0+ This query will return list of FULLTEXT indexes */ SELECT TABLE_SCHEMA AS `Table_Schema`, TABLE_NAME AS `Table_Name`, INDEX_NAME AS `Index_Name`, SEQ_IN_INDEX AS `Seq_In_Index`, COLUMN_NAME AS `Column_Name`, CARDINALITY AS `Cardinality`, NULLABLE AS `Nullable` FROM information_schema.STATISTICS WHERE INDEX_TYPE LIKE 'FULLTEXT%' ORDER BY TABLE_SCHEMA, TABLE_NAME ; &300%00 300ROM INFORMATION_SCHEMA.STATISTICS s INNER JOIN INFORMATION_SCHEMA.TABLES t ON s.TABLE_SCHEMA = t.TABLE_SCHEMA AND s.TABLE_NAME = t.TABLE_NAME INNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, MAX(SEQ_IN_INDEX) AS max_columns FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA != 'mysql' GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) AS s2 ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA AND s.TABLE_NAME = s2.TABLE_NAME AND s.INDEX_NAME = s2.INDEX_NAME WHERE t.TABLE_SCHEMA != 'mysql' /* Filter out the mysql system DB */ AND t.TABLE_ROWS > 10 /* Only tables with some rows */ AND s.CARDINALITY IS NOT NULL /* Need at least one non-NULL value in the field */ AND ( s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01) ) < 1.00 /* Selectivity < 1.0 b/c unique indexes are perfect anyway */ ORDER BY `sel %`, s.TABLE_SCHEMA, s.TABLE_NAME /* Switch to `Sel %` DESC for best non-unique indexes */ LIMIT 10 ; 3( ;Ratio > 3009)/* Requirement : MySQL v5.0+ This query will return ratio of number of tables having primary key against total number of tables */ SELECT @tblWithoutPk := (SELECT COUNT(*) FROM information_schema.`TABLES` AS t LEFT JOIN information_schema.`KEY_COLUMN_USAGE` AS c ON t.TABLE_NAME = c.TABLE_NAME AND c.CONSTRAINT_SCHEMA = t.TABLE_SCHEMA WHERE t.TABLE_SCHEMA NOT IN ('information_schema', 'mysql') AND constraint_name IS NULL) AS `Tables_Without_PK`, @tblwithPk := (SELECT COUNT(*) FROM information_schema.`TABLES` AS t LEFT JOIN information_schema.`KEY_COLUMN_USAGE` AS c ON t.TABLE_NAME = c.TABLE_NAME AND c.CONSTRAINT_SCHEMA = t.TABLE_SCHEMA WHERE t.TABLE_SCHEMA NOT IN ('information_schema', 'mysql') AND constraint_name = 'PRIMARY') AS `Tables_With_PK`, @tblwithPk / (@tblWithoutPk + @tblwithPk) * 100 AS `Ratio` ; = 0 <604800 :all VV(AQ Table_Schema,Table_Name,Index_Name B 604800?' /* Requirement : Percona based MySQL server v5.5+ This query returns list of unused indexes */ SELECT DISTINCT s.TABLE_SCHEMA AS `Table_Schema`, s.TABLE_NAME AS `Table_Name`, s.INDEX_NAME AS `Index_Name` FROM information_schema.STATISTICS `s` LEFT JOIN information_schema.INDEX_STATISTICS INDXS ON ( s.TABLE_SCHEMA = INDXS.TABLE_SCHEMA AND s.TABLE_NAME = INDXS.TABLE_NAME AND s.INDEX_NAME = INDXS.INDEX_NAME ) WHERE INDXS.TABLE_SCHEMA IS NULL AND s.`TABLE_SCHEMA` != 'mysql' ; @ allO 0 P 300 Q all )9.B T 604800 V 300U 0RX# /* Requirement : MySQL Cluster v7.1.3 This query will return percentage of free logbuffer size */ SELECT @total := (SELECT SUM(total) FROM ndbinfo.logbuffers) AS `Total`, @used := (SELECT SUM(used) FROM ndbinfo.logbuffers) AS `Used`, ((@total - @used) / @total) * 100 AS `Free_Redo_Buffer`; W all  S- Used_Data_Memory#RE /* Requirement : MySQL Cluster v7.1.3 This query will return percentage of free data memory */ SELECT @total_data_memory := (SELECT SUM(total) AS `Total_Memory` FROM ndbinfo.memoryusage WHERE memory_type = 'Data memory') AS `Total_Data_Memory`, @used_data_memory := (SELECT SUM(used) Used_Memory FROM ndbinfo.memoryusage WHERE memory_type = 'Data memory') AS `Used_Data_Memory`, ( @total_data_memory - @used_data_memory ) / (@total_data_memory) * 100 AS `Percentage_Of_Free_Data_Memory` ;  _ Total ` 604800 [ 0^ /* Requirement : MySQL Cluster v7.1.3 This query will return number of not running nodes , returns 0 incase of all nodes are running*/ SELECT @total := (SELECT VARIABLE_VALUE FROM information_schema.`GLOBAL_STATUS` WHERE VARIABLE_NAME = 'NDB_NUMBER_OF_DATA_NODES') AS `Total`, @running := (SELECT VARIABLE_VALUE FROM information_schema.`GLOBAL_STATUS` WHERE VARIABLE_NAME = 'NDB_NUMBER_OF_READY_DATA_NODES') AS `Running`, @total - @running AS `Nodes_Not_Running` ; \ 300 ] all Z 604800 Y Used )hhpx (08@HPxph`X     +*)('&%/.-,8 0pxp@8(0 xph`XPH @ @ HTHKHBH9H0H(H HHH987<;: ? > = B A @ ] \ [ Z Y X W V U T S R Q P Oxwvuts r q p o n m ` _ ^~}|{zy k`*E< {all x604800 r 604800" n 300s0m 0Tu'/* Requirement : MySQL Cluster v7.1.3 This query will return percentage of free logspace size */ SELECT @total := (SELECT SUM(total) FROM ndbinfo.logspaces) AS `Total`, @used := (SELECT SUM(used) FROM ndbinfo.logspaces) AS `Used`, ((@total - @used) / @total) * 100 AS `Free_Redo_Log_Space` ;5 vally0w3Free_Redo_Log_Space t300q- Host_Name, Users[p5 /* Requirement : MySQL v5.0+ This query will return currently connected users */ SELECT SUBSTRING_INDEX(HOST, ':', 1) AS `Host_Name`, GROUP_CONCAT(DISTINCT USER) AS `Users`, COUNT(*) AS `No_Of_Connections` FROM information_schema.PROCESSLIST WHERE `user` != 'system user' GROUP BY `Host_Name` ORDER BY No_Of_connections DESC ; o all z300 `` z300y1 x24000w v'MySQL-Clusterb=select @total_logspace := (select distinct (total) from ndbinfo.`logspaces`) as Total_Logspace, @used_logspace := (select distinct (used) from ndbinfo.`logspaces`) as Used_Logspace, (@total_logspace - @used_logspace) / (@total_logspace) * 100 as `Percentage_Of_Free_Logspace` ;