暂无图片
暂无图片
2
暂无图片
暂无图片
暂无图片

用Tableau可视化Vertica数据库之“MBPS&IOPS By Function”篇

原创 Quanwen Zhao 2021-12-24
6191

目录

前言

本月下旬,我在墨天轮上连续发表了几篇关于Vertica数据库原创博客文章,它们按发表的时间先后顺序分别是:
因为Vertica数据库非常适合海量数据的分析和处理,所以今天这篇文章将要介绍的是将Oracle数据库的MBPS&IOPS By Function性能查询数据导入Vertica数据库,然后用Tableau工具连接到Vertica,并对导入的性能表进行可视化操作。另外,我们知道Oracle数据库的动态性能视图V$IOSTAT_FUNCTION里有下面的一些数据列和对应的数据类型
DESC v$iostat_function
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 FUNCTION_ID                                                                NUMBER
 FUNCTION_NAME                                                              VARCHAR2(18)
 SMALL_READ_MEGABYTES                                                       NUMBER
 SMALL_WRITE_MEGABYTES                                                      NUMBER
 LARGE_READ_MEGABYTES                                                       NUMBER
 LARGE_WRITE_MEGABYTES                                                      NUMBER
 SMALL_READ_REQS                                                            NUMBER
 SMALL_WRITE_REQS                                                           NUMBER
 LARGE_READ_REQS                                                            NUMBER
 LARGE_WRITE_REQS                                                           NUMBER
 NUMBER_OF_WAITS                                                            NUMBER
 WAIT_TIME                                                                  NUMBER

其中,我们使用SQL语句查询V$IOSTAT_FUNCTION中的FUNCTION_IDFUNCTION_NAME后发现Oracle的IO按FUNCTION分类总共有14个不同的类别,请看下面的查询语句和结果:

SET PAGESIZE 30

COLUMN function_name FOR a18

SELECT function_id, function_name FROM v$iostat_function ORDER BY 1;

FUNCTION_ID FUNCTION_NAME
----------- ------------------------------------
          0 RMAN
          1 DBWR
          2 LGWR
          3 ARCH
          4 XDB
          5 Streams AQ
          6 Data Pump
          7 Recovery
          8 Buffer Cache Reads
          9 Direct Reads
         10 Direct Writes
         11 Smart Scan
         12 Archive Manager
         13 Others

14 rows selected.

因此,我们需要从个维度(最近1分钟最近1小时)可视化我们所提到的14个类别(在图表中也可将其称作“图例”)的IO(IO分为MBPSIOPS两类,即每秒的IO读写容量和每秒的IO读写请求)情况。其中,最近1分钟的数据保存在动态性能视图V$IOFUNCMETRIC里,最近1小时的数据保存在动态性能视图V$IOFUNCMETRIC_HISTORY上。所以两个维度两个IO类别的互相组合,我们将要使用个SQL查询来实现我们的业务需求。

[返回到顶部目录]

将Oracle数据库的MBPS&IOPS By Function查询导入Vertica数据库

将Oracle数据库的MBPS&IOPS By Function查询保存为CSV文件

和前两篇文章的方法相同,我们在SQL Develooper中分别以脚本方式运行下面的四个SQL查询并将其保存为CSV文件。具体的操作步骤有些繁琐,所以这里只贴出SQL代码,依次(首先,最近1分钟和最近1小时的MBPS;其次,最近1分钟和最近1小时的IOPS)如下所示:

-- Converting rows to columns Based on I/O Megabytes per Second in Last 1 Minute.
-- Vertical Axis Name: MB Per Sec

SET FEEDBACK  off;
SET SQLFORMAT csv;

SET LINESIZE 200
SET PAGESIZE 10

COLUMN sample_time   FORMAT a11
COLUMN function_name FORMAT a18
COLUMN io_mbps       FORMAT 999,999,999.999

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

WITH ifm AS
(
  SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi:ss') sample_time
       , function_name
       , ROUND((small_read_mbps+small_write_mbps+large_read_mbps+large_write_mbps), 3) io_mbps
  FROM v$iofuncmetric
)
SELECT * FROM ifm
PIVOT ( MAX(io_mbps)
        FOR function_name IN
        (  'Buffer Cache Reads' AS "Buffer Cache Reads"
         , 'Direct Reads'       AS "Direct Reads"
         , 'Direct Writes'      AS "Direct Writes"
         , 'DBWR'               AS "DBWR"
         , 'LGWR'               AS "LGWR"
         , 'ARCH'               AS "ARCH"
         , 'RMAN'               AS "RMAN"
         , 'Recovery'           AS "Recovery"
         , 'Data Pump'          AS "Data Pump"
         , 'Streams AQ'         AS "Streams AQ"
         , 'XDB'                AS "XDB"
         , 'Others'             AS "Others"
         , 'Archive Manager'    AS "Archive Manager"
         , 'Smart Scan'         AS "Smart Scan"
        )
      )
