博客

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/，如需转载，请注明出处，否则将追究法律责任。