RAM 4 G Mysql Percona 5.1.43 XtraDB 9.1(ไม่รู้เรียกถูกปล่าว) เครื่อง Client cannot connect บ่อยมาก
ไม่รู้จะแก้ยังไงแล้วครับ แล้วถามอีกข้อนะครับ เปลี่ยนจากเครื่อง Slave มาเป็น Master ต้องแก้ไข้อะไรบ้างครับ
ขอบคุณครับ
# MySQL Config (my.cnf) For Server RAM 4 GB & Percona Server
# By Sothorn sothorn@gmail.com
# Modify 54-02-02 @kaibangrachan
###############################################################################
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set = tis620
[mysqld]
###############################################################################
#**** MySQL Replication Master
#server-id=1
#log-bin=/var/backup/log/mysql/mysql-bin.log
#binlog-do-db=database_name1
#binlog-do-db=database_name_2
#replicate-wild-do-table= %.%
#expire_logs_days=10
#max_binlog_size=256M
#innodb_flush_log_at_trx_commit=1
#sync_binlog=1
#slave-skip-errors=all
###########**** End Replication ################################################
###########****MySQL Serever ##################################################
port = 3306
socket = /var/lib/mysql/mysql.sock
back_log = 512 ## Default 50 ##### Command to check backlog is
# /sbin/sysctl net.ipv4.tcp_max_syn_backlog
## net.ipv4.tcp_max_syn_backlog = 1024
max_connections = 1000 ## Default 151
max_allowed_packet = 512M ### my.cnf = 16 MB Default 1048576 Range 1024-1073741824 Limit 1 GB
# table_cache = 2048 ## > MySQL version 5.1.3 instead table_open_cache
table_open_cache = 2048 # Default 64 #Range 1-524288 # if use MySQL version > 5.1.3 use table_cache instead table_open_cache
tmp_table_size = 256M ##5-10% RAM ## my.cnf 64 MB # Range 1024-4294967295
table_definition_cache = 2048 ##Default 256 ##Range 256-524288
max_heap_table_size = 1024M #Default 16777216 (16MB) Range 16384-18446744073709547520
sort_buffer_size = 8M ### original 8M Old Config 1 M ##Default 2097144 Max Value 18446744073709547520
join_buffer_size = 2M # Max 4 GB my.cnf 8 M
thread_cache_size = 8 ##Default 0 Range 0-16384
# You should try [number of CPUs]*(2..4) for thread_concurrency
##Core CPU * 2
thread_concurrency = 16 # Default 10 Range 1-512
query_cache_type = 1
query_cache_size = 64M ##query_cache_size= 10 % of RAM ## original = 64 MB
query_cache_limit = 2M ###5-10% RAM ## Original = 2 MB ##Default 1048576 (1 MB)
binlog_cache_size = 2M #The size of the cache to hold the SQL statements for the binary log during a transaction.
#Default 32768 Range 4096-18446744073709547520
long_query_time = 2 ## Default 10 my.cnf 1 MB
log-short-format
slow_query_log
log_bin_trust_function_creators = 1 ## Default FALSE ### Need for Replicate
##The options in the following list affect which statements are written to the binary log,
#and thus sent by a replication master server to its slaves.
#default-character-set = tis620 ## Old version
character-set-server = tis620
skip-name-resolve
skip-character-set-client-handshake
skip-external-locking
init_connect = 'SET NAMES tis620'
########### End MySQL Server #################################################
#########*** MyISAM Specific options ##########################################
#key_buffer = 30-40% of RAM #if user MyISAM engine key_buffer = 30-40% of RAM
key_buffer = 64M ## Original 32 MB
read_buffer_size = 2M ## Original 2MB
read_rnd_buffer_size = 16M ## Original 16 MB Old 4 MB
myisam_sort_buffer_size = 128M ## Original = 128 MB 10-20 % of RAM
myisam_max_sort_file_size = 10G ## Original 10 GB
myisam_repair_threads = 1
myisam_recover
concurrent_insert = 2
#########**** End MyISAM ######################################################
######### *** INNODB Specific options #########################################
default-storage-engine = INNODB
innodb_file_per_table ##InnoDB creates each new table using its own .ibd file for storing data and indexes
innodb_data_home_dir = /var/lib/mysql/
innodb_log_group_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_buffer_pool_size = 1800M ###innodb_buffer_pool_size = 70-80 % of RAM
innodb_additional_mem_pool_size = 16M ### Original 16 MB Not Change
innodb_file_io_threads = 4 ### This value is 4 for Unix.
innodb_log_file_size = 256M #### 64M-512M depending on server size ##25%-100% of your buffer pool size logfile size will
##increase the time needed for the recovery process
innodb_log_buffer_size = 1M ## The default value is 1MB. Sensible values range from 1MB to 8MB
innodb_thread_concurrency = 16 ## 2*(NumCPUs+NumDisks) โ€“ in theory # Default 8 Range 0-1000
innodb_log_files_in_group = 3 # Total number of files in the log group. A value of 2-3 is usually good enough
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
###innodb_doublewrite No need for slave
innodb_doublewrite = 1
innodb_flush_method = O_DIRECT
innodb_support_xa=0 # Enable By Default. You can disable this variable to reduce the number of disk flushes
#and get better InnoDB performance.
innodb_open_files = 2048 ## It specifies the maximum number of .ibd files that InnoDB can keep open at one time. Default 300
innodb_locks_unsafe_for_binlog=1
#innodb_use_sys_malloc = 1
#innodb_flush_log_at_trx_commit = 1 ## use with MySQL Replication
#########******* End InnoDB ###################################################
#########*****@Percona Specific options ######################################
#innodb_read_io_threads = 8 #Default Value 1 #Range 1-64
#innodb_write_io_threads = 8 # Default Value 1 #Range 1-64
#innodb_read_ahead = both #Default Value 'linear' # Allowed Values 'none', 'random'(*), 'linear', 'both'
#innodb_io_capacity = 4000 # Default Value 200 #Range 100 - 999999999
#innodb_adaptive_checkpoint = estimate #Default Value 'none'(~1.0.5), 'estimate'(1.0.6~)
#Allowed values 'none', 'reflex', 'estimate',
#'keep_average' or 0/1/2/3 (for compatibility)
#innodb_log_block_size = 512 #Default Value 512 #Units Bytes
#innodb_adaptive_flushing = false #Default Value TRUE #Range TRUE/FALSE
#innodb_checkpoint_age_target = 0 # Default Value 0 #Range 0+
#innodb_flush_neighbor_pages = 1 # Default Value 1 Range 0-1
#innodb_ibuf_max_size = 1024M ##Default Value Half the size of the InnoDB buffer pool
#Range 0 - Half the size of the InnoDB buffer pool #Units Bytes
#innodb_ibuf_accel_rate = 100 # Default Value 100 #Range 100 - 999999999
#innodb_ibuf_active_contract = 1 #Default Value 0(~1.0.5), 1(1.0.6~) #Range 0 - 1
#innodb_enable_unsafe_group_commit = 0 # Default Value 0 #Range 0 - 1
#innodb_flush_log_at_trx_commit_session = 3 # Default Value 3 #Range 0-3
############################## After That no need to edit #####################
[mysqldump]
# Do not buffer the whole result set in memory before writing it to
# file. Required for dumping very large tables
quick
max_allowed_packet = 512M
[mysql]
prompt=mysql-slave> \ ## MySQL Prompt
no-auto-rehash
default-character-set=tis620
# Only allow UPDATEs and DELETEs that use keys.
#safe-updates
[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
#socket = /var/run/mysqld/mysqld.sock #for Ubuntu
#nice = 0 # for Ubunutu
# Increase the amount of open files allowed per process. Warning: Make
# sure you have set the global system limit high enough! The high value
# is required for a large number of opened tables
open-files-limit = 32768
## Reference
# 1.
http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html# 2.
http://dev.mysql.com/doc/refman/5.1/en/mysqld-option-tables.html# 3. my-innodb-heavy-4G.cnf
# 4.
http://www.mysqlperformanceblog.com/2006/08/04/innodb-double-write/# 5.
http://www.percona.com/docs/wiki/percona-server:features:innodb_io?redirect=1# 6.
http://www.mysqlperformanceblog.com/2009/09/15/which-adaptive-should-we-use/#For the greatest possible durability and consistency in a replication setup using InnoDB with transactions,
#use innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1 in your master server my.cnf file.
แสดงกระทู้