ORDER BY sample_time
;
-- Converting rows to columns Based on I/O Megabytes per Second in Last 1 Hour (interval by each minute).
-- Vertical Axis Name: MB Per Sec

SET FEEDBACK  off;
SET SQLFORMAT csv;

SET LINESIZE 200
SET PAGESIZE 80

COLUMN sample_time   FORMAT a11
COLUMN function_name FORMAT a18
COLUMN io_mbps       FORMAT 999,999,999.999

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

WITH ifmh AS
(
  SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi:ss') sample_time
       , function_name
       , ROUND((small_read_mbps+small_write_mbps+large_read_mbps+large_write_mbps), 3) io_mbps
  FROM v$iofuncmetric_history
)
SELECT * FROM ifmh
PIVOT ( MAX(io_mbps)
        FOR function_name IN
        (  'Buffer Cache Reads' AS "Buffer Cache Reads"
         , 'Direct Reads'       AS "Direct Reads"
         , 'Direct Writes'      AS "Direct Writes"
         , 'DBWR'               AS "DBWR"
         , 'LGWR'               AS "LGWR"
         , 'ARCH'               AS "ARCH"
         , 'RMAN'               AS "RMAN"
         , 'Recovery'           AS "Recovery"
         , 'Data Pump'          AS "Data Pump"
         , 'Streams AQ'         AS "Streams AQ"
         , 'XDB'                AS "XDB"
         , 'Others'             AS "Others"
         , 'Archive Manager'    AS "Archive Manager"
         , 'Smart Scan'         AS "Smart Scan"
        )
      )
ORDER BY sample_time
;
-- Converting rows to columns Based on I/O Requests per Second in Last 1 Minute.
-- Horizontal Axis Name: I/O Per Sec

SET FEEDBACK  off;
SET SQLFORMAT csv;

SET LINESIZE 200
SET PAGESIZE 10

COLUMN sample_time   FORMAT a11
COLUMN function_name FORMAT a18
COLUMN iops          FORMAT 999,999,999.999

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

WITH ifm AS
(
  SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi:ss') sample_time
       , function_name
       , ROUND((small_read_iops+small_write_iops+large_read_iops+large_write_iops), 3) iops
  FROM v$iofuncmetric
)
SELECT * FROM ifm
PIVOT ( MAX(iops)
        FOR function_name IN
        (  'Buffer Cache Reads' AS "Buffer Cache Reads"
         , 'Direct Reads'       AS "Direct Reads"
         , 'Direct Writes'      AS "Direct Writes"
         , 'DBWR'               AS "DBWR"
         , 'LGWR'               AS "LGWR"
         , 'ARCH'               AS "ARCH"
         , 'RMAN'               AS "RMAN"
         , 'Recovery'           AS "Recovery"
         , 'Data Pump'          AS "Data Pump"
         , 'Streams AQ'         AS "Streams AQ"
         , 'XDB'                AS "XDB"
         , 'Others'             AS "Others"
         , 'Archive Manager'    AS "Archive Manager"
         , 'Smart Scan'         AS "Smart Scan"
        )
      )
ORDER BY sample_time
;
-- Converting rows to columns Based on I/O Requests per Second in Last 1 Hour (interval by each minute).
-- Horizontal Axis Name: I/O Per Sec

SET FEEDBACK  off;
SET SQLFORMAT csv;

SET LINESIZE 200
SET PAGESIZE 80

COLUMN sample_time   FORMAT a11
COLUMN function_name FORMAT a18
COLUMN iops          FORMAT 999,999,999.999

ALTER SESSION SET nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

WITH ifmh AS
(
  SELECT TO_CHAR(end_time, 'yyyy-mm-dd hh24:mi:ss') sample_time
       , function_name
       , ROUND((small_read_iops+small_write_iops+large_read_iops+large_write_iops), 3) iops
  FROM v$iofuncmetric_history
)
SELECT * FROM ifmh
PIVOT ( MAX(iops)
        FOR function_name IN
        (  'Buffer Cache Reads' AS "Buffer Cache Reads"
         , 'Direct Reads'       AS "Direct Reads"
         , 'Direct Writes'      AS "Direct Writes"
         , 'DBWR'               AS "DBWR"
         , 'LGWR'               AS "LGWR"
         , 'ARCH'               AS "ARCH"
         , 'RMAN'               AS "RMAN"
         , 'Recovery'           AS "Recovery"
         , 'Data Pump'          AS "Data Pump"
         , 'Streams AQ'         AS "Streams AQ"
         , 'XDB'                AS "XDB"
         , 'Others'             AS "Others"
         , 'Archive Manager'    AS "Archive Manager"
         , 'Smart Scan'         AS "Smart Scan"
        )
      )
