mysql cluster，持续研究，一点点深入，最终希望部署到生产环境去

mchdba · 发表于 2012-8-16 19:45

-- ==============================
【3】ERROR 1296 (HY000): Got error 157 'Unknown error code' from NDBCLUSTER
-- ==============================

mysql> use bg;
Database changed
mysql>
mysql>
mysql> show create table bgt1;
ERROR 1296 (HY000): Got error 157 'Unknown error code' from NDBCLUSTER
mysql>
mysql>
mysql>
mysql>
mysql>
mysql> show create table bgt1;
ERROR 1296 (HY000): Got error 157 'Unknown error code' from NDBCLUSTER
mysql>
mysql>
mysql> create table bgt2(id int,name varchar(20))engine=ndb;
ERROR 1005 (HY000): Can't create table 'bg.bgt2' (errno: 157)

【ok】之后关闭sql节点，重新启动，搞定了！
[root@banggo data]# /etc/rc.d/init.d/mysqld stop
Shutting down MySQL....                                  [确定]
[root@banggo data]# /etc/rc.d/init.d/mysqld start
Starting MySQL...                                        [确定]
[root@banggo data]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.19-ndb-7.2.4-gpl MySQL Cluster Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use bg;
Database changed
mysql> show tables;
+--------------+
| Tables_in_bg |
+--------------+
| bgt1       |
+--------------+
1 row in set (0.03 sec)

mysql> desc bgt1;
+-------+-------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11)    | NO | PRI | 0    |    |
| name  | varchar(20) | YES  |    | NULL |    |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mchdba · 发表于 2012-8-16 19:46

-- =============================================================================
【4】ERROR 1296 (HY000): Got error 157 'Unknown error code' from NDBCLUSTER
-- =============================================================================
2012-07-18 09:58:15 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.41. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:16 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.39. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:16 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.39. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:17 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.41. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:17 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.41. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:18 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.39. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:18 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.39. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:19 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.41. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:19 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.41. Returned eror: 'No free node id found for mysqld(API).'
2012-07-18 09:58:20 [MgmtSrvr] WARNING  -- Failed to allocate nodeid for API at 10.100.200.39. Returned eror: 'No free node id found for mysqld(API).'

【ok】：在config.ini里面加空的sql节点，以便自动扩展s
[API]
[API]

mchdba · 发表于 2012-8-16 19:47

-- =============================================================================
【5】数据节点报错
-- =============================================================================
2012-07-18 23:34:48 [ndbd] INFO    -- Start initiated (mysql-5.5.19 ndb-7.2.4)
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
Adding 7164Mb to ZONE_LO (32896,229247)
Adding 4301Mb to ZONE_LO (262145,137607)
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
WOPool::init(61, 9)
RWPool::init(22, 14)
2012-07-18 23:35:00 [ndbd] INFO    -- timerHandlingLab now: 12658118985 sent: 12658118306 diff: 679
2012-07-18 23:36:09 [ndbd] WARNING  -- Ndb kernel thread 0 is stuck in: Allocating memory elapsed=60029
2012-07-18 23:36:09 [ndbd] INFO    -- Watchdog: User time: 244  System time: 6855
2012-07-18 23:36:35 [ndbd] ALERT -- Node 4: Forced node shutdown completed. Occured during startphase 0. Initiated by signal 9.

【ok】：调整参数值
调小参数，内存超过了。

mchdba · 发表于 2012-8-16 19:50

-- =============================================================================
【6】建立表空间报错
-- =============================================================================
mysql> CREATE LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_1.log' INITIAL_SIZE 1024 M UNDO_BUFFER_SIZE 128 M ENGINE NDBCLUSTER;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'M UNDO_BUFFER_SIZE 128 M ENGINE NDBCLUSTER' at line 1
mysql>

【ok】M不识别
-- CREATE LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_1.log' INITIAL_SIZE 536870912 UNDO_BUFFER_SIZE 67108864 ENGINE NDBCLUSTER;
mysql> CREATE LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_1.log' INITIAL_SIZE 536870912 UNDO_BUFFER_SIZE 67108864 ENGINE NDBCLUSTER;
Query OK, 0 rows affected (27.95 sec)

-- CREATE TABLESPACE ts_1  ADD DATAFILE 'data_1.dat' USE LOGFILE GROUP lg_1 INITIAL_SIZE 536870912 ENGINE NDBCLUSTER;
mysql> CREATE TABLESPACE ts_1  ADD DATAFILE 'data_1.dat' USE LOGFILE GROUP lg_1 INITIAL_SIZE 536870912 ENGINE NDBCLUSTER;
Query OK, 0 rows affected (28.79 sec)

# 创建使用磁盘存储的表：
CREATE TABLE `bgtdisk` (
  `Name` varchar(50) NOT NULL,
  `ProviderName` varchar(200) NOT NULL,
  PRIMARY KEY (`Name`)
) tablespace ts_1 storage disk ENGINE=ndbcluster DEFAULT CHARSET=utf8;

mchdba · 发表于 2012-8-16 19:51

-- =============================================================================
【7】配置报错
-- =============================================================================
Caused by error 2353: 'Insufficent nodes for system restart(Restart error). Temporary error, restart node'.

【error】：没有解决，后来重新部署了cluster。

mchdba · 发表于 2012-8-16 19:51

