RE: Issues with few tables while importing database withenginetypeNDBCLUSTER - Tangirala, Srikalyan - com.mysql.lists.cluster

29 messages in com.mysql.lists.clusterRE: Issues with few tables while impo...

From	Sent On	Attachments
Mikael Ronström	05 May 2007 01:34
Robin Bowes	05 May 2007 08:55
Sebastien LELIEVRE	15 May 2007 05:42
Johan Andersson	16 May 2007 02:57
Sebastien LELIEVRE	16 May 2007 03:04
Johan Andersson	16 May 2007 03:15
Sebastien LELIEVRE	16 May 2007 03:33
Sebastien LELIEVRE	16 May 2007 08:46
Mikael Ronström	16 May 2007 13:06
Tangirala, Srikalyan	16 May 2007 13:18
Matthew Montgomery	16 May 2007 21:29
Tangirala, Srikalyan	17 May 2007 06:15
Matthew Montgomery	17 May 2007 07:45
Tangirala, Srikalyan	17 May 2007 08:26
Matthew Montgomery	17 May 2007 08:52
Tangirala, Srikalyan	17 May 2007 11:06
Sebastien LELIEVRE	18 May 2007 01:59
Sebastien LELIEVRE	18 May 2007 02:29
Rosario Pingaro	20 May 2007 20:45
Jon Stephens	20 May 2007 23:30
Rosario Pingaro	21 May 2007 00:59
Jon Stephens	21 May 2007 02:53
Sebastien LELIEVRE	21 May 2007 03:09
Jonathan Miller	25 May 2007 06:00
Tangirala, Srikalyan	29 May 2007 08:50
Geert Vanderkelen	29 May 2007 09:55
Tangirala, Srikalyan	29 May 2007 11:13
Tangirala, Srikalyan	29 May 2007 13:16
Stewart Smith	31 May 2007 03:47

Subject:	RE: Issues with few tables while importing database withenginetypeNDBCLUSTER
From:	Tangirala, Srikalyan (Srik...@nortelgov.com)
Date:	05/17/2007 11:06:19 AM
List:	com.mysql.lists.cluster

Hi Mathew: Excellent!!! Thank you so much for the information. It seems to be working now. Your continuous support is much appreciated. I would have to go ahead and import some more databases and monitor the health of the MySQL too. I would definitely fire some more questions when needed.

Best Regards, Srikalyan Tangirala

-----Original Message----- From: Matthew Montgomery [mailto:mmon...@mysql.com] Sent: Thursday, May 17, 2007 11:53 AM To: Tangirala, Srikalyan Cc: cluster Subject: RE: Issues with few tables while importing database withenginetypeNDBCLUSTER

On Thu, 2007-05-17 at 11:26 -0400, Tangirala, Srikalyan wrote:

Hi Mathew: Thank you for your continuous support. We tried to increase the memory from 256 M to 1.5 GB and it just gives me the same error again and again. I guess, my approach is perfectly wrong. I could import 59 tables out of 60 tables in the database with the same foreign key references. The only table which is giving the issues is this table. I could run the same table with engine InnoDB but not with NDBCluster. Index memory is 768M and data memory is 512M.

Another thing is that could you be more precise of how to use the ndb_size.pl script against the InnoDB version of the database schema to find out what minimum values are suggested these and other options?? I'm sorry troubling you but I am lost in fixing this issue.

Config.ini file looks like this: # Options affecting ndbd processes on all data nodes: [NDBD DEFAULT] NoOfReplicas=2 # Number of replicas DataMemory=512M # How much memory to allocate for data storage IndexMemory=768M # How much memory to allocate for index storage <-- Too

HIGH!

Reduce IndexMemory. There is no way you will have 768MB of index and 512MB of data usage.

The problem is that the "metadata" objects describing the OrderedIndex objects are stored in separate buffer from IndexMemory. The number of these metadata objects allowed is defined by MaxNoOfOrderedIndexes variable. To resolve this particular issue immediately you could simply double this value and do a rolling restart (restart ndb_mgmd then restart each ndbd).

With regard to ndb_size.pl please check out: http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-utilities-ndb-size.html

When you have run this script, compare the suggested parameter values reported for your database to the default values for each shown here:

