转载：http://www.itpub.net/forum.php?mod=viewthread&action=printable&tid=1707139
ITPUB论坛
标题: mysql cluster，持续研究，一点点深入，最终希望部署到生产环境去 [打印本页]作者: mchdba 时间: 2012-8-16 17:05 标题:mysql cluster，持续研究，一点点深入，最终希望部署到生产环境去
本帖最后由 mchdba 于 2012-8-20 14:20 编辑
混迹于mysql板块一年多，以学习居多，解答别人问题的比较少，所以决定将自己最近对cluster的学习心得share出来，感谢大家的厚爱(技术新星徽章一枚)，当然资料里面也许有很多我不知道的bug，欢迎大家提供宝贵的建议。
笨鸟先飞，菜鸟慢慢飞！大家一起分享，一起进步！
Cluster学习.pptx(532.15 KB, 下载次数: 186)
详细的cluster部署以及原理分析文档，大家可以按照里面的一步步来部署cluster
cluster-备份恢复测试.txt(10.29 KB, 下载次数: 78)
cluster-备份恢复测试，已经经过实践测试验证过的。
error.txt(33.82 KB, 下载次数: 80)
error.txt，是我在部署使用cluster过程中遇到的印象比较深刻或者难以解决的问题的汇总。
常用参数配置.txt(3.9 KB, 下载次数: 82)
常用参数配置.txt，里面记载了许多常用的参数的配置
96G_config.ini.txt(2.06 KB, 下载次数: 70)
网友哲哲提供的他线上96G内存的data node的config.ini的配置文档
32G_config.ini.txt(708 Bytes, 下载次数: 64)
网友巧元天下提供的他线上32G内存的data node的config.ini的配置文档
1.sh.txt(4.17 KB, 下载次数: 63)
验证管理节点config.ini参数配置的shell脚本，看memory是否超标，运行的时候需要附带上面data node id，
而且cluster要处于工作状态，最起码manage node和data node要处于启动状态
附件: [cluster搭建]
Cluster学习.pptx (2012-8-16 17:00, 532.15 KB) / 下载次数 186
http://www.itpub.net/forum.php?mod=attachment&aid=Nzk5ODMyfDg3NmJkMTNkfDEzNTk2ODgxNjh8MHww
附件:
cluster-备份恢复测试.txt (2012-8-16 19:57, 10.29 KB) / 下载次数 78
http://www.itpub.net/forum.php?mod=attachment&aid=Nzk5ODU5fDJlY2ZlNTgzfDEzNTk2ODgxNjh8MHww
附件:
error.txt (2012-8-16 19:57, 33.82 KB) / 下载次数 80
http://www.itpub.net/forum.php?mod=attachment&aid=Nzk5ODU4fGE4YTBkYjRhfDEzNTk2ODgxNjh8MHww
附件:
常用参数配置.txt (2012-8-16 19:57, 3.9 KB) / 下载次数 82
http://www.itpub.net/forum.php?mod=attachment&aid=Nzk5ODU3fGE2ZjFkY2U1fDEzNTk2ODgxNjh8MHww
附件:
96G_config.ini.txt (2012-8-16 19:57, 2.06 KB) / 下载次数 70
http://www.itpub.net/forum.php?mod=attachment&aid=Nzk5ODU2fDMzZWRlNjY4fDEzNTk2ODgxNjh8MHww
附件:
32G_config.ini.txt (2012-8-16 19:57, 708 Bytes) / 下载次数 64
http://www.itpub.net/forum.php?mod=attachment&aid=Nzk5ODU1fDE2ZTdhY2ZmfDEzNTk2ODgxNjh8MHww
附件:
1.sh.txt (2012-8-16 19:57, 4.17 KB) / 下载次数 63
http://www.itpub.net/forum.php?mod=attachment&aid=Nzk5ODU0fGY3NjI0OTMwfDEzNTk2ODgxNjh8MHww
作者: mchdba 时间: 2012-8-16 17:07
不想一段段贴出来了，就把一直保存下来的发出来了。
作者: mchdba 时间: 2012-8-16 17:11
mysql cluster的参数配置文档，我目前的环境是 4个data node 内存8G，4个sql节点 内存4G，1个管理节点，内存4G，都是多核。
config.ini.txt(2.87 KB, 下载次数: 22)
附件: [请用editplus打开，txt打开可能格式比较乱]
config.ini.txt (2012-8-16 17:11, 2.87 KB) / 下载次数 22
http://www.itpub.net/forum.php?mod=attachment&aid=Nzk5ODMzfDMxNmEwZTllfDEzNTk2ODgxNjh8MHww
作者: mchdba 时间: 2012-8-16 17:12
mchdba 发表于 2012-8-16 17:11
mysql cluster的参数配置文档，我目前的环境是 4个data node 内存8G，4个sql节点 内存4G，1个管理节点，内存 ...
用editplus打开，用txt打开格式可能会比较乱
作者: mchdba 时间: 2012-8-16 17:44
常用参数配置.txt(3.9 KB, 下载次数: 16)
cluster常用参数配置 介绍
附件:
常用参数配置.txt (2012-8-16 17:27, 3.9 KB) / 下载次数 16
http://www.itpub.net/forum.php?mod=attachment&aid=Nzk5ODM3fDFmNTJhMzIxfDEzNTk2ODgxNjh8MHww
作者: mchdba 时间: 2012-8-16 17:46
32G_config.ini.txt(708 Bytes, 下载次数: 10)
32G的数据节点的配置文件（一个朋友提供的线上环境）
附件:
32G_config.ini.txt (2012-8-16 17:45, 708 Bytes) / 下载次数 10
http://www.itpub.net/forum.php?mod=attachment&aid=Nzk5ODQwfGYzMDUzNzI1fDEzNTk2ODgxNjh8MHww
作者: mchdba 时间: 2012-8-16 17:50
96G_config.ini.txt(2.06 KB, 下载次数: 6)
96G内存的数据节点的配置参考文件。
附件:
96G_config.ini.txt (2012-8-16 17:49, 2.06 KB) / 下载次数 6
http://www.itpub.net/forum.php?mod=attachment&aid=Nzk5ODQxfGY2NjE5Zjc3fDEzNTk2ODgxNjh8MHww
作者: mchdba 时间: 2012-8-16 17:55
1.sh.txt(4.17 KB, 下载次数: 5)
这个文件是sh的，下载下来请去掉后缀名字.txt，在管理节点执行，sh 1.sh 4; 后面带一个数据节点id的参数。
作用是算下你的config.ini配置是否超标了，检测配置所消耗的内存是否在承受范围之内。
附件:
1.sh.txt (2012-8-16 17:53, 4.17 KB) / 下载次数 5
http://www.itpub.net/forum.php?mod=attachment&aid=Nzk5ODQyfDFkNGNmNWYwfDEzNTk2ODgxNjh8MHww
作者: mchdba 时间: 2012-8-16 19:44
本帖最后由 mchdba 于 2012-8-16 19:52 编辑
记录下曾经遇到个印象深刻的各种问题以及解决的办法，大家有好的办法，请留言吧！
-- =============================================================================
【1】查看表空间数据文件大小
-- =============================================================================
mysql> SELECT TABLESPACE_NAME, FILE_NAME, EXTENT_SIZE*TOTAL_EXTENTS/1024/1024 AS TOTAL_MB, EXTENT_SIZE*FREE_EXTENTS/1024/1024 AS FREE_MB, EXTRA FROM information_schema.FILES WHERE FILE_TYPE="DATAFILE";
+-----------------+------------+----------------+----------------+----------------+
| TABLESPACE_NAME | FILE_NAME | TOTAL_MB | FREE_MB | EXTRA |
+-----------------+------------+----------------+----------------+----------------+
| ts_1 | data_2.dat | 10240.00000000 | 10146.00000000 | CLUSTER_NODE=4 |
| ts_1 | data_2.dat | 10240.00000000 | 10146.00000000 | CLUSTER_NODE=5 |
| ts_1 | data_2.dat | 10240.00000000 | 10146.00000000 | CLUSTER_NODE=6 |
| ts_1 | data_2.dat | 10240.00000000 | 10162.00000000 | CLUSTER_NODE=7 |
| ts_1 | data_1.dat | 512.00000000 | 19.00000000 | CLUSTER_NODE=4 |
| ts_1 | data_1.dat | 512.00000000 | 19.00000000 | CLUSTER_NODE=5 |
| ts_1 | data_1.dat | 512.00000000 | 16.00000000 | CLUSTER_NODE=6 |
| ts_1 | data_1.dat | 512.00000000 | 0.00000000 | CLUSTER_NODE=7 |
+-----------------+------------+----------------+----------------+----------------+
作者: mchdba 时间: 2012-8-16 19:45
-- ============================
【2】：建库报错
mysql> CREATE DATABASE zhang;
Query OK, 1 row affected, 2 warnings (0.01 sec)
mysql>
mysql>
mysql>
mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------+
| Warning | 1296 | Got error 4009 'Cluster Failure' from NDB. Could not acquire global schema lock |
| Warning | 1296 | Got error 4009 'Cluster Failure' from Could not log query '%s' on other mysqld's |
+---------+------+----------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
【ok】安全模式启动
/usr/local/mysql/bin/mysqld_safe &
作者: 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，搞定。
作者: mchdba 时间: 2012-8-16 20:15
-- =============================================================================
【13】 1528 错误
-- =============================================================================
mysql> CREATE LOGFILE GROUP lg_1
-> ADD UNDOFILE 'undo_2.log'
-> INITIAL_SIZE 634217728
-> UNDO_BUFFER_SIZE 134217728
-> ENGINE NDBCLUSTER;
ERROR 1528 (HY000): Failed to create LOGFILE GROUP
mysql>
mysql>
mysql>
mysql> show warnings;
+---------+------+---------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------+
| Warning | 1296 | Got error 1504 'Out of logbuffer memory' from NDB |
| Error | 1528 | Failed to create LOGFILE GROUP |
+---------+------+---------------------------------------------------+
2 rows in set (0.00 sec)
【ok】
# UNDO_BUFFER_SIZE不能超过config.ini的配置RedoBuffer=32M大小，否则创建失败，
CREATE LOGFILE GROUP lg_1
ADD UNDOFILE 'undo_2.log'
INITIAL_SIZE 634217728
UNDO_BUFFER_SIZE 33554432
ENGINE NDBCLUSTER;
CREATE TABLESPACE ts_1
ADD DATAFILE 'data_10.dat'
USE LOGFILE GROUP lg_1
INITIAL_SIZE 32212254720
EXTENT_SIZE 33554432
ENGINE NDBCLUSTER;
执行结束之后，需要去数据目录/var/lib/mysql-cluster/里面看看undo文件和data文件，如果存在，那么就证明建立成功了。
可以建立2个表空间
CREATE TABLESPACE ts_2
ADD DATAFILE 'data_20.dat'
USE LOGFILE GROUP lg_1
INITIAL_SIZE 10737418240
EXTENT_SIZE 33554432
ENGINE NDBCLUSTER;
create table bguserdb.mcbak like test.mc;
alter table bguserdb.mcbak tablespace ts_1 storage disk ENGINE=ndbcluster DEFAULT CHARSET=utf8;
insert into bguserdb.mcbak select * from test.mc;
CREATE TABLESPACE ts_1
ADD DATAFILE 'data_10.dat‘ USE LOGFILE GROUP lg_1 INITIAL_SIZE 32212254720
EXTENT_SIZE 33554432 ENGINE NDBCLUSTER;
作者: mchdba 时间: 2012-8-16 20:17
本帖最后由 mchdba 于 2012-8-16 20:17 编辑
-- =============================================================================
【14】 1528 错误
-- =============================================================================
mysql> insert into bguserdb.mv select * from test.mc;
ERROR 1297 (HY000): Got temporary error 410 'REDO log files overloaded (decrease TimeBetweenLocalCheckpoints or increase NoOfFragmentLogFiles)' from NDBCLUSTER
mysql>
【】增加2个参数的值
TimeBetweenLocalCheckpoints=30
NoOfFragmentLogFiles=128
作者: mchdba 时间: 2012-8-16 20:19
本帖最后由 mchdba 于 2012-8-16 20:19 编辑
-- =============================================================================
【15】 1005 错误
-- =============================================================================
mysql> create table bu.mcbak like test.mc;
Query OK, 0 rows affected (0.07 sec)
mysql> alter table bu.mcbak tablespace ts_1 storage disk ENGINE=ndbcluster DEFAULT CHARSET=utf8;
ERROR 1005 (HY000): Can't create table 'bu.#sql-14ab_2' (errno: 140)
mysql> show errors;
+-------+------+--------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------+
| Error | 1005 | Can't create table 'bu.#sql-14ab_2' (errno: 140) |
+-------+------+--------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------+
| Warning | 1296 | Got error 755 'Invalid tablespace' from NDB |
| Error | 1005 | Can't create table 'bu.#sql-14ab_2' (errno: 140) |
+---------+------+--------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
【ok】重新建立表空间：
CREATE LOGFILE GROUP lg_1
ADD UNDOFILE 'undo_1.log'
INITIAL_SIZE 334217728
UNDO_BUFFER_SIZE 33554432
ENGINE NDBCLUSTER;
CREATE TABLESPACE ts_1 ADD DATAFILE 'data_11.dat' USE LOGFILE GROUP lg_1 INITIAL_SIZE 4294967296 EXTENT_SIZE 33554432 ENGINE NDBCLUSTER;
alter table bu.mc tablespace ts_1 storage disk ENGINE=ndbcluster DEFAULT CHARSET=utf8;
insert into bu.mc select * from test.mc limit 0,100000;
作者: mchdba 时间: 2012-8-16 20:21
-- =============================================================================
【16】 1005 错误
-- =============================================================================
mysql> alter table oib TABLESPACE ts_1 STORAGE DISK ENGINE=ndbcluster DEFAULT CHARSET=utf8;
ERROR 1005 (HY000): Can't create table 'os.#sql-711_3' (errno: 851)
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------------------------------------------------+
| Warning | 1296 | Got error 851 'Maximum 8052 bytes of FIXED columns supported, use varchar or COLUMN_FORMAT DYNMIC instead' from NDB |
| Error | 1005 | Can't create table 'os.#sql-711_3' (errno: 851) |
+---------+------+---------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
[暂无解决方案]google之，无果！
作者: mchdba 时间: 2012-8-16 20:22
-- =============================================================================
【17】 1297 错误
-- =============================================================================
mysql> alter table ebelkTABLESPACE ts_1 STORAGE DISK ENGINE=ndbcluster DEFAULT CHARSET=utf8 comment '';
ERROR 1297 (HY000): Got temporary error 1501 'Out of undo space' from NDBCLUSTER
mysql>
mysql> show errors;
+-------+------+--------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------+
| Error | 1297 | Got temporary error 1501 'Out of undo space' from NDBCLUSTER |
| Error | 1296 | Got error 4350 'Transaction already aborted' from NDBCLUSTER |
| Error | 1180 | Got error 4350 during COMMIT |
+-------+------+--------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql>
[ok]增加数据文件，数据文件空间可能不够了。
alter tablespace ts_1 add datafile 'data_12.dat' initial_size 10737418240 engine NDBCLUSTER;
alter tablespace ts_1 add datafile 'data_13.dat' initial_size 10737418240 engine NDBCLUSTER;
alter tablespace ts_1 add datafile 'data_14.dat' initial_size 10737418240 engine NDBCLUSTER;
作者: mchdba 时间: 2012-8-16 20:23
-- =============================================================================
【18】 1297 错误
-- =============================================================================
mysql> alter table aau TABLESPACE ts_1 STORAGE DISK ENGINE=ndbcluster DEFAULT CHARSET=utf8 comment '';
ERROR 1297 (HY000): Got temporary error 1501 'Out of undo space' from NDBCLUSTER
mysql> show warnings;
+---------+------+--------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------+
| Warning | 1297 | Got temporary error 1501 'Out of undo space' from NDB |
| Error | 1297 | Got temporary error 1501 'Out of undo space' from NDBCLUSTER |
| Warning | 1296 | Got error 4350 'Transaction already aborted' from NDB |
| Error | 1296 | Got error 4350 'Transaction already aborted' from NDBCLUSTER |
| Error | 1180 | Got error 4350 during COMMIT |
+---------+------+--------------------------------------------------------------+
5 rows in set (0.00 sec)
mysql>
【】暂时未解决。
作者: mchdba 时间: 2012-8-16 20:24
-- =============================================================================
【19】 1297 错误
-- =============================================================================
ndb_mgm> Node 7: Forced node shutdown completed. Occured during startphase 4. Caused by error 2303: 'System error, node killed during node restart by other node(Internal error, programming error or missing error message, please report a bug). Temporary error, restart node'.
Node 4: Forced node shutdown completed. Occured during startphase 4. Caused by error 2308: 'Another node failed during system restart, please investigate error(s) on other node(s)(Restart error). Temporary error, restart node'.
Node 5: Forced node shutdown completed. Occured during startphase 4. Caused by error 2308: 'Another node failed during system restart, please investigate error(s) on other node(s)(Restart error). Temporary error, restart node'.
Node 6: Forced node shutdown completed. Occured during startphase 4. Caused by error 2308: 'Another node failed during system restart, please investigate error(s) on other node(s)(Restart error). Temporary error, restart node'.
【ok】baidu，google之，没有得到相似的案列，看到一个emporary error, restart node的提示，不得已 restart node 节点
重新在4个data 节点，执行 ndbd --initial，等待2分钟，搞定！oh，my god，不知道其中的原理，但是data node确实是起来了。
ndb_mgm> Node 4: Started (version 7.2.7)
Node 7: Started (version 7.2.7)
Node 5: Started (version 7.2.7)
Node 6: Started (version 7.2.7)
启动sql节点，执行检查数据
mysql> SELECT TABLESPACE_NAME, FILE_NAME, EXTENT_SIZE*TOTAL_EXTENTS/1024/1024 AS TOTAL_MB, EXTENT_SIZE*FREE_EXTENTS/1024/1024 AS FREE_MB, EXTRA FROM information_schema.FILES WHERE FILE_TYPE="DATAFILE";
Empty set (0.00 sec)
mysql>
oh my god，原来的数据文件都无法显示了。简直是暴力破解啊，^_^！
作者: mchdba 时间: 2012-8-16 21:44
本帖最后由 mchdba 于 2012-8-16 21:51 编辑
-- =============================================================================
【20】 Out of undo space
-- =============================================================================
mysql> insert into mc select * from zzbak_mc limit 0,10000;
ERROR 1297 (HY000): Got temporary error 1501 'Out of undo space' from NDBCLUSTER
mysql> show warnings;
+---------+------+--------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------+
| Warning | 1297 | Got temporary error 1501 'Out of undo space' from NDB |
| Error | 1297 | Got temporary error 1501 'Out of undo space' from NDBCLUSTER |
+---------+------+--------------------------------------------------------------+
2 rows in set (0.00 sec)
【ok】参考网址 http://forums.mysql.com/read.php?25,413217,413217
I add another undo log file ,it's ok now ,but counld some one tell me how can I know the usage of my undo log file ? SELECT FILE_NAME FROM information_schema.files WHERE FILE_TYPE='UNDO LOG'\G;
执行：alter LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_21.log' UNDO_BUFFER_SIZE 33554432 ENGINE NDBCLUSTER;
mysql> alter LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_21.log' ENGINE NDBCLUSTER;
Query OK, 0 rows affected (4.33 sec)
-- 查看系统已有的undo文件
mysql> SELECT FILE_NAME FROM information_schema.files WHERE FILE_TYPE='UNDO LOG';
+-------------+
| FILE_NAME |
+-------------+
| undo_2.log |
| undo_2.log |
| undo_2.log |
| undo_2.log |
| undo_21.log |
| undo_21.log |
| undo_21.log |
| undo_21.log |
| NULL |
+-------------+
9 rows in set (0.05 sec)
mysql>SELECT FILE_NAME FROM information_schema.files WHERE FILE_TYPE='UNDO LOG'\G;
搞定，OK。
mysql> insert into mc select * from zzbak_mc limit 0,10000;
Query OK, 10000 rows affected (0.80 sec)
Records: 10000 Duplicates: 0 Warnings: 0
mysql> insert into mc select * from zzbak_mc limit 810000,890000;
Query OK, 890000 rows affected (2 min 10.10 sec)
Records: 890000 Duplicates: 0 Warnings: 0
作者: mchdba 时间: 2012-8-17 12:33
-- =============================================================================
【21】 后台日志报错，批量insert数据
-- =============================================================================
2012-08-16 22:38:05 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 38285338476552 nodes: 0000000000000000000000000000000000000000000000000000000000016400
2012-08-16 22:38:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 8914/8) ref: 0fa2000e from: 0fa2000e
2012-08-16 22:38:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 8914/8) ref: 0fa2000a from: 0fa2000a
2012-08-16 22:38:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 8914/8) ref: 0fa20010 from: 0fa20010
2012-08-16 22:38:05 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 38285338476553 nodes: 0000000000000000000000000000000000000000000000000000000000016400
2012-08-16 22:38:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 8914/9) ref: 0fa2000e from: 0fa2000e
2012-08-16 22:38:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 8914/9) ref: 0fa2000a from: 0fa2000a
2012-08-16 22:38:05 [ndbd] ERROR -- c_gcp_list.seize() failed: gci: 38289633443840 nodes: 0000000000000000000000000000000000000000000000000000000000016400
2012-08-16 22:38:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 8914/9) ref: 0fa20010 from: 0fa20010
2012-08-16 22:38:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 8915/0) ref: 0fa2000a from: 0fa2000a
2012-08-16 22:38:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 8915/0) ref: 0fa20010 from: 0fa20010
2012-08-16 22:38:05 [ndbd] WARNING -- ACK wo/ gcp record (gci: 8915/0) ref: 0fa2000e from: 0fa2000e
【ok】减少insert的数量，一次批量从100W减少到50W，ok，不会报异常信息了。
作者: mchdba 时间: 2012-8-17 12:33
-- =============================================================================
【22】 第4个data节点意外down了
-- =============================================================================
ndb_mgm> Node 7: Forced node shutdown completed. Caused by error 2305: 'Node lost connection to other nodes and can not form a unpartitioned cluster, please investigate if there are error(s) on other node(s)(Arbitration error). Temporary error, restart node'.
ndb_mgm>
【ok】执行ndbd 重新启动起来
作者: arron刘 时间: 2012-8-17 15:46
作者: mchdba 时间: 2012-8-18 23:12
arron刘 发表于 2012-8-17 15:46
-- =============================================================================
【22】 管理台老是报警，诡异
-- =============================================================================
一次性批量录入了100W数据，管理节点控制台报如下信息：
ndb_mgm> Node 7: Data usage increased to 80%(64016 32K pages of total 80000)
Node 4: Data usage increased to 80%(64007 32K pages of total 80000)
Node 5: Data usage increased to 80%(64014 32K pages of total 80000)
Node 6: Data usage increased to 80%(64001 32K pages of total 80000)
Node 4: Data usage increased to 90%(72005 32K pages of total 80000)
Node 5: Data usage increased to 90%(72014 32K pages of total 80000)
Node 7: Data usage increased to 90%(72010 32K pages of total 80000)
Node 6: Data usage increased to 90%(72009 32K pages of total 80000)
Node 6: Data usage decreased to 89%(71772 32K pages of total 80000)
Node 7: Data usage decreased to 89%(71271 32K pages of total 80000)
Node 4: Data usage decreased to 89%(71215 32K pages of total 80000)
Node 5: Data usage decreased to 88%(70607 32K pages of total 80000)
Node 5: Data usage decreased to 79%(63850 32K pages of total 80000)
Node 7: Data usage decreased to 79%(63673 32K pages of total 80000)
Node 4: Data usage decreased to 78%(62947 32K pages of total 80000)
Node 6: Data usage decreased to 78%(62932 32K pages of total 80000)
没有搞懂这是什么情况呢？
作者: mchdba 时间: 2012-8-19 17:03
-- =============================================================================
【23】 ERROR 1297 (HY000)
-- =============================================================================
mysql> insert into bu.up_2012 select * from test.up_2012 limit 1200000,10000;
ERROR 1297 (HY000): Got temporary error 899 'Rowid already allocated' from NDBCLUSTER
mysql>
【ok】网上都说需要调大datamemory，但是我的是磁盘表，应该不是这个原因，不得已只有restart cluster了试试了，还好搞定了！
mysql> replace into bu.up_2012 select * from test.up_2012 limit 1200000,300000;
Query OK, 300000 rows affected (59.97 sec)
Records: 300000 Duplicates: 0 Warnings: 0
赞叹一句，网上关于STORAGE DISK ndbcluster table的测试的资料好少啊！
作者: mchdba 时间: 2012-8-19 20:05
-- =============================================================================
【25】 修改了config.in之后，data node 启动，需要 ndbd --initial重新加载之后，
原有的*.dat数据文件无效，启动不起来，sql节点启动之后，show tables; 原来的磁盘表都不见了，诡异啊！
-- =============================================================================
1 修改config.inf
2 ndb_mgmd -f /usr/local/mysql/cluster-conf/config.ini --reload 重新加载管理节点
3 ndbd --initial 重新启动data node
4 service mysqld start
5 use bu; show tables; 原来的表不存在了，丢失了。
看的文档是说，原有的数据文件加载不了，我想问的是：
1 如果我已经导入了很多数据，这个时候发现自己的配置参数不合理，我要修改配置参数，能不能在线修改并生效。
2 如果1失败，我能否在重新启动管理节点之后，启动data node的时候，能加载进去以前的数据呢？
作者: kerlion 时间: 2012-8-20 10:06
想修改配置，需要以--initial或reload的方式重新启动ndb_mgmd
ndbd一般不需要--initial启动，只有当有文件文件损坏的时候，它会删除所有本地文件，然后从其他节点的member取回数据，如果其它都是down的，那数据库就丢失了
mysql cluster很不稳定，启动很可能失败，修改场数据前最好备份一下，实在不行全部--initial启动，恢复数据
作者: mchdba 时间: 2012-8-20 11:06
kerlion 发表于 2012-8-20 10:06
想修改配置，需要以--initial或reload的方式重新启动ndb_mgmd
ndbd一般不需要--initial启动，只有当有文件 ...
你好，谢谢提醒，我记得了，我再去尝试试试看！
作者: mchdba 时间: 2012-8-20 11:07
kerlion 发表于 2012-8-20 10:06
想修改配置，需要以--initial或reload的方式重新启动ndb_mgmd
ndbd一般不需要--initial启动，只有当有文件 ...
再请教一个小问题：
ERROR 1297 (HY000): Got temporary error 410 'REDO log files overloaded (decrease TimeBetweenLocalCheckpoints or increase NoOfFragmentLogFiles)' from NDBCLUSTER
cluster里面的 redo log file 在哪里可以看到，我知道undo log文件，但是不知道redo log file 在哪里呢？
作者: kerlion 时间: 2012-8-20 17:04
我也不知道，都是内部文件，也不用管，但有参数控制其大小
作者: mchdba 时间: 2012-8-20 18:42
kerlion 发表于 2012-8-20 17:04
我也不知道，都是内部文件，也不用管，但有参数控制其大小
那个参数我知道的，有控制文件数量的，有控制文件大小的，但是就是不知道这些文件在哪里，也不知道redo log到底使用了多少？
作者: mchdba 时间: 2012-8-27 15:33
本帖最后由 mchdba 于 2012-8-28 16:32 编辑
-- =============================================================================
【26】 批量insert 500W记录报错
-- =============================================================================
2012-08-26 00:37:25 [ndbd] INFO -- findNeighbours from: 4861 old (left: 5 right: 5) new (65535 65535)
2012-08-26 00:37:25 [ndbd] INFO -- Watchdog: User time: 976564 System time: 1189718
2012-08-26 00:37:25 [ndbd] WARNING -- Watchdog: Warning overslept 410 ms, expected 100 ms.
2012-08-26 00:37:25 [ndbd] INFO -- Arbitrator decided to shutdown this node
2012-08-26 00:37:25 [ndbd] INFO -- QMGR (Line: 5975) 0x00000002
2012-08-26 00:37:25 [ndbd] INFO -- Error handler shutting down system
2012-08-26 00:37:25 [ndbd] INFO -- Error handler shutdown completed - exiting
2012-08-26 00:37:38 [ndbd] ALERT -- Node 6: Forced node shutdown completed. Caused by error 2305: 'Node lost connection to other nodes and can not form a unpartitioned cluster, please investigate if there are error(s) on other node(s)(Arbitration error). Temporary error, restart node'.
[ok]：google之，大部分的方案都是 restart data node。
而且一般的ndbd启动无效，需要ndbd --initial 重新加载才行。
作者: mchdba 时间: 2012-8-29 11:08
-- =============================================================================
【27】 导入数据错误；
-- =============================================================================
CREATE TABLE `pp_order` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`pid` varchar(255) DEFAULT NULL,
`itemCode` varchar(255) DEFAULT NULL,
`itemLocalCode` varchar(255) DEFAULT NULL,
`stockLocalCode` varchar(255) DEFAULT NULL,
`itemDetailLink` varchar(255) DEFAULT NULL,
`itemName` varchar(255) DEFAULT NULL,
`itemPicLink` varchar(255) DEFAULT NULL,
`itemRetailPrice` varchar(255) DEFAULT NULL,
`itemDealPrice` varchar(255) DEFAULT NULL,
`stockAttr` varchar(255) DEFAULT NULL,
`itemAdjustPrice` varchar(255) DEFAULT NULL,
`itemDealCount` varchar(255) DEFAULT NULL,
`itemDealState` varchar(255) DEFAULT NULL,
`account` varchar(255) DEFAULT NULL,
`itemFlag` varchar(255) DEFAULT NULL,
`refundState` varchar(255) DEFAULT NULL,
`refundStateDesc` varchar(255) DEFAULT NULL,
`availableAction` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `dealCode` (`pid`(30))
) TABLESPACE ts_1 STORAGE DISK DEFAULT CHARSET=utf8;
mysql> ERROR 1005 (HY000): Can't create table 'bgapidb.pp_order' (errno: 851)
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------------------------------------------------+
| Warning | 1296 | Got error 851 'Maximum 8052 bytes of FIXED columns supported, use varchar or COLUMN_FORMAT DYNMIC instead' from NDB |
| Error | 1005 | Can't create table 'bgapidb.pp_order' (errno: 851) |
+---------+------+---------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
【OK】，磁盘表的varchar列的占据的总长度不能多于8052B，所以合理设计字段的长度。搞定
mysql> CREATE TABLE bgapidb.`pp_order` (
-> `Id` INT(11) NOT NULL AUTO_INCREMENT,
-> `pid` VARCHAR(30) DEFAULT NULL,
-> `itemCode` VARCHAR(40) DEFAULT NULL,
-> `itemLocalCode` VARCHAR(16) DEFAULT NULL,
-> `stockLocalCode` VARCHAR(16) DEFAULT NULL,
-> `itemDetailLink` VARCHAR(100) DEFAULT NULL,
-> `itemName` VARCHAR(100) DEFAULT NULL,
-> `itemPicLink` VARCHAR(100) DEFAULT NULL,
-> `itemRetailPrice` VARCHAR(10) DEFAULT NULL,
-> `itemDealPrice` VARCHAR(10) DEFAULT NULL,
-> `stockAttr` VARCHAR(120) DEFAULT NULL,
-> `itemAdjustPrice` VARCHAR(10) DEFAULT NULL,
-> `itemDealCount` VARCHAR(10) DEFAULT NULL,
-> `itemDealState` VARCHAR(30) DEFAULT NULL,
-> `account` VARCHAR(10) DEFAULT NULL,
-> `itemFlag` VARCHAR(10) DEFAULT NULL,
-> `refundState` VARCHAR(32) DEFAULT NULL,
-> `refundStateDesc` VARCHAR(32) DEFAULT NULL,
-> `availableAction` VARCHAR(60) DEFAULT NULL,
-> PRIMARY KEY (`Id`),
-> KEY `dealCode` (`pid`(30))
-> ) TABLESPACE ts_1 STORAGE DISK ENGINE=NDBCLUSTER DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.24 sec)
作者: mchdba 时间: 2012-8-29 22:13
-- =============================================================================
【30】 Got temporary error 899 'Rowid already allocated' from NDB
-- =============================================================================
CREATE TABLE lg.`nc_xxx` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`ymd` datetime NOT NULL COMMENT '请求日期',
`request_url` varchar(200) NOT NULL COMMENT '请求URL',
`post_data` text COMMENT '提交参数',
`return_data` text COMMENT '返回参数',
`ipaddr` varchar(20) DEFAULT NULL COMMENT '发送请求IP',
`status` char(3) DEFAULT NULL COMMENT '请求状态:0 已请求 返回异常 1 已请求 正常返回 ',
`created` datetime DEFAULT NULL COMMENT '该记录创建时间',
`modified` datetime DEFAULT NULL COMMENT '记录修改时间',
`api_type` varchar(55) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8;
ERROR 1005 (HY000): Can't create table 'lg.xxxx' (errno: 708)
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------+
| Warning | 1296 | Got error 708 'No more attribute metadata records (increase MaxNoOfAttributes)' from NDB |
| Error | 1005 | Can't create table 'bglogdb.nc_api_logs' (errno: 708) |
+---------+------+------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
【ok】，将MaxNoOfAttributes值增加到24760，然后重新启动cluster，重新建表 搞定。
作者: mchdba 时间: 2012-9-4 14:14
-- ==================================================================================================================
【31】 ERROR : Got temporary error 1234 'REDO log files overloaded (increase disk hardware)' from NDBCLUSTER
-- ==================================================================================================================
[root@banggo ~]# time mysqlslap --engine=ndbcluster --auto-generate-sql-write-number=100000 --auto-generate-sql-guid-primary --concurrency=200,400,600 --number-of-queries=1000000 --iterations=2 --number-char-cols=10 --number-int-cols=10 --auto-generate-sql --create-schema=ndb –-commint=1000 --auto-generate-sql-load-type=write
mysqlslap: Cannot run query INSERT INTO t1 VALUES (uuid(),412380163,1002100976,271523606,1458696239,1603135659,696200156,1997806484,42367420,1574962246,
1628033484,'5QF0PnnE7cfW1qKl2J245d0XcXRDB6THOlHqOwwXZc1aQDKLOEPTrHsJgCOsIu1aXAGv6DQXpJ67PISpOlJ723QbFrRPlK08WG3STL0IYyIxGmnYOyfI15t9oKaPYa',
'Z2jcNCpNNMLhJTJy1jog2idvrQ8PrgsPbbt
ynbD2xaj833zwmncgTDcKXkmNGWgr5Zpk82nxSM6NH5JwkncEtCaOoCf5iJwfteOtY3r1PpGjmSfWF7k0KBP0F45yO2','T9Ytu4olxEQEpCiKjpRRqfW7gm3lcJf5SBrF8f05tRBaXtuEbEaPt4OZiRJI
nqO727M2mF7YaJ654tCX7mw0iCQHgdbJRrQLyhNdwN9Mi7ResXAz9yssBI1jnc2EKK','SAtrnhEo3ooNJguTY4JOoMap5LHzRdCvo5fRnlGiQ45lcs68wPYcoyPlBilfCpQ0uxJZjR89X5m8xs8lHhLxX
ejjLKEP9YHvWcL54T6RasROkc9t9Nrg1QFEeLffuw','QGZnDT8JwbBOrns0g1NNZHhpXJAByw7P4Y4H15cygfEXQ6NzzfEYwwDJ7GW6CWNHuJQwO3mwasJtqbkp6yL2uRD2jk0v6F40QXojQQX0Irl8IT
xOSBIEeX8L788DF5','6acmH2DouXXgYp9T5TYNk2nsFutO00LyaxkrrxgdL3HttJEqofEOh1Hxn2Dn2addNMK6BsjYwtPhCYXsBdHkWaZISmxWCbzRpKZ04HZ0AqZEE56PiNADpbNHMKgQnf',
'ucrJdwc2oekMszRzG1EI64uLD8itaLPKyGgRC8Ttf5FX4aMDRTXQPPnePTOsjpCh5k0AlL3OQAnNk2c3XzTwHHQieqaLfnulNun8FiOas4xS6PNTOtPh2jOgayu5dg',
'itbTtiBIGvE5FKWfpvY6Saps6FjsLCZ3x0Zrac1jOFofbBIGyiMQG3BFJmzgqOjoGGXrlOQ9fEo6H7M7pcQyfT5y7wexLMddW8K7YmG3tx9AwNAdrcRN5YLCLs1i6f',
'BSfYc4As73h96dOGFGsifp7stSku2iZDB5dn9oXGr5Pxiq5xyxgTefDX7yQ9YrMjwsM5gmMXrfLZLQNBGvgmauIhkci8RwkGo6LKkbkR75tSNGYX34IdySAa4kjXgR',
'nLP1h2SPTbMEwmWS9PYR2Nn4prgOFLrui9vF3OhYr3nnh9FrbgATvyQkh69wJsjt1e9wuqTel8PR9ZIcDKy8IGksEtoamXRf2ScoAxQwxkFyHPRMlPMwZ6onsSDesh')
ERROR : Got temporary error 1234 'REDO log files overloaded (increase disk hardware)' from NDBCLUSTER
【ok】google很久，增加了那2个值，但是启动起来还是报很多错误，后来想到一个外招，换个库名字，--create-schema=ndb12，搞定。
作者: baiyaj 时间: 2012-12-5 14:55
楼主现在mysql cluster用得怎么样了，上成产了吗
作者: mchdba 时间: 2012-12-5 15:34
baiyaj 发表于 2012-12-5 14:55
楼主现在mysql cluster用得怎么样了，上成产了吗
还没有开始用，双十一 innodb主，cluster从，结果量大的时候，主从延时了，后来一看就不敢上了，现在还在做深入研究中， haiyaj朋友，你呢，做的怎么样了？
作者: zhangshengdong 时间: 2012-12-6 09:43
凶得啊，各种测试，各种实验啊，NICE。
我这边也没有上mysql cluster。关键还是觉得性能有差。
作者: baiyaj 时间: 2012-12-6 15:55
最近刚搭了一个测试环境，说实话，个人觉得mysql cluster的设计还是很好的，只是现在实现的还不够完美而已，问题还是很多；由于硬件条件、网络条件所限，根本不可能达到官网的基准测试达到的性能，上生产肯定没啥信心啊
作者: mchdba 时间: 2012-12-6 16:53
baiyaj 发表于 2012-12-6 15:55
最近刚搭了一个测试环境，说实话，个人觉得mysql cluster的设计还是很好的，只是现在实现的还不够完美而已， ...
在哪里可以看到 “官网的基准测试达到的性能”，提供个连接给我吧！
作者: baiyaj 时间: 2012-12-6 17:49
mchdba 发表于 2012-12-6 16:53
在哪里可以看到 “官网的基准测试达到的性能”，提供个连接给我吧！
MySQL集群基准测试.pdf(546.46 KB, 下载次数: 11)
你可以到mysql官网查看，还有其它的文档
附件: [mysql集群基准测试]
MySQL集群基准测试.pdf (2012-12-6 17:49, 546.46 KB) / 下载次数 11
http://www.itpub.net/forum.php?mod=attachment&aid=ODE3MzE1fDNlZWM1M2M3fDEzNTk2ODgxNjh8MHww
作者: grantguan 时间: 2012-12-7 11:11
mark 一下，方便以后查找
作者: 86673611 时间: 2012-12-7 11:36
看一看，谢谢
作者: xin1shou3 时间: 2013-1-17 03:07
baiyaj 发表于 2012-12-6 15:55
最近刚搭了一个测试环境，说实话，个人觉得mysql cluster的设计还是很好的，只是现在实现的还不够完美而已， ...
请问能够贴出你搭建测试环境的具体步骤吗？
谢谢！
baiyaj 47#
发表于 2012-12-6 15:55:38 |
最近刚搭了一个测试环境，说实话，个人觉得mysql cluster的设计还是很好的，只是现在实现的还不够完美而已，问题还是很多；由于硬件条件、网络条件所限，根本不可能达到官网的基准测试达到的性能，上生产肯定没啥信心啊
作者: mchdba 时间: 2013-1-17 22:29
xin1shou3 发表于 2013-1-17 03:07
请问能够贴出你搭建测试环境的具体步骤吗？
谢谢！
官方的测试的网络环境是万兆网卡。
| 欢迎光临 ITPUB论坛 (http://www.itpub.net/) | Powered by Discuz! X2 |
1869
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
