之前安装时没注意mysql的优化,先想对mysql做一下优化.首先看一下没有优化之前各个参数:mysql预编译参数:
./configure --prefix=/usr/local/mysql --with-ssl --with-readline --with-big-tables --enable-assembler
top
32553 mysql 20 0 125m 17m 4064 s 0.0 1.7 5:13.01 mysqld
使用mysqlreport获取mysql运行参数:
mysql 5.0.40-log uptime 15 22:1:21 sat feb 4 10:04:23 2012__ key _________________________________________________________________buffer used 62.00k of 16.00m %used: 0.38 current 1.90m %usage: 11.89write hit 22.29%read hit 99.83%__ questions ___________________________________________________________total 85.58k 0.1/s dms 77.61k 0.1/s %total: 90.69 com_ 5.37k 0.0/s 6.28 com_quit 2.52k 0.0/s 2.95 +unknown 78 0.0/s 0.09slow 10 s 0 0/s 0.00 %dms: 0.00 log: offdms 77.61k 0.1/s 90.69 select 72.77k 0.1/s 85.03 93.76 update 2.68k 0.0/s 3.13 3.45 insert 1.09k 0.0/s 1.27 1.41 delete 1.07k 0.0/s 1.25 1.38 replace 0 0/s 0.00 0.00com_ 5.37k 0.0/s 6.28 set_option 2.60k 0.0/s 3.04 change_db 2.52k 0.0/s 2.94 show_fields 77 0.0/s 0.09__ select and sort _____________________________________________________scan 5.17k 0.0/s %select: 7.10range 2.75k 0.0/s 3.77full join 0 0/s 0.00range check 0 0/s 0.00full rng join 0 0/s 0.00sort scan 5.97k 0.0/ssort range 4.30k 0.0/ssort mrg pass 0 0/s__ table locks _________________________________________________________waited 24 0.0/s %total: 0.03immediate 91.00k 0.1/s__ tables ______________________________________________________________open 36 of 64 %cache: 56.25opened 42 0.0/s__ connections _________________________________________________________max used 5 of 100 %max: 5.00total 2.52k 0.0/s__ created temp ________________________________________________________disk table 4.15k 0.0/stable 7.11k 0.0/s size: 32.0mfile 5 0.0/s__ threads _____________________________________________________________running 1 of 1cached 0 of 0 %hit: 0.04created 2.52k 0.0/sslow 0 0/s__ aborted _____________________________________________________________clients 0 0/sconnects 0 0/s__ bytes _______________________________________________________________sent 226.71m 164.8/sreceived 12.59m 9.2/s__ innodb buffer pool __________________________________________________usage 304.00k of 8.00m %used: 3.71read hit 84.42%pages free 493 %total: 96.29 data 19 3.71 %drty: 0.00 misc 0 0.00 latched 0 0.00reads 77 0.0/s from file 12 0.0/s 15.58 ahead rnd 1 0.0/s ahead sql 0 0/swrites 0 0/sflushes 0 0/swait free 0 0/s__ innodb lock _________________________________________________________waits 0 0/scurrent 0time acquiring total 0 ms average 0 ms max 0 ms__ innodb data, pages, rows ____________________________________________data reads 25 0.0/s writes 3 0.0/s fsync 3 0.0/s pending reads 0 writes 0 fsync 0pages created 0 0/s read 19 0.0/s written 0 0/srows deleted 0 0/s inserted 0 0/s read 0 0/s updated 0 0/s
首先在预编译参数上进行优化
./configure --prefix=/usr/local/mysql \--without-debug \ # 取消调试模式提高性能--with-extra-charsets=utf8,gbk \ # 仅仅指定需要的默认字符集提高性能--enable-assembler \ # 使用汇编模式提高性能--with-mysqld-ldflags=-all-static \ # 以静态方式编译提高性能--with-client-ldflags=-all-static \--with-unix-socket-path=/tmp/mysql.sock \ # 使用unix socket提高性能--with-ssl
安装完成后进一步优化my.cnf:因为mysql 只会 cache 索引(*.myi)，因此您只要将数据库中所有的 myi 档案加总起来就是key buffer 的值,计算myi档案的总大小:
du -hc `find /usr/local/mysql/var/ -name *.myi`4.0k /usr/local/mysql/var/myblog/wp_term_taxonomy.myi8.0k /usr/local/mysql/var/myblog/wp_posts.myi8.0k /usr/local/mysql/var/myblog/wp_usermeta.myi8.0k /usr/local/mysql/var/myblog/wp_commentmeta.myi16k /usr/local/mysql/var/myblog/wp_options.myi12k /usr/local/mysql/var/myblog/wp_postmeta.myi8.0k /usr/local/mysql/var/myblog/wp_comments.myi4.0k /usr/local/mysql/var/myblog/wp_links.myi4.0k /usr/local/mysql/var/myblog/wp_term_relationships.myi4.0k /usr/local/mysql/var/myblog/wp_users.myi8.0k /usr/local/mysql/var/myblog/wp_terms.myi16k /usr/local/mysql/var/mysql/help_relation.myi4.0k /usr/local/mysql/var/mysql/time_zone_name.myi16k /usr/local/mysql/var/mysql/help_keyword.myi4.0k /usr/local/mysql/var/mysql/func.myi4.0k /usr/local/mysql/var/mysql/time_zone.myi20k /usr/local/mysql/var/mysql/help_topic.myi4.0k /usr/local/mysql/var/mysql/columns_priv.myi4.0k /usr/local/mysql/var/mysql/procs_priv.myi4.0k /usr/local/mysql/var/mysql/time_zone_leap_second.myi4.0k /usr/local/mysql/var/mysql/user.myi4.0k /usr/local/mysql/var/mysql/tables_priv.myi4.0k /usr/local/mysql/var/mysql/host.myi4.0k /usr/local/mysql/var/mysql/time_zone_transition_type.myi4.0k /usr/local/mysql/var/mysql/proc.myi4.0k /usr/local/mysql/var/mysql/help_category.myi4.0k /usr/local/mysql/var/mysql/db.myi4.0k /usr/local/mysql/var/mysql/time_zone_transition.myi192k total
修改my.cnf参数大小:
vi /etc/my.cnf# 降低key_buffer的值key_buffer = 4m
重启mysql执行top命令:
18125 mysql 20 0 109m 11m 2152 s 0.0 1.1 0:00.08 mysqld
看到mysql的内存利用率降低到1.1,这时候还不适宜执行mysqlreport查看等待启动一天后查看.由于现在访问量较低,所以参数适量调低,需要实时监控mysql运行状况适当运行参数.
原文地址：mysql优化笔记, 感谢原作者分享。