Tuning MySQL

From KnowledgeTree Document Management Made Simple

Contents 1 Overview 2 InnoDB 3 MyISAM 4 Query cache

Overview

KnowledgeTree makes heavy use of the database for generating pages, and this means that a poorly tuned database will cause a major slow-down of the application.

InnoDB

For MySQL databases, KnowledgeTree makes use of the InnoDB database engine for almost all tables, as it provides transaction and foreign key support. The InnoDB database engine must be available for KnowledgeTree to function.

The primary performance configuration setting for InnoDB tables is the innodb_buffer_pool_size setting. This setting is in the mysqld section of the my.cnf (my.ini on Windows) file. The optimum setting for this depends on the amount of memory available to the database server (factoring in other processes). On a machine with about 512MB of memory dedicated to the database, a value of 256MB is suggested. Don't make it larger than around 80% of the memory you're dedicating to the database.

[mysqld]
...
innodb_buffer_pool_size = 256M

MyISAM

The "sequence tables" in KnowledgeTree use MyISAM tables, as do the tables that contain "full text" (document contents, searchable text) indexes. As these are fairly infrequently used, the key_buffer_size variable can be relatively small. It should be at least 16MB large, though, and preferably about 64MB on a system with 512MB dedicated to the database server.

[mysqld]
...
key_buffer_size = 256M

Query cache

The query cache in MySQL will cache the results of queries so that if they are asked again, it returns the cached results. While KnowledgeTree 3.0.2 and above uses caching internally, this is still a good idea. The query_cache_size and query_cache_limit settings are the most important ones here. query_cache_size is how big the cache should be for all results, and should be at least 8MB, and preferably be 16MB. In a system with 512MB dedicated to the database, a size of 32MB is suggested. query_cache_limit is the maximum size of an individual result. Reasonable values for this are between 512KB and 2MB.

[mysqld]
...
query_cache_size = 32M
query_cache_limit = 1M