use of com.yahoo.dba.perf.myperf.common.DBInstanceInfo 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;
}
use of com.yahoo.dba.perf.myperf.common.DBInstanceInfo 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;
}
use of com.yahoo.dba.perf.myperf.common.DBInstanceInfo in project mysql_perf_analyzer by yahoo.
the class MetricsRetentionTask method run.
@Override
public void run() {
Thread.currentThread().setName("MetricsRetentionTask");
logger.info("Starting metrics purge job");
if (this.context.getMetricDb() == null) {
logger.info("MetricsDB has yet to set.");
return;
}
//get all dbids
List<Integer> ids = new ArrayList<Integer>();
if (this.dbidToPurge == null) {
for (Map.Entry<String, DBGroupInfo> e : context.getDbInfoManager().getClusters().entrySet()) {
DBGroupInfo g = e.getValue();
for (DBInstanceInfo i : g.getInstances()) {
ids.add(i.getDbid());
}
}
} else {
for (int id : this.dbidToPurge) ids.add(id);
}
Calendar c = Calendar.getInstance();
c.add(Calendar.DATE, -this.renentionDays);
Date dt = c.getTime();
//now get current timestamp
java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyyMMddHHmmss");
sdf.setTimeZone(TimeZone.getTimeZone("UTC"));
long endDate = Long.parseLong(sdf.format(dt));
List<MetricsGroup> mgs = new ArrayList<MetricsGroup>();
String[] groupNames = this.context.getMetricsDef().getGroupNames();
for (String grpName : groupNames) {
MetricsGroup group = this.context.getMetricsDef().getGroupByName(grpName);
//not supposed to be so
if (group == null)
continue;
if (//no sub group, add self
group.getSubGroups().size() == 0) {
mgs.add(group);
} else {
for (MetricsGroup g : group.getSubGroups()) mgs.add(g);
}
}
for (Map.Entry<String, UserDefinedMetrics> entry : this.context.getMetricsDef().getUdmManager().getUdms().entrySet()) {
MetricsGroup group = entry.getValue().getMetricsGroup();
//not supposed to be so
if (group == null)
continue;
mgs.add(group);
}
for (int dbid : ids) {
logger.info("Check and purge db: " + dbid);
for (MetricsGroup g : mgs) {
if (this.dbidToPurge != null)
this.context.getMetricDb().purgeAll(g.getSinkTableName(), dbid);
else
this.context.getMetricDb().purge(g.getSinkTableName(), dbid, endDate);
}
}
java.text.SimpleDateFormat sdf2 = new java.text.SimpleDateFormat("yyyyMMdd");
sdf.setTimeZone(TimeZone.getTimeZone("UTC"));
purgeAlertReports(Integer.parseInt(sdf2.format(dt)));
logger.info("Ended metrics purge job");
if (this.dbidToPurge == null)
this.context.getMetricDb().purgeAlerts(endDate);
//for now, we only do it once a day.
//TODO keep consistency with DB add/update/delete
logger.info("Retention job done.");
}
use of com.yahoo.dba.perf.myperf.common.DBInstanceInfo in project mysql_perf_analyzer by yahoo.
the class ReplLagQueryProcessor method querySingle.
@Override
public ResultList querySingle(MyPerfContext context, DBInstanceInfo dbinfo, String appUser, DBConnectionWrapper connWrapper, QueryParameters qps) throws SQLException {
QueryParameters qps2 = new QueryParameters();
qps2.setSql("mysql_repl_slave");
ResultList rList = null;
rList = context.getQueryEngine().executeQueryGeneric(qps2, connWrapper, qps.getMaxRows());
String master = null;
String port = null;
if (rList != null) {
java.util.LinkedHashMap<String, String> kvPairs = new java.util.LinkedHashMap<String, String>(rList.getRows().size());
for (ResultRow row : rList.getRows()) {
if (row.getColumns() == null || row.getColumns().size() < 2)
continue;
kvPairs.put(row.getColumns().get(0), row.getColumns().get(1));
}
master = kvPairs.get("Master_Host");
port = kvPairs.get("Master_Port");
if (master != null && !master.isEmpty() && port != null && !port.isEmpty()) {
logger.info("Query master status from (" + master + ":" + port + ")");
DBInstanceInfo dbinfo2 = new DBInstanceInfo();
dbinfo2.setHostName(master);
dbinfo2.setPort(port);
dbinfo2.setDatabaseName("information_schema");
dbinfo2.setDbType("mysql");
String url = dbinfo2.getConnectionString();
DBCredential cred = null;
if (appUser != null) {
try {
//first, check if the user has his own credential
cred = context.getMetaDb().retrieveDBCredential(appUser, dbinfo.getDbGroupName());
if (cred != null) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
DriverManager.setLoginTimeout(60);
conn = DriverManager.getConnection(url, cred.getUsername(), cred.getPassword());
if (conn != null) {
stmt = conn.createStatement();
rs = stmt.executeQuery("show master status");
//TODO rearrange order
ResultList rList2 = new ResultList();
rList2.setColumnDescriptor(rList.getColumnDescriptor());
for (String e : SLAVE_LAG_ENTRIES) {
ResultRow row = new ResultRow();
row.setColumnDescriptor(rList.getColumnDescriptor());
row.addColumn(e);
row.addColumn(kvPairs.get(e));
rList2.addRow(row);
kvPairs.remove(e);
}
while (rs != null && rs.next()) {
ResultRow row = new ResultRow();
row.setColumnDescriptor(rList.getColumnDescriptor());
row.addColumn("Master: File");
row.addColumn(rs.getString("File"));
rList2.addRow(row);
row = new ResultRow();
row.setColumnDescriptor(rList.getColumnDescriptor());
row.addColumn("Master: Position");
row.addColumn(rs.getString("Position"));
rList2.addRow(row);
//TODO gtid set
}
//now add slave position info
for (String e : SLAVE_POS_ENTRIES) {
ResultRow row = new ResultRow();
row.setColumnDescriptor(rList.getColumnDescriptor());
row.addColumn(e);
row.addColumn(kvPairs.get(e));
rList2.addRow(row);
kvPairs.remove(e);
}
//now add remaining entries
for (Map.Entry<String, String> e : kvPairs.entrySet()) {
ResultRow row = new ResultRow();
row.setColumnDescriptor(rList.getColumnDescriptor());
row.addColumn(e.getKey());
row.addColumn(e.getValue());
rList2.addRow(row);
}
return rList2;
}
} catch (Exception ex) {
} finally {
DBUtils.close(rs);
DBUtils.close(stmt);
DBUtils.close(conn);
}
}
} catch (Exception ex) {
}
}
}
}
return rList;
}
use of com.yahoo.dba.perf.myperf.common.DBInstanceInfo in project mysql_perf_analyzer by yahoo.
the class ReplShowProcessor method queryDetail.
/**
* Recursive probe. Note repl must be inside replStatus map
* @param context
* @param cred
* @param replStatus
* @param repl
*/
private void queryDetail(MyPerfContext context, DBCredential cred, Map<String, ReplStatus> replStatusMap, ReplServer replServer, int depth) {
logger.info("Probing " + replServer.toString());
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
//store new hosts
ReplStatus repl = replStatusMap.get(replServer.toString());
Map<String, String> hosts = new java.util.LinkedHashMap<String, String>();
boolean toprobe = depth < context.getMyperfConfig().getReplTopologyMaxDepth() && !replServer.probed;
try {
DBInstanceInfo dbinfo2 = new DBInstanceInfo();
dbinfo2.setHostName(replServer.host);
dbinfo2.setPort(replServer.port);
dbinfo2.setDatabaseName("information_schema");
dbinfo2.setDbType("mysql");
String url = dbinfo2.getConnectionString();
DriverManager.setLoginTimeout(60);
conn = DriverManager.getConnection(url, cred.getUsername(), cred.getPassword());
//we might not have permission
if (conn == null)
return;
stmt = conn.createStatement();
//first, probe master status
rs = stmt.executeQuery("show master status");
if (rs != null && rs.next()) {
java.sql.ResultSetMetaData meta = rs.getMetaData();
int col = meta.getColumnCount();
for (int i = 1; i <= col; i++) {
String colName = meta.getColumnLabel(i);
String val = rs.getString(i);
if ("File".equalsIgnoreCase(colName))
repl.masterFile = val;
else if ("Position".equalsIgnoreCase(colName))
repl.masterPosition = val;
else if ("Executed_Gtid_Set".equalsIgnoreCase(colName))
repl.masterExecutedGtidSet = val;
}
}
DBUtils.close(rs);
rs = null;
//then slave status
rs = stmt.executeQuery("show slave status");
if (rs != null && rs.next()) {
java.sql.ResultSetMetaData meta = rs.getMetaData();
int col = meta.getColumnCount();
for (int i = 1; i <= col; i++) {
String colName = meta.getColumnLabel(i);
String val = rs.getString(i);
if ("Master_Host".equalsIgnoreCase(colName))
repl.masterHost = getHostnameByIp(val);
else if ("Master_Port".equalsIgnoreCase(colName))
repl.masterPort = val;
else if ("Master_Log_File".equalsIgnoreCase(colName))
repl.masterLogFile = val;
else if ("Read_Master_Log_Pos".equalsIgnoreCase(colName))
repl.readMasterLogPos = val;
else if ("Relay_Master_Log_File".equalsIgnoreCase(colName))
repl.relayMasterLogFile = val;
else if ("Exec_Master_Log_Pos".equalsIgnoreCase(colName))
repl.execMasterLogPos = val;
else if ("Executed_Gtid_Set".equalsIgnoreCase(colName))
repl.executedGtidSet = val;
else if ("Seconds_Behind_Master".equalsIgnoreCase(colName))
repl.lag = val;
else if ("Slave_IO_Running".equalsIgnoreCase(colName))
repl.io = val;
else if ("Slave_SQL_Running".equalsIgnoreCase(colName))
repl.sql = val;
}
if (toprobe)
hosts.put(repl.masterHost, repl.masterPort);
}
DBUtils.close(rs);
rs = null;
if (toprobe) {
rs = stmt.executeQuery("select host from information_schema.processlist where command like 'Binlog Dump%'");
while (rs != null && rs.next()) {
String host = rs.getString(1);
if (host != null && host.indexOf(":") > 0) {
host = host.substring(0, host.indexOf(':'));
}
host = getHostnameByIp(host);
if (host != null && !host.isEmpty())
//we cannot probe port, assume 3306
hosts.put(host, "3306");
}
DBUtils.close(rs);
rs = null;
}
DBUtils.close(stmt);
stmt = null;
DBUtils.close(conn);
conn = null;
} catch (Exception ex) {
logger.log(Level.WARNING, "Failed to retrieve repl info from " + repl.hostname + ":" + repl.port, ex);
} finally {
DBUtils.close(rs);
DBUtils.close(stmt);
DBUtils.close(conn);
}
//probe
for (Map.Entry<String, String> server : hosts.entrySet()) {
if (replStatusMap.containsKey(server.getKey() + ":" + server.getValue()))
continue;
ReplStatus replChild = new ReplStatus();
replChild.hostname = server.getKey();
replChild.port = server.getValue();
ReplServer childServer = new ReplServer(replChild.hostname, replChild.port);
replStatusMap.put(childServer.toString(), replChild);
queryDetail(context, cred, replStatusMap, childServer, depth + 1);
}
logger.info("End of probing " + replServer.toString());
}
Aggregations