libgfaipc/gfasysinfo/src/mysqlinfo.cpp

#include <stdio.h>
#include <unistd.h>
#include <string.h>
#include <sys/stat.h>
#include <sys/types.h>
#include <sys/syscall.h>
#include <iostream>
#include <fstream>
#include <sstream>
#include <gfa/gfasitarautils.h>
#include "mysqlinfo.h"
#include "mysqlwrap.h"
#include "procfile.h"
#include "../../src/procmem.h"

/////////////////////////////////////////////////////////////////////////////

#ifdef _DEBUG
#define TRACE(...)							fprintf(stdout, __VA_ARGS__), fflush(stdout)
#else	//	_DEBUG
#define TRACE(...)
#endif	//	_DEBUG

#define UNUSED(v)							(void)v
#define _countof(a)							(sizeof(a) / sizeof(*a))

#define _TIMESPEC_2_US(ts)					(((clock64_t)(ts).tv_sec) * 1000000LL + ((clock64_t)(ts).tv_nsec) / 1000LL)
#define _MYSQL_PROCESS_NAME					"mysqld"

/////////////////////////////////////////////////////////////////////////////

#define _MYSQL_HOST							"localhost"
#define _QUICK_UPDATE_INTERVAL_MS			3000LL
#define _SLOW_UPDATE_INTERVAL_MS			(4LL * _QUICK_UPDATE_INTERVAL_MS)
#define _LAZY_UPDATE_INTERVAL_MS			(5LL * _SLOW_UPDATE_INTERVAL_MS)

/////////////////////////////////////////////////////////////////////////////

static bool _IsIgnoredDb(const char *pszDb)
{
	static const char *pszIgnoredDBs[] = {"information_schema", "mysql", "test"};

	for(size_t i = 0; i < _countof(pszIgnoredDBs); ++i)
	{
		if(!strcmp(pszDb, pszIgnoredDBs[i]))
			return true;
	}

	return false;
}

static std::string & _rtrim(std::string &s, const char *sep)
{
	std::size_t f = s.find_last_not_of(sep);
	if(f != std::string::npos)
		s.erase(f + 1);
	return s;
}

static CMySqlVar _QuerySingleServerVariable(CMySqlDB &db, const char *pszVarName)
{
	char sql[256];
	sprintf(sql, "SHOW VARIABLES WHERE `Variable_Name` = '%s';", pszVarName);
	CMySqlResult res = db.Query(sql);
	if(!res.error())
	{
		CMySqlRow row;
		if(res.FetchRow(row))
			return row["Value"];
	}

	return CMySqlVar();
}

static CMySqlVar _QuerySingleGlobalStatusValue(CMySqlDB &db, const char *pszValName)
{
	char sql[256];
	sprintf(sql, "SHOW GLOBAL STATUS WHERE `Variable_name` = '%s';", pszValName);
	CMySqlResult res = db.Query(sql);
	if(!res.error())
	{
		CMySqlRow row;
		if(res.FetchRow(row))
			return row["Value"];
	}

	return CMySqlVar();
}

static pid_t _ReadPid(const char *pszPidFilePath)
{
	std::ifstream fPid(pszPidFilePath);
	std::string str;

	if(fPid.good())
	{
		str.assign((std::istreambuf_iterator<char>(fPid)), std::istreambuf_iterator<char>());
		return (pid_t)atoi(str.c_str());
	}

	return (pid_t)-1;
}

static clock64_t _GetHeartbeatUs(void)
{
	struct timespec	ts;
	::clock_gettime(CLOCK_MONOTONIC, &ts);
	return _TIMESPEC_2_US(ts);
}

