????3.2 ????????
?????????MySQL????????linux?????????????????Щ??????????MySQL??????????linux??????????????
????net.ipv4.tcp_fin_timeout = 30
????#TIME_WAIT???????????60s
????net.ipv4.tcp_tw_reuse = 1
????#1???????????????TIME_WAIT socket?????????μ?TCP?????0??????
????net.ipv4.tcp_tw_recycle = 1
????#1???????TIME_WAIT socket????????0??????
????net.ipv4.tcp_max_tw_buckets = 4096
????#??????TIME_WAIT socket?????????????????????????????????ЩTIME_WAIT????????????
????net.ipv4.tcp_max_syn_backlog = 4096
????#????SYN???д?????????г??????????????????
??????linux???У???????????????????????????????1024???????“too many files open”???????????????????????????
????# vi /etc/security/limits.conf #?????????????*??????????????????????????????????Ч
????* soft nofile 65535
????* hoft nofile 65535
????# ulimit -SHn 65535 #??????Ч
????3.3 ???????
?????????????棬?????????????linux?????????з?????????????????????????棩???????????????????????д????????????????????綹??????С?????????????????sync??????????????????????????????????仺????????????????????????????????????????????????
????SSD??????SAS??????RAID????????RAID1+0???????RAID1??RAID5?и?????д?????IOPS??????????????????????????I/O???檔
????4????????????
?????????????????????????????????????????????????????????????????????????~~~???????????????????????????I/O?????????????????У????????I/O????????
????4.1 ??????????д????
????????????????У???????????????????????????????????????????д????????????????????????????????????????????????????????????????LVS??HAProxy??Nginx???????????д?????????????????????????????д????Ч?????????????????????????????д???????????y?????????????????MySQL Proxy??Amoeba??????????????????У????????????????????????????????????????????????????????????2000 QPS?????5??????1w QPS???????????????????????
???????????????д???????????????д???????????????????????????????????????????????????????????????ó????????????п????????????????????2????????????????????????????????????????????MySQL????洢??????????????????????????????????????????????????????????????????perl?????????????????????????MySQL-MMM??Master-Master replication managerfor Mysql??Mysql????????????????????????????????????????????????д????????Ч?????????????
???????????????http://lizhenliang.blog.51cto.com/7876557/1290431
??????д???????http://lizhenliang.blog.51cto.com/7876557/1305083
????MySQL-MMM?????http://lizhenliang.blog.51cto.com/7876557/1354576
????4.2 ???????
????????????????????????????????浽????У??????滺??????????????????????????з????????????????????????б?????????????棬????????????????浽?????????????л???????У???????????????溣????????????????????????????????memcached??redis??memcached??????????????????????У??????QPS???8w?????????????????????redis???????????memcached??
?????????????

????4.3 ???
????????????????????????з?????????????У?????web??bbs??blog????????????????????з??????????????????????????????????
????4.4 ???
?????????????????????????????????м???????????????2???????????????????????????????????????????????????С?????????????????????????Ч??????????????
??????????????鷳???????????????SQL????????????????????????????merge?洢????????????????????????????????????в????????????????????????Щ????????????????????????????????????????????????????С???????????????????????I/O?????
??????????????????????
??????????????????????????ε???????????????????????????????????ε????????????У??????????ζ???????????У????????????е???η???????С?
??????????????????????????????????????????????????????????????
????4.5 ????
???????????????????????????飬??Щ?????????????????????????????????????????????????????????????????????λ?????????????????????????????????????????I/O??д?????????????
?????????????楨?????????????????????????
????5??????????
??????????????????????????DBA??????????????????????????????????????????????????
????5.1 ????????????
????QPS??Queries Per Second???????????????????????????????????
????TPS??Transactions Per Second?????????????
???????show status??????????????300???????????????????м?????????????????QPS??TPS?????￡?
????Uptime??????????????е???????λ??
????Questions???????????????????
????Com_select???????????????????????
????Com_insert?????????
????Com_delete?????????
????Com_update?????′???
????Com_commit?????????
????Com_rollback?????????
????????????????????????Questions?????QPS??
????mysql> show global status like 'Questions';
????mysql> show global status like 'Uptime';
????QPS = Questions / Uptime
????????Com_commit??Com_rollback?????TPS??
????mysql> show global status like 'Com_commit';
????mysql> show global status like 'Com_rollback';
????mysql> show global status like 'Uptime';
????TPS = (Com_commit + Com_rollback) / Uptime
??????????????????Com_select??Com_insert??Com_delete??Com_update?????QPS
????mysql> show global status where Variable_name in('com_select'??'com_insert'??'com_delete'??'com_update');
???????1??????У???????????????????????????????ζ????????????QPS
????TPS????????
????mysql> show global status where Variable_name in('com_insert'??'com_delete'??'com_update');
????????TPS????????????????????????????????????????ɡ?
???????????????????????????ó????????????myisam???????????Questions???????????????????innodb????????????Com_*??????????
????5.2 ????????????
????MySQL????????????????????????SQL????????????set?????????????????Ч????????my.cnf????????Ч??
????mysql> set global slow-query-log=on #?????????????
????mysql> set global slow_query_log_file='/var/log/mysql/mysql-slow.log'; #??????????????λ??
????mysql> set global log_queries_not_using_indexes=on; #?????????????????
????mysql> set global long_query_time=1; #???????????1s??????????
?????????????????????????MySQL?????mysqldumpslow?????????????????????
????# mysqldumpslow -t 3 /var/log/mysql/mysql-slow.log #??????????????
????????????percona?????pt-query-digest??????????????????棬?????slow log??binlog??general log??
??????????????????pt-query-digest /var/log/mysql/mysql-slow.log
????????binlog?????mysqlbinlog mysql-bin.000001 >mysql-bin.000001.sql
????pt-query-digest –type=binlog mysql-bin.000001.sql
????????????????pt-query-digest –type=genlog localhost.log
????5.3 ???????
?????????????????????????????????????????????????????????????????????G?????????????????????????????Ч??????????????????????????????????????????????????????????mysqldump??mysqlhotcopy??xtrabackup???mysqldump?????????С?????????????????????????????????????????mysqlhotcopy??xtrabackup????????????????????????????????????????????????????????xtrabackup??????????????
????Xtrabackup?????????ò????http://lizhenliang.blog.51cto.com/7876557/1612800
????5.4 ????????
?????????MySQL??????????硢?????????±????????????????????????????MySQL?????????????????????myisamchk??mysqlcheck??
????myisamchk????????myisam?????????????
???????ò?????
????-f –force ?????????????????????????????
????-r –recover ?????
????-q –quik ??????
????-a –analyze ??????
????-o –safe-recover ????????????-r???????????????????????
????-F –fast ????????????????
???????????weibo?????:
????# cd /var/lib/mysql/weibo
????# myisamchk -r -q *.MYI
????mysqlcheck??myisam??innodb????????????????????????????????????????????????????????????
???????ò?????
????-a –all-databases ??????е??
????-r –repair ?????
????-c –check ???????????
????-a –analyze ??????
????-o –optimize ?????
????-q –quik ??????????
????-F –fast ????????????????
???????????weibo?????:
????mysqlcheck -r -q -uroot -p123 weibo