Sonali's Blog

You are here

Sonali's blog

Calculating Memory Requirements for NDB Storage Engine

While calculating the storage requirements in NDB, extra consideration is needed when calculating storage requirement for NDB tables. For tables using the NDB cluster storage engine, there is the factor of 4 – byte alignment to be taken into account when calculating storage requirements. This means that all NDB data storage is done in multiples of 4 bytes.

For Example, let’s say if a column takes 14 bytes to store. In NDB it requires 16 bytes to store. 2bytes will be padding. Because of this only in NDB TINYINT, SMALLINT, MEDUMINT, and INT all require 4 bytes storage per record due to the alignment factor. This rule is not applied in case of BIT data type.

BIT(X) – in NDB storage engine this column will take X bite of storage space, if a table definition contains 1 or more BIT column (up to 32 BIT columns) then NDB Cluster reserves 4 Bytes (32 bytes) per row for these . If the table definition contains more than 32 BIT columns, then NDB Cluster reserves 8 bytes per row. NDB storage engine also reserve 4 bytes per row if the table definition contains any columns defined as NULL up to32 NULL columns.

In NDB versions earlier than 5.1, all columns in NDB table were fixed in size and stored in memory. However in 5.1 the NDB storage defines supports variable size data types in memory also. Disk data columns are always stored as fixed width column. Each row is stored in one or two parts, depending on whether it has any variable- size columns are stored together as the fixed size part of the row. If the row has any variable sized columns, then the fixed size part of the row contain a pointer to a variable –size column is stored. Each row with TEXT or BLOB columns made up of two separate parts. Fixed 256 bytes of row are stored in a table and other 256 bytes are stored in hidden table. The rows in this second table are always 2,000 bytes long.

If the size of the rows is less than 256 then it is stored in single table. If this size of rows is greater than 256 then size is calculated by the following formula = 256+ X + (200-(X-256) %2000) ( X stands for the size of row)

Each row stored in an NDB table requires an overhead of 16 bytes per row in addition to added due to 4 byte alignment. For variable size data type 8 bytes of this is used as a pointer to the variable sized part of the row. Index also added to per-row storage requirement. Each ordered index uses 10 bytes of DataMemory per row. A hash index uses 25 bytes of IndexMemory per row.

In – Memory Storage

From MySQL 5.1, NDB storage can store data in memory and on disk. By default, MySQL cluster will set all tables created with NDB storage engine to in-memory data storage. Data in MySQL cluster can be stored either on disk or in memory. However indexes are always stored in memory. Because of in memory storage of indexes and data, retrieval of data is very fast. To calculate memory usage first step is calculating the amount of RAM that each row uses in each table. Then multiply the size of each row by the number of rows to get the size database and then work out the memory usage on each storage node.

To calculate data memory, calculate how much each row is going to use (Keep all consideration of data type in mind that are discussed above) Then add a fixed row overhead. Each ordered indexes like 10 bytes of storage. To calculate how many ordered indexes are there add all index you have defined and ordered indexes are also created when primary and unique indexes are defined (unless index are created with USING HASH option). Each primary key or hash index (defined by user or NDB automatically occupies 25 bytes per storage).

When a unique key is created other than primary key an additional hidden table is created. Each such table has 2 columns. One column has value declared as unique by user. The other column is a value generated NDB makes a primary key of this hidden table. Data in NDB is stored in pages. Each page stores up to 32, 765 bytes of data. Each page can only store data of one table and hidden table associated with that table.

No. of rows per pages = 32,768 Size of each row (Calculated above) This will tell you how many rows get per page of 128 bytes

Total memory requirement in byte = (Size of each row x No. rows) + (No. of pages x 128) This calculation needs to be done for all tables. RAM needed on each strong node = (Total memory requirement for one copy of your tables x No. of replicas) / No. of data nodes.

This only gives estimate for the RAM required for data storage. More RAM is required for buffers, temporary storage and so on.

Disk Based Storage

From MySQL 5.1 cluster can also use disk based storage by allowing columns that are not indexes to be stored on disk. The disk data is stored in a table space which is a collection of one or more data files on data node.

Undo logging Requires Space

In case of disk based storage, UNDO log files and data files are created which also use disk. The UNDO logs are stored on data nodes. A group of data files is called a table space. Group of log files is called a log file group. This log files group must be created before creating table space. Each table space is written to data files in table space are split up into extends. An extent can be defined as unit of allocation whose size is fixed at the time of table space creation. When a table requires space to store rows, it searches any extent that currently has free space. If no free space is found, a free extent in one of the data file of the table space is allocated to table. If there is no free extent available , no extent can be allocated and no more rows can be added to the table. An extent can have data from only one table.

Note: Views, trigger, stored routines, user, and accounts can’t be stored in the cluster. These components are required on each MySQL sever, than it is necessary to copy all components to all servers.

 

Tags: 

OSSCube is now Sensio Labs Training partner to provide Symfony training worldwide

OSSCube is Sensio Labs Partner

I am happy to announce that we have partnered with Sensio Labs, a company founded by creators of Symfony PHP Development Framework, Fabien Potencier, Gregory Pascal, and Samuel Potencier. Under this agreement we are now the official partners of Sensio Labs and are authorized to conduct official Symfony Training Programs and offer Sensio Labs Authorized Developer certification (to be launched soon). Our team will also receive training and certification on Symfony PHP framework by Sensio Labs.

