[Mysqldumpslow Error] Died at/usr/local/mysql/bin/mysqldumpslow line 161, & lt; & gt; chunk 236.

Source: Internet
Author: User
Tags mysql slow query log


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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.