[root@slave ]# /usr/local/mysql/bin/mysqldumpslow -s -t 15 /root/db01-102-slow.log Reading mysql slow query log from 15 /root/db01-102-slow.logCan't open 15: 沒有那個文件或目錄 at /usr/local/mysql/bin/mysqldumpslow line 91.Count: 22Time=9.61s (211s)Lock=0.00s (0s)Rows=1.0 (22), [dubbo]@2hostsselect count(*)from coupon_lotleft join coupon on coupon.coupon_lot_id = coupon_lot.coupon_lot_idLEFT JOIN brandON brand.brand_id=coupon_lot.brand_idLEFT JOIN product_categoryon product_category.category_id=coupon_lot.category_idCount: 177Time=1.40s (247s)Lock=0.00s (0s)Rows=1.0 (177), [dubbo]@2hostsselect count(*) from user_infoLEFT JOIN system_region aON province_id =a.region_idLEFT JOIN system_region bON city_id = b.region_idLEFT JOIN system_region cON district_id = c.region_idCount: 22Time=13.20s (290s)Lock=0.00s (0s)Rows=10.0 (220), [dubbo]@2hostsselect coupon_id, coupon_lot.coupon_lot_id, coupon_lot.remark,coupon.code,coupon_lot.coupon_type, coupon.state,coupon_lot.ckey, coupon_lot.cvalue, coupon_lot.discount, coupon_lot.type, coupon_lot.channel, coupon_lot.is_reusable, coupon_lot.start_datetime,coupon_lot.end_datetime, coupon_lot.create_datetime, coupon.update_datetime ,coupon_lot.department,coupon_lot.state as couponLotState, coupon_lot.create_operator,coupon_lot.coupon_category,coupon_lot.pdt_code,brand.brand_name,product_category.category_namefrom coupon_lotleft join coupon on coupon.coupon_lot_id = coupon_lot.coupon_lot_idLEFT JOIN brandON brand.brand_id=coupon_lot.brand_idLEFT JOIN product_categoryon product_category.category_id=coupon_lot.category_idgroup by coupon_lot.coupon_lot_idorder by coupon_lot.create_datetime DESClimit N,NCount: 8Time=1.09s (8s)Lock=0.00s (0s)Rows=10.0 (80), [dubbo]@2hostsselect return_sn,relating_order_sn, return_from,oi.order_out_sn ,add_time , return_type, return_pay, ui.usename as userName, return_consignee, return_goods_count ,return_total_fee, total_paid, return_desc, return_order_status,return_pay_status ,return_shipping_status, order_return.lock_operator, order_return.return_invoice_nofrom order_returnLEFT JOIN order_info oion order_return.relating_order_sn = oi.order_sn left join user_info ui on oi.user_id = ui.user_idleft join order_payment on order_payment.order_sn = oi.order_snorder by order_return.add_time DESClimit N,NCount: 1Time=1.00s (1s)Lock=0.00s (0s)Rows=1.0 (1), root[root]@localhostselect sleep(N)Count: 1Time=1.13s (1s)Lock=0.00s (0s)Rows=10.0 (10), [dubbo]@[xx]select return_sn,relating_order_sn, return_from,oi.order_out_sn ,add_time , return_type, return_pay, ui.usename as userName, return_consignee, return_goods_count ,return_total_fee, total_paid, return_desc, return_order_status,return_pay_status ,return_shipping_status, order_return.lock_operator, order_return.return_invoice_nofrom order_returnLEFT JOIN order_info oion order_return.relating_order_sn = oi.order_sn left join user_info ui on oi.user_id = ui.user_idleft join order_payment on order_payment.order_sn = oi.order_snorder by order_return.add_time DESC, oi.order_sn limit N,NCount: 5Time=2.47s (12s)Lock=0.00s (0s)Rows=0.0 (0), [dubbo]@2hostsselectuser_id, usename, nickname, password, user_group_id, realname, gender, email, mobilephone, birthday, province_id, city_id, district_id, marry, income, remarks, create_datetime, active_state, verityEmail, verityPhone, credit, last_login, last_ip, is_locked, channel, tag, union_partner_uuid, union_user_safekey, is_black, qq, weibo_id, verity_email_key, verity_phone_key, verity_email_datetime, verity_phone_datetime, verity_question, verity_answer, cps_code, cps_time_from, cps_time_to, cps_cookie, first_channelfrom user_info where mobilephone = 'S' and (channel is null or trim(channel) ='S')