http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-config-params-ndbd.html

If the default is higher than the recommended value from ndb_size.pl do not adjust the value. Obviously if the recommended vales are lower than the defaults raise them accordingly. You should factor in a degree of padding in these numbers, using slightly larger numbers than suggested by the script.

# For DataMemory and IndexMemory, we have used the # default values. Since the "world" database takes up # only about 500KB, this should be more than enough for # this example Cluster setup.

# TCP/IP options: [TCP DEFAULT] portnumber=2202 # This the default; however, you can use any # port that is free for all the hosts in the cluster # Note: It is Recommended that you do not specify the portnumber at all and allow the default value to be # used instead

# Management process options: [NDB_MGMD] hostname=172.31.22.78 # Hostname or IP address of MGM node datadir=/var/lib/mysql-cluster # Directory for MGM node log files

# Options for data node "A": [NDBD] # (one [NDBD] section per data node) hostname=172.31.22.80 # Hostname or IP address datadir=/usr/local/mysql/data # Directory for this data node's data files

# Options for data node "B": [NDBD] hostname=172.31.22.81 # Hostname or IP address datadir=/usr/local/mysql/data # Directory for this data node's data files

#SQL node options: [MYSQLD] hostname=172.31.22.79 # Hostname or IP address # (additional mysqld connections can be # specified for this node for various # purposes such as running ndb_restore) Any suggestions?

Best Regards, Sri

-----Original Message----- From: Matthew Montgomery [mailto:mmon...@mysql.com] Sent: Thursday, May 17, 2007 10:46 AM To: Tangirala, Srikalyan Cc: cluster Subject: RE: Issues with few tables while importing database withenginetype NDBCLUSTER

On Thu, 2007-05-17 at 09:15 -0400, Tangirala, Srikalyan wrote:

Hi Mathew: First of all thank you for the well needed information. Well, the problem still persists even after setting up the Data memory of 512 M and Index Memory of 256 M. The errors shown are as follows:

