Re: Question about communication between mysqld and ndbd

[Frazer Clement]

Hi Lukas,
  NDB splits your tables into fragments, and replicates the fragments 
across the 2 nodes in your nodegroup.  For NoOfReplicas=2, each table 
fragment has 1 primary replica and 1 backup replica on different nodes.
  When both nodes are up, some fragments are primary on each NDBD node.  
Ndb balances the assignment of primary replicas across nodes to balance 
the load (Hosting a primary replica takes more work than hosting a 
backup replica).
  When only one node is up, all fragments are primary on it.
  To process queries on the table, NDB generally has to access the 
primary replica of each fragment that is read, to obtain locks to ensure 
consistency.
  When both nodes are up, this *will* involve communication to both nodes.
  When only one node is up, only one node is involved.

  I believe that this is what causes the timings you report.  e.g. even 
though you connect to the local NDBD, and it has a *copy* of all the 
data, the other NDBD is still involved in query processing to ensure we 
get a consistent view of data that may be getting concurrently updated.
Frazer


Lukas Pecha wrote:
> Okay,
> I tried to replicate the problem on some directly connected computers
> (directly connected gigabit lan, Core 2 Duo, two gigs of RAM, one
> subnet, same configuration as posted previously, opensuse 11, x64,
> minimal installation, no other applications running, no other network
> devices involved). I used MySQL cluster 6.3.17 and then 7.0.6. It's
> still slow, even when I try to simplify the configuration to the bare
> minimum.
>
> The queries on table with 20000 rows, which I am interested in, costs
> still about 0,9 sec and when I double the table to have 50000 rows, the
> time is doubled too (1,8 sec). It's better than 15 seconds, but still
> not good. It also seems, that indexes are not functioning.
> I was trying to replicate test, which is described here:
> http://bugs.mysql.com/bug.php?id=44760 and my query time is about
> four-times bigger than here - one distinct() cost me 0,5 seconds and not
> 0,1.
>
> I hope, that there is some explanation for this.
> Thanks in advance,
> Lukas Pecha
>
> Jonathan "Jeb" Miller napsal(a):
>   
>> Seems like you need to have a cluster at each location, and then use
>> Geographical Replication in a circular model between them.
>>
>> Having a cluster separated in the manner you speak is not supported.
>>
>> Best wishes,
>> /Jeb
>>
>> Lukas Pecha wrote:
>>     
>>> Well, those machines are not at one location, they are separated by a
>>> few kilometers. There is only 100Mbps connection between them, gigabit
>>> is not possible. They are also on different subnets. But I need to have
>>> two applications to share the same database data and to have
>>> simultanneous read/write access, so I thought, that since the database
>>> shouldn't grow bigger than a few megabytes (about twenty megs, I think,
>>> and most of the tables have only tens of rows, few hundreds max) in the
>>> future, that this connection will suffice. Link is stable and round trip
>>> time is about 7 ms. MySQL cluster also helps me to achieve the high
>>> availability goal - database will still be up even if one location
>>> completely burns out.
>>>
>>> Here are my configuration files for cluster and mysqld: (Maybe I should
>>> lower the numbers of allocated memory, I just picked them randomly)
>>> And, I have to add - I have also two management nodes, running on the
>>> same two nodes as ndbd and mysqld. Everything seems to run smoothly, it
>>> even passed some "tearing off the cable/power supply" tests. It just has
>>> such a long query responses.
>>>
>>> Thanks again in advance for any advice
>>>
>>> Lukas Pecha
>>>
>>> -----------------config.ini------------
>>> [ndbd default]
>>> NoOfReplicas=2
>>> DataMemory=512M
>>> IndexMemory=192M
>>>
>>> [tcp default]
>>> portnumber=2202 [ndb_mgmd]
>>> Id=1
>>> ArbitrationRank=0
>>> hostname=192.168.1.10
>>> datadir=/mysql-mgm
>>> [ndb_mgmd]
>>> Id=2
>>> ArbitrationRank=0
>>> hostname=192.168.1.140
>>> datadir=/mysql-mgm
>>> # SQL node options:
>>> [ndbd]
>>> Id=3
>>> DataDir=/mysql-data
>>> HostName=192.168.1.10
>>> BackupDataDir=/mysql-backup
>>> HeartbeatIntervalDbDb=8000
>>> HeartbeatIntervalDbApi=8000
>>> [ndbd]
>>> Id=4
>>> DataDir=/mysql-data
>>> HostName=192.168.1.140
>>> BackupDataDir=/mysql-backup
>>> HeartbeatIntervalDbDb=8000
>>> HeartbeatIntervalDbApi=8000
>>> [mysqld]
>>> Id=5
>>> ArbitrationRank=0
>>> HostName=192.168.1.10
>>> [mysqld]
>>> Id=6
>>> ArbitrationRank=0
>>> HostName=192.168.1.140
>>>
>>> ----------------my.cnf-------------
>>> [mysqld]
>>> ndbcluster
>>> ndb-connectstring="192.168.1.10;192.168.1.140"
>>> default-table-type=NDBCLUSTER
>>> [mysql_cluster]
>>> ndb-connectstring="192.168.1.10;192.168.1.140"
>>>
>>> Jeff Sturm napsal(a):
>>>  
>>>       
>>>> Lukas,
>>>>
>>>> 15 seconds is a substantial performance hit.  I don't have a direct
>>>> answer to your question, but I wonder how these two machines are
>>>> networked--do they share a subnet?  Are you using a 1Gbps Ethernet?
>>>>
>>>> With only two machines, I would also experiment with a crossover cable.
>>>>
>>>>      
>>>>         
>>>>> -----Original Message-----
>>>>> From: Lukas Pecha [mailto:pecha@stripped]
>>>>> Sent: Friday, June 26, 2009 7:35 AM
>>>>> To: cluster@stripped
>>>>> Subject: Question about communication between mysqld and ndbd
>>>>>
>>>>> Hi,
>>>>> I set up a small mysql-cluster configuration with two ndbd/API
>>>>>           
>>>>>           
>>>> machines
>>>>      
>>>>         
>>>>> - so there is ndbd and mysqld running on both of them. But I am not
>>>>>           
>>>>>           
>>>> able to find a
>>>>      
>>>>         
>>>>> way how to change a way the mysqld process communicates with ndbd -
> it
>>>>>           
>>>>>           
>>>> seems,
>>>>      
>>>>         
>>>>> that the machines are sending request to remote ndbd (not local), so
>>>>>           
>>>>>           
>>>> even not much
>>>>      
>>>>         
>>>>> complex queries take a long time (one simple query with "order by"
>>>>>           
>>>>>           
>>>> statement takes
>>>>      
>>>>         
>>>>> about 15 second and the table has only about twenty thousand rows).
>>>>>           
>>>>>           
>>>> When I kill one
>>>>      
>>>>         
>>>>> of the ndbd processes, the query takes only 0,2 sec on the machine
>>>>>           
>>>>>           
>>>> with both ndbd
>>>>      
>>>>         
>>>>> and mysqld running and still 15 seconds on the other machine. When I
>>>>>           
>>>>>           
>>>> start it up again,
>>>>      
>>>>         
>>>>> both machines have again the response time about 15 seconds.
>>>>>
>>>>> So my question is - is there a way, how to tell mysqld, that it
> should
>>>>>           
>>>>>           
>>>> use only local ndb
>>>>      
>>>>         
>>>>> daemon for querying? Or will this issue be resolved, if I start
> using
>>>>>           
>>>>>           
>>>> indexing
>>>>      
>>>>         
>>>>> everywhere I could?
>>>>>
>>>>> Thanks in advance
>>>>> Lukas Pecha
>>>>>           
>>>>>           
>>>>       
>>>>         
>>>   
>>>       
>
>   

-- 
Frazer Clement, Software Engineer, MySQL Cluster 
Sun Microsystems - www.mysql.com
Office: Edinburgh, UK

Are you MySQL certified?  www.mysql.com/certification