2021年6月9日 星期三
2021年6月1日 星期二
MySQL NDB Cluster 實務規劃
Data Node 記憶體需求計算
官方的 Data Node 記憶體需求計算方式
(SizeofDatabase × NumberOfReplicas × 1.1 ) / NumberOfDataNodes 範例：(10G * 2 *1.1)/ 2 =11G
目前線上狀況
Data node 記憶體設定
DataMemory=200G IndexMemory=20G
Data node 記憶體實際使用狀況
上圖記憶體量計算方式 總共 6553600 pages，每個 page 32k 總 bytes = 6553600*32*1024=214748364800 總 G 數 = 214748364800 /1024/1024/1024 =200 G //實際使用狀況 Data memory=6098653 pages Bytes = 6098653*32*1024=199840661504 G 數 = 199840661504/1024/1024/1024=186.11G Index memory = 359457 pages Bytes = 359457*8*1024=2944671744 G 數 = 2.74 G
規劃目標4倍
Data node 記憶體設定
//目前 DataMemory 使用 186 G，取 192 G //目前 IndexMemory 使用 2.74 G，取 4 G DataMemroy 需求 = (192*4*2*1.1)/2= 844 G IndexMemory 需求 = (4*4*2*1.1)/2= 17.6 G #實際設定 1 規劃目標4倍 1 實體記憶體 1 T 2 系統保留 96G 3 DataMemory = 896G 4 IndexMemory= 32G #實際設定 2 規劃目標3.5倍 1 實體記憶體 768 G 2 系統保留 48G 3 DataMemory = 688G 4 IndexMemory= 32G
設定檔
config.ini
[tcp default] #The default size of the send buffer is 2MB, #which is the size recommended in most situations. #The minimum size is 64 KB; #the theoretical maximum is 4 GB SendBufferMemory=4M #Specifies the size of the buffer used when receiving data from the TCP/IP socket. #The default value of this parameter is 2MB. #The minimum possible value is 16KB; the theoretical maximum is 4GB. ReceiveBufferMemory=2M #通过网络传输消息ID(取值: Y/N或1/0) #This feature is disabled by default in production builds #SendSignalId=0 #启用该参数将在所有消息置于发送缓冲之前,为所有参数计算校验和. #默认禁止该特性.(取值: Y/N或1/0) #This feature is disabled by default in production builds #Checksum=0 [ndb_mgmd] #Management Node 1 HostName=10.10.100.157 DataDir=/var/lib/mysql-cluster [ndb_mgmd] #Management Node 2 HostName=10.10.100.199 DataDir=/var/lib/mysql-cluster [ndbd default] NoOfReplicas=2 # Number of replicas DataMemory=928G # Memory allocate for data storage #In NDB 7.5 and earlier, this parameter controls the amount of storage used for hash indexes in NDB Cluster #size = ( (fragments * 32K) + (rows * 18) ) # * fragment_replicas #fragments is the number of fragments, #fragment_replicas is the number of fragment replicas (normally two), #and rows is the number of rows. #If a table has one million rows, #eight fragments, and two fragment replicas, the expected index memory usage is calculated as shown here: #((8 * 32K) + (1000000 * 18)) * 2 = ((8 * 32768) + (1000000 * 18)) * 2 # = (262144 + 18000000) * 2 # = 18262144 * 2 = 36524288 bytes = ~35MB IndexMemory=32G #This parameter determines how much memory is allocated for strings such as table names #A value greater than 100 is interpreted as a number of bytes. #The default value is 25—that is, 25 percent of the default maximum. #StringMemory=25 DataDir=/var/lib/mysql-cluster //cluster 總表上限 //The default value of this parameter is 128. //The minimum is 8 and the maximum is 20320. //Each table object consumes approximately 20KB per node. MaxNoOfTables = 2048 //cluster 總共可定義多少屬性(總欄位上限) //The default value is 1000, with the minimum possible value being 32. //The maximum is 4294967039. //每個attrubute消耗200bytes per node MaxNoOfAttributes = 51200 //隨時保留多少ordered indexes 供使用 //Each unique index and primary key has both an ordered index and a hash index //MaxNoOfOrderedIndexes sets the total number of ordered indexes that can be in use in the system at any one time. //The default value of this parameter is 128. //Each index object consumes approximately 10KB of data per node. MaxNoOfOrderedIndexes = 2048 //For each unique index that is not a primary key, //a special table is allocated that maps the unique key to the primary key of the indexed table. //By default, an ordered index is also defined for each unique index. //To prevent this, you must specify the USING HASH option when defining the unique index. //The default value is 64. Each index consumes approximately 15KB per node. MaxNoOfUniqueHashIndexes = 2048 #每個 unique hash index 會產生 3個 triggers #每個 ordered index 會產生 1個 triggers #Internal update, insert, and delete triggers are allocated for each unique hash index. #(This means that three triggers are created for each unique hash index.) #However, an ordered index requires only a single trigger object. #Backups also use three trigger objects for each normal table in the cluster. #The default value is 768. MaxNoOfTriggers = 7168 #把 pages 鎖定在實體記憶體中 #這個不要打開，如果記憶體不夠，會錯誤 #LockPagesInMainMemory=0 //每個transaction 在每個data node都需要transaction record #TotalNoOfConcurrentTransactions = # (maximum number of tables accessed in any single transaction + 1) # * number of SQL nodes #The default value is 4096. MaxNoOfConcurrentTransactions = 409600 #要大於 MaxNoOfConcurrentTransactions #The default value is 32768. MaxNoOfConcurrentOperations = 3276800 #MaxNoOfLocalOperations 預設是 MaxNoOfConcurrentOperations*1.1 #For queries using a unique hash index, #another temporary set of operation records is used during a query's execution phase. #This parameter sets the size of that pool of records. #Only in rare cases of extremely high parallelism #using unique hash indexes should it be necessary to increase this value. #The default value of this parameter is 8192. MaxNoOfConcurrentIndexOperations=16384 #The memory affected by this parameter is used for #tracking operations fired when updating index tables and reading unique indexes. #This memory is used to store the key and column information for these operations. #It is only very rarely that the value for this parameter needs to be altered from the default #The default value for TransactionBufferMemory is 1MB. #TransactionBufferMemory=1M #The default value of MaxNoOfFiredTriggers is 4000, #which is sufficient for most situations. #In some cases it can even be decreased if the DBA feels certain the need for parallelism in the cluster is not high. #MaxNoOfFiredTriggers=4000 #This parameter is used to control the number of parallel scans that can be performed in the cluster. #Each transaction coordinator can handle the number of parallel scans defined for this parameter. #The default value of MaxNoOfConcurrentScans is 256. The maximum value is 500. MaxNoOfConcurrentScans=500 #This parameter specifies whether a data node process should exit or perform an automatic restart when an error condition is encountered. #This parameter's default value is 1; this means that, by default, an error causes the data node process to halt. #When an error is encountered and StopOnError is 0, the data node process is restarted. #StopOnError=1 #設定redo log 檔 size #The default value for this parameter is 16M FragmentLogFileSize=256M #The default parameter value is 16, NoOfFragmentLogFiles=16 #The default value for this parameter is 1MB. BackupMaxWriteSize = 2M #The default value for this parameter is 16MB. BackupDataBufferSize = 32M #The default value for this parameter is 16MB BackupLogBufferSize = 32M [ndbd] HostName=10.10.100.157 NodeId=3 [ndbd] HostName=10.10.100.199 NodeId=4 [mysqld] #Node 5 HostName=10.10.100.165 [mysqld] #Node 6 HostName=10.10.100.166 [mysqld] #Node 7 HostName=10.10.100.200 [mysqld] #Node 8 HostName=10.10.100.201
Data Node設定檔
/etc/my.cnf
[mysqld] ndbcluster ndb-connectstring=10.10.100.157 ndb-connectstring=10.10.100.199 [MYSQL_CLUSTER] ndb-connectstring=10.10.100.157 ndb-connectstring=10.10.100.199
SQL Node設定檔
/etc/my.cnf
[mysqld] ndbcluster ndb-connectstring=10.10.100.157 ndb-connectstring=10.10.100.199 default-storage-engine=NDBCLUSTER validate_password_length=6 validate_password_policy=LOW datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid max_connections=5000 max_connect_errors=1844674407370954751 connect_timeout=15 default-time-zone='+07:00' max_user_connections=4900 #slow query slow_query_log=1 slow_query_log_file=/var/lib/mysql/slow-query.log long_query_time=1 max_heap_table_size=1024M tmp_table_size=1024M [MYSQL_CLUSTER] ndb-connectstring=10.10.100.157 ndb-connectstring=10.10.100.199
Linux Command sysctl
sysctl
設定 linux kernel 變數
sysctl 變數
//顯示某個變數 shell> sysctl kernel.ostype //列出全部變數 shell> sysctl -a //grep 變數 shell> sysctl -a | grep tcp_timeout //改變數 shell> sysctl -w variable = value sysctl -w net.ipv4.ip_forward=1
修改設定檔 sysctl.conf
shell> vi /etc/sysct.conf //加入變數、值 net.ipv4.ip_forward=1
載入設定檔，不需重開機
//載入 /etc/sysct.conf shell> sysctl -p //重新載入以下所有檔案 shell> sysctl --system /run/sysctl.d/*.conf /etc/sysctl.d/*.conf /usr/local/lib/sysctl.d/*.conf /usr/lib/sysctl.d/*.conf /lib/sysctl.d/*.conf /etc/sysctl.confipv4 tcp 變數
訂閱：
文章 (Atom)