mysql> CREATE TABLE `user_site_list_email_options` ( -> `user_id` varchar(12) NOT NULL default '', -> `stid` varchar(10) NOT NULL default '', -> `email_receiving_options` set('Site Visit','SV: DataLogger Info Not Correct','TT Problem','CTT Problem') default NULL, -> PRIMARY KEY (`user_id`,`stid`), -> KEY `user_id` (`user_id`), -> KEY `site` (`stid`), -> CONSTRAINT `user_site_list_email_options_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`name`), -> CONSTRAINT `user_site_list_email_options_ibfk_2` FOREIGN KEY (`stid`) REFERENCES `site` (`stid`) -> ) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1; ERROR 1005 (HY000): Can't create table 'coopdb_op.user_site_list_email_options' (errno: 136) mysql> mysql> SHOW ERRORS;

+-------+------+-------------------------------------------------------------

----------------------+ | Level | Code | Message |

+-------+------+-------------------------------------------------------------

----------------------+ | Error | 1296 | Got error 904 'Out of fragment records (increase MaxNoOfOrderedIndexes)' from NDB | | Error | 1005 | Can't create table 'coopdb_op.user_site_list_email_options' (errno: 136) |

+-------+------+-------------------------------------------------------------

----------------------+ 2 rows in set (0.00 sec)

Like I suspected, the first error message is more telling. (increase MaxNoOfOrderedIndexes)'

Cluster pre-allocates some memory for Ordered Index object entries. The error actually indicates that it's not a problem with the IndexMemory size. There simply were not enough of these allocations. The default value is 128.

See:

http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-ndbd-definition.html#mys

ql-cluster-param-ndbd-definition-maxnooforderedindexes

You should try running the ndb_size.pl script against the InnoDB version of the database schema to find out what minimum values are suggested these and other options.

I understand that the first error is a Insufficient memory issue. But, how much should I set it up is the main issue. I know that I might sound silly asking you this question but again I tried all the different combinations with Datamemory and Index memory. As I mentioned earlier in the previous email that I'm using the basic cluster configuration and each node is allocated 3/4th GB of memory. Any Suggestions my friend????

Best, Sri

-----Original Message----- From: Matthew Montgomery [mailto:mmon...@mysql.com] Sent: Thursday, May 17, 2007 12:30 AM To: Tangirala, Srikalyan Cc: cluster Subject: Re: Issues with few tables while importing database with enginetype NDBCLUSTER

Hello Srikalyan,

On Wed, 2007-05-16 at 16:18 -0400, Tangirala, Srikalyan wrote:

Hi Group, I have an issue with the importing the database into the cluster environment. We have four nodes with one MGM, one SQL server, two data nodes. Now, I am trying to import the database which has an engine type as InnoDB. So as we know there are two ways for it to be replicated in Cluster environment. Either modify the tables before importing or Alter table later on for the tables in the database. I tried both the mechanisms while importing the database. 1. First if I modify and then import the database with engine = NDBCLUSTER, some of the tables are dropped as they have error saying that

ERROR 1005 (HY000): Can't create table 'coopdb_op.user_site_list_email_options' (errno: 136)

MySQL error code 136: No more room in index file.

Also after you see this error generated run SHOW ERRORS. There may be multiple error messages returned however only the last (mysqld) error code is presented in the mysql command line client. These additional messages may be more specific in terms of what is going on inside the cluster.

Check that you have set a sufficiently large value for IndexMemory. You can cause the NDBD nodes to print out their IndexMemory and DataMemory usage to the cluster log file. This is done using the "ALL DUMP 1000" command within the ndb_mgm interface.

ndb_mgm> ALL DUMP 1000 Sending dump signal with data: 0x000003e8 Sending dump signal with data: 0x000003e8

You should find a block of text like this in your node_#_cluster.log

-- Node 2: Data usage is 5%(1290 32K pages of total 25600) -- Node 2: Index usage is 5%(381 8K pages of total 6688) -- Node 2: Resource 0 min: 0 max: 639 curr: 0 -- Node 3: Data usage is 5%(1290 32K pages of total 25600) -- Node 3: Index usage is 5%(381 8K pages of total 6688) -- Node 3: Resource 0 min: 0 max: 639 curr: 0

I am able to execute this CREATE table code against my test cluster without any errors. Please note the CONSTRAINT clauses are ignored resulting in the following schema.

mysql> show create table user_site_list_email_options\G *************************** 1. row *************************** Table: user_site_list_email_options Create Table: CREATE TABLE `user_site_list_email_options` ( `user_id` varchar(12) NOT NULL default '', `stid` varchar(10) NOT NULL default '', `email_receiving_options` set('Site Visit','SV: DataLogger Info Not Correct','TT Problem','CTT Problem') default NULL, PRIMARY KEY (`user_id`,`stid`), KEY `user_id` (`user_id`), KEY `site` (`stid`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

I know that the issue is with the foreign key. But, there are many foreign key that are referred before that, which would not cause any problem

but

only

the few tables create these problems.

2. If I create it as it is with engine type InnoDB, it would import all the tables in the database but I cannot Alter the table.

Let me attach the create statement of the table also, for further reference. Please let me know if you have any suggestions?

CREATE TABLE `user_site_list_email_options` ( -> `user_id` varchar(12) NOT NULL default '', -> `stid` varchar(10) NOT NULL default '', -> `email_receiving_options` set('Site Visit','SV: DataLogger

Info

Not

Correct','TT Problem','CTT Problem') default NULL, -> PRIMARY KEY (`user_id`,`stid`), -> KEY `user_id` (`user_id`), -> KEY `site` (`stid`), -> CONSTRAINT `user_site_list_email_options_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`name`), -> CONSTRAINT `user_site_list_email_options_ibfk_2` FOREIGN KEY (`stid`) REFERENCES `site` (`stid`) -> ) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1;

Best, Sri

-----Original Message----- From: Mikael Ronström [mailto:mik...@mysql.com] Sent: Wednesday, May 16, 2007 4:07 PM To: Sebastien LELIEVRE Cc: cluster Subject: Re: Performance White Papers on MySQL Cluster

Hi,

16 maj 2007 kl. 17.46 skrev Sebastien LELIEVRE:

Hi again,

I performed some tests agin, and still got the error.

In fact, I got an error 157 and, after restarting the SQL Node, an error 136

