Examples with ResultList com.yahoo.dba.perf.myperf.common.ResultList used on opensource projects

Example 6 with ResultList

use of com.yahoo.dba.perf.myperf.common.ResultList in project mysql_perf_analyzer by yahoo.

the class MetricsDbBase method retrieveMetrics.

public ResultList retrieveMetrics(String metricGroupName, int dbid, long startDate, long endDate) {
    int[] snaps = this.getSnapshostRange(startDate, endDate);
    //no data
    if (snaps == null)
        return null;
    //later, connection pooling
    ResultList rList = null;
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    String sql = "select * from " + metricGroupName + " where dbid=? and snap_id between ? and ? order by dbid, snap_id";
    //String sql = "select * from "+metricGroupName+" where dbid=?";
    logger.log(Level.FINE, "To retrieve metrics " + metricGroupName + " for db " + dbid + " with time range (" + startDate + ", " + endDate + "), snap (" + snaps[0] + ", " + snaps[1] + ")");
    try {
        conn = createConnection(true);
        stmt = conn.prepareStatement(sql);
        stmt.setFetchSize(1000);
        //stmt.setMaxRows(5000);
        stmt.setInt(1, dbid);
        stmt.setInt(2, snaps[0]);
        stmt.setInt(3, snaps[1]);
        rs = stmt.executeQuery();
        rList = ResultListUtil.fromSqlResultSet(rs, 5000);
    } catch (Exception ex) {
        logger.log(Level.SEVERE, "Failed to retrieve metrics " + metricGroupName + " for db " + dbid + " with time range (" + startDate + ", " + endDate + ")", ex);
    } finally {
        DBUtils.close(stmt);
        DBUtils.close(conn);
    }
    return rList;
}

Example 7 with ResultList

use of com.yahoo.dba.perf.myperf.common.ResultList in project mysql_perf_analyzer by yahoo.

the class MetricsDbBase method retrieveUDMMetrics.

/** 
	   * Retrieve user defined merics
	   * @param metrics
	   * @param dbid
	   * @param startDate
	   * @param endDate
	   * @return
	   */
public ResultList retrieveUDMMetrics(String metric, int dbid, long startDate, long endDate) {
    int[] snaps = this.getSnapshostRange(startDate, endDate);
    //no data
    if (snaps == null)
        return null;
    //later, connection pooling
    ResultList rList = null;
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    int code = 0;
    if (this.metricCodeMap.containsKey(metric)) {
        code = this.metricCodeMap.get(metric);
    } else {
        logger.warning("Failed to find metrics code for " + metric + ", " + this.metricCodeMap);
        return null;
    }
    String sql = "select SNAP_ID, TS, METRIC_ID, VALUE from METRIC_GENERIC where dbid=? and snap_id between ? and ? and METRIC_ID=? order by dbid, METRIC_ID, snap_id";
    //String sql = "select * from "+metricGroupName+" where dbid=?";
    logger.log(Level.INFO, "To retrieve " + metric + ", " + code + " on db " + dbid + " with time range (" + startDate + ", " + endDate + "), using " + sql);
    try {
        conn = createConnection(true);
        stmt = conn.prepareStatement(sql);
        stmt.setFetchSize(1000);
        //stmt.setMaxRows(5000);
        stmt.setInt(1, dbid);
        stmt.setInt(2, snaps[0]);
        stmt.setInt(3, snaps[1]);
        stmt.setLong(4, code);
        rs = stmt.executeQuery();
        if (rs == null)
            return rList;
        rList = new ResultList();
        //java.sql.ResultSetMetaData meta =  rs.getMetaData();
        ColumnDescriptor desc = new ColumnDescriptor();
        desc.addColumn("SNAP_ID", true, 1);
        desc.addColumn("TS", true, 2);
        desc.addColumn(metric, true, 3);
        rList.setColumnDescriptor(desc);
        int rowCnt = 0;
        //List<ColumnInfo> cols = desc.getColumns();
        while (rs.next()) {
            //logger.info(new java.util.Date()+": process "+rowCnt+" rows");
            ResultRow row = new ResultRow();
            row.setColumnDescriptor(desc);
            java.util.ArrayList<String> cols2 = new java.util.ArrayList<String>(3);
            cols2.add(rs.getString(1));
            cols2.add(rs.getString(2));
            cols2.add(rs.getString(4));
            row.setColumns(cols2);
            rList.addRow(row);
            rowCnt++;
            if (rowCnt >= 5000)
                break;
        }
        logger.info(new java.util.Date() + ": Process results done: " + rList.getRows().size());
    } catch (Exception ex) {
        logger.log(Level.SEVERE, "Failed to retrieve UDM " + metric + " for db " + dbid + " with time range (" + startDate + ", " + endDate + ")", ex);
    } finally {
        DBUtils.close(stmt);
        DBUtils.close(conn);
    }
    return rList;
}

