Description:
`all report memoryusage` output.
ndb_mgm> all report memoryusage;
Node 1: Data usage is 94%(186653 32K pages of total 196608)
Node 1: Index usage is 35%(46391 8K pages of total 131264)
Node 2: Data usage is 94%(186653 32K pages of total 196608)
Node 2: Index usage is 35%(46391 8K pages of total 131264)
Node 3: Data usage is 95%(186778 32K pages of total 196608)
Node 3: Index usage is 35%(46468 8K pages of total 131264)
Node 4: Data usage is 95%(186778 32K pages of total 196608)
Node 4: Index usage is 35%(46468 8K pages of total 131264)
ndb_mgm>
But, insert failed and response `The table 'tablename' is full`.
$ echo 'insert into sbtest1 values (3005295, 3005296, '93054071097-47801313505-35806879014-90352758077-67480521979-97543598358-85289011509-67786845790-70623610686-89993829268', '77795562155-79924046329-39210320290-40668007848-84482400384');' | /usr/local/mysql/bin/mysql sbtest
ERROR 1114 (HY000) at line 1: The table 'sbtest1' is full
$
How to repeat:
Use sysbench(Version 0.5) and load data.
=========
/usr/local/sysbench/bin/sysbench \
--test=/usr/local/sysbench/lua/parallel_prepare.lua \
--db-driver=mysql \
--oltp-tables-count=60 \
--oltp-table-size=3000000 \
--num-threads=4 \
--mysql_table_engine=ndbcluster \
--mysql-db=sbtest \
--mysql-user=sbtest \
--mysql-password=password \
--mysql-socket=/tmp/mysql.sock \
run
=========
loading data, sysbench reports error.
--
ALERT: Error 1114 The table 'sbtest19' is full
FATAL: failed to execute function `thread_init': /usr/local/sysbench/lua/common.lua:83: db_bulk_insert_next() failed
ALERT: Error 1114 The table 'sbtest20' is full
FATAL: failed to execute function `thread_init': /usr/local/sysbench/lua/common.lua:83: db_bulk_insert_next() failed
--
I continue insert(only sbtest1 table) and then occurs error.
--
$ echo 'insert into sbtest1 values (3005295, 3005296, '93054071097-47801313505-35806879014-90352758077-67480521979-97543598358-85289011509-67786845790-70623610686-89993829268', '77795562155-79924046329-39210320290-40668007848-84482400384');' | /usr/local/mysql/bin/mysql sbtest
ERROR 1114 (HY000) at line 1: The table 'sbtest1' is full
$
--
But, below query is ok.
--
$ echo 'insert into sbtest1 values (3005296, 3005296, '93054071097-47801313505-35806879014-90352758
077-67480521979-97543598358-85289011509-67786845790-70623610686-89993829268', '77795562155-79924046329-39210320290-
40668007848-84482400384');' | /usr/local/mysql/bin/mysql sbtest
$
--
so i think particular fragment is full.
mysql> select fq_name, node_id, fragment_num, fixed_elem_alloc_bytes, fixed_elem_free_bytes from ndbinfo.memory_per_fragment where fq_name like 'sbtest%' and fq_name like 'sbtest/def/sbtest1' and fixed_elem_free_bytes = 0 order by fq_name, node_id;
+--------------------+---------+--------------+------------------------+-----------------------+
| fq_name | node_id | fragment_num | fixed_elem_alloc_bytes | fixed_elem_free_bytes |
+--------------------+---------+--------------+------------------------+-----------------------+
| sbtest/def/sbtest1 | 3 | 21 | 26902528 | 0 |
| sbtest/def/sbtest1 | 4 | 21 | 26902528 | 0 |
+--------------------+---------+--------------+------------------------+-----------------------+
2 rows in set (0.30 sec)
mysql>
* Environment(test servers)
ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)] 4 node(s)
id=1 @192.168.1.100 (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0, *)
id=2 @192.168.1.101 (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0)
id=3 @192.168.1.110 (mysql-5.6.28 ndb-7.4.10, Nodegroup: 1)
id=4 @192.168.1.111 (mysql-5.6.28 ndb-7.4.10, Nodegroup: 1)
[ndb_mgmd(MGM)] 1 node(s)
id=10 @192.168.1.1 (mysql-5.6.28 ndb-7.4.10)
[mysqld(API)] 30 node(s)
id=51 @192.168.1.10 (mysql-5.6.28 ndb-7.4.10)
id=52 @192.168.1.11 (mysql-5.6.28 ndb-7.4.10)
id=53 @192.168.1.12 (mysql-5.6.28 ndb-7.4.10)
id=54 @192.168.1.10 (mysql-5.6.28 ndb-7.4.10)
id=55 @192.168.1.11 (mysql-5.6.28 ndb-7.4.10)
id=56 @192.168.1.12 (mysql-5.6.28 ndb-7.4.10)
id=57 (not connected, accepting connect from any host)
id=58 (not connected, accepting connect from any host)
id=59 (not connected, accepting connect from any host)
id=60 (not connected, accepting connect from any host)
id=61 (not connected, accepting connect from any host)
id=62 (not connected, accepting connect from any host)
id=63 (not connected, accepting connect from any host)
id=64 (not connected, accepting connect from any host)
id=65 (not connected, accepting connect from any host)
id=66 (not connected, accepting connect from any host)
id=67 (not connected, accepting connect from any host)
id=68 (not connected, accepting connect from any host)
id=69 (not connected, accepting connect from any host)
id=70 (not connected, accepting connect from any host)
id=71 (not connected, accepting connect from any host)
id=72 (not connected, accepting connect from any host)
id=73 (not connected, accepting connect from any host)
id=74 (not connected, accepting connect from any host)
id=75 (not connected, accepting connect from any host)
id=76 (not connected, accepting connect from any host)
id=77 (not connected, accepting connect from any host)
id=78 (not connected, accepting connect from any host)
id=79 (not connected, accepting connect from any host)
id=80 (not connected, accepting connect from any host)
ndb_mgm>
* configuration(config.ini)
[NDB_MGMD DEFAULT]
Portnumber=1186
[NDB_MGMD]
NodeId=10
HostName=192.168.1.1
DataDir=/usr/local/mysql/data
ArbitrationRank=1
[TCP DEFAULT]
SendBufferMemory=256M
ReceiveBufferMemory=256M
[NDBD DEFAULT]
NoOfReplicas=2
CompressedBackup=1
DataMemory=6G
IndexMemory=1G
StringMemory=10M
LockPagesInMainMemory=1
MaxNoOfTables=4096
MaxNoOfOrderedIndexes=4000
MaxNoOfTriggers=3500
RedoBuffer=512M
MaxNoOfConcurrentTransactions=50000
MaxNoOfConcurrentOperations=200000
MaxNoOfConcurrentScans=500
MaxNoOfLocalScans=10000
MaxParallelScansPerFragment=1024
MaxNoOfUniqueHashIndexes=200
TransactionDeadlockDetectionTimeout=3000
InitFragmentLogFiles=FULL
NoOfFragmentLogFiles=24
NoOfFragmentLogParts=8
FragmentLogFileSize=512M
ODirect=1
CompressedLCP=1
TimeBetweenLocalCheckpoints=20
TimeBetweenGlobalCheckpoints=2000
TimeBetweenEpochs=100
Numa=1
MemReportFrequency=0
MaxNoOfExecutionThreads=12
LongMessageBuffer=32M
TimeBetweenEpochsTimeout=32000
TimeBetweenWatchdogCheckInitial=60000
TransactionInactiveTimeout=60000
HeartbeatIntervalDbDb=15000
HeartbeatIntervalDbApi=15000
StopOnError=false
BatchSizePerLocalScan=512
BuildIndexThreads=4
[NDBD]
NodeId=1
HostName=192.168.1.100
DataDir=/usr/local/mysql/data
[NDBD]
NodeId=2
HostName=192.168.1.101
DataDir=/usr/local/mysql/data
[NDBD]
NodeId=3
HostName=192.168.1.110
DataDir=/usr/local/mysql/data
[NDBD]
NodeId=4
HostName=192.168.1.111
DataDir=/usr/local/mysql/data
[MYSQLD DEFAULT]
[MYSQLD]
NodeId=51
[MYSQLD]
...
(snip)
...
[MYSQLD]
* configuration(SQL node: my.cnf)
[mysqld]
ndbcluster
ndb-connectstring=192.168.1.1:1186
ndb-cluster-connection-pool=2
query_cache_type=0
query_cache_size=0
performance_schema = OFF
skip-name-resolve
Suggested fix:
`all report memoryusage` output accurate report or provides other method.
