[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