Re: How to estimate memory usage ?
If I understand the calculation example correctly, then this proc is getting fairly close but since you can't use indirection for TableNames in a query,
the block at line 45 (below this comment) needs a dynamic SQL solution
I'm out of time for today----feel free to improve & repost this proc
DELIMITER $$
DROP PROCEDURE IF EXISTS `db`.`up_Table_CalcMemUsage` $$
CREATE PROCEDURE `minggl`.`up_Table_CalcMemUsage` (In pTableName Char(40), In pDBName Char(40))
BEGIN
Declare vRecCount, vlength_of_row Int Default 0;
Declare vBtreeSize, vHashSize SmallInt Default 0;
Declare vSizeofCharPointer TinyInt Default 4; /* 8 for 64 bit machines */
If 'this is a' = '64 bit server' then
Set vSizeofCharPointer = 8;
End if;
If Coalesce(pTableName,'') = '' then
Set pTableName = 'myTable';
End if;
If Coalesce(pDBName,'') = '' then
Set pDBName = 'db';
End if;
SELECT TABLE_ROWS, AVG_ROW_LENGTH
into vRecCount, vlength_of_row
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = pDBName
AND table_name LIKE pTableName;
Drop Temporary Table If Exists `ttIndexes`;
/* Use pDBName; cannot specify the DB name when creating a temp table but Use not allowed either */
CREATE TEMPORARY TABLE `ttIndexes` (
`Idx_ColName` CHAR(35),
`Idx_Type` CHAR(20),
`Idx_Unique` Boolean,
`Idx_Card` smallInt,
`Idx_Max_Length` SmallInt )
ENGINE = MEMORY;
/* I know I could skip the memory table & use a materialized view in the queries below, but this is more readable
and I'm sure I'll find other uses for this construct once I start playing with it */
Insert into ttIndexes
(Idx_ColName, Idx_Type, Idx_Unique, Idx_Card, Idx_Max_Length)
SELECT COLUMN_NAME, INDEX_TYPE, Not(NON_UNIQUE), CARDINALITY
/* since you can't use indirection for TableNames in a query,
this block needs a dynamic SQL solution
& I'm out of time for today----feel free to improve & repost this proc */
, (Select Max(Length(ISS.COLUMN_NAME)) from pDBName.pTableName)
FROM INFORMATION_SCHEMA.STATISTICS ISS
WHERE table_name = pTableName AND table_schema = pDBName ;
/* Assuming 32-bit machine: sizeof(char*) == 4.
One btree index, key length 20.
Two hash indexes.
Row length 33.
SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) × 4)
+ SUM_OVER_ALL_HASH_KEYS(sizeof(char*) × 2)
+ ALIGN(length_of_row+1, sizeof(char*)): */
Select Sum(Idx_Max_Length + (vSizeofCharPointer * 4))
into vBtreeSize
from ttIndexes
where Idx_Type = 'BTREE';
Select Sum(vSizeofCharPointer * 2)
into vHashSize
from ttIndexes
where Idx_Type = 'HASH';
/* It would be really nice to compare the calculated results to what the SHOW Table Status routine returns
as it's estimate based on allocated blocks---a large discrepancy would indicate a memory leak or other problem */
Select (vRecCount * (vBtreeSize + vHashSize + ALIGN(vlength_of_row+1, vSizeofCharPointer))) as MemUsedbyTable;
END $$
DELIMITER ;