Re: Question about communication between mysqld and ndbd

[Lukas Pecha]

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
>>>>           
>>>
>>>       
>>
>>   
>