[root@db-master-2 ~]# /usr/local/mysql/bin/mysqldumpslow--helpUsage: mysqldumpslow [ OPTS... ] [ LOGS... ]Parse and summarize the MySQL slow query log. Options are--verboseverbose--debugdebug--help write this text to standard output-v verbose-d debug-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is defaultal: average lock timear: average rows sentat: average query time c: count l: lock time r: rows sent t: query time-r reverse the sort order (largest last instead of first)-t NUM just show the top n queries-a don't abstract all numbers to N and strings to 'S'-n NUM abstract numbers with at least n digits within names-g PATTERN grep: only consider stmts that include this string-h HOSTNAMEhostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all-i NAMEname of server instance (if using mysql.server startup script)-l don't subtract lock time from total time

[root@db-master-2 ~]# /usr/local/mysql/bin/mysqldumpslow -s at -t 15 /root/db01-102-slow.log Reading mysql slow query log from /root/db01-102-slow.logCount: 22Time=13.20s (290s)Lock=0.00s (0s)Rows=10.0 (220), [dubbo]@2hostsselect coupon_id, coupon_lot.coupon_lot_id, coupon_lot.remark,coupon.code,coupon_lot.coupon_type, coupon.state,coupon_lot.ckey, coupon_lot.cvalue, coupon_lot.discount, coupon_lot.type, coupon_lot.channel, coupon_lot.is_reusable, coupon_lot.start_datetime,coupon_lot.end_datetime, coupon_lot.create_datetime, coupon.update_datetime ,coupon_lot.department,coupon_lot.state as couponLotState, coupon_lot.create_operator,coupon_lot.coupon_category,coupon_lot.pdt_code,brand.brand_name,product_category.category_namefrom coupon_lotleft join coupon on coupon.coupon_lot_id = coupon_lot.coupon_lot_idLEFT JOIN brandON brand.brand_id=coupon_lot.brand_idLEFT JOIN product_categoryon product_category.category_id=coupon_lot.category_idgroup by coupon_lot.coupon_lot_idorder by coupon_lot.create_datetime DESClimit N,NCount: 22Time=9.61s (211s)Lock=0.00s (0s)Rows=1.0 (22), [dubbo]@2hostsselect count(*)from coupon_lotleft join coupon on coupon.coupon_lot_id = coupon_lot.coupon_lot_idLEFT JOIN brandON brand.brand_id=coupon_lot.brand_idLEFT JOIN product_categoryon product_category.category_id=coupon_lot.category_idCount: 5Time=2.47s (12s)Lock=0.00s (0s)Rows=0.0 (0), [dubbo]@2hostsselectuser_id, usename, nickname, password, user_group_id, realname, gender, email, mobilephone, birthday, province_id, city_id, district_id, marry, income, remarks, create_datetime, active_state, verityEmail, verityPhone, credit, last_login, last_ip, is_locked, channel, tag, union_partner_uuid, union_user_safekey, is_black, qq, weibo_id, verity_email_key, verity_phone_key, verity_email_datetime, verity_phone_datetime, verity_question, verity_answer, cps_code, cps_time_from, cps_time_to, cps_cookie, first_channelfrom user_info where mobilephone = 'S' and (channel is null or trim(channel) ='S')Count: 177Time=1.40s (247s)Lock=0.00s (0s)Rows=1.0 (177), [dubbo]@2hostsselect count(*) from user_infoLEFT JOIN system_region aON province_id =a.region_idLEFT JOIN system_region bON city_id = b.region_idLEFT JOIN system_region cON district_id = c.region_idCount: 1Time=1.13s (1s)Lock=0.00s (0s)Rows=10.0 (10), [dubbo]@[10.10.3.8]select return_sn,relating_order_sn, return_from,oi.order_out_sn ,add_time , return_type, return_pay, ui.usename as userName, return_consignee, return_goods_count ,return_total_fee, total_paid, return_desc, return_order_status,return_pay_status ,return_shipping_status, order_return.lock_operator, order_return.return_invoice_nofrom order_returnLEFT JOIN order_info oion order_return.relating_order_sn = oi.order_sn left join user_info ui on oi.user_id = ui.user_idleft join order_payment on order_payment.order_sn = oi.order_snorder by order_return.add_time DESC, oi.order_sn limit N,NCount: 8Time=1.09s (8s)Lock=0.00s (0s)Rows=10.0 (80), [dubbo]@2hostsselect return_sn,relating_order_sn, return_from,oi.order_out_sn ,add_time , return_type, return_pay, ui.usename as userName, return_consignee, return_goods_count ,return_total_fee, total_paid, return_desc, return_order_status,return_pay_status ,return_shipping_status, order_return.lock_operator, order_return.return_invoice_nofrom order_returnLEFT JOIN order_info oion order_return.relating_order_sn = oi.order_sn left join user_info ui on oi.user_id = ui.user_idleft join order_payment on order_payment.order_sn = oi.order_snorder by order_return.add_time DESClimit N,NCount: 1Time=1.00s (1s)Lock=0.00s (0s)Rows=1.0 (1), root[root]@localhostselect sleep(N)Died at /usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 236.

