For larger helpdesks, there are certain queries that can take a long time to run. For example, some reports can be quite intensive. To stop these intensive queries from slowing down the rest of the helpdesk you can configure a "read only" database to use instead.

MySQL Replication

To use this feature, you need to have a second database server that is a clone of your main, master database. You can achieve this through MySQL replication: http://dev.mysql.com/doc/refman/5.0/en/replication.html

Configuring the read-only database

In your /config.php file, locate the "Read Only Database" section.

Fill in the database details. That's it!

How it works

This feature works by simply changing the database that Deskpro sends certain read queries to. For example, when running a report, instead of executing the report against the main database, it will open a new connection to your read database and execute it there instead.

Advanced Configuration

There are several different places where Deskpro can use a "read" database, and you can configure them each separately. As you saw above, you configured a read database by editing $DP_CONFIG['db_read'] variables in config.php. You can append a specific scope the "db_read" to have Deskpro use specific database config in specific places:

For examlpe, if we wanted to use a read database for just ticket filtering and nothing else, we would do two things;

1. Do not fill in the default db_read section in /config.php. By not specifying a default, we can be sure a read db is not used for other locaitons.

2. Create a new db_read_filter_tickets section like this:
   

   

Multiple Read Databases

You can also specify an array of configurations for the same scope, and Deskpro will choose a configuration at random. For example, say you have 3 databases you wanted to spread load across. You could create configuration like this which defines connection configurationf or all three, and Deskpro will just choose one randomly when a connection is required:

Using PHP to dynamically select a database connection

Starting with build #321, you can now specify a PHP callback function that will be called when a read connection is requested. Specify it like this:

$DP_CONFIG['db_read_mapper'] = function($type, array $context = null) { /*...*/ }

$type will be a read type that is being requested. These types are currently supported:

• reports

• search

• search.searcher.content

• search.filter

• search.filter.(tickets|people|articles|news|downloads|feedback)

$context may be either null or an array of context variables. The context value will change based on which kind of connection is being requested. For example, in ticket searches $context['query'] will be the raw MySQL query that is waiting to be executed.

The function must return a string that represents the db_search_* config name. For example, if you wanted to use db_search_myconnection then you would return the string "myconnection". Returning any falsey value will result in Deskpro using the default logic for selecting a connection (as described above).

Here is an example that selects a new connection for all ticket searches that search on messages or subjects: 

$DP_CONFIG['db_read_ticket_message_search'] = array(
	'host'     => 'db.example.com',
	'user'     => 'myuser',
	'password' => 'mypassword',
	'dbname'   => 'my_database'
);

$DP_CONFIG['db_read_mapper'] = function($type, array $context = null) {
	if (
		$type == 'search.filter.tickets'
		&& $context
		&& !empty($context['query'])
		&& preg_match('#(tickets_messages|tickets_search_message|tickets_search_subject)#', $context['query'])
	) {
		return 'ticket_message_search';
	}
};