ORDER BY sample_time
;

由于CSV文件的内容过多,所以我把它们分别上传到了我的GitHub,您可以查看这4个文件:crtc_oracle_io_mbps_in_last_1_minute.csvcrtc_oracle_io_mbps_in_last_1_hour.csvcrtc_oracle_iops_in_last_1_minute.csvcrtc_oracle_iops_in_last_1_hour.csv

[返回到父目录] [返回到顶部目录]

将所有CSV文件上传到Vertica服务器的/home/dbadmin目录下

这里,省略具体的上传步骤和相关授权操作,最终的上传结果如下所示(用“<<==”标明):

[dbadmin@test ~]$ ls -lrht
total 184K
drwxr-xr-x 5 dbadmin verticadba  134 Dec 15 14:06 vdb_oracle_perf
......
-rw-r--r-- 1 dbadmin verticadba  225 Dec 23 10:54 crtc_oracle_io_mbps_in_last_1_minute.csv  <<==
-rw-r--r-- 1 dbadmin verticadba 3.0K Dec 23 10:56 crtc_oracle_io_mbps_in_last_1_hour.csv    <<==
-rw-r--r-- 1 dbadmin verticadba  233 Dec 23 10:57 crtc_oracle_iops_in_last_1_minute.csv     <<==
-rw-r--r-- 1 dbadmin verticadba 3.6K Dec 23 10:59 crtc_oracle_iops_in_last_1_hour.csv       <<==

[返回到父目录] [返回到顶部目录]

用vsql客户端命令连接到Vertica数据库

用Linux命令su切换Vertica数据库服务器的root用户到dbadmin用户,然后用vsql命令进行连接,下面是具体的操作过程:

[root@test ~]# su - dbadmin

[dbadmin@test ~]$ 
[dbadmin@test ~]$ /opt/vertica/bin/vsql -h 127.0.0.1 vdb_oracle_perf dbadmin
Password: 
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

vdb_oracle_perf=> 

[返回到父目录] [返回到顶部目录]

在Vertica数据库中创建相关的MBPS&IOPS By Function表

public的schema下,分别创建表crtc_oracle_io_mbps_in_last_1_minutecrtc_oracle_io_mbps_in_last_1_hourcrtc_oracle_iops_in_last_1_minutecrtc_oracle_iops_in_last_1_hour

