List:Cluster« Previous MessageNext Message »
From:Karl Kloppenborg Date:October 1 2010 1:27am
Subject:Re: MySQL Cluster -- Table Full
View as plain text  
Hi guys,

In light of the continual problems we face, Aaron will be taking over this so I can
continue testing...

Thanks for helping guys!

Cheers,
Karl Kloppenborg.


On 30/09/2010, at 17:31, Johan Andersson wrote:

> 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=1
>>>>>>>>> 
>>>>>>>>>                           
>>>>> --
>>>>> MySQL Cluster Mailing List
>>>>> For list archives: http://lists.mysql.com/cluster
>>>>> To unsubscribe:    http://lists.mysql.com/cluster?unsub=1
>>>>> 
>>>>>           
>>>>       
>> 
>> 
>> --
>> MySQL Cluster Mailing List
>> For list archives: http://lists.mysql.com/cluster
>> To unsubscribe:    http://lists.mysql.com/cluster?unsub=1
>> 
>>  
> 

Thread
MySQL Cluster -- Table FullKarl Kloppenborg30 Sep
  • Re: MySQL Cluster -- Table FullAdam Dixon30 Sep
    • Re: MySQL Cluster -- Table FullKarl Kloppenborg30 Sep
  • Re: MySQL Cluster -- Table FullJonas Oreland30 Sep
    • Re: MySQL Cluster -- Table FullKarl Kloppenborg30 Sep
      • Re: MySQL Cluster -- Table FullJonas Oreland30 Sep
        • Re: MySQL Cluster -- Table FullKarl Kloppenborg30 Sep
          • Re: MySQL Cluster -- Table FullJonas Oreland30 Sep
            • Re: MySQL Cluster -- Table FullJohan Andersson30 Sep
              • Re: MySQL Cluster -- Table FullKarl Kloppenborg30 Sep
                • Re: MySQL Cluster -- Table FullJohan Andersson30 Sep
                  • Re: MySQL Cluster -- Table FullKarl Kloppenborg1 Oct
                    • Re: MySQL Cluster -- Table FullAaron Weller // Crucial Paradigm1 Oct
                      • Re: MySQL Cluster -- Table FullJohan Andersson1 Oct
                        • Re: MySQL Cluster -- Table FullAaron Weller // Crucial Paradigm7 Oct
                      • Re: MySQL Cluster -- Table FullJohan Andersson1 Oct
                      • Re: MySQL Cluster -- Table FullJon Stephens1 Oct
  • Re: MySQL Cluster -- Table FullMoses28 Apr
    • Re: MySQL Cluster -- Table FullJohan Andersson28 Apr
  • Re: MySQL Cluster -- Table FullMoses6 May
Re: MySQL Cluster -- Table FullAaron Weller // Crucial Paradigm1 Oct
  • Re: MySQL Cluster -- Table FullJohan Andersson1 Oct