How many queries per second are you executing?
is any server parameter i need to tweak.
just i am running single query for testing purpose and if i add many queries server hangs. afraid to keep
in production where more than 100 session will interact.
se
just 1 query consuming high cpu from top command and if many sessions connects server hangs:
load averages: 0.37, 0.19, 0.12 13:35:26
92 processes: 87 sleeping, 2 zombie, 2 stopped, 1 on cpu
CPU states: 44.4% idle, 27.9% user, 1.1% kernel, 26.5% iowait, 0.0% swap
Memory: 2048M real, 154M free, 1796M swap in use, 3722M swap free
PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
20907 mysql 12 59 0 0K 0K sleep 45:57 30.29% mysqld
372 root 6 59 0 4000K 2464K sleep 62:55 0.08% picld
21082 root 1 59 0 2248K 1312K cpu/1 0:00 0.02% top
643 root 24 59 0 3904K 2288K sleep 0:21 0.00% nscd
723 root 1 59 0 2688K 1256K sleep 0:20 0.00% arraymon
526 root 17 59 0 65M 3616K sleep 0:12 0.00% vxsvc
586 root 1 59 0 2504K 1448K sleep 0:10 0.00% inetd
1619 root 1 59 0 80M 2360K sleep 0:05 0.00% Xsun
4344 root 2 49 0 10M 2864K sleep 0:02 0.00% dtsession
16 root 1 59 0 9192K 3896K sleep 0:02 0.00% vxconfigd
4352 root 1 59 0 7520K 2616K sleep 0:01 0.00% sdtperfmeter
1401 root 7 59 0 2624K 1920K sleep 0:01 0.00% mibiisa
1 root 1 59 0 1288K 192K sleep 0:01 0.00% init
18449 oracle 1 59 0 3592K 1112K stop 0:00 0.00% su
10332 oracle 1 60 0 0K 0K stop 0:00 0.00% mysql
mysql> show processlist;
+----+------+----------------------+------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+----------------------+------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 7 | root | localhost | crms | Query | 0 | NULL | show processlist |
| 61 | ccc | 203.187.217.209:2624 | crms | Query | 30 | Copying to tmp table | select a.id, call_category,logged_date, a.call_id, customer_name, phone, mobile, account_number, a |
+----+------+----------------------+------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Did any of the slowlog queries have Rows Examined > 1000?
right
# Time: 090519 13:31:32
# User@Host: ccc[ccc] @ [203.187.217.209]
# Query_time: 80 Lock_time: 0 Rows_sent: 652 Rows_examined: 406034
SET timestamp=1242720092;
select a.id, call_category,logged_date, a.call_id, customer_name, phone, mobile, account_number, address, a.area, city, complaint,
process_code, agent_reply, agent_comments, fstatus, cstatus, assignto, sub_process_code, cleared_date, closed_date, cleared_datetime,
closed_datetime, cust_type, node_name, max(c.iqara_id) as iqara_id, count(distinct b.history_id) as repeatcalls, modem_status, soc_desc, action_taken, rem_desc,ifnull(reopened,0) as reopened, CASE
WHEN fstatus = 'Closed' THEN 0
WHEN cstatus = 'Pending' THEN TIMESTAMPDIFF(MINUTE,a.logged_date,now())/60
WHEN cstatus = 'Cleared' THEN TIMESTAMPDIFF(MINUTE,a.logged_date,a.cleared_datetime)/60
WHEN cstatus = 'Closed' THEN TIMESTAMPDIFF(MINUTE,a.logged_date,a.closed_datetime)/60
END as open_hrs, CASE
WHEN fstatus = 'Closed' THEN 0
WHEN cstatus = 'Pending' THEN TIMESTAMPDIFF(MINUTE,a.logged_date,now())%60
WHEN cstatus = 'Cleared' THEN TIMESTAMPDIFF(MINUTE,a.logged_date,a.cleared_datetime)%60
WHEN cstatus = 'Closed' THEN TIMESTAMPDIFF(MINUTE,a.logged_date,a.closed_datetime)%60
END as open_min from new_call a left join history b on a.id=b.id
left join history_iqara c on a.id=c.id
left join modem d on d.cust_acct_id=cast(a.account_number as SIGNED)
left join userdetail e on a.account_number = e.account_no
left join mast_area f on f.cust_acct_id = cast(a.account_number as SIGNED)
left join compsource_m g on a.comp_source = g.soc_id
left join co_ordinator_remarks_m h on c.rem_no = h.rem_no
left join call_promo i on a.call_id = i.call_id
left join subprocesscode_m j on a.sub_process_code = j.spc
left join master_call_category k on j.mast_call_category_id = k.id
where city = 'Vadodara' and process_code = 3 and sub_process_code in ('3.a','3.b','3.c','3.d','3.f','3.g','3.e','3.h','3.i') and fdate >= '2009-03-01' and fdate <= '2009-05-19' group by a.id,k.call_category, a.call_id, customer_name, phone, mobile, account_number, address, a.area, city, complaint,
process_code, agent_reply, agent_comments, fstatus, cstatus, assignto, sub_process_code, cleared_date,
closed_date, cleared_datetime, closed_datetime, logged_date, cust_type, node_name , modem_status, soc_desc, iqara_id, action_taken, rem_desc,reopened
having c.iqara_id = (select max(iqara_id) from history_iqara where id = a.id) or(c.iqara_id is null) order by logged_date desc;
Thanks
Prakash GR