vdb_oracle_perf=> 
vdb_oracle_perf=> CREATE TABLE public.crtc_oracle_io_mbps_in_last_1_minute
vdb_oracle_perf-> (sample_time        TIMESTAMP,
vdb_oracle_perf(>  buffer_cache_reads NUMBER(12,3),
vdb_oracle_perf(>  direct_reads       NUMBER(12,3),
vdb_oracle_perf(>  direct_writes      NUMBER(12,3),
vdb_oracle_perf(>  dbwr               NUMBER(12,3),
vdb_oracle_perf(>  lgwr               NUMBER(12,3),
vdb_oracle_perf(>  arch               NUMBER(12,3),
vdb_oracle_perf(>  rman               NUMBER(12,3),
vdb_oracle_perf(>  recovery           NUMBER(12,3),
vdb_oracle_perf(>  data_pump          NUMBER(12,3),
vdb_oracle_perf(>  streams_aq         NUMBER(12,3),
vdb_oracle_perf(>  xdb                NUMBER(12,3),
vdb_oracle_perf(>  others             NUMBER(12,3),
vdb_oracle_perf(>  archive_manager    NUMBER(12,3),
vdb_oracle_perf(>  smart_scan         NUMBER(12,3)
vdb_oracle_perf(> );
CREATE TABLE
vdb_oracle_perf=> 
vdb_oracle_perf=> 
vdb_oracle_perf=> CREATE TABLE public.crtc_oracle_io_mbps_in_last_1_hour
vdb_oracle_perf-> (sample_time        TIMESTAMP,
vdb_oracle_perf(>  buffer_cache_reads NUMBER(12,3),
vdb_oracle_perf(>  direct_reads       NUMBER(12,3),
vdb_oracle_perf(>  direct_writes      NUMBER(12,3),
vdb_oracle_perf(>  dbwr               NUMBER(12,3),
vdb_oracle_perf(>  lgwr               NUMBER(12,3),
vdb_oracle_perf(>  arch               NUMBER(12,3),
vdb_oracle_perf(>  rman               NUMBER(12,3),
vdb_oracle_perf(>  recovery           NUMBER(12,3),
vdb_oracle_perf(>  data_pump          NUMBER(12,3),
vdb_oracle_perf(>  streams_aq         NUMBER(12,3),
vdb_oracle_perf(>  xdb                NUMBER(12,3),
vdb_oracle_perf(>  others             NUMBER(12,3),
vdb_oracle_perf(>  archive_manager    NUMBER(12,3),
vdb_oracle_perf(>  smart_scan         NUMBER(12,3)
vdb_oracle_perf(> );
CREATE TABLE
vdb_oracle_perf=> 
vdb_oracle_perf=> 
vdb_oracle_perf=> CREATE TABLE public.crtc_oracle_iops_in_last_1_minute
vdb_oracle_perf-> (sample_time        TIMESTAMP,
vdb_oracle_perf(>  buffer_cache_reads NUMBER(12,3),
vdb_oracle_perf(>  direct_reads       NUMBER(12,3),
vdb_oracle_perf(>  direct_writes      NUMBER(12,3),
vdb_oracle_perf(>  dbwr               NUMBER(12,3),
vdb_oracle_perf(>  lgwr               NUMBER(12,3),
vdb_oracle_perf(>  arch               NUMBER(12,3),
vdb_oracle_perf(>  rman               NUMBER(12,3),
vdb_oracle_perf(>  recovery           NUMBER(12,3),
vdb_oracle_perf(>  data_pump          NUMBER(12,3),
vdb_oracle_perf(>  streams_aq         NUMBER(12,3),
vdb_oracle_perf(>  xdb                NUMBER(12,3),
vdb_oracle_perf(>  others             NUMBER(12,3),
vdb_oracle_perf(>  archive_manager    NUMBER(12,3),
vdb_oracle_perf(>  smart_scan         NUMBER(12,3)
vdb_oracle_perf(> );
CREATE TABLE
vdb_oracle_perf=> 
vdb_oracle_perf=> 
vdb_oracle_perf=> CREATE TABLE public.crtc_oracle_iops_in_last_1_hour
vdb_oracle_perf-> (sample_time        TIMESTAMP,
vdb_oracle_perf(>  buffer_cache_reads NUMBER(12,3),
vdb_oracle_perf(>  direct_reads       NUMBER(12,3),
vdb_oracle_perf(>  direct_writes      NUMBER(12,3),
vdb_oracle_perf(>  dbwr               NUMBER(12,3),
vdb_oracle_perf(>  lgwr               NUMBER(12,3),
vdb_oracle_perf(>  arch               NUMBER(12,3),
vdb_oracle_perf(>  rman               NUMBER(12,3),
vdb_oracle_perf(>  recovery           NUMBER(12,3),
vdb_oracle_perf(>  data_pump          NUMBER(12,3),
vdb_oracle_perf(>  streams_aq         NUMBER(12,3),
vdb_oracle_perf(>  xdb                NUMBER(12,3),
vdb_oracle_perf(>  others             NUMBER(12,3),
vdb_oracle_perf(>  archive_manager    NUMBER(12,3),
vdb_oracle_perf(>  smart_scan         NUMBER(12,3)
vdb_oracle_perf(> );
CREATE TABLE
vdb_oracle_perf=> 

[返回到父目录] [返回到顶部目录]

使用COPY命令将CSV文件导入刚创建的表中

在上一步操作中,我们已经创建成功了4个表。现在我们用COPY命令将上传到Vertica数据库服务器的4个CSV文件分别导入到那4个表中。操作步骤依次为:

vdb_oracle_perf=> 
vdb_oracle_perf=> COPY public.crtc_oracle_io_mbps_in_last_1_minute FROM '/home/dbadmin/crtc_oracle_io_mbps_in_last_1_minute.csv' EXCEPTIONS '/home/dbadmin/imp_io_mbps_1.log' DELIMITER AS ',';
NOTICE 7850:  In a multi-threaded load, rejected record data may be written to additional files
HINT:  Exceptions may be written to files [/home/dbadmin/imp_io_mbps_1.log], [/home/dbadmin/imp_io_mbps_1.log.1], etc
 Rows Loaded 
-------------
           1
(1 row)
vdb_oracle_perf=> 
vdb_oracle_perf=> COPY public.crtc_oracle_io_mbps_in_last_1_hour FROM '/home/dbadmin/crtc_oracle_io_mbps_in_last_1_hour.csv' EXCEPTIONS '/home/dbadmin/imp_io_mbps_2.log' DELIMITER AS ',';
NOTICE 7850:  In a multi-threaded load, rejected record data may be written to additional files
HINT:  Exceptions may be written to files [/home/dbadmin/imp_io_mbps_2.log], [/home/dbadmin/imp_io_mbps_2.log.1], etc
 Rows Loaded 
-------------
          61
(1 row)
vdb_oracle_perf=> 
vdb_oracle_perf=> COPY public.crtc_oracle_iops_in_last_1_minute FROM '/home/dbadmin/crtc_oracle_iops_in_last_1_minute.csv' EXCEPTIONS '/home/dbadmin/imp_iops_1.log' DELIMITER AS ',';
NOTICE 7850:  In a multi-threaded load, rejected record data may be written to additional files
HINT:  Exceptions may be written to files [/home/dbadmin/imp_iops_1.log], [/home/dbadmin/imp_iops_1.log.1], etc
 Rows Loaded 
-------------
           1
(1 row)
vdb_oracle_perf=> 
vdb_oracle_perf=> COPY public.crtc_oracle_iops_in_last_1_hour FROM '/home/dbadmin/crtc_oracle_iops_in_last_1_hour.csv' EXCEPTIONS '/home/dbadmin/imp_iops_2.log' DELIMITER AS ',';
NOTICE 7850:  In a multi-threaded load, rejected record data may be written to additional files
HINT:  Exceptions may be written to files [/home/dbadmin/imp_iops_2.log], [/home/dbadmin/imp_iops_2.log.1], etc
 Rows Loaded 
-------------
          61
(1 row)

vdb_oracle_perf=> 

[返回到父目录] [返回到顶部目录]

用Tableau可视化Vertica数据库的表

按Function Name分类的最近1分钟的IO MBPS

打开Tableau Desktop工具,然后连接到Vertica数据库,选择schema为public,然后将表crtc_oracle_io_mbps_in_last_1_minute拖动到指定位置,点击底部的工作表,进入工作表编辑区。详见下面两个屏幕截图:



接着,将工作区中左侧“数据”标签卡内“表”的度量名称Sample Time用鼠标拖到位于工作区上方标签名为“列”的右侧“标签框”中,同样的方法,将“表”14个度量值分别拖到位于工作区上方标签名为“行”的右侧“标签框”中,屏幕截图如下所示:


然后将标签名为“行”内的其余13个度量依次用鼠标拖动到工作区中部的图表纵坐标轴名称为“Smart Scan”的区域,也就是将这14个度量都合并到一个纵坐标轴上,顺便修改图表的名称和纵坐标轴名称,最终的效果如图所示:


因为每个度量在最近1分钟的数据只有一个值显示,显然,所有度量在纵坐标轴上显示的话,这个柱状条形图看起来很臃肿!因此,将这个14个度量换到横坐标轴上,详见下面的两个屏幕截图:



正如我们所看到的,只有LgwrOthers这两个度量有取值。

[返回到父目录] [返回到顶部目录]

按Function Name分类的最近1小时的IO MBPS

因为上一环节我们已经非常详细地说明了在Tablesau Desktop中可视化表crtc_oracle_io_mbps_in_last_1_minute的每一步骤,所以在这里,我们进行快速地操作,见如下屏幕截图。


最终,按Function Name分类的最近1小时的IO MBPS的面积堆叠图是这样的:


[返回到父目录] [返回到顶部目录]

按Function Name分类的最近1分钟的IOPS

这14个度量均在纵坐标轴上显示的条形柱状图为:


接着,我们将那14个度量都转换到横坐标轴上显示。其中,有取值的4个度量对应的屏幕截图分别如下所示:





[返回到父目录] [返回到顶部目录]

按Function Name分类的最近1小时的IOPS

最近1小时的IOPS的面积堆叠图设置相对简单,最终效果见下图:


以上就是这篇文章用Tableau可视化Vertica数据库之“MBPS&IOPS By Function”篇的所有内容。另外,您也可以从acquire_io_mbps_by_function.sqlacquire_iops_by_function.sql查看我前面提到的所有SQL源代码。如果您有好的建议或意见,欢迎在文章底部的评论区提出,我将逐条阅读,并在最快时间内回复。

[返回到父目录] [返回到顶部目录]

参考内容

「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论