This time, the warning was clear enough : my MaxNoOfOrderedIndexes (default 128) was not enough.

I've just added the line below to my [NDBD] sections, and it seems to work just fine : MaxNoOfOrderedIndexes = 1024

Hmm, interesting, ok good that it works for you now.

Though, the mysql_load_db.sh still have some glitches. For instance, it specifies -p $DB_PASSWORD to the MySQL command where it should be -p$DB_PASSWORD (no space)

Ok, will fix that in the next version.

During Data injection, the process froze :

Node 4: Event buffer status: used=256B(100%) alloc=256B(0%) max=0B apply_gci=212554 latest_gci=217644

I solved this by modifying some TCP values in the config.ini :

[TCP DEFAULT] SendBufferMemory=1024K ReceiveBufferMemory=256K

Yep, have had occasional similar problems, personally even set it to 2048K.

Once I've done this, all the script has been fine.

Do not forget to specify your password (and user, if you do not want to log in as 'root'). You can do it whether in the file or in given parameters.

Sounds reasonable if it's not a pure benchmark environment.

Before using the run_mysql.sh script, be sure that the (MySQL) client path defined in it is the good one.

Yep, I usually had to set LD_LIBRARY_PATH. Personally I have automated the entire DBT2 runs by a number of scripts that you can find in the dis_trunk download. The run_dbt2_test.sh will by use of a set of scripts start the cluster, start the MySQL Servers, create the tables, create the stored procedures, load the data and finally run a set of predefined test cases.

I did not manage to launch the script without specifying a socket.

I ran this command : sh run_mysql.sh --user dbt2 --connections 20 --time 300 --warehouses 3 --socket /var/lib/mysql/mysql.sock

The dbt2.sh I use set things up using ports and ip addresses (See dis_trunk scripts) This is necessary to be able to run clients from one server where MySQL Servers are on several machines.

I've run those test for 5 mins (300 secs)

I wasn't able to retrieve the Response Time (s) Average during those tests. I do not not know if it has to be enabled or so.

that is in the output file of the test if I haven't completely forgotten.

Anyway, Great thanks to iCauldron, Dolphinics, and the Linux Foundation for providing such tools !

iClaustron :) Great to see that it is of use

Rgrds Mikael

Thank you a lot, Johan, for providing me those hints which helped me solve this case!

I hope all those quotes will help some other than myself :)

Cheers,

Hi,

Here is the result :

I've also looked at the disk fre space on data nodes, and I saw that node 3 got a directory called ndb_2_fs/ that was 1.9GB weight.

Node 3 was completely out of free disk space. I've removed this directory (that is useless since it's ndb_3_fs/ that concerns us here)

I'm going to give the dbt2 create script another try.

Cheers,

Johan Andersson a écrit :

Hi,

can you do ndb_mgm > all dump 1000 It will print out the datamemory/indexmemory that is used by the data nodes in the cluster log (written in the datadir of the management server, ndb_mgmd).

That will print out something like this in the cluster log: 2007-05-16 12:14:44 [MgmSrvr] INFO -- Node 2: Data usage is 46%(32735 32K pages of total 70400) 2007-05-16 12:14:44 [MgmSrvr] INFO -- Node 2: Index usage is 15%(9668 8K pages of total 64032) 2007-05-16 12:14:44 [MgmSrvr] INFO -- Node 2: Resource 0 min: 37665 max: 71039 curr: 32736 2007-05-16 12:14:44 [MgmSrvr] INFO -- Node 2: Resource 1 min: 0 max: 0 curr: 1 2007-05-16 12:14:44 [MgmSrvr] INFO -- Node 2: Resource 3 min: 70400 max: 70400 curr: 32735 2007-05-16 12:14:44 [MgmSrvr] INFO -- Node 3: Data usage is 46%(32715 32K pages of total 70400) 2007-05-16 12:14:44 [MgmSrvr] INFO -- Node 3: Index usage is 15%(9668 8K pages of total 64032) 2007-05-16 12:14:44 [MgmSrvr] INFO -- Node 3: Resource 0 min: 37685 max: 71039 curr: 32716 2007-05-16 12:14:44 [MgmSrvr] INFO -- Node 3: Resource 1 min: 0 max: 0 curr: 1 2007-05-16 12:14:44 [MgmSrvr] INFO -- Node 3: Resource 3 min: 70400 max: 70400 curr: 32715

