[mysqldumpslow 报错] Died at /usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 236.
来源:互联网 发布:蚂蜂窝连app接不到网络 编辑:程序博客网 时间:2024/06/06 01:39
总结:是因为top数目太多了,mysqldumpslow遍历不过来的缘故。
/usr/local/mysql/bin/mysqldumpslow -s -t 15 /root/db01-102-slow.log
1,把r去掉试试,还是报错,参数不识别。
- [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.log
- Can't open 15: 没有那个文件或目录 at /usr/local/mysql/bin/mysqldumpslow line 91.
- Count: 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_id
- 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_id
- Count: 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,N
- Count: 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,N
- Count: 1 Time=1.00s (1s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost
- select sleep(N)
- Count: 1 Time=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_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,N
- Count: 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')
2,查看下mysqldumpslow 使用参数,-s后面需要带参数的,看了西夏at比较合适
- [root@db-master-2 ~]# /usr/local/mysql/bin/mysqldumpslow --help
- Usage: 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
- [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.log
- Count: 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,N
- Count: 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_id
- Count: 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_id
- Count: 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,N
- Count: 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,N
- Count: 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.
4,去看看/usr/local/mysql/bin/mysqldumpslow的第161行左右,到底是在操作什么脚本。
- 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;
- my $v = $stmt{$_} || die;
/usr/local/mysql/bin/mysqldumpslow -s at -t 15 /root/db01-102-slow.log 有 -t 15这一句,那么就是要显示出前15个记录,那么如果我的slow.log统计出来只有12个不到15个,而这里有继续遍历到15的话,那进程肯定会Died。
所以这里有2个解决方案:
1 修改/usr/local/mysql/bin/mysqldumpslow里面的源代码,加null判断。
这个过程有些复杂,暂时略过吧,期待后续空了再研究。
2 修改命令 /usr/local/mysql/bin/mysqldumpslow -s at -t 15 /root/db01-102-slow.log ,把-t 15修改成-t 10或者-t 5试试。
试了好几次,发现临界点在-t 7上面,如下:
- [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.log
- Count: 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,N
- Count: 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_id
- Count: 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), [xx]@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_id
- Count: 1 Time=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_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,N
- Count: 8 Time=1.09s (8s) Lock=0.00s (0s) Rows=10.0 (80), [xx]@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,N
- Count: 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.
- -- 临界点 -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.log
- Count: 22 Time=13.20s (290s) Lock=0.00s (0s) Rows=10.0 (220), [xx]@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,N
- Count: 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_id
- Count: 5 Time=2.47s (12s) Lock=0.00s (0s) Rows=0.0 (0), [xx]@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), [xx]@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_id
- Count: 1 Time=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_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,N
- Count: 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,N
- Count: 1 Time=1.00s (1s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost
- select sleep(N)
- [root@db-master-2 ~]#
最终总结:
mysqldumpslow报错:Died at /usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 236. 是因为top数目太多了,mysqldumpslow遍历不过来的缘故。
- [mysqldumpslow 报错] Died at /usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 236.
- [mysqldumpslow 报错] Died at /usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 236.
- mysqldumpslow
- mysqldumpslow
- mysqldumpslow
- innobackupex: Error: The xtrabackup child process has died at /usr/bin/innobackupex line 2672
- 小黑小波比./usr/local/mysql/bin/mysqld_safe --user=mysql&报错
- mysql重置密码报错/usr/local/mysql/bin/mysqld does not exist
- MySQL慢查询分析mysqldumpslow
- MySQL慢查询分析mysqldumpslow
- MySQL慢查询分析mysqldumpslow
- mysql日志分析工具-mysqldumpslow
- MySQL慢查询分析mysqldumpslow
- mysql-慢查询-mysqldumpslow简介
- mysqldumpslow 分析mysql慢查询
- MySQL 慢查询分析 mysqldumpslow
- 编译安装openssl报错:POD document had syntax errors at /usr/bin/pod2man line 69. make: *** [install_docs]
- hombrew安装composer报错 /usr/bin occurs before /usr/local/bin
- 持续集成1---初步
- iOS计算中英文混合字符串长度的方法
- Nagios监控mysql从库报错: libmysqlclient.so.18: cannot open shared object file: No such
- android之adb
- 面试题38:数字在排序数组中出现的次数
- [mysqldumpslow 报错] Died at /usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 236.
- Android开源项目——工具库篇
- spring kaptcha 验证码使用
- [leetcode] 55.Jump Game
- HAOI2015 解题报告
- MySQL通过自增一列在Select ... into outfile...里面实现CSV导出带字段的效果
- poj 1606 bfs方法
- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- TextView属性大全