|
Mysql Cluster 调优测试情况 测试环境：
10.37.2.101 管理节 10.37.2.102 Sql节点和数据节点 10.37.2.103 Sql节点和数据节点
一个管理节点，两个sql节点和两个数据节点
注：sql节点和数据节点共用一台机器，千兆网卡，千兆交换机（共用）
目前的两个文件的参数配置如下：
my.cnf 配置参数
# Options for mysqld process:
[mysqld]
skip-external-locking
skip-innodb //去掉不需要的引擎
skip-name-resolve //去掉域名解析
key_buffer_size = 512M
根据动态参数分析，我个人觉的已经够用了
| Key_blocks_unused | 428682 |
| Key_blocks_used | 376 |
| Key_read_requests | 597072 |
| Key_reads | 3 |
| Key_write_requests | 214842 |
| Key_writes | 163021 |
table_open_cache = 2048
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 128M
myisam_sort_buffer_size = 32M
thread_cache_size = 512
//500客户端并发操作时，Threads_created较大，增加thread_cache_size值为512
query_cache_size= 256M
//这个参数默认为0，设定该值后性能提升很多，这个参数很关键
bulk_insert_buffer_size = 512M
thread_concurrency = 8 # Try number of CPU's*2 for thread_concurrency
//根据cpu数*2设定
max_connections=1024
//500客户端并发的时候报“Too many connections”，提高到1024
back_log=200
tmp_table_size = 64M
long_query_time = 2
binlog_cache_size = 1M
max_allowed_packet = 128M
ndbcluster # run NDB storage engine
ndb-connectstring=10.37.2.101 # location of management server
# Options for ndbd process:
[mysql_cluster]
ndb-connectstring=10.37.2.101 # location of management server
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192
//其他的参数参考my-huge.cnf配置，并有所放大
config.ini 配置参数
#options affecting ndbd processes on all data nodes:
[ndbd default]
NoOfReplicas=2 # Number of replicas
# Data Memory, Index Memory, and String Memory #
DataMemory=1200M
IndexMemory=300M
//
# Transaction Parameters #
MaxNoOfConcurrentTransactions=4096 //默认大小
MaxNoOfConcurrentOperations=500000
//并发更新100万条记录，两个数据节点，100万/2个数据节点=50万
MaxNoOfLocalOperations=550000
//根据MaxNoOfLocalOperations= MaxNoOfConcurrentOperations * 1.1设定
# Transaction Temporary Storage #
MaxNoOfConcurrentIndexOperations=8192
MaxNoOfFiredTriggers=4000
TransactionBufferMemory=1M
# Scans and buffering #
MaxNoOfConcurrentScans=500
MaxNoOfLocalScans=1000
BatchSizePerLocalScan=64
LongMessageBuffer=1M
# Buffering and Logging
UndoIndexBuffer=8M
UndoDataBuffer=128M
RedoBuffer=64M
# Controlling Timeouts, Intervals, and Disk Paging #
TimeBetweenWatchDogCheck=6000
TimeBetweenWatchDogCheckInitial=6000
StartPartialTimeout=30000
StartPartitionedTimeout=60000
StartFailureTimeout=1000000
HeartbeatIntervalDbDb=2000
HeartbeatIntervalDbApi=3000
TimeBetweenLocalCheckpoints=20
TimeBetweenGlobalCheckpoints=2000
TransactionInactiveTimeout=0
TransactionDeadlockDetectionTimeout=1200
DiskSyncSize=4M
DiskCheckpointSpeed=10M
DiskCheckpointSpeedInRestart=100M
ArbitrationTimeout=10
# Logging and Checkpointing #
NoOfFragmentLogFiles=300 // 有‘REDO log files overloaded’报错，参考config.huge.ini文件配置300
FragmentLogFileSize=16M
MaxNoOfOpenFiles=40
InitialNoOfOpenFiles=27
MaxNoOfSavedMessages=25
# Backup Parameters #
BackupMemory=64M
//大多数参数参考config.huge.ini文件配置，部分参数值做了改动，但效果不是很明显。
# TCP/IP options:
[tcp default]
SendBufferMemory=2M
portnumber=2202 # This the default; however, you can use any port that is free
# for all the hosts in the cluster
# Note: It is recommended that you do not specify the port
# number at all and allow the default value to be used instead
# Management process options:
[ndb_mgmd]
hostname=10.37.2.101 # Hostname or IP address of management node
datadir=/var/lib/mysql-cluster # Directory for management node log files
# Options for data node "A":
[ndbd]
# (one [ndbd] section per data node)
hostname=10.37.2.102 # Hostname or IP address
datadir=/usr/local/mysql/data # Directory for this data node's data files
# Options for data node "B":
[ndbd]
hostname=10.37.2.103 # Hostname or IP address
datadir=/usr/local/mysql/data # Directory for this data node's data files
# SQL node options:
[mysqld]
hostname=10.37.2.102 # Hostname or IP address
# (additional mysqld connections can be
# specified for this node for various
# purposes such as running ndb_restore)
[mysqld]
hostname=10.37.2.103 # SQL NODE
目前存在的问题：
1、插入数据比较耗时（相对与查询和修改），Table_locks_waited不能立即获得的表的锁的次数，这个动态参数值比较高
| Table_locks_immediate | 665574 |
| Table_locks_waited | 99934
手册给出的解决方案：如果该值较高，并且有性能问题，你应首先优化查询，然后拆分表或使用复制。
2、整体的测试效果不理想，虽然和默认比有很大提高。下面是最近的测试数据：
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | ./mysqlslap --create-schema='test1' --concurrency=100 --auto-generate-sql-load-type=write --auto-generate-sql-guid-primary --number-int-cols=6 --number-char-cols=8 --auto-generate-sql --auto-generate-sql-write-number=1 --auto-generate-sql-unique-query-number=0 --engine=ndb,myisam --iterations=5 --number-of-queries=10000 --debug-info | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | ./mysqlslap --create-schema='test' --concurrency=1 --query=update.sql --engine=ndb --number-of-queries=10000 --iterations=5 --debug-info;
update.sql:update test15 set id=500 where id=500;（先插入1万条数据） | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | ./mysqlslap --create-schema='test' --concurrency=1 --query=select.sql --engine=ndb --number-of-queries=10000 --iterations=5 --debug-info;
select.sql:select id,last_name,first_name,sex,birthday,joindate,dep,salary,remark,A,B,C,D,E,F,G,H from test15 where id=5000; （先插入1万条数据） | | | | | | | | | | | | | | | | | | | |
| 
|Archiver|小黑屋|手机版|MariaDB社区
( 京ICP备07012489号 ) 
|
IP卡
狗仔卡
发表于 2011-5-17 11:07:57
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
转播
分享
淘帖
顶
踩
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
显身卡