static unsigned long long _GetDirectoryDiscUsage(const char *pszDirName)
{
	if(!pszDirName || !*pszDirName)
		return false;
	char szCmd[PATH_MAX];
	sprintf(szCmd, "du -sk %s", pszDirName);
	FILE *pf = popen(szCmd, "r");
	if(pf)
	{
		unsigned long long nSize = 0;
		char szLine[32];
		fgets(szLine, sizeof(szLine), pf);
		pclose(pf);

		if(sscanf(szLine, "%llu", &nSize) == 1)
		{
			return nSize * 1024ULL;
		}
	}
	return 0;
}

static bool _GetAppTimes(pid_t &pid, const char *pszProcName, GFA_APPCTRL_APPTIMES &at)
{
	CProcPidStatFile ppsf;
	static double fUtimeOld = 0.0, fStimeOld = 0.0;
	static clock64_t nHeartbeatOld = 0, nStartTime = 0;

	if(pid)
	{
		if(!ppsf.ReadFile(pid))
		{
			fUtimeOld = fStimeOld = 0.0;
			nHeartbeatOld = nStartTime = 0;
			if(!ppsf.ReadFile(pszProcName))
				return false;
			pid = ppsf.pid();
		}
	}
	else
	{
		if(!ppsf.ReadFile(pszProcName))
		{
			fUtimeOld = fStimeOld = 0.0;
			nHeartbeatOld = nStartTime = 0;
			return false;
		}
		pid = ppsf.pid();
	}
	
	if(!nStartTime)
		nStartTime = (clock64_t)(ppsf.starttime() * 1000000.0);

	double fInt, fUse, fUtime, fStime;
	clock64_t nHeartbeat = _GetHeartbeatUs();

	fUtime = ppsf.utime();
	fStime = ppsf.stime();
	at.fCpuTime = fUtime + fStime;

	fInt = (double)llabs(nHeartbeat - nStartTime) / 1000000.0;
	fUse = fUtime + fStime;
	at.fCpuAvg = fUse * 100.0 / fInt;

	if(nHeartbeatOld)
	{
		fInt = (double)llabs(nHeartbeat - nHeartbeatOld) / 1000000.0;
		fUse = fUtime - fUtimeOld + fStime - fStimeOld;
		at.fCpuCur = fUse * 100.0 / fInt;
	}

	fUtimeOld = fUtime;
	fStimeOld = fStime;
	nHeartbeatOld = nHeartbeat;
	return true;
}

static bool _GetAppMem(pid_t &pid, const char *pszProcName, GFA_APPCTRL_APPMEM &am)
{
	if(!pid && pszProcName && *pszProcName)
	{
		std::string cmd = CProcFile::FormatString("pidof -s %s", pszProcName);
		FILE *pf = popen(cmd.c_str(), "r");
		if(pf)
		{
			char szPid[16];
			fgets(szPid, sizeof(szPid), pf);
			std::string sPid(szPid);
			pid = CProcFile::StrToIntegral(_rtrim(sPid, " \r\n\t\v"));
			pclose(pf);
		}
	}

	if(pid)
	{
	    CProcMem pm(pid);

		if(pm.Update())
		{
			const VM_VALUE &rVmPeak = pm.VmPeak();
		    am.vmPeak = rVmPeak.valid ? rVmPeak.cb : 0;

			const VM_VALUE &rVmSize = pm.VmSize();
		    am.vmSize = rVmSize.valid ? rVmSize.cb : 0;

			const VM_VALUE &rVmHWM = pm.VmHWM();
		    am.vmHWM = rVmHWM.valid ? rVmHWM.cb : 0;

			const VM_VALUE &rVmRSS = pm.VmRSS();
		    am.vmRSS = rVmRSS.valid ? rVmRSS.cb : 0;
			return true;
		}
	}

	return false;
}

static size_t _GetTableFileSize(const char *pszDataDir, const char *pszDbsName, const char *pszTableName, const char *pszExt)
{
	std::string sdd(pszDataDir);
	struct stat s;
	char szPath[PATH_MAX];
	memset(&s, 0, sizeof(s));
	sprintf(szPath, "%s/%s/%s.%s", _rtrim(sdd, "/").c_str(), pszDbsName, pszTableName, pszExt);
	if(!stat(szPath, &s))
		return (size_t)s.st_size;
	return 0;
}

