From: Johan Andersson Date: September 30 2010 7:31am Subject: Re: MySQL Cluster -- Table Full List-Archive: http://lists.mysql.com/cluster/7795 Message-Id: <4CA43CC6.3060504@oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hi, You are running out of extents --> add data file Then if you are going to load in a lot of records (>100M) you need to add max_rows too. max_rows will underneath make the data nodes to create more fragments. With many rows, more fragments than the default are needed to store the primary key hash index. BR johan Karl Kloppenborg wrote: > Hi Johan, > > Can you please explain that? > > I am a bit confused.... > > Cheers, > Karl. > On 30/09/2010, at 16:51, Johan Andersson wrote: > > >> Karl, >> Just to summarize: >> - You must do ALTER TS ADD DATAFILE _and_ set max_rows. >> >> BR >> johan >> >> Jonas Oreland wrote: >> >>> On 09/30/10 07:27, Karl Kloppenborg wrote: >>> >>> >>>> Hey Jonas, >>>> >>>> Thanks for the reply, we will try implement the Max_rows after the rebuild takes place, >>>> However I have a few questions: >>>> >>>> 1) after doing some writes and getting the table full I executed the show warnings: >>>> >>>> +-------+------+--------------------------------------------------------+ >>>> | Level | Code | Message | >>>> +-------+------+--------------------------------------------------------+ >>>> | Error | 1296 | Got error 1601 'Out extents, tablespace full' from NDB | >>>> | Error | 1114 | The table 'my_ndb_awesome_large_table' is full | >>>> +-------+------+--------------------------------------------------------+ >>>> 2 rows in set (0.00 sec) >>>> >>>> >>> 1601 means data-on-disk if i'm not mistaken. >>> >>> ndbd's tablespaces doesn't auto grow. >>> "alter tablespace X add datafile Y initial_size=10G" or something should do it. >>> >>> /Jonas >>> >>> >>> >>>> Could you explain this? >>>> >>>> 2) I will try implementing max_rows.. >>>> >>>> 3) we are not using ndbmtd but each server has 16 cores / 144GB should we? >>>> >>>> Thanks! >>>> Karl Kloppenborg. >>>> >>>> On 30/09/2010, at 15:19, Jonas Oreland wrote: >>>> >>>> >>>> >>>>> On 09/30/10 07:13, Karl Kloppenborg wrote: >>>>> >>>>> >>>>>> Hi guys, >>>>>> >>>>>> We initially didn't but we got this problem at 92 million rows, >>>>>> >>>>>> After a lot of research we found a post that stated maybe increasing the max rows would help, however after reading more on max_rows with NDB it was found that it is not used by NDBcluster engine and ignored? is this true? >>>>>> >>>>>> >>>>> 1) after you get "table full", issue "show warnings", this will show you exact error code >>>>> >>>>> 2) maxrows *should* help >>>>> >>>>> 3) are you using ndbmtd ? >>>>> >>>>> /Jonas >>>>> >>>>> >>>>> >>>>>> However take note, that we require that it hold 600 million rows... challenge.. >>>>>> >>>>>> I will also add my create table syntax to show you what we're doing. >>>>>> >>>>>> CREATE TABLE `my_ndb_awesome_large_table ` ( >>>>>> `user_id` int(4) NOT NULL, >>>>>> `description` varchar(50) NOT NULL, >>>>>> `type` varchar(64) NOT NULL, >>>>>> `count` int(4) NOT NULL, >>>>>> `after` int(3) NOT NULL, >>>>>> `active` int(1) NOT NULL, >>>>>> `lastactivity` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP >>>>>> ) ENGINE=ndbcluster DEFAULT CHARSET=utf8 >>>>>> >>>>>> Any thoughts on what this virtual "level" might be? because as you can see in my last email have not run out of index / data space? >>>>>> >>>>>> >>>>>> On 30/09/2010, at 14:59, Jonas Oreland wrote: >>>>>> >>>>>> >>>>>> >>>>>>> Are you using "maxrows" in your table definition ? >>>>>>> >>>>>>> /Jonas >>>>>>> >>>>>>> On 09/30/10 06:15, Karl Kloppenborg wrote: >>>>>>> >>>>>>> >>>>>>>> Hi, >>>>>>>> We have setup a MySQL cluster (pretty standard two NDB nodes + two management servers) >>>>>>>> 2x cluster processing nodes (running the MySQL NDB daemon + MySQL server) >>>>>>>> - 144GB ram >>>>>>>> - 8x 300GB SAS - Raid 10 - Data-Storage = 135GB Ram - Index-Storage = 5GB RAM >>>>>>>> However at 92Million rows in a table, it is returning the TableFull error? >>>>>>>> >>>>>>>> My config is as follows: >>>>>>>> [NDBD DEFAULT] >>>>>>>> NoOfReplicas=2 >>>>>>>> LockPagesInMainMemory=1 >>>>>>>> >>>>>>>> DataMemory=131G >>>>>>>> IndexMemory=10G >>>>>>>> >>>>>>>> TimeBetweenLocalCheckpoints=6 >>>>>>>> NoOfFragmentLogFiles=500 >>>>>>>> >>>>>>>> >>>>>>>> [MYSQLD DEFAULT] >>>>>>>> >>>>>>>> [NDB_MGMD DEFAULT] >>>>>>>> >>>>>>>> [TCP DEFAULT] >>>>>>>> SendBufferMemory=8M >>>>>>>> ReceiveBufferMemory=8M >>>>>>>> >>>>>>>> # Section for the cluster management node >>>>>>>> [NDB_MGMD] >>>>>>>> ID=1 #LB1 ID is 1 >>>>>>>> Datadir=/var/lib/mysql-cluster >>>>>>>> HostName=#.#.#.# #PRIVATE IP OF LB1 >>>>>>>> >>>>>>>> [NDB_MGMD] >>>>>>>> HostName=#.#.#.# #PRIVATE IP OF LB2 >>>>>>>> ID=2 #ID of LB2 is 2 >>>>>>>> Datadir=/var/lib/mysql-cluster >>>>>>>> >>>>>>>> # Section for the storage nodes >>>>>>>> [NDBD] >>>>>>>> # IP address of the first storage node >>>>>>>> HostName=#.#.#.# # PRIVATE IP OF DB1 >>>>>>>> DataDir=/var/lib/mysql-cluster >>>>>>>> >>>>>>>> [NDBD] >>>>>>>> # IP address of the second storage node >>>>>>>> HostName=#.#.#.# #PRIVATE IP OF DB2 >>>>>>>> DataDir=/var/lib/mysql-cluster >>>>>>>> >>>>>>>> # one [MYSQLD] per storage node >>>>>>>> [MYSQLD] >>>>>>>> [MYSQLD] >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> Can anyone please shed some light on this matter? >>>>>>>> >>>>>>>> Cheers, >>>>>>>> Karl Kloppenborg -- >>>>>>>> MySQL Cluster Mailing List >>>>>>>> For list archives: http://lists.mysql.com/cluster >>>>>>>> To unsubscribe: http://lists.mysql.com/cluster?unsub=jonas@stripped >>>>>>>> >>>>>>>> >>>>>>>> >>>> -- >>>> MySQL Cluster Mailing List >>>> For list archives: http://lists.mysql.com/cluster >>>> To unsubscribe: http://lists.mysql.com/cluster?unsub=jonas@stripped >>>> >>>> >>>> >>> >>> > > > -- > MySQL Cluster Mailing List > For list archives: http://lists.mysql.com/cluster > To unsubscribe: http://lists.mysql.com/cluster?unsub=johan@stripped > >