cfZen

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

<!--- set these according to your database size: 14400 and 1800 were used after extensive testing
	on a system with 8G ram and quad core in 2009 for an 18G database; you can easily try different
	values here by trying them in the URL
	(ex: /OPTIMIZE_ALL_DB_tables.cfm?queryTimeout=5&numOfTables=5) --->
<cfparam name="URL.RequestTimeout" default="14400">
<cfparam name="URL.queryTimeout" default="1800">
<cfparam name="URL.myDatasource" default="my_database">
<cfparam name="URL.numOfTables" default="1000000">

<!--- set u/p to one that has permissions to run OPTIMIZE & FLUSH on your tables --->
<cfset Variables.myUsername = "db_user">
<cfset Variables.myPass = "my_password">
<cfset Variables.toAddresses = "webmaster@mydomain.com;aaron@mydomain.com">
<cfset Variables.fromAddress = "webmaster@mydomain.com">

<cfsetting requesttimeout="#URL.RequestTimeout#">


<!--- get host and instance if they don't already exist --->
<cfif NOT IsDefined("Server.hostName")>

	<cftry>
		<cfset local.oInetAddress = CreateObject("java", "java.net.InetAddress")>
		<cfset Server.hostName = local.oInetAddress.getLocalHost().getHostName()>

		<cfcatch type="application">
			<cfset Server.hostName = "Could not determine with java.net.InetAddress">
		</cfcatch>
	</cftry>

</cfif>

<cfif NOT IsDefined("Server.instanceName")>

	<cftry>
		<cfset local.oJRun = CreateObject("Java", "jrunx.kernel.JRun")>
		<cfset Server.instanceName = local.oJRun.getServerName()>

		<cfcatch type="application">
			<cfset Server.instanceName = "Could not determine with jrunx.kernel.JRun">
		</cfcatch>
	</cftry>

</cfif>



<cfquery name="rsAllTables"
		datasource="#URL.myDatasource#"
		username="#Variables.myUsername#"
		password="#Variables.myPass#">
	SHOW TABLES IN peoplexs_test;
</cfquery>

<!--- <cfdump var="#rsAllTables#" format="text"> --->
<cfset processInfo = "">

<cfoutput query="rsAllTables" maxrows="#URL.numOfTables#">

	<cfset dbErrorInfo = "">
	<cfset anyErrorInfo = "">
	<cfset beginProcessInfo = "">
	<cfset endProcessInfo = "">

	<cfsavecontent variable="beginProcessInfo">
	--- <strong>"OPTIMIZE TABLE #rsAllTables.TABLES_IN_PEOPLEXS_TEST#" started</strong>... <br/>
	</cfsavecontent>
	<cfset processInfo &= beginProcessInfo>

	#beginProcessInfo#
	<cfflush>

	<cftry>

		<cfquery name="qOptimizeTable"
				datasource="peoplexs_test"
				username="#Variables.myUsername#"
				password="#Variables.myPass#"
				timeout="#URL.queryTimeout#">
			OPTIMIZE TABLE #rsAllTables.TABLES_IN_PEOPLEXS_TEST#;
		</cfquery>

		<cfquery name="qFlushTable"
				datasource="peoplexs_test"
				username="#Variables.myUsername#"
				password="#Variables.myPass#">
			FLUSH TABLE #rsAllTables.TABLES_IN_PEOPLEXS_TEST#;
		</cfquery>

		<cfcatch type="database">
			<cfsavecontent variable="dbErrorInfo">
			<span style="color:red;">
				Database ERROR: "#cfcatch.Cause.Message#" on
				"#cfcatch.SQL#"
			</span><br/>
			</cfsavecontent>
			<cfset processInfo &= dbErrorInfo>
			#dbErrorInfo#
		</cfcatch>

		<cfcatch type="any">
			<cfsavecontent variable="anyErrorInfo">
			<span style="color:red;">
			<cfdump var="#cfcatch#" format="text">
			</span><br/>
			</cfsavecontent>
			<cfset processInfo &= anyErrorInfo>
			#anyErrorInfo#
		</cfcatch>

	</cftry>

	<cfsavecontent variable="endProcessInfo">
	------ done with #rsAllTables.TABLES_IN_PEOPLEXS_TEST#. <br/><br/>
	</cfsavecontent>

	<cfset processInfo &= endProcessInfo>
	#endProcessInfo#
	<cfflush>

</cfoutput>

<cfmail to="#Variables.toAddresses#"
        from="#Variables.fromAddress#"
        subject="OPTIMIZE_ALL_DB_tables.cfm on #Server.hostName#/#Server.instanceName# at #DateFormat(Now(), 'yyyy-mm-dd')#"
        type="HTML"
        server="localhost">
<cfoutput>#Trim(processInfo)#</cfoutput>
</cfmail>