Can you please send what you get!

B.r, johan

Sebastien LELIEVRE wrote:

Hi Johan

Johan Andersson a écrit :

Hi Sebastien,

Can you please send you config.ini?

Here it is :

[NDBD DEFAULT] NoOfReplicas=2 DataMemory=800M IndexMemory=52M

[MYSQLD DEFAULT]

[NDB_MGMD DEFAULT]

[TCP DEFAULT]

# Managment Server [NDB_MGMD] HostName=192.168.28.5

# Storage Engines [NDBD] HostName=192.168.28.6 DataDir= /var/lib/mysql-cluster MaxNoOfAttributes = 5000

[NDBD] HostName=192.168.28.7 DataDir=/var/lib/mysql-cluster MaxNoOfAttributes = 5000

# 1 MySQL Client [MYSQLD]

Which version of MySQL Cluster are you using?

5.1.17

Do you have any other data in the database?

Yes, I have an application called OBM (http://obm.aliacom.fr/ news.php) running for test purposes (ENGINE used is NDBCLUSTER, too)

B.R, johan andersson

Cheers,

Sebastien LELIEVRE wrote:

Hi,

I am trying to use this testing utility in order to retrieve my solution performance over our current architecture.

I have compiled the DBT2 sources with the MySQL support, but it seems that the whole Database (dbt2) can't be deployed on the Cluster.

By looking into the script code, I have seen that some tables are using special keys feature.

For instance :

CREATE TABLE customer ( c_id int(11) NOT NULL default '0', c_d_id int(11) NOT NULL default '0', c_w_id int(11) NOT NULL default '0', c_first varchar(16) default NULL, c_middle char(2) default NULL, c_last varchar(16) default NULL, c_street_1 varchar(20) default NULL, c_street_2 varchar(20) default NULL, c_city varchar(20) default NULL, c_state char(2) default NULL, c_zip varchar(9) default NULL, c_phone varchar(16) default NULL, c_since timestamp NOT NULL, c_credit char(2) default NULL, c_credit_lim decimal(24,12) default NULL, c_discount double default NULL, c_balance decimal(24,12) default NULL, c_ytd_payment decimal(24,12) default NULL, c_payment_cnt double default NULL, c_delivery_cnt double default NULL, c_data varchar(500), PRIMARY KEY (c_w_id,c_d_id,c_id), KEY c_w_id (c_w_id,c_d_id,c_last,c_first) );

... can't be created nor migrated with the NDBCLUSTER Engine.

(There are some other tables with this structure)

Error returned in N°136

My NDBD Default values are set to 800MB for data and 52MB for Indexes.

I am using the http://www.iclaustron.com/dbt2-0.37.12.tar.gz and even tried with the 0.40 available on SourceForge

I also downloaded the dis_trunk scripts from iCauldron but they seems to be useful after the Database is intalled

I may be able to provide some other information if you need to

I must be missing something, sorry :S

Mikael Ronström a écrit :

Hi, I've been working on some very interesting benchmarking using the DBT2 test suite developed by OSDL for MySQL Cluster. As part of this work I've made significant additions to the DBT2 test to enable clustered test runs. I've also developed a set of scripts to enable easy start and stop of MySQL Cluster processes.

The benchmarks include comparisons of various connect methods using Ethernet and Dolphin Express cards. It also discusses improvements using the latest version of the Intel Core2 architecture.

As part of this work I discovered a couple of essential performance optimisations and scalability optimisations. All these improvements are currently being integrated in MySQL Cluster Carrier Grade Edition. To enable those wanting "bleeding edge"-access I've also made the benchmark version available on www.iclaustron.com

A short white paper and the full white paper can be downloaded from www.dolphinics.com and a MySQL-focused version of the white paper can be downloaded from www.mysql.com. www.iclaustron.com contains the exact links for all material available from various places.

The full white paper contains also recommendations of HW architectures to use for optimal MySQL Cluster performance and scalability.

Rgrds Mikael