Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-6890

performance issues due to FK constraints without index on child

    XMLWordPrintable

Details

    • Incident report
    • Status: Closed
    • Critical
    • Resolution: Fixed
    • 2.0.6
    • 2.1.4
    • Installation (I)

    Description

      Various performance issues are seen due to missing indexes on foreign keys.

      select     acc.OWNER, acc.TABLE_NAME
          acc.CONSTRAINT_NAME,
      from       dba_cons_columns acc,
          dba_constraints ac
      where      ac.CONSTRAINT_NAME = acc.CONSTRAINT_NAME
      and       ac.CONSTRAINT_TYPE = 'R'
      and     acc.OWNER not in ('SYS','SYSTEM','SYSMAN','DBSNMP','QUEST','PERFSTAT')
      and     not exists (
              select  'TRUE'
              from    dba_ind_columns b
              where   b.TABLE_OWNER = acc.OWNER
              and     b.TABLE_NAME = acc.TABLE_NAME
              and     b.COLUMN_NAME = acc.COLUMN_NAME
              and     b.COLUMN_POSITION = acc.POSITION)
      order   by acc.OWNER, acc.TABLE_NAME, acc.CONSTRAINT_NAME, acc.COLUMN_NAME, acc.POSITION
      

      The following indexes should be added:

      create index CONFIG_IF1 ON CONFIG(ALERT_USRGRPID) ONLINE;
      create index CONFIG_IF2 ON CONFIG(DISCOVERY_GROUPID) ONLINE;
      create index DRULES_IF1 ON DRULES(PROXY_HOSTID) ONLINE;
      create index GRAPHS_IF1 ON GRAPHS(TEMPLATEID) ONLINE;
      create index GRAPHS_IF2 ON GRAPHS(YMIN_ITEMID) ONLINE;
      create index GRAPHS_IF3 ON GRAPHS(YMAX_ITEMID) ONLINE;
      create index GRAPH_DISCOVERY_IF1 ON GRAPH_DISCOVERY(PARENT_GRAPHID) ONLINE;
      create index HOSTS_IF1 ON HOSTS(MAINTENANCEID) ONLINE;
      create index HTTPSTEPITEM_IF1 ON HTTPSTEPITEM(ITEMID) ONLINE;
      create index HTTPTESTITEM_IF2 ON HTTPTESTITEM(ITEMID) ONLINE;
      create index ICON_MAP_IF1 ON ICON_MAP(DEFAULT_ICONID) ONLINE;
      create index ICON_MAPPING_IF1 ON ICON_MAPPING(ICONID) ONLINE;
      create index ITEMS_IF1 ON ITEMS(INTERFACEID) ONLINE;
      create index ITEM_DISCOVERY_IF1 ON ITEM_DISCOVERY(PARENT_ITEMID) ONLINE;
      create index MAINTENANCES_GROUPS_IF1 ON MAINTENANCES_GROUPS(GROUPID) ONLINE;
      create index MAINTENANCES_HOSTS_IF1 ON MAINTENANCES_HOSTS(HOSTID) ONLINE;
      create index MAINTENANCES_WINDOWS_IF1 ON MAINTENANCES_WINDOWS(TIMEPERIODID) ONLINE;
      create index NODES_IF1 ON NODES(MASTERID) ONLINE;
      create index OPCOMMAND_IF1 ON OPCOMMAND(SCRIPTID) ONLINE;
      create index OPCOMMAND_GRP_IF1 ON OPCOMMAND_GRP(GROUPID) ONLINE;
      create index OPCOMMAND_HST_IF1 ON OPCOMMAND_HST(HOSTID) ONLINE;
      create index OPGROUP_IF1 ON OPGROUP(GROUPID) ONLINE;
      create index OPMESSAGE_IF1 ON OPMESSAGE(MEDIATYPEID) ONLINE;
      create index OPMESSAGE_GRP_IF1 ON OPMESSAGE_GRP(USRGRPID) ONLINE;
      create index OPMESSAGE_USR_IF1 ON OPMESSAGE_USR(USERID) ONLINE;
      create index OPTEMPLATE_IF1 ON OPTEMPLATE(TEMPLATEID) ONLINE;
      create index SCREENS_IF1 ON SCREENS(TEMPLATEID) ONLINE;
      create index SCREENS_ITEMS_IF1 ON SCREENS_ITEMS(SCREENID) ONLINE;
      create index SCRIPTS_IF1 ON SCRIPTS(USRGRPID) ONLINE;
      create index SCRIPTS_IF2 ON SCRIPTS(GROUPID) ONLINE;
      create index SLIDES_IF1 ON SLIDES(SCREENID) ONLINE;
      create index SYSMAPS_IF1 ON SYSMAPS(BACKGROUNDID) ONLINE;
      create index SYSMAPS_IF2 ON SYSMAPS(ICONMAPID) ONLINE;
      create index SYSMAPS_ELEMENTS_IF1 ON SYSMAPS_ELEMENTS(SYSMAPID) ONLINE;
      create index SYSMAPS_ELEMENTS_IF2 ON SYSMAPS_ELEMENTS(ICONID_OFF) ONLINE;
      create index SYSMAPS_ELEMENTS_IF3 ON SYSMAPS_ELEMENTS(ICONID_ON) ONLINE;
      create index SYSMAPS_ELEMENTS_IF4 ON SYSMAPS_ELEMENTS(ICONID_DISABLED) ONLINE;
      create index SYSMAPS_ELEMENTS_IF5 ON SYSMAPS_ELEMENTS(ICONID_MAINTENANCE) ONLINE;
      create index SYSMAPS_LINKS_IF1 ON SYSMAPS_LINKS(SYSMAPID) ONLINE;
      create index SYSMAPS_LINKS_IF2 ON SYSMAPS_LINKS(SELEMENTID1) ONLINE;
      create index SYSMAPS_LINKS_IF3 ON SYSMAPS_LINKS(SELEMENTID2) ONLINE;
      create index SYSMAPS_LINK_TRIGGERS_IF1 ON SYSMAPS_LINK_TRIGGERS(TRIGGERID) ONLINE;
      create index TRIGGERS_IF1 ON TRIGGERS(TEMPLATEID) ONLINE;
      create index TRIGGER_DISCOVERY_IF1 ON TRIGGER_DISCOVERY(PARENT_TRIGGERID) ONLINE;
      create index USERS_GROUPS_IF1 ON USERS_GROUPS(USERID) ONLINE;
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              ntatsumi Noriaki Tatsumi
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: