5分鐃叔でわ申鐃暑、MySQL鐃塾ワ申鐃緒申愀鐃緒申離鐃緒申紂種申縫鵐亜鐃�
MySQL鐃塾ワ申鐃遵ー鐃祝ングにわ申鐃緒申鐃緒申鐃緒申鐃祝緒申鐃竣となわ申鐃緒申鐃�(鐃出ッフワ申)鐃緒申連鐃塾パワ申瓠種申鐃緒申砲弔鐃緒申董鐃�
- 鐃緒申鐃遵ー鐃祝ングのポワ申鐃緒申鐃�
- DSAS鐃塾とわ申鐃緒申DB鐃緒申鐃緒申鐃緒申(鐃渋ワ申鐃緒申4GB)鐃塾実際わ申鐃緒申鐃緒申鐃緒申
鐃緒申泙箸鐃銃みまわ申鐃緒申
鐃殉わ申鐃緒申必鐃竣ワ申鐃緒申鐃緒申鐃緒申未侶彁鐃緒申鐃渋鰹申鐃粛わ申曚鐃緒申討覆鐃緒申鐃緒申鐃緒申鐃緒申奪鐃緒申鐃緒申討鐃緒申鐃暑ス鐃緒申鐃緒申廛箸鐃述介しまわ申鐃緒申
鐃緒申鐃藷、誌申鐃粛にわ申鐃銃みてわ申鐃緒申鐃緒申鐃緒申鐃緒申
鐃殉わ申鐃叔緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃�
鐃出ッフワ申鐃祝わ申2鐃縦のワ申鐃緒申鐃竣わ申鐃緒申鐃緒申泙鐃緒申鐃�
- 鐃緒申鐃緒申鐃緒申鐃出ワ申丱奪侫鐃�
- 鐃緒申鐃緒申奪疋丱奪侫鐃�
鐃緒申鐃緒申鐃緒申鐃出ワ申丱奪侫鐃緒申鐃�mysqld鐃緒申鐃塾でわ申鐃塾バッフワ申鐃緒申1鐃縦わ申鐃緒申鐃緒申鐃楯わ申鐃緒申鐃緒申里如鐃� 鐃緒申鐃緒申鐃緒申个鐃緒申鐃� 鐃緒申鐃緒申奪疋丱奪侫鐃緒申魯鐃緒申鐃獣ワ申(鐃緒申鐃粛ワ申鐃緒申鐃緒申鐃�)鐃緒申鐃夙に鰹申鐃楯わ申鐃緒申鐃緒申里任鐃緒申鐃�
鐃緒申鐃遵ー鐃祝ングの際にはワ申鐃緒申鐃緒申鐃出ワ申/鐃緒申鐃緒申奪匹琉磴わ申鐃春種申鐃緒申鐃緒申茲�鐃祝わ申鐃殉わ申鐃順う鐃緒申 鐃淑わ申鐃淑ら、鐃緒申鐃緒申奪疋丱奪侫鐃緒申鐃渋随申鐃緒申離鐃緒申鐃緒申鐃緒申鐃緒申鐃銃わ申函鐃緒申鐃緒申優鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃夙わ申鐃緒申縫鐃緒申辰箸鐃緒申鐃緒申屬縫鐃緒申鐃緒申鐃渋�鐃祝なってわ申鐃殉わ申鐃緒申鐃緒申任鐃緒申鐃�
innodb_buffer_pool_size
- 鐃緒申鐃緒申
- InnoDB鐃塾デ￥申鐃緒申鐃巡イ鐃緒申妊奪鐃緒申鐃緒申鬟�ワ申奪鐃緒申紊刻申襪随申鐃塾ワ申鐃緒申鐃緒申鐃塾逸申任鐃緒申鐃�
- 鐃出ッフワ申鐃緒申鐃緒申
- 鐃緒申鐃緒申鐃緒申鐃出ワ申
- DSAS鐃緒申
- 512MB
鐃緒申鐃緒申鐃緒申鐃出ワ申丱奪侫鐃緒申覆里如鐃緒申匹鐃緒申鐃夙鰹申鐃緒申鐃緒申討討鐃緒申鐃緒申鐃緒申鐃緒申鐃�
MySQL AB鐃塾ドワ申鐃緒申鐃緒申箸砲蓮鐃緒申鐃旬ワ申鐃緒申鐃�80%鐃夙わ申颪�わ申討鐃緒申泙鐃緒申鐃�
innodb_additional_mem_pool_size
- 鐃緒申鐃緒申
- InnoDB鐃緒申鐃緒申鐃緒申鐃叔￥申鐃緒申鐃淑どわ申鐃楯誌申鐃緒申鐃暑た鐃緒申鐃緒申琉鐃叔わ申鐃緒申
- 鐃出ッフワ申鐃緒申鐃緒申
- 鐃緒申鐃緒申鐃緒申鐃出ワ申
- DSAS鐃緒申
- 20MB
鐃緒申鐃緒申曚鐃緒申鐃緒申未乏鐃緒申鐃緒申鐃銃わ申必鐃竣はわ申鐃緒申泙鐃緒申鐃渋�鐃緒申覆鐃緒申覆辰鐃緒申薀�鐃初ー鐃緒申鐃緒申鐃祝わ申鐃塾旨￥申鐃駿告が出まわ申鐃塾で￥申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃巡せ鐃出わ申鐃緒申鐃塾ではなわ申鐃緒申鐃夙思わ申鐃殉わ申鐃緒申
innodb_log_buffer_size
- 鐃緒申鐃緒申
- InnoDB鐃塾刻申鐃緒申鐃緒申鐃緒申鐃緒申録鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申琉鐃叔わ申鐃緒申
- 鐃出ッフワ申鐃緒申鐃緒申
- 鐃緒申鐃緒申鐃緒申鐃出ワ申
- DSAS鐃緒申
- 16MB
鐃緒申鐃緒申鐃銃わ申鐃緒申8MB鐃緒申多鐃緒申鐃銃わ申64MB鐃叔緒申分鐃叔￥申鐃緒申鐃緒申泙鐃渋随申鐃緒申鐃緒申鐃宿�鐃竣はわ申鐃緒申泙鐃緒申鐃� 鐃淑わ申鐃淑ら、鐃出ッフワ申鐃熟トワ申鵐競鐃緒申鐃緒申鐃緒申COMMIT鐃緒申鐃緒申襪器申函鐃緒申泙鐃緒申鐃緒申鐃緒申奪妊鐃緒申鐃緒申鐃緒申縫侫鐃獣ワ申鐃遵さ鐃緒申鐃塾で￥申鐃循わ申鐃塾パワ申瓠種申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃循わ申鐃緒申鐃緒申鐃緒申鐃叔わ申鐃緒申
innodb_log_file_size
- 鐃緒申鐃緒申
- InnoDB鐃塾刻申鐃緒申鐃緒申鐃緒申鐃緒申録鐃緒申鐃緒申妊鐃緒申鐃緒申鐃緒申鐃塾フワ申鐃緒申鐃緒申任鐃緒申鐃�
鐃緒申鐃緒申任呂覆鐃緒申里任鐃緒申鐃緒申鐃緒申紂種申縫鵐阿両鐃叔緒申鐃竣なので駕申鐃盾し鐃殉わ申鐃緒申 - 鐃出ッフワ申鐃緒申鐃緒申
- ---
- DSAS鐃緒申
- 128MB
鐃順き鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃術フワ申鐃緒申鐃殉ンスわ申鐃緒申鐃遵し鐃殉わ申鐃緒申
鐃淑わ申鐃淑ら、innodb_log_file鐃緒申鐃緒申鐃獣ぱわ申鐃祝なわ申函鐃緒申鐃緒申鐃緒申鐃�innodb_buffer_pool鐃緒申鐃緒申旅鐃緒申鐃緒申鐃緒申譴随申鐃淑�鐃塾デ￥申鐃緒申鐃薯、デワ申鐃緒申鐃緒申鐃緒申鐃�InnoDB鐃塾デ￥申鐃緒申鐃春ワ申鐃緒申鐃緒申暴颪�出わ申鐃緒申鐃緒申鐃淳になってわ申鐃暑か鐃緒申任鐃緒申鐃�
鐃叔わ申鐃塾で￥申innodb_buffer_pool_size鐃緒申鐃順き鐃緒申鐃緒申鐃緒申鐃初、innodb_log_file_size鐃盾あ鐃緒せ鐃緒申調鐃緒申鐃緒申鐃淑わ申鐃夙パフワ申鐃緒申鐃殉ンスわ申鐃緒申鐃遵し鐃殉わ申鐃緒申
innodb_log_file_size鐃緒申鐃粛は￥申1MB鐃淑常申如鐃�32bit鐃殉ワ申鐃緒申両鐃緒申鐃�4GB鐃淑駕申鐃祝わ申鐃淑わ申鐃緒申个覆鐃淑わ申鐃緒申MySQL AB鐃塾ドワ申鐃緒申鐃緒申箸砲禄颪い討鐃緒申鐃殉わ申鐃緒申
鐃殉わ申鐃緒申鐃盾う鐃述とつ常申造鐃緒申鐃緒申鐃殉わ申鐃緒申innodb_log_file鐃緒申innodb_log_files_in_group鐃塾随申(鐃叔フワ申鐃緒申箸鐃�2)鐃緒申鐃緒申鐃縦わ申鐃緒申鐃緒申里任鐃緒申鐃緒申鐃�innodb_log_file_size鐃緒申innodb_log_files_in_group鐃緒申innodb_buffer_pool_size鐃緒申曚鐃緒申討發わ申鐃緒申泙鐃緒申鐃�
鐃殉とわ申鐃夙わ申鐃塾よう鐃祝なわ申泙鐃緒申鐃�
1MB < innodb_log_file_size < MAX_innodb_log_file_size < 4GB MAX_innodb_log_file_size = innodb_buffer_pool_size * 1/innodb_log_files_in_group
鐃盾う鐃述とわ申鐃緒申鐃春わ申鐃淑わ申鐃緒申个覆鐃淑わ申鐃塾は￥申innodb_log_file_size鐃緒申鐃順き鐃緒申鐃緒申鐃緒申个鐃緒申鐃循ど￥申InnoDB鐃塾ワ申鐃緒申奪鐃緒申鐃所カ鐃出ワ申了鐃緒申屬鐃縦刻申鐃緒申鐃緒申鐃緒申鐃処う鐃祝なわ申箸鐃緒申鐃緒申鐃緒申任鐃緒申鐃�
sort_buffer_size
- 鐃緒申鐃緒申
- ORDER BY鐃緒申GROUP BY鐃塾とわ申鐃祝使わ申鐃緒申鐃緒申鐃緒申鐃緒申琉鐃叔わ申鐃緒申
- 鐃出ッフワ申鐃緒申鐃緒申
- 鐃緒申鐃緒申奪鐃�
- DSAS鐃緒申
- 2MB
鐃緒申鐃緒申奪疋丱奪侫鐃緒申覆里如鐃緒申鐃緒申澆鐃緒申腓�鐃緒申鐃緒申鐃緒申肇鐃緒申蠅�足鐃緒申覆鐃緒申覆鐃塾わ申鐃緒申鐃春でわ申鐃緒申
DSAS鐃叔わ申2MB鐃緒申4MB鐃祝わ申鐃銃わ申鐃殉わ申鐃緒申
read_rnd_buffer_size
- 鐃緒申鐃緒申
- 鐃緒申鐃緒申鐃夙醐申縫譽鰹申鐃緒申匹鐃緒申匹鐃夙わ申鐃祝使わ申鐃緒申鐃緒申鐃緒申鐃緒申琉鐃叔わ申鐃緒申
鐃叔ワ申鐃緒申鐃緒申I/O鐃緒申鐃緒申鐃緒申里鐃�ORDER BY鐃緒申鐃緒申能鐃緒申鐃遵が鐃緒申鐃峻でわ申鐃殉わ申鐃緒申 - 鐃出ッフワ申鐃緒申鐃緒申
- 鐃緒申鐃緒申奪鐃�
- DSAS鐃緒申
- 1MB
鐃緒申鐃緒申皀刻申鐃獣ドバッフワ申鐃淑ので￥申鐃緒申鐃緒申鐃緒申堂瓩�鐃祝わ申鐃緒申鐃春わ申必鐃竣でわ申鐃緒申
DSAS鐃叔わ申512KB鐃緒申2MB鐃祝わ申鐃銃わ申鐃殉わ申鐃緒申
join_buffer_size
- 鐃緒申鐃緒申
- 鐃緒申鐃緒申妊奪鐃緒申鐃緒申鐃緒申僂鐃緒申覆鐃緒申董鐃緒申屮鐃緒申鐃塾とわ申鐃祝使わ申鐃緒申鐃緒申鐃緒申鐃緒申琉鐃叔わ申鐃緒申
- 鐃出ッフワ申鐃緒申鐃緒申
- 鐃緒申鐃緒申奪鐃�
- DSAS鐃緒申
- 256KB
鐃緒申鐃緒申奪疋丱奪侫鐃緒申任鐃緒申鐃�
鐃緒申鐃盾そ鐃盾イ鐃緒申妊奪鐃緒申鐃緒申鐃緒申箸鐃緒申覆鐃緒申茲�鐃淑テ￥申鐃瞬ワ申鐃緒申魯僖侫鐃緒申鐃緒申泪鵐晃鐃緒申鐃塾器申鐃緒申鐃緒申鐃初す鐃緒申鐃緒申鬚韻鐃駿わ申鐃淑ので￥申鐃緒申鐃塾パワ申瓠種申鐃緒申呂鐃緒申鐃循わ申鐃順き鐃緒申鐃緒申鐃緒申必鐃竣はなわ申鐃叔わ申鐃順う鐃緒申
read_buffer_size
- 鐃緒申鐃緒申
- 鐃緒申鐃緒申妊奪鐃緒申鐃緒申鐃緒申僂鐃緒申覆鐃緒申董鐃緒申屮襯刻申鐃緒申鐃緒申里箸鐃緒申忙箸鐃緒申鐃緒申鐃緒申鐃緒申鐃塾逸申任鐃緒申鐃�
- 鐃出ッフワ申鐃緒申鐃緒申
- 鐃緒申鐃緒申奪鐃�
- DSAS鐃緒申
- 1MB
鐃緒申鐃緒申癲�鐃術フワ申鐃緒申鐃殉ンスわ申佑鐃緒申鐃淑わ申弌鐃緒申鐃緒申鐃叔ッワ申鐃緒申鐃緒申箸鐃緒申茲�鐃淑ワ申鐃緒申鐃緒申鐃夙�鐃峻わ申鐃緒申戮鐃緒申覆里如鐃緒申鐃緒申鐃循わ申多鐃緒申鐃緒申鐃緒申必鐃竣はなわ申鐃叔わ申鐃順う鐃緒申
key_buffer_size
- 鐃緒申鐃緒申
- MyISAM鐃塾ワ申鐃緒申妊奪鐃緒申鐃緒申鬟�ワ申奪鐃緒申紊刻申鐃緒申鐃緒申鐃緒申鐃塾逸申任鐃緒申鐃�
- 鐃出ッフワ申鐃緒申鐃緒申
- 鐃緒申鐃緒申鐃緒申鐃出ワ申
- DSAS鐃緒申
- 256MB
鐃緒申鐃緒申鐃緒申鐃出ワ申丱奪侫鐃緒申任鐃緒申鐃�
多鐃緒申鐃緒申鐃緒申鐃緒申討鐃緒申鐃緒申鐃緒申僖侫鐃緒申鐃緒申泪鵐垢鐃緒申鐃緒申紊件申泙鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申丱鐃出ッフワ申鐃淑のでどわ申鐃緒申罰鐃緒申鐃緒申鐃銃わ申鐃殉わ申鐃緒申
鐃盾し鐃緒申MyISAM鐃緒申(鐃緒申鐃殉わ申)鐃夙ってなわ申鐃塾なわ申弌鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申討曚鐃緒申離僖鐃潤ー鐃緒申鐃祝回すのもア鐃緒申任鐃緒申鐃�
鐃緒申鐃淑みに￥申鐃緒申鐃緒申鐃緒申鐃緒申奪鐃緒申鐃塾ヒッワ申率鐃熟￥申SHOW STATUS鐃緒申鐃粛わ申箸辰董鐃緒申焚鐃緒申亮鐃緒申濃鐃緒申个任鐃緒申泙鐃緒申鐃�
鐃緒申鐃緒申鐃緒申鐃緒申奪鐃緒申鐃塾ヒッワ申率 = 100 - ( key_reads / key_read_requests 鐃緒申 100 )
myisam_sort_buffer_size
- 鐃緒申鐃緒申
- MyISAM鐃叔以駕申鐃塾誌申鐃塾ワ申鐃緒申妊奪鐃緒申鐃緒申離鐃緒申鐃緒申箸忙箸鐃緒申鐃緒申鐃緒申鐃緒申鐃塾逸申任鐃緒申鐃�
- REPAIR TABLE
- CREATE INDEX
- ALTER INDEX
- 鐃出ッフワ申鐃緒申鐃緒申
- 鐃緒申鐃緒申奪鐃�
- DSAS鐃緒申
- 1MB
鐃縮常申離鐃緒申鐃緒申鐃�(DML)鐃叔は使わ申鐃淑わ申鐃処う鐃淑ので￥申鐃緒申鐃緒申曚鐃渋随申鐃緒申鐃緒申鐃宿�鐃竣はなわ申鐃叔わ申鐃順う鐃緒申
mymemcheck - 鐃緒申鐃緒申鐃熟�鐃塾ワ申鐃緒申鐃獣ワ申鐃縦￥申鐃緒申
鐃叔醐申鐃�mymemcheck鐃夙わ申鐃緒申鐃緒申鐃緒申鐃緒申鐃塾ツ￥申鐃緒申鐃述介しまわ申鐃緒申
鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申匹呂鐃緒申鐃緒申蕕�鐃初。
- mymemcheck v1.01 (gzip)
mymemcheck鐃熟￥申my.cnf鐃盾し鐃緒申鐃緒申SHOW VARIABLES鐃塾件申未鮓気法鐃緒申焚鐃緒申鐃�3鐃縦のワ申鐃緒申鐃獣ワ申鐃緒申圓鐃緒申泙鐃緒申鐃�
- 鐃緒申鐃緒申鐃宿�鐃竣わ申物鐃緒申鐃緒申鐃緒申鐃緒申腓�鐃緒申
- IA-32鐃緒申Linux鐃叔のヒ￥申鐃竣ワ申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申
- innodb_log_file_size鐃塾削申鐃順サ鐃緒申鐃緒申
鐃渋行件申鐃緒申鐃熟わ申鐃塾よう鐃祝なわ申泙鐃緒申鐃�
$ ./mymemcheck my.cnf
[ minimal memory ]
ref
* 鐃緒申High Performance MySQL鐃緒申, Solving Memory Bottlenecks, p125
global_buffers
key_buffer_size 268435456 256.000 [M]
innodb_buffer_pool_size 536870912 512.000 [M]
innodb_log_buffer_size 16777216 16.000 [M]
innodb_additional_mem_pool_size 20971520 20.000 [M]
net_buffer_length 16384 16.000 [K]
thread_buffers
sort_buffer_size 2097152 2.000 [M]
myisam_sort_buffer_size 1048576 1024.000 [K]
read_buffer_size 1048576 1024.000 [K]
join_buffer_size 262144 256.000 [K]
read_rnd_buffer_size 1048576 1024.000 [K]
max_connections 250
min_memory_needed = global_buffers + (thread_buffers * max_connections)
= 843071488 + 5505024 * 250
= 2219327488 (2.067 [G])
[ 32bit Linux x86 limitation ]
ref
* http://dev.mysql.com/doc/mysql/en/innodb-configuration.html
* need to include read_rnd_buffer.
* no need myisam_sort_buffer because allocate when repair, check alter.
2G > process heap
process heap = innodb_buffer_pool + key_buffer
+ max_connections * (sort_buffer + read_buffer + read_rnd_buffer)
+ max_connections * stack_size
= 536870912 + 268435456
+ 250 * (2097152 + 1048576 + 1048576)
+ 250 * 262144
= 1919418368 (1.788 [G])
2G > 1.788 [G] ... safe
[ maximum size of innodb_log_file_size ]
ref
* http://dev.mysql.com/doc/mysql/en/innodb-start.html
1MB < innodb_log_file_size < MAX_innodb_log_file_size < 4GB
MAX_innodb_log_file_size = innodb_buffer_pool_size * 1/innodb_log_files_in_group
= 536870912 * 1/2
= 268435456 (256.000 [M])
innodb_log_file_size < MAX_innodb_log_file_size
134217728 < 268435456
128.000 [M] < 256.000 [M] ... safe
鐃緒申鐃緒申鐃緒申鐃�MySQL AB鐃塾ドワ申鐃緒申鐃緒申箸覆匹暴颪�わ申討鐃緒申鐃緒申鐃緒申損鐃緒申燹�鐃緒申鐃塾ままでわ申鐃緒申
鐃術ワ申瓠種申鐃銃縁申里鐃緒申愀鐃緒申鐃緒申鐃緒申辰討鐃緒申襪鰹申箸鐃渋随申鐃緒申鐃緒申鐃塾で￥申鐃術ワ申瓠種申鐃緒申鐃緒申儿鐃緒申鐃緒申鐃楯は￥申鐃緒申鐃塾よう鐃淑ワ申鐃緒申鐃獣ワ申鐃縦￥申鐃緒申燃鐃叔э申鐃緒申鐃塾わ申鐃緒申鐃緒申鐃塾ではなわ申鐃緒申鐃夙思わ申鐃殉わ申鐃緒申
