Running Reports and Storage Checks On a Replicated Database Server

Reports spend a lot of time accessing the database. They do so in mostly-sequential fashion, starting at A, and proceeding straight to Z (within the limits of disk fragmentation) and they place an intensive load on the disks. Users, on the other hand, access the database sporadically, and at random.

The job that checks Storage Space is similarly read-intensive. In addition, it locks all of the databases as it runs.

When those jobs and users are accessing the same disk, they interfere with one another: User access is impeded, and reports do not run with optimum efficiency. But both reports and the Storage Space Check are read-only. Neither of them need to update the database, so they can be run on the replicated version of the databases, on the backup server.

The resulting architecture, shown in the diagram below, speeds things up for everyone.

Requirements

1. Setup a secondary (replicated) database.
2. Create a MySQL user on the replicated database with limited access permissions:

   • READ access on all databases
   • INSERT,DROP,EXECUTE,CREATE TEMPORARY TABLES access to the relationals database.

Note: relationals is the database that contains the information for all tenant databases--who owns them, their sizes, and so on. Once you've set up a replicated database and configured the platform to use it for reporting and storage checks, the resulting efficiency gains are achieved for all tenants.

Configure the Platform to Run Reports and Storage Checks on the Replicated Server

1. Open {install_dir}/tomcat/conf/server.xml
2. Find <Context path="/networking" ...>. 
3. Copy the existing Resource tag for name="jdbc/RN" and make the following changes (shown below) to create a similar Resource tag for name="jdbc/REDUNDANTSRC1": 
   1. Change the username to the user created for the replicated database, for example: username="replicatedDBuser".
   2. Specify that user's password in password="xxxxxx". 
   3. Change the URL to url="jdbc:mysql://IP_address_of_replicated_DB/ 
4. Save the changes
5. Restart memcached and the application server. 

Changes to Server.xml:

This excerpt shows a copy of the resource descriptor for jdbc/RN,with differences highlighted:

<Context path="/networking" docBase="networking" debug="0">
  <Logger className="org.apache.catalina.logger.FileLogger"
      prefix="networking_log." suffix=".txt"
      timestamp="true"/>
  
  <Resource name="jdbc/RN" auth="Container" type="javax.sql.DataSource"
      factory="org.apache.commons.dbcp.BasicDataSourceFactory"
      driverClassName="com.mysql.jdbc.Driver"
      maxActive="200" maxIdle="30" maxWait="30000"
      timeToLiveMins="5" removeAbandoned="true"
      removeAbandonedTimeout="10" logAbandoned="true"
      validationQuery="SELECT now()"
      username="root" password="xxxxx"
      url="jdbc:mysql://IP_address_of_primary_database/relationals?
zeroDateTimeBehavior=convertToNull &autoReconnect=true&useUnicode=true
&characterEncoding=utf8&jdbcCompliantTruncation=false"
  />
  
  <Resource name="jdbc/REDUNDANTSRC1" auth="Container" type="javax.sql.DataSource"
      factory="org.apache.commons.dbcp.BasicDataSourceFactory"
      driverClassName="com.mysql.jdbc.Driver"
      maxActive="200" maxIdle="30" maxWait="30000"
      timeToLiveMins="5" removeAbandoned="true"
      removeAbandonedTimeout="10" logAbandoned="true"
      validationQuery="SELECT now()"
      username="replicatedDBuser" password="xxxxxx"
      url="jdbc:mysql://IP_address_of_replicated_DB/relationals?
zeroDateTimeBehavior=convertToNull&autoReconnect=true&useUnicode=true
&characterEncoding=utf8&jdbcCompliantTruncation=false"
  />
</Context>