9 messages in com.mysql.lists.clusterRE: Why table becomes so big
FromSent OnAttachments
David27 Feb 2006 22:49 
Dan Trainor27 Feb 2006 23:31 
David28 Feb 2006 00:18 
Dan Trainor28 Feb 2006 00:22 
Burnett, Stewart28 Feb 2006 02:45 
Stewart Smith28 Feb 2006 15:52 
David28 Feb 2006 18:18 
Stewart Smith28 Feb 2006 20:36 
Burnett, Stewart01 Mar 2006 00:37 
Subject:RE: Why table becomes so big
From:Burnett, Stewart (stew@capgemini.com)
Date:02/28/2006 02:45:54 AM
List:com.mysql.lists.cluster

David,

Part of the problem is the varchar fields, these are stored as full length in ndbcluster, but are shortened in MyISAM.

You also have two indexes on varchar fields, these will also use the full length.

Cluster has some row overheads 16 bytes per data row 10 bytes per ordinary index row 25 bytes per primary key row

With my (poor) understanding of sizing NDB tables I can calc about 1.2Gb of space required (including indexes).

Check the "15.4.4.5. Defining MySQL Cluster Data Nodes" section of the manual - DataMemory and IndexMemory settings explain some of the sizing.

Stewart

-----Original Message----- From: David [mailto:jian@gmail.com] Sent: 28 February 2006 06:50 To: clus@lists.mysql.com Subject: Why table becomes so big

I have a table with MyISAM store engine and its size is about 340M with 5,000,000 rows When I convert it to NDBCLUSTER store engine, its size becomes 1.4G My table architecture is:

CREATE TABLE `user1` ( `id` int(11) NOT NULL, `username` varchar(18) NOT NULL default '', `nickname` varchar(20) NOT NULL default '', `password` varchar(32) NOT NULL default '', `sex` char(1) NOT NULL default '', `rdate` date NOT NULL default '0000-00-00', `rtime` time NOT NULL default '00:00:00', `securecode` varchar(32) NOT NULL default '', `vis` tinyint(1) NOT NULL default '0', `logdate` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`), KEY `username` (`username`), KEY `nickname` (`nickname`) ) ENGINE=ndbcluster DEFAULT CHARSET=gb2312

its index are: +-------+------------+----------+--------------+-------------+-----------+-- -----------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+----------+--------------+-------------+-----------+-- -----------+----------+--------+------+------------+---------+ | user1 | 0 | PRIMARY | 1 | id | A | 4999253 | NULL | NULL | | BTREE | | | user1 | 1 | username | 1 | username | A | 4999253 | NULL | NULL | | BTREE | | | user1 | 1 | nickname | 1 | nickname | A | 4999253 | NULL | NULL | | BTREE | | +-------+------------+----------+--------------+-------------+-----------+-- -----------+----------+--------+------+------------+---------+

Thanks :)

-- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

-- MySQL Cluster Mailing List For list archives: http://lists.mysql.com/cluster To unsubscribe: http://lists.mysql.com/cluster?unsub=stew@capgemini.co.uk

This message contains information that may be privileged or confidential and is
the property of the Capgemini Group. It is intended only for the person to whom
it is addressed. If you are not the intended recipient, you are not authorized
to read, print, retain, copy, disseminate, distribute, or use this message or
any part thereof. If you receive this message in error, please notify the
sender immediately and delete all copies of this message.