MySQL Forums
Forum List  »  Memory Storage Engine

Re: How to estimate memory usage ?
Posted by: Dewey Gaedcke
Date: December 28, 2006 03:58PM

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 ;

Options: ReplyQuote


Subject
Views
Written By
Posted
13021
November 14, 2006 06:45AM
7563
November 20, 2006 09:33AM
5998
December 12, 2006 05:43PM
8429
December 18, 2006 09:45AM
5710
December 21, 2006 11:57AM
6357
December 21, 2006 01:47PM
6181
December 28, 2006 01:50PM
Re: How to estimate memory usage ?
6926
December 28, 2006 03:58PM
5167
January 03, 2007 05:05AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.