Symfony is one of the best web application framework for PHP projects. Our team loves developing web applications on it and it is one of the best PHP development framework that is available and aims to speed up the creation and maintenance of web applications, and to replace the repetitive coding tasks by power, control and pleasure. The very small number of prerequisites makes Symfony easy to install on any configuration; you just need Linux or Windows with a web server and PHP installed. It is compatible with almost every database system. In addition, it has a very small overhead, so the benefits of the framework don't come at the cost of an increase of hosting costs.

This partnership gives us access to resources, insights and roadmap of symfony and we will also launch symfony trainings. We will be launching Symfony 1.2 training, Doctrine training and Symfony 1.2 + Doctrine training. Beside training we are already offering services like symfony code audits, product development in symfony. for more information visit our Symfony Services page.

I am sure this partnership will go a long way in enabling our customers to leverage the power of the symfony framework.


Call for paper of ZendCon 09 is now Open

ZendCon Logo
This is a great opportunity to showcase your work and best skills. ZendCon is mother of all PHP conferences. Don't forget to send in your paper. You would have an opportunity to meet "Who is Who" of PHP world including creators of PHP!

Tags: 

OSSCube invited at International PHP Conference 2009 to take MySQL Cluster Workshop and MySQL Performance Tuning Session

International PHP Conference Spring 2009 I am excited at being invited to participate at the International PHP Conference at Berlin where I will be taking a session on MySQL Cluster Workshop and a half day workshop on MySQL Performance Tuning & Optimization Capsule. I will be traveling to Berlin, Germany for this conference which will be held from 25 to 27th May. The focus of this conference is to connect the world through the usage of PHP.

The International PHP Conference is one of the biggest events on PHP which attracts PHP enthusiasts from all over the world and is known to be the most popular PHP Conference outside USA. The conference has been previously held in major cities like Frankfurt, Mainz, Barcelona and Montreal among others, and is known for the quality technical talks delivered there by experts. This year the International PHP Conference Spring Edition promises to be a huge success with an impressive line up of speakers on a number of topics on the PHP realm. The conference intends to explore the innovations coming with the next generation of PHP and discuss the the topic of connecting the world through the usage of PHP.

The event is organized by Software & Support, one of the most innovative publishing houses in the IT sector. The advisory board of the event consists of Björn Schotte, the Editor-in-Chief of the PHP Magazine and Head of Chair of the International PHP Conferences,Sebastian Bergmann, long-time contributor to various PHP projects, including PHP itself and the creator of PHPUnit and other Open Source tools and Robert Lippert, the editor of the PHP Magazine. It will be fun to share stage with the other speakers in the conference like Lukas Smith (Who is a release manager for PHP 5.3 and prolific contributor to php) Sebastian Bergmann, C. Arntz, core developer in times of PHP4 and an active member in the community, David Zülke is the Lead Developer of Agavi, an open source MVC framework for PHP, and CEO of Bitextender GmbH. I will conduct a session on MySQL Performance Tuning & Optimization Capsule which will be intended for PHP, Ruby and Java developers on performance tuning and optimization of MySQL. From my experience and learning I will cover the the deadly mistakes to be avoided and will take real life examples of optimizing application many times. I will also conduct a half day workshop on MySQL Cluster which is going to be a hands on training to setup MySQL cluster and effectively administer it. I will provide tips on fine tuning the cluster and on helpful tools around the MySQL Cluster. Though I speak regularly at conference, unconferences and meet-up groups but this one is going to be special! Anyone in the area, please send me a message and we can meet.


Leading two MySQL Sessions at OSSPAC 09

I have started packing my bags for OSSPAC 09, Singapore scheduled on 16-18 Feb at Grand Hyatt. I would be taking a tutorial on "MySQL Clusters" and a session on "Secrets of Best MySQL Optimization Practice". If you are attending OSSPAC 09, it will be a pleasure to meet you. Please email sonali@osscube.com and we will fix up a convenient time to meet.

Tags: 

Pages

    client testimonials

    OraMetrix Testimonial

    We’ve reduced our costs by 60% and were able to improve our data quality by moving from SalesForce.com to SugarCRM.
    Our service provider, OSSCube exceeded our expectations! Delivery was very fast and they were extremely responsive to any issues uncovered during UAT. We're looking forward to reaping the benefits of SugarCRM long into the future.

    Phillip Getto, CTO
    Orametrix, US

    Client Testimonial

    Thank you all. I am very fortunate to be working with such a skilled, dedicated and gracious team. I only wish I could be there in person to express my gratitude.

    Client Representative
    Building and Construction Network, US

    CUSolutions Testimonial

    The requests that OSSCube has been working on are making our jobs better so I wanted to pass the kudos along to your team!

    Project Leader of Web and Data Services
    CU Solutions Group, US

    Smartstart Testimonial

    We wanted to express our appreciation for the work your team performed on the SmartStart application. The team was always professional, following up consistently and appropriately, and followed through on every deliverable in a detailed manner. And we appreciate the professional workforce you are building at OSSCube.

    Ben and Sue, Client Representatives
    SmartStart application, US

    Tesco Testimonial

    Well done for the successful BSS migration! I'm really conscious that there has been a huge amount of work that has gone into planning and managing this event, and it's a credit to everyone involved that it has gone as smoothly as it has done. It's been particularly encouraging to see the level of dedication and the feeling of working as 'one team' which has ensured that issues have been cleared down in a speedy fashion.

    Client Representative
    Virgin Media, US

    upcoming webinars