List: | Cluster | « Previous MessageNext Message » | |

From: | Jim Hoadley | Date: | April 12 2005 2:02pm |

Subject: | Re: mysql cluster mem req calc for fusion | ||

View as plain text |

Mikael -- > I corrected some calculations and made an even more accurate > descriptions of how it works here. Thanks! As always. I think the major issue you've pointed out is the TEXT field. We were counting 256 bytes for each TEXT field * number of records, and to that adding the sum of the bytes above 256. You're saying that the TEXT record overages are in increments of 2K. So that a record with 290 bytes takes up 2384 bytes (2048 + 256 bytes) and not 290 (34 + 256 bytes). Mikael, you also mention "each TEXT field means an added BLOB table." Is there overhead associated with that? If so, how much to add in to the calculation? Other comments and questions.... > 1) Only hash indexes from Primary Key and Unique Keys use Index Memory > and they use > 25 + PK size (25 + UK size) or 33 + PK size if PK > 32 bytes Think we already got this. > 2) KEY's use 10 bytes per record of DataMemory. Already got this. > 3) DataMemory grows in chunks meaning that number of pages for a table > could be upto 20% higher than > calculated but in the mean should be around 10% higher. Curious, is this "around 10% higher" the 1.1 that's used in the (size * 1.1) * number of replicas / number of nodes formula? > 6) There is an 16 bytes overhead per record (12 bytes for tables with > only NOT NULL fields) Already got this. > 7) There is 128 bytes of overhead per page Already got this. > 8) More memory is needed for the Unique indexes Not sure what you mean, here. -- Jim Hoadley Sr Software Engineer Dealer Fusion, Inc --- Mikael Ronstr> Hi Jim, > I corrected some calculations and made an even more accurate > descriptions of how it works here. > > Table 1 > Record Size 860 + 16 bytes overhead = 876 bytes > Ordered Indexes = 40 bytes per record > Hash Index Memory 29 bytes > > Page Size is 32768 bytes and there is a 128 bytes header so there will > be a fixed amount of > 32640 / 876 = 37 records per page > > Now when all pages are filled up they actually grow the size by 18,75% > at the time and always at least two page as shown by the > code below. > This gives the number of pages to be > 2, 4, 6, 8, 11, 14, 17, 21, 25, 31, 37, 45, 54, 65, 79, 94, 112 > > This is the growth per fragment in DBTUP (with a 4-node system there > will be 8 fragments in DBTUP) > > void Dbtup::allocMoreFragPages(Fragrecord* const regFragPtr) > { > Uint32 noAllocPages = regFragPtr->noOfPages >> 3; // 12.5% > noAllocPages += regFragPtr->noOfPages >> 4; // 6.25% > noAllocPages += 2; > /* -----------------------------------------------------------------*/ > // We will grow by 18.75% plus two more additional pages to grow > // a little bit quicker in the beginning. > /* -----------------------------------------------------------------*/ > allocFragPages(regFragPtr, noAllocPages); > }//Dbtup::allocMoreFragPages() > > So with 37 records per page and number of records 13497 we get 365 > pages. Now these are distributed on the 8 fragments by random > so this gives around 45.625 pages per fragment if four of them so > either 45 or 54 dependent on how the hashing function distributes > the records. > > Thus say 7 * 54 + 45 as an example of how the randomness could work it > out => 433 pages => 14.188 MBytes > > The 40 bytes per record from 4 ordered indexes are stored also in > DataMemory => 40 * 13497 = 540 kBytes > > DataMemory for this table = 14.188 + 0.54 MBytes = 14.728 MBytes > > IndexMemory requirement is 13497 * 29 = 392 kBytes > > Another problem I saw is the calculation of size of UNIQUE indexes. > These are calculated as follows: > They are stored as a separate table with record size = (PK + UK size) > using a normal hash index > with UK as primary key and as all tables have a record overhead of 12 > bytes (or 16 in rare cases with NULL > fields in a UK). Minimum size of table is as described before as well. > > > So the corrections needed are > 1) Only hash indexes from Primary Key and Unique Keys use Index Memory > and they use > 25 + PK size (25 + UK size) or 33 + PK size if PK > 32 bytes > > 2) KEY's use 10 bytes per record of DataMemory. > > 3) DataMemory grows in chunks meaning that number of pages for a table > could be upto 20% higher than > calculated but in the mean should be around 10% higher. > > 4) Smaller tables have a larger overhead since at least 64 kBytes of > DataMemory and 16 kBytes of IndexMemory > is allocated per fragment (there are (2 * NoOfReplicas * no of data > nodes) such fragments per table). So for a 4-node > system a small table is at least 1 MByte of DataMemory and 256 kBytes > of IndexMemory. > > 5) I'm not sure how you calculated size of TEXT beyond the 256 bytes > stored in the original table, these grow in extents > of 2 kBytes and also each TEXT field means an added BLOB table and I > noticed that this will not be neglible in your > application. > > 6) There is an 16 bytes overhead per record (12 bytes for tables with > only NOT NULL fields) > > 7) There is 128 bytes of overhead per page > > 8) More memory is needed for the Unique indexes > > So hopefully these figures add to your understanding of how it works > and how to calculate the actual data size. Please remember > that a hash function introduces a certain randomness into the picture > and there are also growth in chunks so a perfect model > will be very difficult to achieve. But hopefully the gap between the > model and the reality is now decreased. Also the 10 bytes mentioned > for ordered indexes is an estimate and is based on a more complicated > model below but should not differ very much from reality. > > Rgrds Mikael > > Mikael Ronstr> MySQL AB, www.mysql.com > > Jumpstart your cluster: > http://www.mysql.com/consulting/packaged/cluster.html > > > -- > MySQL Cluster Mailing List > For list archives: http://lists.mysql.com/cluster > To unsubscribe: http://lists.mysql.com/cluster?unsub=1 > > __________________________________ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/

Thread | ||
---|---|---|

• Re: mysql cluster mem req calc for fusion | Mikael RonstrĂ¶m | 11 Apr |

• Re: mysql cluster mem req calc for fusion | Jim Hoadley | 12 Apr |

• Re: mysql cluster mem req calc for fusion | Mikael RonstrĂ¶m | 12 Apr |