Re: Size of tables: are VARCHARs efficient? How do I "narrow" this table? - Alex Davies - com.mysql.lists.cluster

5 messages in com.mysql.lists.clusterRe: Size of tables: are VARCHARs effi...

From	Sent On	Attachments
Alex Davies	20 Feb 2005 05:18
Mikael Ronström	21 Feb 2005 00:03
Alex Davies	21 Feb 2005 03:20
Mikael Ronström	21 Feb 2005 05:18
pek...@mysql.com	21 Feb 2005 05:29

Subject:	Re: Size of tables: are VARCHARs efficient? How do I "narrow" this table?
From:	Alex Davies (davi...@gmail.com)
Date:	02/21/2005 03:20:45 AM
List:	com.mysql.lists.cluster

Dear Mikael, Lewis and anyone else,

Thank-you for your replies. I can confirm that what is inserted is different to what actually comes out, even though no errors are reported.

I have reduced the VARCHAR's to the smallest size possible to hold the data but since some fields are large this still requires several gigabytes of RAM which I don't have! If I change the field types to TEXT (which seems odd to me since they are "larger" fields) will this help at all?

With many thanks,

Alex Davies

On Mon, 21 Feb 2005 09:04:09 +0100, Mikael Ronström <mik...@mysql.com> wrote:

Hi Alex,

2005-02-20 kl. 14.19 skrev Alex Davies:

Dear All,

I am trying to add the table attached below to a cluster. It has a large number of VARCHAR(200) - most of which do not contain 200 chars but some do.

The table has 30,000 rows in it. The total size of the table, however, is only 3.4mb in MyISAM.

Obviously around 110 Bytes per row, max size of a row is around 3 kBytes. MySQL Cluster 4.1 and 5.0 have fixed size rows and must therefore store the full size of varchar's. In MySQL Cluster 5.1 we have introduced var sized support in the NDB Cluster storage engine (bk tree not yet available). This is the reason why such a large DataMemory is needed.

As soon as I ALTER it to NDBCLUSTER or try to insert the rows into it, I used to get a "Table Full" error. However, I have repeated the alter several times and now it inserts fine (mysql returns "Your SQL-query has been executed successfully (Query took 128.7730 sec)") but only very small amounts of data actually insert - most fields are blank or have a ? in them.

Given the size above I presume it could be correct. However if there is a difference on what is inserted to what is read than most definitely it is a bug.

So lots of space can be saved if the VARCHAR's max size can be decreased.

Rgrds Mikael

My config.ini contains these parameters: [NDBD DEFAULT] NoOfReplicas=2 DataMemory = 700MB IndexMemory = 200MB MaxNoOfAttributes = 3000 MaxNoOfOrderedIndexes = 5000 MaxNoOfUniqueHashIndexes = 5000

Each storage node (there are two) has 1024MB of RAM.

I would be most grateful for any suggestions on how I could deal with this problem - either by changing the table (as long as its functionality remains similar), or changing my cluster configuration.

With many thanks,

Alex Davies

CREATE TABLE `e107_profiles` ( `uid` int(11) NOT NULL default '0', `gender` char(1) NOT NULL default '', `country` varchar(50) NOT NULL default '', `sign` varchar(32) NOT NULL default '', `sexuality` varchar(32) NOT NULL default '', `profession` varchar(200) NOT NULL default '', `relationship` varchar(200) NOT NULL default '', `partner` varchar(200) NOT NULL default '', `interests` varchar(200) NOT NULL default '', `happy` varchar(200) NOT NULL default '', `sad` varchar(200) NOT NULL default '', `cigs` varchar(32) NOT NULL default '', `alcohol` varchar(32) NOT NULL default '', `drugs` varchar(32) NOT NULL default '', `fetish` varchar(100) NOT NULL default '', `habits` varchar(100) NOT NULL default '', `ownwords` text NOT NULL, `race` varchar(32) NOT NULL default '', `hair` varchar(32) NOT NULL default '', `eyes` varchar(32) NOT NULL default '', `height` varchar(32) NOT NULL default '', `waist` varchar(32) NOT NULL default '', `weight` varchar(32) NOT NULL default '', `weight_type` char(3) default NULL, `body` varchar(32) NOT NULL default '', `hairy` varchar(32) NOT NULL default '', `yim` varchar(75) NOT NULL default '', `music` varchar(32) NOT NULL default '', `food` varchar(100) NOT NULL default '', `author` varchar(50) NOT NULL default '', `club` varchar(50) NOT NULL default '', `animal` varchar(50) NOT NULL default '', `person` varchar(50) NOT NULL default '', `website` varchar(100) NOT NULL default '', `place` varchar(100) NOT NULL default '', `tv` varchar(50) NOT NULL default '', `movie` varchar(50) NOT NULL default '', `thing` varchar(50) NOT NULL default '', `final_q` varchar(32) NOT NULL default '', `final` text NOT NULL, `updated` int(11) NOT NULL default '0', `hidebday` tinyint(4) default NULL, `pic_adult` tinyint(4) default NULL, `webcam` varchar(100) default NULL, `sound_file` varchar(100) default NULL, `search_settings` varchar(150) default NULL, `profile_adult` int(11) default NULL, `view_adult` int(11) default NULL ) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1;

This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender immediately by e-mail and delete this e-mail permanently.

Jumpstart your cluster: http://www.mysql.com/consulting/packaged/cluster.html