Wednesday, November 10, 2010

Mysql : percentile of queries completed in a specific duration, on weekly basis

What I have :
A MySql table with 2 columns : load_start_time,load_end_time, both the
columns contain unix timestamps.

What I need :
Weekwise data in the following format :
Week Time_taken_for_load_to_complete percentile_of_loads_completed_in_that_time

Here are the requisite queries :

drop table if exists tbl;

create table tbl as ( select
week(date(from_unixtime(load_start_time))) as w,round((load_end_time -
load_start_time)/10) as 10SecInterval,count(1) as numReqs from
user_load_data where load_start_time is not null and load_end_time is
not null group by w,10SecInterval having(10SecInterval < 240 and
10SecInterval >= 0));

--compute percentile
drop table if exists tbl1;
create table tbl1 as ( select a.w,a.10SecInterval*10 as
seconds,round(100*((select sum(numReqs) from tbl as b where b.w = a.w
and b.10SecInterval <= a.10SecInterval)/(select sum(numReqs) from tbl
as c where c.w = a.w))) as percentile from tbl as a);
select * from tbl1 limit 200;

No comments:

Blog Archive