156 my @sorted = sort { $stmt{$b}->{$opt{s}} <=> $stmt{$a}->{$opt{s}} } keys %stmt;157 @sorted = @sorted[0 .. $opt{t}-1] if $opt{t};158 @sorted = reverse @sorted if $opt{r};159 160 foreach (@sorted) {161 my $v = $stmt{$_} || die;162 my ($c, $t,$at, $l,$al, $r,$ar) = @{ $v }{qw(c t at l al r ar)};163 my @users = keys %{$v->{users}};164 my $user= (@users==1) ? $users[0] : sprintf "%dusers",scalar @users;165 my @hosts = keys %{$v->{hosts}};166 my $host= (@hosts==1) ? $hosts[0] : sprintf "%dhosts",scalar @hosts;167 printf "Count: %dTime=%.2fs (%ds)Lock=%.2fs (%ds)Rows=%.1f (%d), $user/@$host/n%s/n/n",168 $c, $at,$t, $al,$l, $ar,$r, $_;169 }170 171 sub usage {172 my $str= shift;

my $v = $stmt{$_} || die;

[root@slave~]# /usr/local/mysql/bin/mysqldumpslow -s at -t 8 /root/db01-102-slow.log Reading mysql slow query log from /root/db01-102-slow.logCount: 22Time=13.20s (290s)Lock=0.00s (0s)Rows=10.0 (220), [dubbo]@2hostsselect coupon_id, coupon_lot.coupon_lot_id, coupon_lot.remark,coupon.code,coupon_lot.coupon_type, coupon.state,coupon_lot.ckey, coupon_lot.cvalue, coupon_lot.discount, coupon_lot.type, coupon_lot.channel, coupon_lot.is_reusable, coupon_lot.start_datetime,coupon_lot.end_datetime, coupon_lot.create_datetime, coupon.update_datetime ,coupon_lot.department,coupon_lot.state as couponLotState, coupon_lot.create_operator,coupon_lot.coupon_category,coupon_lot.pdt_code,brand.brand_name,product_category.category_namefrom coupon_lotleft join coupon on coupon.coupon_lot_id = coupon_lot.coupon_lot_idLEFT JOIN brandON brand.brand_id=coupon_lot.brand_idLEFT JOIN product_categoryon product_category.category_id=coupon_lot.category_idgroup by coupon_lot.coupon_lot_idorder by coupon_lot.create_datetime DESClimit N,NCount: 22Time=9.61s (211s)Lock=0.00s (0s)Rows=1.0 (22), [dubbo]@2hostsselect count(*)from coupon_lotleft join coupon on coupon.coupon_lot_id = coupon_lot.coupon_lot_idLEFT JOIN brandON brand.brand_id=coupon_lot.brand_idLEFT JOIN product_categoryon product_category.category_id=coupon_lot.category_idCount: 5Time=2.47s (12s)Lock=0.00s (0s)Rows=0.0 (0), [dubbo]@2hostsselectuser_id, usename, nickname, password, user_group_id, realname, gender, email, mobilephone, birthday, province_id, city_id, district_id, marry, income, remarks, create_datetime, active_state, verityEmail, verityPhone, credit, last_login, last_ip, is_locked, channel, tag, union_partner_uuid, union_user_safekey, is_black, qq, weibo_id, verity_email_key, verity_phone_key, verity_email_datetime, verity_phone_datetime, verity_question, verity_answer, cps_code, cps_time_from, cps_time_to, cps_cookie, first_channelfrom user_info where mobilephone = 'S' and (channel is null or trim(channel) ='S')Count: 177Time=1.40s (247s)Lock=0.00s (0s)Rows=1.0 (177), [xx]@2hostsselect count(*) from user_infoLEFT JOIN system_region aON province_id =a.region_idLEFT JOIN system_region bON city_id = b.region_idLEFT JOIN system_region cON district_id = c.region_idCount: 1Time=1.13s (1s)Lock=0.00s (0s)Rows=10.0 (10), [xx]@[xxx]select return_sn,relating_order_sn, return_from,oi.order_out_sn ,add_time , return_type, return_pay, ui.usename as userName, return_consignee, return_goods_count ,return_total_fee, total_paid, return_desc, return_order_status,return_pay_status ,return_shipping_status, order_return.lock_operator, order_return.return_invoice_nofrom order_returnLEFT JOIN order_info oion order_return.relating_order_sn = oi.order_sn left join user_info ui on oi.user_id = ui.user_idleft join order_payment on order_payment.order_sn = oi.order_snorder by order_return.add_time DESC, oi.order_sn limit N,NCount: 8Time=1.09s (8s)Lock=0.00s (0s)Rows=10.0 (80), [xx]@2hostsselect return_sn,relating_order_sn, return_from,oi.order_out_sn ,add_time , return_type, return_pay, ui.usename as userName, return_consignee, return_goods_count ,return_total_fee, total_paid, return_desc, return_order_status,return_pay_status ,return_shipping_status, order_return.lock_operator, order_return.return_invoice_nofrom order_returnLEFT JOIN order_info oion order_return.relating_order_sn = oi.order_sn left join user_info ui on oi.user_id = ui.user_idleft join order_payment on order_payment.order_sn = oi.order_snorder by order_return.add_time DESClimit N,NCount: 1Time=1.00s (1s)Lock=0.00s (0s)Rows=1.0 (1), root[root]@localhostselect sleep(N)Died at /usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 236.-- 臨界點 -t 7[root@db-master-2 ~]# /usr/local/mysql/bin/mysqldumpslow -s at -t 7 /root/db01-102-slow.log Reading mysql slow query log from /root/db01-102-slow.logCount: 22Time=13.20s (290s)Lock=0.00s (0s)Rows=10.0 (220), [xx]@2hostsselect coupon_id, coupon_lot.coupon_lot_id, coupon_lot.remark,coupon.code,coupon_lot.coupon_type, coupon.state,coupon_lot.ckey, coupon_lot.cvalue, coupon_lot.discount, coupon_lot.type, coupon_lot.channel, coupon_lot.is_reusable, coupon_lot.start_datetime,coupon_lot.end_datetime, coupon_lot.create_datetime, coupon.update_datetime ,coupon_lot.department,coupon_lot.state as couponLotState, coupon_lot.create_operator,coupon_lot.coupon_category,coupon_lot.pdt_code,brand.brand_name,product_category.category_namefrom coupon_lotleft join coupon on coupon.coupon_lot_id = coupon_lot.coupon_lot_idLEFT JOIN brandON brand.brand_id=coupon_lot.brand_idLEFT JOIN product_categoryon product_category.category_id=coupon_lot.category_idgroup by coupon_lot.coupon_lot_idorder by coupon_lot.create_datetime DESClimit N,NCount: 22Time=9.61s (211s)Lock=0.00s (0s)Rows=1.0 (22), [dubbo]@2hostsselect count(*)from coupon_lotleft join coupon on coupon.coupon_lot_id = coupon_lot.coupon_lot_idLEFT JOIN brandON brand.brand_id=coupon_lot.brand_idLEFT JOIN product_categoryon product_category.category_id=coupon_lot.category_idCount: 5Time=2.47s (12s)Lock=0.00s (0s)Rows=0.0 (0), [xx]@2hostsselectuser_id, usename, nickname, password, user_group_id, realname, gender, email, mobilephone, birthday, province_id, city_id, district_id, marry, income, remarks, create_datetime, active_state, verityEmail, verityPhone, credit, last_login, last_ip, is_locked, channel, tag, union_partner_uuid, union_user_safekey, is_black, qq, weibo_id, verity_email_key, verity_phone_key, verity_email_datetime, verity_phone_datetime, verity_question, verity_answer, cps_code, cps_time_from, cps_time_to, cps_cookie, first_channelfrom user_info where mobilephone = 'S' and (channel is null or trim(channel) ='S')Count: 177Time=1.40s (247s)Lock=0.00s (0s)Rows=1.0 (177), [xx]@2hostsselect count(*) from user_infoLEFT JOIN system_region aON province_id =a.region_idLEFT JOIN system_region bON city_id = b.region_idLEFT JOIN system_region cON district_id = c.region_idCount: 1Time=1.13s (1s)Lock=0.00s (0s)Rows=10.0 (10), [dubbo]@[xxx]select return_sn,relating_order_sn, return_from,oi.order_out_sn ,add_time , return_type, return_pay, ui.usename as userName, return_consignee, return_goods_count ,return_total_fee, total_paid, return_desc, return_order_status,return_pay_status ,return_shipping_status, order_return.lock_operator, order_return.return_invoice_nofrom order_returnLEFT JOIN order_info oion order_return.relating_order_sn = oi.order_sn left join user_info ui on oi.user_id = ui.user_idleft join order_payment on order_payment.order_sn = oi.order_snorder by order_return.add_time DESC, oi.order_sn limit N,NCount: 8Time=1.09s (8s)Lock=0.00s (0s)Rows=10.0 (80), [dubbo]@2hostsselect return_sn,relating_order_sn, return_from,oi.order_out_sn ,add_time , return_type, return_pay, ui.usename as userName, return_consignee, return_goods_count ,return_total_fee, total_paid, return_desc, return_order_status,return_pay_status ,return_shipping_status, order_return.lock_operator, order_return.return_invoice_nofrom order_returnLEFT JOIN order_info oion order_return.relating_order_sn = oi.order_sn left join user_info ui on oi.user_id = ui.user_idleft join order_payment on order_payment.order_sn = oi.order_snorder by order_return.add_time DESClimit N,NCount: 1Time=1.00s (1s)Lock=0.00s (0s)Rows=1.0 (1), root[root]@localhostselect sleep(N)[root@db-master-2 ~]#