[internetworkers] SQL Help?

Ilan Volow listboy at clarux.com
Mon Feb 25 16:41:42 EST 2008


I'm a PostgreSQL guy, so take my educated guess with a grain of salt,  
but you might want to try this just to see if it works:

SELECT
perfstat_section.iteration
, latx_stats_data.label
, SUM(IF(latx_stats_data.statname = 'read_ops', `statval`, 0)) AS  
'read_ops'
, SUM(IF(latx_stats_data.statname = 'read_data', `statval`, 0)) AS  
'read_data'
, SUM(IF(latx_stats_data.statname = 'read_latency', `statval`, 0)) AS  
'read_latency'
, SUM( IF(latx_stats_data.statname = 'read_latency', `statval`, 0) *  
IF(latx_stats_data.statname = 'read_latency', `statval`, 0) ) AS  
'concurrency'

FROM (`latx_stats`)
JOIN `perfstat_section` ON perfstat_section.secid = latx_stats.secid
JOIN `latx_stats_data` ON latx_stats.latxid = latx_stats_data.latxid

WHERE latx_stats.perfid = '1'
AND latx_stats.object = 'volume'
AND perfstat_section.hostname = 'ny-filer1'
AND latx_stats_data.statname IN ('total_ops', 'avg_latency',  
'read_ops', 'read_data', 'read_latency')

GROUP BY `iteration`, `label`
ORDER BY `read_ops` DESC
LIMIT 10;

If you haven't already thought about it, this kind of statement might  
be a good candidate for a database view.


On Feb 24, 2008, at 6:11 PM, Scott Russell wrote:

> SELECT
> perfstat_section.iteration
> , latx_stats_data.label
> , SUM(IF(latx_stats_data.statname = 'read_ops', `statval`, 0)) AS  
> 'read_ops'
> , SUM(IF(latx_stats_data.statname = 'read_data', `statval`, 0)) AS  
> 'read_data'
> , SUM(IF(latx_stats_data.statname = 'read_latency', `statval`, 0))  
> AS 'read_latency'
>
> FROM (`latx_stats`)
> JOIN `perfstat_section` ON perfstat_section.secid = latx_stats.secid
> JOIN `latx_stats_data` ON latx_stats.latxid = latx_stats_data.latxid
>
> WHERE latx_stats.perfid = '1'
> AND latx_stats.object = 'volume'
> AND perfstat_section.hostname = 'ny-filer1'
> AND latx_stats_data.statname IN ('total_ops', 'avg_latency',  
> 'read_ops', 'read_data', 'read_latency')
>
> GROUP BY `iteration`, `label`
> ORDER BY `read_ops` DESC
> LIMIT 10;

Ilan Volow
"Implicit code is inherently evil, and here's the reason why:"



-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.ibiblio.org/pipermail/internetworkers/attachments/20080225/077aa113/attachment.html 


More information about the InterNetWorkers mailing list