整理月平均性能数据
35::
在centreon_storage库中创建关系表test1
CREATE TABLE test1 AS SELECT
index_data.host_name,
index_data.service_description,
metrics.metric_id,
metrics.metric_name
FROM
index_data,
metrics
WHERE
index_data.id = metrics.index_id;
把data_bin,test1 表的数据转移到172.25.128.77上面。放到bigdata数据库中。
cd /var/lib/mysql/centreon_storage
scp -p data_bin.* test1.* 172.*.*.77:/mysql/bigdata
77:
[root@nagiosdb log]# mysql -u root -p123456 --socket=/var/lib/mysql/mysql.sock
要指定一下socket登陆。
drop database bigdata;
create database bigdata;
use bigdata;
repair table test1;
repair table data_bin;
因为是直接拷贝数据文件,需要repair表。data_bin需要时间较长。
性能数据:
CREATE TABLE http_time AS SELECT
b.host_name,
b.service_description,
b.metric_name,
a.VALUE,
a.id_metric,
b.metric_id
FROM
data_bin a,
test1 b
WHERE
a.id_metric = b.metric_id
AND a.ctime >= unix_timestamp('2016-04-01')
AND a.ctime < unix_timestamp('2016-05-01')
/*
AND b.metric_name = 'time';
AND b.metric_name = 'CpuUser';
AND b.metric_name = 'phyUsed';
AND b.metric_name LIKE '/%';
*/
AND b.metric_name = 'time';
数据取值调整:
CREATE TABLE http_time_1 AS SELECT
host_name,
service_description,
metric_name,
ROUND(AVG(VALUE)*1000,2) as avg,
ROUND(MAX(VALUE)*1000,2) as max,
ROUND(MIN(VALUE)*1000,2) as min
FROM
http_time
GROUP BY
host_name
drop table cpu_user_1;
flush tables;
CREATE TABLE cpu_user_1 AS SELECT
host_name,
service_description,
metric_name,
ROUND(AVG(VALUE),2) as avg,
MAX(VALUE) as max,
MIN(VALUE) as min
FROM
cpu_user
GROUP BY
host_name
旧数据取值调整:
CREATE TABLE cpuuser_1 AS SELECT
host_name,
service_description,
metric_name,
AVG(VALUE) as avg,
MAX(VALUE) as max,
MIN(VALUE) as min
FROM
cpuuser
GROUP BY
host_name
SELECT
a.host_name,
b.host_name,
a.service_description,
a.metric_name,
round(a.avg,2),
a.max,
a.min,
b.service_description,
b.metric_name,
round(b.avg*1000,2),
round(b.max*1000,2),
round(b.min*1000,2)
FROM
cpuuser_1 a,
httptime_1 b
where a.host_name = b.host_name
35::
在centreon_storage库中创建关系表test1
CREATE TABLE test1 AS SELECT
index_data.host_name,
index_data.service_description,
metrics.metric_id,
metrics.metric_name
FROM
index_data,
metrics
WHERE
index_data.id = metrics.index_id;
把data_bin,test1 表的数据转移到172.25.128.77上面。放到bigdata数据库中。
cd /var/lib/mysql/centreon_storage
scp -p data_bin.* test1.* 172.*.*.77:/mysql/bigdata
77:
[root@nagiosdb log]# mysql -u root -p123456 --socket=/var/lib/mysql/mysql.sock
要指定一下socket登陆。
drop database bigdata;
create database bigdata;
use bigdata;
repair table test1;
repair table data_bin;
因为是直接拷贝数据文件,需要repair表。data_bin需要时间较长。
性能数据:
CREATE TABLE http_time AS SELECT
b.host_name,
b.service_description,
b.metric_name,
a.VALUE,
a.id_metric,
b.metric_id
FROM
data_bin a,
test1 b
WHERE
a.id_metric = b.metric_id
AND a.ctime >= unix_timestamp('2016-04-01')
AND a.ctime < unix_timestamp('2016-05-01')
/*
AND b.metric_name = 'time';
AND b.metric_name = 'CpuUser';
AND b.metric_name = 'phyUsed';
AND b.metric_name LIKE '/%';
*/
AND b.metric_name = 'time';
数据取值调整:
CREATE TABLE http_time_1 AS SELECT
host_name,
service_description,
metric_name,
ROUND(AVG(VALUE)*1000,2) as avg,
ROUND(MAX(VALUE)*1000,2) as max,
ROUND(MIN(VALUE)*1000,2) as min
FROM
http_time
GROUP BY
host_name
drop table cpu_user_1;
flush tables;
CREATE TABLE cpu_user_1 AS SELECT
host_name,
service_description,
metric_name,
ROUND(AVG(VALUE),2) as avg,
MAX(VALUE) as max,
MIN(VALUE) as min
FROM
cpu_user
GROUP BY
host_name
旧数据取值调整:
CREATE TABLE cpuuser_1 AS SELECT
host_name,
service_description,
metric_name,
AVG(VALUE) as avg,
MAX(VALUE) as max,
MIN(VALUE) as min
FROM
cpuuser
GROUP BY
host_name
SELECT
a.host_name,
b.host_name,
a.service_description,
a.metric_name,
round(a.avg,2),
a.max,
a.min,
b.service_description,
b.metric_name,
round(b.avg*1000,2),
round(b.max*1000,2),
round(b.min*1000,2)
FROM
cpuuser_1 a,
httptime_1 b
where a.host_name = b.host_name
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29500582/viewspace-2075306/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29500582/viewspace-2075306/