Example 8 with ResultList

use of com.yahoo.dba.perf.myperf.common.ResultList in project mysql_perf_analyzer by yahoo.

the class MetricsDbBase method retrieveMetricsStatus.

public ResultList retrieveMetricsStatus(String metricGroupName, int dbid, long startDate, long endDate) {
    int[] snaps = this.getSnapshostRange(startDate, endDate);
    //no data
    if (snaps == null)
        return null;
    //later, connection pooling
    ResultList rList = null;
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    String sql = "select DBID, SNAP_ID, TS, SQL_TIME, SLOW_QUERIES from " + metricGroupName + " where dbid=? and snap_id between ? and ? order by dbid, snap_id";
    //String sql = "select * from "+metricGroupName+" where dbid=?";
    logger.log(Level.FINE, "Retrieve metrics status from " + metricGroupName + " for db " + dbid + " with time range (" + startDate + ", " + endDate + ")");
    try {
        conn = createConnection(true);
        stmt = conn.prepareStatement(sql);
        stmt.setInt(1, dbid);
        stmt.setInt(2, snaps[0]);
        stmt.setInt(3, snaps[1]);
        rs = stmt.executeQuery();
        rList = ResultListUtil.fromSqlResultSet(rs, 5000);
    } catch (Exception ex) {
        logger.log(Level.SEVERE, "Failed to retrieve metrics " + metricGroupName + " for db " + dbid + " with time range (" + startDate + ", " + endDate + ")", ex);
    } finally {
        DBUtils.close(rs);
        DBUtils.close(stmt);
        DBUtils.close(conn);
    }
    return rList;
}

Example 9 with ResultList

use of com.yahoo.dba.perf.myperf.common.ResultList in project mysql_perf_analyzer by yahoo.

the class VardiffController method handleRequestImpl.