-- =============================================================================
【8】ERROR 1528 (HY000): Failed to create LOGFILE GROUP
-- =============================================================================
mysql> CREATE LOGFILE GROUP lg_02 ADD UNDOFILE 'undo_02.log' INITIAL_SIZE 5368709120 UNDO_BUFFER_SIZE 67108864 ENGINE NDBCLUSTER;
ERROR 1528 (HY000): Failed to create LOGFILE GROUP
mysql>
mysql>
mysql>
mysql> show errors;
+-------+------+--------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------+
| Error | 1528 | Failed to create LOGFILE GROUP |
+-------+------+--------------------------------+
1 row in set (0.00 sec)

mysql>

解决办法：
原来现在的MYSQL只支持创建一个LOGFILE GROUP文件。
只有删掉原来的才可以创建新的。

mchdba · 发表于 2012-8-16 19:53

-- =============================================================================
【9】ERROR 1114 (HY000): The table 'UserMvpbak' is full
-- =============================================================================
mysql> insert into UserMvpbak select * from UserMvp limit 800000,200000;
ERROR 1114 (HY000): The table 'UserMvpbak' is full
mysql>
alter tablespace ts_1
      add datafile 'data_3.dat'
      initial_size 21474836480
         engine ndb;

【ok】原有的数据文件空间满了，需要增加新的数据文件
mysql> alter tablespace ts_1
->    add datafile 'data_2.dat'
->    initial_size 10737418240
->    engine ndb;

Query OK, 0 rows affected (1 min 54.30 sec)

mysql>

mchdba · 发表于 2012-8-16 20:13

-- =============================================================================
【10】ERROR 1114 (HY000): The table 'UserMvpbak' is full
-- =============================================================================
mysql> insert into um select * from um limit 940000,3000000;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

[ok] 减少录入的数据量
mysql> replace into um select * from um limit 940000,800000;
Query OK, 755105 rows affected (2 min 44.54 sec)
Records: 755105 Duplicates: 0 Warnings: 0

mchdba · 发表于 2012-8-16 20:13

-- =============================================================================
【11】Node 4: Forced node shutdown completed. Occured during startphase 0. Initiated by signal 11.
-- =============================================================================
2012-08-15 12:35:38 [ndbd] INFO    -- Start initiated (mysql-5.5.25 ndb-7.2.7)
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
2012-08-15 12:35:45 [ndbd] WARNING  -- Ndb kernel thread 0 is stuck in: Polling for Receive elapsed=159
2012-08-15 12:35:45 [ndbd] INFO    -- timerHandlingLab now: 498814955 sent: 498814782 diff: 173
2012-08-15 12:35:45 [ndbd] INFO    -- Watchdog: User time: 28  System time: 497
2012-08-15 12:35:47 [ndbd] INFO    -- timerHandlingLab now: 498816970 sent: 498816803 diff: 167
2012-08-15 12:35:49 [ndbd] INFO    -- Watchdog: User time: 39  System time: 741
2012-08-15 12:35:49 [ndbd] WARNING  -- Watchdog: Warning overslept 262 ms, expected 100 ms.
2012-08-15 12:35:49 [ndbd] INFO    -- timerHandlingLab now: 498818963 sent: 498818779 diff: 184
Adding 5201Mb to ZONE_LO (1152,166408)
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
NDBFS/AsyncFile: Allocating 310392 for In/Deflate buffer
2012-08-15 12:35:56 [ndbd] WARNING  -- Ndb kernel thread 0 is stuck in: Allocating memory elapsed=6002
2012-08-15 12:35:56 [ndbd] INFO    -- Watchdog: User time: 61  System time: 1179
2012-08-15 12:36:02 [ndbd] WARNING  -- Ndb kernel thread 0 is stuck in: Allocating memory elapsed=12006
2012-08-15 12:36:02 [ndbd] INFO    -- Watchdog: User time: 64  System time: 1763
2012-08-15 12:36:08 [ndbd] WARNING  -- Ndb kernel thread 0 is stuck in: Allocating memory elapsed=18030
2012-08-15 12:36:08 [ndbd] INFO    -- Watchdog: User time: 70  System time: 1957
2012-08-15 12:36:12 [ndbd] ALERT -- Node 4: Forced node shutdown completed. Occured during startphase 0. Initiated by signal 11.

[ok] 注释如下三个参数，启动ok！
#MaxNoOfConcurrentTransactions=100000
#MaxNoOfConcurrentOperations=10000000
#MaxNoOfLocalOperations=11000000

mchdba · 发表于 2012-8-16 20:14

-- =============================================================================
【12】启动管理节点报错
-- =============================================================================
[root@banggo mysql-cluster]# ndb_mgmd -f /usr/local/mysql/cluster-conf/config.ini --reload
MySQL Cluster Management Server mysql-5.5.25 ndb-7.2.7
2012-08-15 21:36:54 [MgmtSrvr] ERROR -- at line 18: Illegal value 128 for parameter LockPagesInMainMemory.
Legal values are between 0 and 2
2012-08-15 21:36:54 [MgmtSrvr] ERROR -- at line 18: Could not parse name-value pair in config file.
2012-08-15 21:36:54 [MgmtSrvr] ERROR -- Could not load configuration from '/usr/local/mysql/cluster-conf/config.ini'
2012-08-15 21:36:54 [MgmtSrvr] ERROR -- Could not determine which nodeid to use for this node. Specify it with --ndb-nodeid=<nodeid> on command line
[root@banggo mysql-cluster]#
[root@banggo mysql-cluster]#

【ok】LockPagesInMainMemory值设置太大了！改成默认的1，搞定。

mysql cluster，持续研究，一点点深入，最终希望部署到生产环境去

浏览过的版块