From StatsJam
[edit] Exploring Definitions of Idle Time
Rather than arbitrarily defining idle time, let's take a look at the time between commands to get a sense of what makes the most sense. There are two ways we can calculate summaries of command time. First, we can summarize across all log files, without regard to user. Second, we can summarize the mean times per user, then calculate summary stats per user. In both cases, we consider only significant users (Defining Significant Users). (We would use median here, too, but there are too many data points for our current implementation of "median".)
And, unfortunately, our DB has decided to grind to a halt, making these queries nigh near impossible...
This form gives you a chance to modify the query and quickly see
the results but it does not update the wiki page..
<query>
SELECT
log_date,
time_diff_in_secs
FROM
(SELECT
log_date,
(later_table.elapsed_time_usec - earlier_table.elapsed_time_usec) / 1000000.0 AS time_diff_in_secs
FROM
event_record AS earlier_table,
event_record AS later_table,
interaction_log
WHERE
interaction_log.log_num = later_table.log_num AND
later_table.log_num = earlier_table.log_num AND
later_table.command_num = (earlier_table.command_num + 1) AND
interaction_log.log_num IN (
SELECT
log_num
FROM
interaction_log,
(SELECT
users_who_saved_table.user_id
FROM
(SELECT
interaction_log.user_id,
EXTRACT(EPOCH FROM (MAX(interaction_log.log_date)-MIN(interaction_log.log_date)))/60/60/24 AS num_days_diff
FROM
interaction_log,
(SELECT
DISTINCT event_record.log_num
FROM
event_record
WHERE
event_record.event_type_id = 7 -- This is the ID for an image save event
) AS logs_with_save_table
WHERE
interaction_log.log_num = logs_with_save_table.log_num
AND user_id NOT IN (SELECT user_id FROM ingimp_developer_ids)
GROUP BY
interaction_log.user_id
) AS users_who_saved_table
WHERE
num_days_diff >= 1
) AS sig_users_table
WHERE
interaction_log.user_id = sig_users_table.user_id
)
GROUP BY
earlier_table.log_num,
later_table.command_num,
later_table.elapsed_time_usec,
earlier_table.elapsed_time_usec,
log_date
) AS inner_table
WHERE
time_diff_in_secs >= 0 AND
time_diff_in_secs < 90 AND
log_date > 'May 1, 2007'
</query>
<view type="plot_date" caption="Histogram of Time Between Command Invocations"/>
Histogram of Time Between Command Invocations
This form gives you a chance to modify the query and quickly see
the results but it does not update the wiki page..
<query>
SELECT
AVG(time_diff_in_secs) as mean_time_diff,
STDDEV(time_diff_in_secs) as sd_time_diff
FROM
(SELECT
earlier_table.log_num,
(later_table.elapsed_time_usec - earlier_table.elapsed_time_usec) / 1000000.0 AS time_diff_in_secs
FROM
event_record AS earlier_table,
event_record AS later_table
WHERE
later_table.log_num = earlier_table.log_num AND
later_table.command_num = (earlier_table.command_num + 1) AND
later_table.log_num IN (
SELECT
log_num
FROM
interaction_log,
(SELECT
users_who_saved_table.user_id
FROM
(SELECT
interaction_log.user_id,
EXTRACT(EPOCH FROM (MAX(interaction_log.log_date)-MIN(interaction_log.log_date)))/60/60/24 AS num_days_diff
FROM
interaction_log,
(SELECT
DISTINCT event_record.log_num
FROM
event_record
WHERE
event_record.event_type_id = 7 -- This is the ID for an image save event
) AS logs_with_save_table
WHERE
interaction_log.log_num = logs_with_save_table.log_num
AND user_id NOT IN (SELECT user_id FROM ingimp_developer_ids)
GROUP BY
interaction_log.user_id
) AS users_who_saved_table
WHERE
num_days_diff >= 1
) AS sig_users_table
WHERE
interaction_log.user_id = sig_users_table.user_id
)
GROUP BY
earlier_table.log_num,
later_table.command_num,
later_table.elapsed_time_usec,
earlier_table.elapsed_time_usec
) AS time_diff_table
</query>
<view type="table" caption="Summary Stats for Time (in Seconds) Between Command Invocations, Overall"/>
Summary Stats for Time (in Seconds) Between Command Invocations, Overall| mean_time_diff | sd_time_diff |
|---|
| 17.1139183518 | 6115.26711388 |
From the data, 120 seconds seems a reasonable, conservative choice for a time-out.