static void _ZeroSDB(GFA_SYSINFO_DATABASE &sdb, bool bInit, bool bRunning)
{
	if(!bInit)
	{
		memset(&sdb, 0, sizeof(sdb));
		return;
	}

	if(!bRunning)
	{
		memset(&sdb.svr, 0, sizeof(sdb.svr));
		memset(&sdb.res, 0, sizeof(sdb.res));
		memset(&sdb.dbs, 0, sizeof(sdb.dbs));
		sdb.nNumDatabases = 0;
	}
}

static time_t _GetTableCreationDateTime(CMySqlDB &db, const char *pshSchema, const char *pszTable)
{
	char sql[1024];
	sprintf(sql, "SELECT UNIX_TIMESTAMP(`CREATE_TIME`) as `ct` from `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` = '%s' AND `TABLE_NAME` = '%s';", pshSchema, pszTable);
	CMySqlResult res = db.Query(sql);
	if(!res.error())
	{
		CMySqlRow row;
		if(res.FetchRow(row))
		{
			time_t t = (uint64_t)row["ct"];
			return t;
		}
	}
	return 0;
}

/////////////////////////////////////////////////////////////////////////////

static bool _GetDbInfo(const std::string &sDbUser, const std::string &sDbPass, GFA_SYSINFO_DATABASE &sdb)
{
	static bool bSvrInit = false;
	static cy_time_t nTsLastQ = 0, nTsLastS = 0, nTsLastL = 0;
	cy_time_t nTsCur = CCycleTimer::GetMilliTick();

	/////////////////////////////////////////////////////////////////////////
	/////////////////////////////////////////////////////////////////////////
	/////////////////////////////////////////////////////////////////////////
	// quick update section
	
	if(nTsLastQ && ((nTsLastQ + _QUICK_UPDATE_INTERVAL_MS) > nTsCur))
		return true;
	nTsLastQ = nTsCur;
	sdb.bIsInit = true;

	/////////////////////////////////////////////////////////////////////////
	// cpu usage

    if(!_GetAppTimes(sdb.svr.pid, _MYSQL_PROCESS_NAME, sdb.res.at))
	{
		_ZeroSDB(sdb, true, false);
		bSvrInit = false;
		return false;
	}

	/////////////////////////////////////////////////////////////////////////
	// memory usage

    if(!_GetAppMem(sdb.svr.pid, _MYSQL_PROCESS_NAME, sdb.res.am))
	{
		_ZeroSDB(sdb, true, false);
		bSvrInit = false;
		return false;
	}

	/////////////////////////////////////////////////////////////////////////
	// connect

	CMySqlDB db;

	if(!db.Connect(_MYSQL_HOST, sDbUser.c_str(), sDbPass.c_str(), NULL))
	{
		_ZeroSDB(sdb, true, false);
		bSvrInit = false;
		nTsLastS = nTsLastL = 0;
		return false;
	}

    sdb.svr.bRunning = true;

	/////////////////////////////////////////////////////////////////////////
	/////////////////////////////////////////////////////////////////////////
	/////////////////////////////////////////////////////////////////////////
	// slow update section
	
	if(nTsLastS && ((nTsLastS + _SLOW_UPDATE_INTERVAL_MS) > nTsCur))
		return true;
	nTsLastS = nTsCur;

	/////////////////////////////////////////////////////////////////////////
	//

	if(!bSvrInit)
	{
		CMySqlVar vPidFile = _QuerySingleServerVariable(db, "pid_file");

		if(!vPidFile.IsValid())
		{
			_ZeroSDB(sdb, true, false);
			nTsLastS = nTsLastL = 0;
			return false;
		}

        sdb.svr.pid = _ReadPid(vPidFile.StrVal());

		CMySqlVar vSvrVersion = _QuerySingleServerVariable(db, "version");

		if(!vSvrVersion.IsValid())
		{
			_ZeroSDB(sdb, true, false);
			nTsLastS = nTsLastL = 0;
			return false;
		}

        vSvrVersion.CopyStrVal(sdb.svr.szVersion, GFA_MYSQL_MAX_SVR_VERSION_LENGTH - 1);

		CMySqlVar vDataDir = _QuerySingleServerVariable(db, "datadir");

		if(!vDataDir.IsValid())
		{
			_ZeroSDB(sdb, true, false);
			nTsLastS = nTsLastL = 0;
			return false;
		}

        vDataDir.CopyStrVal(sdb.svr.szDataDir, GFA_MYSQL_MAX_DATADIR_LENGTH - 1);

		/////////////////////////////////////////////////////////////////////
		// InnoDB table space

		CMySqlVar vInnoDbFpT = _QuerySingleServerVariable(db, "innodb_file_per_table");

		if(!vInnoDbFpT.IsValid())
		{
			_ZeroSDB(sdb, true, false);
			nTsLastS = nTsLastL = 0;
			return false;
		}

        sdb.svr.bInnoDbFilePerTable = !vInnoDbFpT.StrValCmp("ON");
		bSvrInit = true;
	}

	/////////////////////////////////////////////////////////////////////////
	// Uptime
	
	CMySqlVar vUptime = _QuerySingleGlobalStatusValue(db, "Uptime");

	if(!vUptime.IsValid())
	{
		_ZeroSDB(sdb, true, false);
		nTsLastS = nTsLastL = 0;
		return false;
	}

    sdb.svr.nUptimeSec = CProcFile::StrToIntegral(vUptime.StrVal());

	/////////////////////////////////////////////////////////////////////////
	/////////////////////////////////////////////////////////////////////////
	/////////////////////////////////////////////////////////////////////////
	// lazy update section
	
	if(nTsLastL && ((nTsLastL + _LAZY_UPDATE_INTERVAL_MS) > nTsCur))
		return true;
	nTsLastL = nTsCur;

	sdb.svr.nDiscUsageTotal = _GetDirectoryDiscUsage(sdb.svr.szDataDir);

	/////////////////////////////////////////////////////////////////////////
	// list databases

	CMySqlResult resDb = db.Query("SHOW DATABASES;");
	sdb.nNumDatabases = 0;

	if(resDb.error())
	{
		_ZeroSDB(sdb, true, false);
		nTsLastS = nTsLastL = 0;
		return false;
	}

	CMySqlRow rowDb;
	char sql[256];

	while(resDb.FetchRow(rowDb))
	{
		if(sdb.nNumDatabases >= GFA_MYSQL_MAX_DATABASES)
			break;

		const CMySqlVar &var = rowDb["Database"];

		if(var.IsValid() && !var.IsNull())
		{
			if(!_IsIgnoredDb(var.StrVal()))
			{
				GFA_MYSQL_SCHEMA &curDb = sdb.dbs[sdb.nNumDatabases++];
				var.CopyStrVal(curDb.szName, GFA_MYSQL_MAX_DB_NAME_LENGTH - 1);
				sprintf(sql, "SHOW TABLE STATUS IN `%s` WHERE `Comment` != 'VIEW';", var.StrVal());

				CMySqlRow rowTab;
				CMySqlResult resTab = db.Query(sql);
				curDb.nNumTables = 0;
				curDb.nSizeTotal = 0;

				if(!resTab.error())
				{
					while(resTab.FetchRow(rowTab))
					{
						if(curDb.nNumTables >= GFA_MYSQL_MAX_TABLES_PER_DATABASE)
							break;

						GFA_MYSQL_TABLE &curTab		= curDb.tables[curDb.nNumTables++];
						const CMySqlVar &vName		= rowTab["Name"];
						const CMySqlVar &vEngine	= rowTab["Engine"];
						const CMySqlVar &vVersion	= rowTab["Version"];
						const CMySqlVar &vRowFormat	= rowTab["Row_format"];
						const CMySqlVar &vCollation	= rowTab["Collation"];

						if(vVersion.IsValid())
							curTab.nVersion = vVersion;
						if(vName.IsValid())
							vName.CopyStrVal(curTab.szName, GFA_MYSQL_MAX_TABLE_NAME_LENGTH - 1);
						if(vEngine.IsValid())
							vEngine.CopyStrVal(curTab.szEngine, GFA_MYSQL_MAX_ENGINE_NAME_LENGTH - 1);
						if(vRowFormat.IsValid())
							vRowFormat.CopyStrVal(curTab.szRowFormat, GFA_MYSQL_MAX_ROW_FORMAT_LENGTH - 1);
						if(vCollation.IsValid())
							vCollation.CopyStrVal(curTab.szCollation, GFA_MYSQL_MAX_COLLATION_LENGTH - 1);

                        if(!vEngine.StrValCmp("InnoDB") && sdb.svr.bInnoDbFilePerTable)
						{
							// InnoDB sizes are rough estimates and not reliable, so we simply use the size of the table files, which
							// of course works only in a file-per-table tablespace!
							curTab.nSizeTotal = 0;
                            curTab.nSizeTotal += _GetTableFileSize(sdb.svr.szDataDir, curDb.szName, curTab.szName, "ibd");
                            curTab.nSizeTotal += _GetTableFileSize(sdb.svr.szDataDir, curDb.szName, curTab.szName, "frm");
                            curDb.nSizeTotal += curTab.nSizeTotal;
						}
						
						curTab.nCreateTime = _GetTableCreationDateTime(db, curDb.szName, curTab.szName);
					}
				}
			}
		}
	}

	return true;
}