@Override
protected ModelAndView handleRequestImpl(HttpServletRequest req, HttpServletResponse resp) throws Exception {
    int status = Constants.STATUS_OK;
    String message = "OK";
    logger.info("receive url " + req.getQueryString());
    QueryParameters qps = null;
    DBInstanceInfo dbinfo = null;
    DBInstanceInfo dbinfo2 = null;
    DBConnectionWrapper connWrapper = null;
    DBConnectionWrapper connWrapper2 = null;
    qps = WebAppUtil.parseRequestParameter(req);
    qps.setSql("mysql_global_variables");
    qps.getSqlParams().put("p_1", "");
    String group2 = req.getParameter("p_1");
    String host2 = req.getParameter("p_2");
    //validation input
    String validation = qps.validate();
    if (validation == null || validation.isEmpty()) {
        //do we have such query?
        try {
            QueryInputValidator.validateSql(this.frameworkContext.getSqlManager(), qps);
        } catch (Exception ex) {
            validation = ex.getMessage();
        }
    }
    if (validation != null && !validation.isEmpty())
        return this.respondFailure(validation, req);
    dbinfo = this.frameworkContext.getDbInfoManager().findDB(qps.getGroup(), qps.getHost());
    if (dbinfo == null)
        return this.respondFailure("Cannot find record for DB (" + qps.getGroup() + ", " + qps.getHost() + ")", req);
    dbinfo2 = this.frameworkContext.getDbInfoManager().findDB(group2, host2);
    if (dbinfo2 == null)
        return this.respondFailure("Cannot find record for DB (" + group2 + ", " + host2 + ")", req);
    try {
        connWrapper = WebAppUtil.getDBConnection(req, this.frameworkContext, dbinfo);
        if (connWrapper == null) {
            status = Constants.STATUS_BAD;
            message = "failed to connect to target db (" + dbinfo + ")";
        } else {
            connWrapper2 = WebAppUtil.getDBConnection(req, this.frameworkContext, dbinfo2);
            if (connWrapper2 == null) {
                status = Constants.STATUS_BAD;
                message = "failed to connect to target db (" + dbinfo2 + ")";
            }
        }
    } catch (Throwable th) {
        logger.log(Level.SEVERE, "Exception", th);
        status = Constants.STATUS_BAD;
        message = "Failed to get connection to target db (" + dbinfo + "): " + th.getMessage();
    }
    if (status == -1)
        return this.respondFailure(message, req);
    //when we reach here, at least we have valid query and can connect to db	
    WebAppUtil.storeLastDbInfoRequest(qps.getGroup(), qps.getHost(), req);
    ModelAndView mv = null;
    ResultList rList = null;
    ResultList rList2 = null;
    try {
        rList = this.frameworkContext.getQueryEngine().executeQueryGeneric(qps, connWrapper, qps.getMaxRows());
        rList2 = this.frameworkContext.getQueryEngine().executeQueryGeneric(qps, connWrapper2, qps.getMaxRows());
        logger.info("Done query " + qps.getSql() + " with " + (rList != null ? rList.getRows().size() : 0) + " records, " + (rList2 != null ? rList2.getRows().size() : 0) + " records");
        WebAppUtil.closeDBConnection(req, connWrapper, false, this.getFrameworkContext().getMyperfConfig().isReuseMonUserConnction());
        WebAppUtil.closeDBConnection(req, connWrapper2, false, this.getFrameworkContext().getMyperfConfig().isReuseMonUserConnction());
    } catch (Throwable ex) {
        logger.log(Level.SEVERE, "Exception", ex);
        if (ex instanceof SQLException) {
            SQLException sqlEx = SQLException.class.cast(ex);
            String msg = ex.getMessage();
            logger.info(sqlEx.getSQLState() + ", " + sqlEx.getErrorCode() + ", " + msg);
            //check if the connection is still good
            if (!DBUtils.checkConnection(connWrapper.getConnection())) {
                WebAppUtil.closeDBConnection(req, connWrapper, true, false);
            } else
                WebAppUtil.closeDBConnection(req, connWrapper, true, false);
            if (!DBUtils.checkConnection(connWrapper2.getConnection())) {
                WebAppUtil.closeDBConnection(req, connWrapper2, true, false);
            } else
                WebAppUtil.closeDBConnection(req, connWrapper2, true, false);
        } else {
            WebAppUtil.closeDBConnection(req, connWrapper, false, this.getFrameworkContext().getMyperfConfig().isReuseMonUserConnction());
            WebAppUtil.closeDBConnection(req, connWrapper2, false, this.getFrameworkContext().getMyperfConfig().isReuseMonUserConnction());
        }
        status = Constants.STATUS_BAD;
        message = "Exception: " + ex.getMessage();
    }
    if (status == Constants.STATUS_BAD)
        return this.respondFailure(message, req);
    HashMap<String, String> param1 = new HashMap<String, String>(rList.getRows().size());
    HashMap<String, String> param2 = new HashMap<String, String>(rList2.getRows().size());
    for (ResultRow r : rList.getRows()) {
        param1.put(r.getColumns().get(0).toUpperCase(), r.getColumns().get(1));
    }
    for (ResultRow r : rList2.getRows()) {
        param2.put(r.getColumns().get(0).toUpperCase(), r.getColumns().get(1));
    }
    ColumnDescriptor desc = new ColumnDescriptor();
    desc.addColumn("VARIABLE_NAME", false, 1);
    desc.addColumn("DB1", false, 2);
    desc.addColumn("DB2", false, 3);
    ResultList fList = new ResultList();
    fList.setColumnDescriptor(desc);
    HashSet<String> diffSet = new HashSet<String>();
    for (Map.Entry<String, String> e : param1.entrySet()) {
        String k = e.getKey();
        String v = e.getValue();
        if (v != null)
            v = v.trim();
        else
            v = "";
        String v2 = null;
        if (param2.containsKey(k))
            v2 = param2.get(k);
        if (v2 != null)
            v2 = v2.trim();
        else
            v2 = "";
        if (!v.equals(v2)) {
            ResultRow row = new ResultRow();
            List<String> cols = new ArrayList<String>();
            cols.add(k);
            cols.add(v);
            cols.add(v2);
            row.setColumns(cols);
            row.setColumnDescriptor(desc);
            fList.addRow(row);
            diffSet.add(k);
        }
    }
    for (Map.Entry<String, String> e : param2.entrySet()) {
        String k = e.getKey();
        String v = e.getValue();
        if (v == null || v.isEmpty())
            continue;
        if (diffSet.contains(k) || param1.containsKey(k))
            continue;
        ResultRow row = new ResultRow();
        List<String> cols = new ArrayList<String>();
        cols.add(k);
        cols.add("");
        cols.add(v);
        row.setColumns(cols);
        row.setColumnDescriptor(desc);
        fList.addRow(row);
    }
    mv = new ModelAndView(this.jsonView);
    if (req.getParameter("callback") != null && req.getParameter("callback").trim().length() > 0)
        //YUI datasource binding
        mv.addObject("callback", req.getParameter("callback"));
    mv.addObject("json_result", ResultListUtil.toJSONString(fList, qps, status, message));
    return mv;
}

