Mysqldumpslow:Died at/usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 236.
Conclusion: The number of top jobs is too large, and mysqldumpslow cannot be traversed.
/Usr/local/mysql/bin/mysqldumpslow-s-t 15/root/db01-102-slow.log
1. Try removing r and report an error. The parameter is not recognized.
[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: there is no such file or directory at/usr/local/mysql/bin/mysqldumpslow line 91. count: 22 Time = 9.61 s (211 s) Lock = 0.00 s (0 s) Rows = 1.0 (22), [dubbo] @ 2 hosts select count (*) from coupon_lot left join coupon on coupon. coupon_lot_id = coupon_lot.coupon_lot_id left join brand ON brand. brand_id = coupon_lot.brand_id left join product_category on product_category.category_id = coupon_lot.category_idCount: 177 Time = 1.40 s (247 s) Lock = 0.00 s (0 s) Rows = 1.0 (177 ), [dubbo] @ 2 hosts select count (*) from user_info left join system_region a ON province_id =. region_id left join system_region B ON city_id = B. region_id left join system_region c ON district_id = c. region_idCount: 22 Time = 13.20 s (290 s) Lock = 0.00 s (0 s) Rows = 10.0 (220), [dubbo] @ 2 hosts select coupon_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, role, 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_name from coupon_lot left join coupon on coupon. coupon_lot_id = coupon_lot.coupon_lot_id left join brand ON brand. brand_id = coupon_lot.brand_id left join product_category on product_category.category_id = grouping group by grouping order by coupon_lot.create_datetime DESC limit N, NCount: 8 Time = 1.09 s (8 s) Lock = 0.00 s (0 s) rows = 10.0 (80), [dubbo] @ 2 hosts select return_sn, relating_order_sn, return_from, oi. order_out_sn, add_time, return_type, return_pay, ui. usename as userName, response, return_goods_count, return_total_fee, total_paid, return_desc, return_order_status, return_pay_status, success, success, begin from order_return left join order_info oi on partition = oi. order_sn left join user_info ui on oi. user_id = ui. user_id left join order_payment on order_payment.order_sn = oi. order_sn order by order_return.add_time DESC limit N, NCount: 1 Time = 1.00 s (1 s) Lock = 0.00 s (0 s) Rows = 1.0 (1 ), root [root] @ localhost select sleep (N) Count: 1 Time = 1.13 s (1 s) Lock = 0.00 s (0 s) 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, response, return_goods_count, return_total_fee, total_paid, return_desc, return_order_status, return_pay_status, success, success, begin from order_return left join order_info oi on partition = oi. order_sn left join user_info ui on oi. user_id = ui. user_id left join order_payment on order_payment.order_sn = oi. order_sn order by order_return.add_time DESC, oi. order_sn limit N, NCount: 5 Time = 2.47 s (12 s) Lock = 0.00 s (0 s) Rows = 0.0 (0), [dubbo] @ 2 hosts select user_id, usename, nickname, password, user_group_id, realname, gender, email, mobilephone, birthday, province_id, city_id, identifier, marry, income, remarks, create_datetime, active_state, verityEmail, verityPhone, credit, last_login, last_ip, is_locked, channel, tag, tags, tags, is_black, qq, weibo_id, tags, cps_code, cps_time_from, cps_time_to, cps_cookie, first_channel from user_info where mobilephone ='s and (channel is null or trim (channel) ='s ')
2. Check the mysqldumpslow usage parameters.-s must be followed by parameters. It is more suitable for Xixia.
[root@db-master-2 ~]# /usr/local/mysql/bin/mysqldumpslow --helpUsage: mysqldumpslow [ OPTS... ] [ LOGS... ]Parse and summarize the MySQL slow query log. Options are --verbose verbose --debug debug --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 default al: average lock time ar: average rows sent at: 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 HOSTNAME hostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all -i NAME name of server instance (if using mysql.server startup script) -l don't subtract lock time from total time
3. Add the at parameter after-s of mysqldumpslow:[Root @ slave] #/usr/local/mysql/bin/mysqldumpslow-s at-t 15/root/db01-102-slow.log
[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: 22 Time=13.20s (290s) Lock=0.00s (0s) Rows=10.0 (220), [dubbo]@2hosts select 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_name from coupon_lot left join coupon on coupon.coupon_lot_id = coupon_lot.coupon_lot_id LEFT JOIN brand ON brand.brand_id=coupon_lot.brand_id LEFT JOIN product_category on product_category.category_id=coupon_lot.category_id group by coupon_lot.coupon_lot_id order by coupon_lot.create_datetime DESC limit N,NCount: 22 Time=9.61s (211s) Lock=0.00s (0s) Rows=1.0 (22), [dubbo]@2hosts select count(*) from coupon_lot left join coupon on coupon.coupon_lot_id = coupon_lot.coupon_lot_id LEFT JOIN brand ON brand.brand_id=coupon_lot.brand_id LEFT JOIN product_category on product_category.category_id=coupon_lot.category_idCount: 5 Time=2.47s (12s) Lock=0.00s (0s) Rows=0.0 (0), [dubbo]@2hosts select user_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_channel from user_info where mobilephone = 'S' and (channel is null or trim(channel) ='S')Count: 177 Time=1.40s (247s) Lock=0.00s (0s) Rows=1.0 (177), [dubbo]@2hosts select count(*) from user_info LEFT JOIN system_region a ON province_id =a.region_id LEFT JOIN system_region b ON city_id = b.region_id LEFT JOIN system_region c ON district_id = c.region_idCount: 1 Time=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_no from order_return LEFT JOIN order_info oi on order_return.relating_order_sn = oi.order_sn left join user_info ui on oi.user_id = ui.user_id left join order_payment on order_payment.order_sn = oi.order_sn order by order_return.add_time DESC , oi.order_sn limit N,NCount: 8 Time=1.09s (8s) Lock=0.00s (0s) Rows=10.0 (80), [dubbo]@2hosts 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_no from order_return LEFT JOIN order_info oi on order_return.relating_order_sn = oi.order_sn left join user_info ui on oi.user_id = ui.user_id left join order_payment on order_payment.order_sn = oi.order_sn order by order_return.add_time DESC limit N,NCount: 1 Time=1.00s (1s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost select sleep(N)Died at /usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 236.
The following error is returned:Died at/usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 236.
4. Let's see about 161st rows of/usr/local/mysql/bin/mysqldumpslow. What script is being operated.
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: %d Time=%.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;
The main result is the foreach (@ sorted) traversal function, that is, when chunk 236 is traversed, died is over. There is no way to continue foreach. Because I have written code before and know the line in which the error is reported
my $v = $stmt{$_} || die;
It is the first sentence of traversal, so it must be a value assignment statement. The error Died is reported. chunk 236 is certainly no data has been traversed to the end. Check whether the input parameter is null by using this function, so when the file data has been traversed, but the command is not over, so continue to traverse, when traversing null, an error is reported, so the process is aborted, the solution is to limit not to traverse strings that exceed the length of the original data. Then let's look at the executed command:
/Usr/local/mysql/bin/mysqldumpslow-s at-t 15/root/db01-102-slow.log has-t 15, so it is to display the first 15 records, so if my slow. log statistics show that there are only 12 to 15, and if we continue to traverse to 15, the process will certainly be Died.
Therefore, there are two solutions:
1. Modify the source code in/usr/local/mysql/bin/mysqldumpslow and add null for determination.
This process is a bit complicated. Let's skip it for the time being. We hope we will try again later.
2 modify command/usr/local/mysql/bin/mysqldumpslow-s at-t 15/root/db01-102-slow.log, change-t 15 to-t 10 or-t 5 try.
I tried it several times and found that the critical point was above-t 7, as shown below:
[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: 22 Time = 13.20 s (290 s) Lock = 0.00 s (0 s) Rows = 10.0 (220), [dubbo] @ 2 hosts select 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_name from coupon_lot left join coupon on coupon. coupon_lot_id = coupon_lot.coupon _ Lot_id left join brand ON brand. brand_id = coupon_lot.brand_id left join product_category on product_category.category_id = grouping group by grouping order by coupon_lot.create_datetime DESC limit N, NCount: 22 Time = 9.61 s (211 s) Lock = 0.00 s (0 s) rows = 1.0 (22), [dubbo] @ 2 hosts select count (*) from coupon_lot left join coupon on. coupon_lot_id = coupon_lot.coupon_lot_id Left join brand ON brand. brand_id = coupon_lot.brand_id left join product_category on product_category.category_id = coupon_lot.category_idCount: 5 Time = 2.47 s (12 s) Lock = 0.00 s (0 s) Rows = 0.0 (0 ), [dubbo] @ 2 hosts select user_id, usename, nickname, password, user_group_id, realname, gender, email, mobilephone, birthday, province_id, city_id, district_id, marry, income, remarks, create_datetime, active_st Ate, verityEmail, verityPhone, credit, last_login, last_ip, is_locked, channel, tag, tags, tags, is_black, qq, weibo_id, tags, expires, verity_question, verity_answer, cps_code, cps_time_from, cps_time_to, cps_cookie, first_channel from user_info where mobilephone = 's' and (channel is null or trim (chann El) = 's') Count: 177 Time = 1.40 S (247 s) Lock = 0.00 s (0 s) Rows = 1.0 (177 ), [xx] @ 2 hosts select count (*) from user_info left join system_region a ON province_id =. region_id left join system_region B ON city_id = B. region_id left join system_region c ON district_id = c. region_idCount: 1 Time = 1.13 s (1 s) Lock = 0.00 s (0 s) Rows = 10.0 (10), [xx] @ [xxx] select return_sn, relating_order_sn, return_from, oi. order_o Ut_sn, add_time, return_type, return_pay, ui. usename as userName, response, return_goods_count, return_total_fee, total_paid, return_desc, return_order_status, return_pay_status, success, success, begin from order_return left join order_info oi on partition = oi. order_sn left join user_info ui on oi. user_id = Ui. user_id left join order_payment on order_payment.order_sn = oi. order_sn order by order_return.add_time DESC, oi. order_sn limit N, NCount: 8 Time = 1.09 s (8 s) Lock = 0.00 s (0 s) Rows = 10.0 (80), [xx] @ 2 hosts 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_pai D, return_desc, return_order_status, return_pay_status, return_shipping_status, order_return.lock_operator, order_return.return_invoice_no from order_return left join order_info oi on condition =. order_sn left join user_info ui on oi. user_id = ui. user_id left join order_payment on order_payment.order_sn = oi. order_sn order by order_return.add_time DESC limit N, NCount: 1 Ti Me = 1.00 s (1 s) Lock = 0.00 s (0 s) Rows = 1.0 (1), root [root] @ localhost select sleep (N) died at/usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 236. -- Critical Point-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: 22 Time = 13.20 s (290 s) Lock = 0.00 s (0 s) Rows = 10.0 (220), [xx] @ 2 hosts select 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_name from coupon_lot left join coupon on coupon. coupon_lot_id = coupon_lot.coupon_lot _ Id left join brand ON brand. brand_id = coupon_lot.brand_id left join product_category on product_category.category_id = grouping group by grouping order by coupon_lot.create_datetime DESC limit N, NCount: 22 Time = 9.61 s (211 s) Lock = 0.00 s (0 s) rows = 1.0 (22), [dubbo] @ 2 hosts select count (*) from coupon_lot left join coupon on. coupon_lot_id = coupon_lot.coupon_lot_id LE Ft join brand ON brand. brand_id = coupon_lot.brand_id left join product_category on product_category.category_id = coupon_lot.category_idCount: 5 Time = 2.47 s (12 s) Lock = 0.00 s (0 s) Rows = 0.0 (0 ), [xx] @ 2 hosts select user_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, v ErityEmail, verityPhone, credit, last_login, last_ip, is_locked, channel, tag, tags, tags, is_black, qq, weibo_id, tags, metrics, verity_question, metrics, cps_code, cps_time_from, cps_time_to, cps_cookie, first_channel from user_info where mobilephone ='s and (channel is null or trim (channel) =' S ') Count: 177 Time = 1.40 s (247 s) Lock = 0.00 s (0 s) Rows = 1.0 (177), [xx] @ 2 hosts select count (*) from user_info left join system_region a ON province_id =. region_id left join system_region B ON city_id = B. region_id left join system_region c ON district_id = c. region_idCount: 1 Time = 1.13 s (1 s) Lock = 0.00 s (0 s) 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, response, return_goods_count, return_total_fee, total_paid, return_desc, return_order_status, return_pay_status, success, success, begin from order_return left join order_info oi on partition = oi. order_sn left join user_info ui on oi. user_id = Ui. user_id left join order_payment on order_payment.order_sn = oi. order_sn order by order_return.add_time DESC, oi. order_sn limit N, NCount: 8 Time = 1.09 s (8 s) Lock = 0.00 s (0 s) Rows = 10.0 (80), [dubbo] @ 2 hosts 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_pai D, return_desc, return_order_status, return_pay_status, return_shipping_status, order_return.lock_operator, order_return.return_invoice_no from order_return left join order_info oi on condition =. order_sn left join user_info ui on oi. user_id = ui. user_id left join order_payment on order_payment.order_sn = oi. order_sn order by order_return.add_time DESC limit N, NCount: 1 Ti Me = 1.00 s (1 s) Lock = 0.00 s (0 s) Rows = 1.0 (1), root [root] @ localhost select sleep (N) [root @ db-master-2 ~] #
Conclusion:
Mysqldumpslow:Died at/usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 236. The reason is that the number of top jobs is too large and mysqldumpslow cannot be traversed.