Platon Technologies
not logged in Login Registration
EnglishSlovak
open source software development celebrating 10 years of open source development! Friday, March 29, 2024

File: [Platon] / scripts / perl / accounting / stats-commands.sql (download)

Revision 1.1, Mon Feb 21 13:59:03 2005 UTC (19 years, 1 month ago) by rajo

MySQL selects for process accounting statistics.

/* global */
SELECT
    COUNT(*) AS 'Total number of commands',
    SUM(user_time) / 50.0        AS 'Total user time (sec.)',
    SUM(system_time) / 50.0        AS 'Total system time (sec.)',
    SUM(effective_time) / 50.0    AS 'Total effective time (sec.)'
FROM log_accounting_process \G

/* by command count */
SELECT
    command AS 'Command',
    COUNT(*) AS 'Number of commands',
    SUM(user_time) / 50.0        AS 'Total user time (sec.)',
    SUM(system_time) / 50.0        AS 'Total system time (sec.)',
    SUM(effective_time) / 50.0    AS 'Total effective time (sec.)'
FROM log_accounting_process
GROUP BY command
ORDER BY 'Number of commands' DESC
LIMIT 100;

/* by command user time */
SELECT
    command AS 'Command',
    COUNT(*) AS 'Number of commands',
    SUM(user_time) / 50.0        AS 'Total user time (sec.)',
    MIN(user_time) / 50.0        AS 'Minimal user time (sec.)',
    AVG(user_time) / 50.0        AS 'Average user time (sec.)',
    MAX(user_time) / 50.0        AS 'Maximal user time (sec.)'
FROM log_accounting_process
GROUP BY command
ORDER BY 'Total user time (sec.)' DESC
LIMIT 100;

/* by command system time */
SELECT
    command AS 'Command',
    COUNT(*) AS 'Number of commands',
    SUM(system_time) / 50.0        AS 'Total system time (sec.)',
    MIN(system_time) / 50.0        AS 'Minimal system time (sec.)',
    AVG(system_time) / 50.0        AS 'Average system time (sec.)',
    MAX(system_time) / 50.0        AS 'Maximal system time (sec.)'
FROM log_accounting_process
GROUP BY command
ORDER BY 'Total system time (sec.)' DESC
LIMIT 100;

/* by command effective time */
SELECT
    command AS 'Command',
    COUNT(*) AS 'Number of commands',
    SUM(effective_time) / 50.0        AS 'Total effective time (sec.)',
    MIN(effective_time) / 50.0        AS 'Minimal effective time (sec.)',
    AVG(effective_time) / 50.0        AS 'Average effective time (sec.)',
    MAX(effective_time) / 50.0        AS 'Maximal effective time (sec.)'
FROM log_accounting_process
GROUP BY command
ORDER BY 'Total effective time (sec.)' DESC
LIMIT 100;

--
-- User stats:
--

/* by command count */
SELECT
    p.username AS 'Username',
    COUNT(*) AS 'Number of commands',
    SUM(user_time) / 50.0        AS 'Total user time (sec.)',
    SUM(system_time) / 50.0        AS 'Total system time (sec.)',
    SUM(effective_time) / 50.0    AS 'Total effective time (sec.)'
FROM log_accounting_process AS l
INNER JOIN passwd AS p USING (uid)
GROUP BY l.uid 
ORDER BY 'Number of commands' DESC
LIMIT 100;

/* by command user time */
SELECT
    p.username AS 'Username',
    COUNT(*) AS 'Number of commands',
    SUM(user_time) / 50.0        AS 'Total user time (sec.)',
    MIN(user_time) / 50.0        AS 'Minimal user time (sec.)',
    AVG(user_time) / 50.0        AS 'Average user time (sec.)',
    MAX(user_time) / 50.0        AS 'Maximal user time (sec.)'
FROM log_accounting_process AS l
INNER JOIN passwd AS p USING (uid)
GROUP BY l.uid 
ORDER BY 'Total user time (sec.)' DESC
LIMIT 100;

/* by command system time */
SELECT
    p.username AS 'Username',
    COUNT(*) AS 'Number of commands',
    SUM(system_time) / 50.0        AS 'Total system time (sec.)',
    MIN(system_time) / 50.0        AS 'Minimal system time (sec.)',
    AVG(system_time) / 50.0        AS 'Average system time (sec.)',
    MAX(system_time) / 50.0        AS 'Maximal system time (sec.)'
FROM log_accounting_process AS l
INNER JOIN passwd AS p USING (uid)
GROUP BY l.uid 
ORDER BY 'Total system time (sec.)' DESC
LIMIT 100;

/* by command effective time */
SELECT
    p.username AS 'Username',
    COUNT(*) AS 'Number of commands',
    SUM(effective_time) / 50.0        AS 'Total effective time (sec.)',
    MIN(effective_time) / 50.0        AS 'Minimal effective time (sec.)',
    AVG(effective_time) / 50.0        AS 'Average effective time (sec.)',
    MAX(effective_time) / 50.0        AS 'Maximal effective time (sec.)'
FROM log_accounting_process AS l
INNER JOIN passwd AS p USING (uid)
GROUP BY l.uid 
ORDER BY 'Total effective time (sec.)' DESC
LIMIT 100;


Platon Group <platon@platon.org> http://platon.org/
Copyright © 2002-2006 Platon Group
Site powered by Metafox CMS
Go to Top