之前安装时没注意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.
之前安装时没注意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.89
write 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.09
slow 10 s 0 0/s 0.00 %dms: 0.00 log: off
dms 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.00
com_ 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.10
range 2.75k 0.0/s 3.77
full join 0 0/s 0.00
range check 0 0/s 0.00
full rng join 0 0/s 0.00
sort scan 5.97k 0.0/s
sort range 4.30k 0.0/s
sort mrg pass 0 0/s
__ table locks _________________________________________________________
waited 24 0.0/s %total: 0.03
immediate 91.00k 0.1/s
__ tables ______________________________________________________________
open 36 of 64 %cache: 56.25
opened 42 0.0/s
__ connections _________________________________________________________
max used 5 of 100 %max: 5.00
total 2.52k 0.0/s
__ created temp ________________________________________________________
disk table 4.15k 0.0/s
table 7.11k 0.0/s size: 32.0m
file 5 0.0/s
__ threads _____________________________________________________________
running 1 of 1
cached 0 of 0 %hit: 0.04
created 2.52k 0.0/s
slow 0 0/s
__ aborted _____________________________________________________________
clients 0 0/s
connects 0 0/s
__ bytes _______________________________________________________________
sent 226.71m 164.8/s
received 12.59m 9.2/s
__ innodb buffer pool __________________________________________________
usage 304.00k of 8.00m %used: 3.71
read hit 84.42%
pages
free 493 %total: 96.29
data 19 3.71 %drty: 0.00
misc 0 0.00
latched 0 0.00
reads 77 0.0/s
from file 12 0.0/s 15.58
ahead rnd 1 0.0/s
ahead sql 0 0/s
writes 0 0/s
flushes 0 0/s
wait free 0 0/s
__ innodb lock _________________________________________________________
waits 0 0/s
current 0
time 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 0
pages
created 0 0/s
read 19 0.0/s
written 0 0/s
rows
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.myi 8.0k /usr/local/mysql/var/myblog/wp_posts.myi 8.0k /usr/local/mysql/var/myblog/wp_usermeta.myi 8.0k /usr/local/mysql/var/myblog/wp_commentmeta.myi 16k /usr/local/mysql/var/myblog/wp_options.myi 12k /usr/local/mysql/var/myblog/wp_postmeta.myi 8.0k /usr/local/mysql/var/myblog/wp_comments.myi 4.0k /usr/local/mysql/var/myblog/wp_links.myi 4.0k /usr/local/mysql/var/myblog/wp_term_relationships.myi 4.0k /usr/local/mysql/var/myblog/wp_users.myi 8.0k /usr/local/mysql/var/myblog/wp_terms.myi 16k /usr/local/mysql/var/mysql/help_relation.myi 4.0k /usr/local/mysql/var/mysql/time_zone_name.myi 16k /usr/local/mysql/var/mysql/help_keyword.myi 4.0k /usr/local/mysql/var/mysql/func.myi 4.0k /usr/local/mysql/var/mysql/time_zone.myi 20k /usr/local/mysql/var/mysql/help_topic.myi 4.0k /usr/local/mysql/var/mysql/columns_priv.myi 4.0k /usr/local/mysql/var/mysql/procs_priv.myi 4.0k /usr/local/mysql/var/mysql/time_zone_leap_second.myi 4.0k /usr/local/mysql/var/mysql/user.myi 4.0k /usr/local/mysql/var/mysql/tables_priv.myi 4.0k /usr/local/mysql/var/mysql/host.myi 4.0k /usr/local/mysql/var/mysql/time_zone_transition_type.myi 4.0k /usr/local/mysql/var/mysql/proc.myi 4.0k /usr/local/mysql/var/mysql/help_category.myi 4.0k /usr/local/mysql/var/mysql/db.myi 4.0k /usr/local/mysql/var/mysql/time_zone_transition.myi 192k 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运行状况适当运行参数.
【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:)！