Example 10 with ResultList

use of com.yahoo.dba.perf.myperf.common.ResultList in project mysql_perf_analyzer by yahoo.

the class VarhistoryController method handleRequestImpl.

@Override
protected ModelAndView handleRequestImpl(HttpServletRequest req, HttpServletResponse resp) throws Exception {
    int status = Constants.STATUS_OK;
    String message = "OK";
    logger.info("receive url " + req.getQueryString());
    QueryParameters qps = null;
    DBInstanceInfo dbinfo = null;
    //first session check
    boolean isSessionValid = WebAppUtil.hasValidSession(req);
    if (!isSessionValid)
        return this.respondFailure("session timeout. Please logout and re-login.", req);
    qps = WebAppUtil.parseRequestParameter(req);
    qps.setSql("mysql_global_variables");
    dbinfo = this.frameworkContext.getDbInfoManager().findDB(qps.getGroup(), qps.getHost());
    if (dbinfo == null)
        return this.respondFailure("Cannot find  record for DB (" + qps.getGroup() + ", " + qps.getHost() + ")", req);
    //when we reach here, at least we have valid query and can connect to db	
    WebAppUtil.storeLastDbInfoRequest(qps.getGroup(), qps.getHost(), req);
    ModelAndView mv = null;
    ResultList rList = new ResultList();
    ColumnDescriptor desc = new ColumnDescriptor();
    desc.addColumn("VARIABLE_NAME", false, 1);
    desc.addColumn("VARIABLE_VALUE", false, 2);
    desc.addColumn("COMMENTS", false, 3);
    rList.setColumnDescriptor(desc);
    try {
        ConfigHistory ch = ConfigHistory.load(new File(new File(this.frameworkContext.getFileReposirtoryPath()), "autoscan"), dbinfo);
        if (ch != null && ch.getChanges().size() > 0) {
            {
                ResultRow row = new ResultRow();
                List<String> cols = new ArrayList<String>();
                cols.add("CHANGES");
                cols.add("");
                cols.add(ch.getStartingConfig().getTime() + " - " + ch.getLastCheckedConfig().getTime());
                row.setColumns(cols);
                row.setColumnDescriptor(desc);
                rList.addRow(row);
            }
            //list changed in reverse order
            for (int i = ch.getChanges().size() - 1; i >= 0; i--) {
                ConfigBlock cb = ch.getChanges().get(i);
                ResultRow row = new ResultRow();
                List<String> cols = new ArrayList<String>();
                cols.add("CHANGE TIME");
                cols.add(cb.getTime());
                cols.add("Timestamp (UTC) when checked");
                row.setColumns(cols);
                row.setColumnDescriptor(desc);
                rList.addRow(row);
                HashMap<String, String> changes = new HashMap<String, String>();
                //scan changes with old value
                for (Map.Entry<String, String> e : cb.getVariables().entrySet()) {
                    String key = e.getKey();
                    String val = e.getValue();
                    if (key.startsWith("+-")) {
                        changes.put(key.substring(2), val);
                    }
                }
                for (Map.Entry<String, String> e : cb.getVariables().entrySet()) {
                    String key = e.getKey();
                    String v = e.getValue();
                    row = new ResultRow();
                    cols = new ArrayList<String>();
                    if (key.startsWith("+-"))
                        //skip
                        continue;
                    else if (key.startsWith("+-"))
                        //removed
                        cols.add(key.substring(1));
                    else
                        cols.add(key);
                    cols.add(v);
                    if (changes.containsKey(key))
                        cols.add("Prev Value: " + changes.get(key));
                    else if (key.startsWith("-"))
                        cols.add("Removed");
                    else
                        cols.add("");
                    row.setColumns(cols);
                    row.setColumnDescriptor(desc);
                    rList.addRow(row);
                }
                //add an empty line
                row = new ResultRow();
                cols = new ArrayList<String>();
                cols.add("");
                cols.add("");
                cols.add("");
                row.setColumns(cols);
                row.setColumnDescriptor(desc);
                rList.addRow(row);
            }
        }
        if (ch != null) {
            ConfigBlock cb = ch.getStartingConfig();
            ResultRow row = new ResultRow();
            List<String> cols = new ArrayList<String>();
            cols.add("FIRST RECORD TIME");
            cols.add(cb.getTime());
            if (ch != null && ch.getChanges().size() > 0)
                cols.add("First Recorded Timestamp (UTC)");
            else
                cols.add("No Changes Since First Check (Timestamp UTC)");
            row.setColumns(cols);
            row.setColumnDescriptor(desc);
            rList.addRow(row);
            for (Map.Entry<String, String> e : cb.getVariables().entrySet()) {
                String key = e.getKey();
                String v = e.getValue();
                row = new ResultRow();
                cols = new ArrayList<String>();
                cols.add(key);
                cols.add(v);
                cols.add("");
                row.setColumns(cols);
                row.setColumnDescriptor(desc);
                rList.addRow(row);
            }
        } else {
            status = Constants.STATUS_BAD;
            message = "No variable configuration history has been recorded yet.";
        }
    } catch (Throwable ex) {
        logger.log(Level.SEVERE, "Exception", ex);
        status = Constants.STATUS_BAD;
        message = "Exception: " + ex.getMessage();
    }
    mv = new ModelAndView(this.jsonView);
    if (req.getParameter("callback") != null && req.getParameter("callback").trim().length() > 0)
        //YUI datasource binding
        mv.addObject("callback", req.getParameter("callback"));
    mv.addObject("json_result", ResultListUtil.toJSONString(rList, qps, status, message));
    return mv;
}