/////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////

CMySqlInfo::CMySqlInfo(void) : m_bPaused(false)
{
}

/////////////////////////////////////////////////////////////////////////////

void* CMySqlInfo::ThreadRoutine(void *pParam)
{
	LPEXEC_PARAMS pep = (LPEXEC_PARAMS)pParam;
	TRACE("Enter CMySqlInfo::ThreadRoutine: TID: %ld\n", syscall(SYS_gettid));

	if(pep)
	{
		bool bRun = true;
		int nRet, nSig;
		GFA_SYSINFO_DATABASE sdb;

		while(bRun)
		{
			if((nRet = WaitSignalTimeout(_QUICK_UPDATE_INTERVAL_MS * 1000, &nSig)) == ETIMEDOUT)
			{
				if(!m_bPaused)
				{
					::_GetDbInfo(pep->sUser, pep->sPass, sdb);
					::GfaIpcAppCtrlUpdateDbInfo(pep->hAC, sdb);
				}
			}
			else if(!nRet) // signal received
			{
				TRACE("%s signal %d received.\n", "CMySqlInfo::ThreadRoutine", nSig);

				switch(nSig)
				{
				case S_Update:
					::_GetDbInfo(pep->sUser, pep->sPass, sdb);
					::GfaIpcAppCtrlUpdateDbInfo(pep->hAC, sdb);
					break;
				case S_UpdateAll:
					::_GetDbInfo(pep->sUser, pep->sPass, sdb);
					::GfaIpcAppCtrlUpdateDbInfo(pep->hAC, sdb);
					break;
				case S_Pause:
					m_bPaused = true;
					break;
				case S_Resume:
					m_bPaused = false;
					break;
				case S_Terminate:
					memset(&sdb, 0, sizeof(sdb));
					::GfaIpcAppCtrlUpdateDbInfo(pep->hAC, sdb);
					bRun = false;
					break;
				default:
					break;
				}
			}
			else
			{
				TRACE("%s error %d.\n", "CMySqlInfo::ThreadRoutine", nRet);
			}
		}
	}

	TRACE("%s exit.\n", "CMySqlInfo::ThreadRoutine");
	return NULL;
}