What are the parameters that are needed to be monitored for MYSQL Cluster

Question

I am new to MySQL cluster. I had installed MySQL cluster successfully with 2 data nodes and 1 Management Node and 1 SQL node.

I want to know where and how exactly the core data is getting stored. Whether data is storing in data nodes or SQL node and data is sharing from SQL node.

Also when I searched in internet, it is mentioned like Data is storing in Datamemory & Indexmemory nothing but RAM. Obviously data will be huge around 100 of GB's, then at that time, how does MySQL cluster will support.

Also when I am trying to access(Insert) more than 32768 rows, it is throwing error like "ERROR 1297 (HY000): Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDBCLUSTER"

For this I had changed the config.ini file and set MaxNoOfConcurrentOperations=65536 and restarted all nodes, but still the same issue is coming.

Also I would like to know which and all parameters to be monitored for MySQL cluster. Also any open source tools to monitor MySQL cluster.

Thanks in Advance.

Answer 1

Here are some queries that I uses to Monitor Cluster nodes.

MySQL Cluster % of Free Data Node

select 
    @total_data_memory := 
    (select 
        SUM(total) Total_Memory 
    from
        ndbinfo.memoryusage 
    where memory_type = 'Data memory') as `Total_Data_Memory`,
    @used_data_memory := 
    (select 
        SUM(used) Used_Memory 
    from
        ndbinfo.memoryusage 
    where memory_type = 'Data memory') as `Used_Data_Memory`,
    (
        @total_data_memory - @used_data_memory
    ) / (@total_data_memory) * 100 as `Percentage_Of_Free_Data_Memory` ;

MySQL Cluster Number of Nodes not running

select 
    @total := 
    (select 
        VARIABLE_VALUE 
    from
        information_schema.`GLOBAL_STATUS` 
    where VARIABLE_NAME = 'NDB_NUMBER_OF_DATA_NODES') as `total`,
    @running := 
    (select 
        VARIABLE_VALUE 
    from
        information_schema.`GLOBAL_STATUS` 
    where VARIABLE_NAME = 'NDB_NUMBER_OF_READY_DATA_NODES') as `running`,
    @total - @running as `nodes_not_running` ;

Similarly you can obtain REDO/UNDO logbuffer/logspace information from here: http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-ndbinfo.html

I have used a tool called MONyog which monitors custom queries and alerts in case of failure.