Databases and maintenance

As all databases are a bit different in how the should be maintained we will not try to describe this here. What we will do instead is to discuss different areas which should be discussed or addressed when set up by a DBA.

Setting up the database

The InfoGlue installer supports creating the database for MySQL and SQLServer while it does not for Oracle and DB2. This is because most DBA has told us it is not a good idea. If that is so is probably dependent on how the installation of InfoGlue will operate and how much data and transactions will burden the database. So – if you fell that you want some custom setup for the InfoGlue database you should create the schema and tables first and only let the installer insert the needed data.

What DBA:s has told me is to take special care when dealing with tables which will grow fast in size as table spaces etc should be dimensioned accordingly. Not being a DBA we only suggest you understand what tables will contain much data and change often and make your own decisions based on that.

Key tables:

cmContent – will probably never reach over 10.000 rows for a medium site and there will be no especially long data types for your regular site.

cmContentVersion – could reach up to 100.000 rows for a regular site and it contains a clob which represents the actual texts written and presented on the site so it could contain a fair amount of data. Perhaps up to a one or two hundred megabytes after a while.

cmDigitalAsset – this is the heaviest table as it’s only for storing blobs. So all pfd-files, programs, images and other binary content the users wish to publish through InfoGlue will be stored there. If a site will contain a lot of high-res images or movies the size will quickly be very large. We have seen everything from 10Mb in total to over 100Gb and growing.

cmTransactionHistory – this table will contain a record of all writes to the database (create/update/delete). The amount of rows will be very big but the size in Kb will not. It is also possible to archive and truncate this once in a while to save space.

Separation and scalability on the databases.

In a high profile site with a high workload there are often a need to separate the live database and the cms-database. This will relieve the live sites database from any transactions done in the working area of the tools leaving that database instance to only serve visitors to the sites. It will also speed up the internal tools as the live sites traffic will not slow down the administrative tools. It is also excellent from a maintenance point of view as you then can upgrade the cms-tools including database schema without bringing down the live sites as well if you turn off replication during that time.

We will not discuss how to set up such an separated environment with replication here but for people wanting to replicate between two MySQL servers they should look at http://dev.mysql.com/doc/mysql/en/replication-howto.html.

Other database vendors have similar possibilities which your DBA can tell you about.

To achieve this you probably also have separated the servers which delivers the applications and then you only need to install the InfoGlue deliver live on the live server and point it to the replicated database instead of to the master db. You can do it yourself afterwards as well by modifying the following files jdbc-url under infoglueDeliverLive:



comments powered by Disqus