Improving database performance

You can improve Database Server performance in the following ways:

  • control memory size and limits for very large projects
  • force the Database Server to load data directly from the file system. This option makes sense if you're running the Database Server and the integration build analysis (specifically, kwadmin load) on the same host.

Customizing memory size and limits for the Database Server

An integration build analysis error like the following may indicate a need to customize memory parameters:

kwloaddb - Klocwork Project Database Creation Utility
Error:
at com.klocwork.database.Agent.processSQLError(Unknown Source)
at com.klocwork.database.Agent.doCommand(Unknown Source)

Error occurred while loading database: Database error occurred: 
The table 'tmp_metrics_from' is full in SQL statement 'insert into my_project__shared.'

To customize memory size and limits for the Database Server:

  1. Open the following file for editing: <Server_install>/config/kwmysql.ini
  2. Edit as required. Relevant parameters and suggested values are described in kwmysql.ini parameters.
  3. Save the file.
  4. Restart the Klocwork servers with kwservice (or with Windows Service administration): 
    kwservice --projects-root <projects_root> restart

Next: Resume the integration build analysis.

Kwmysql.ini parameters

If your projects are large, and you want to improve the performance of theKlocwork database, you can customize the kwmysql.ini file, located at <Server_install>/config/kwmysql.ini. Remember to restart the Klocwork Servers after you modify the file.

Suggested changes to default parameters

Parameter and default value Suggested value
key_buffer_size = 80M 200M
max_heap_table_size = 128M 256M
read_buffer_size = 512K 2M
read_rnd_buffer_size = 1M 2M
query_cache_size = 4M 128M
query_cache_limit = 100K 2M
tmp_table_size = 128M 256M
innodb_buffer_pool_size = 512M 1G On a dedicated server, you can increase the size up to 50-80% of RAM on the machine that runs the Klocwork Servers, but be cautious of setting memory usage too high. If the same machine is used for Klocwork integration build analysis, this value should be increased to no more than 50% of available memory. For more information, see the MariaDB documentation at https://mariadb.com/kb/en/library/documentation/.

Suggested additional parameters

You may also want to add these new parameters and values to the file. See the example file that follows.

Additional parameter Suggested value
innodb_log_files_in_group= 2 Defines the number of InnoDB log files (temporary storage for InnoDB transactions). Splitting the log file into multiple smaller files will improve file access speed, thus improving the speed of large transactions. The recommended number of files is 2.
innodb_log_file_size= 48M The recommended log file size is related to the value of the innodb_buffer_pool_size parameter. If the value of innodb_buffer_pool_size is 128M, the recommended log file size is 48M. Reasonable values can range up to no greater than <innodb_buffer_pool_size>/<innodb_log_files_in_group>. For more information, see the MariaDB documentation at https://mariadb.com/kb/en/library/documentation/.
wait_timeout= The number of seconds the server waits for activity on a noninteractive connection before closing it. This value may affect database loading, so make sure this value is not less than the database loading time of the largest project.
innodb_log_group_home_dir= Storing the log files on a hard drive different from the projects_root location will also improve transaction speed (since committed data and transaction data could be retrieved in parallel).
connect_timeout= The number of seconds that the Database Server waits for a connect packet before responding with "Bad handshake". Setting a value higher than the default value of 10 may help if clients frequently encounter errors of the form "Lost connection to MySQL server at 'XXX'" system error.

Example customized kwmysql.ini file

Here's a kwmysql.ini file that's been customized for a deployment based on a machine with 4 GB of RAM, dedicated to running the Klocwork Servers.

Changes or additions to the default file are in italics.

[mysqld]
 #
 character_set_server=utf8
 #
 console
 #
 innodb_file_per_table
 innodb_fast_shutdown=0
 #
 innodb_data_file_path=ibdata1:10M:autoextend 
 #
 key_buffer_size = 200M
 #
 innodb_buffer_pool_size = 1Ginnodb_log_file_size = 100Minnodb_log_files_in_group = 2
 #
 binlog_cache_size = 32K
 #
 read_buffer_size = 2Mread_rnd_buffer_size = 2M
 sort_buffer_size = 4M
 join_buffer_size = 1M
 bulk_insert_buffer_size = 10M
 #
 skip-external-locking
 default-storage-engine = MYISAM
 max_connect_errors=999999999
 lower_case_table_names=1
 #
 query_cache_size = 128Mquery_cache_limit = 2M
 query_cache_type = 1
 #
 myisam_max_sort_file_size = 4G
 myisam_sort_buffer_size = 128M
 tmp_table_size = 256Mmax_heap_table_size = 256M
 table_open_cache=300
 open_files_limit=2048

Use the following formula when setting your memory values:

 innodb_buffer_pool_size + key_buffer_size + query_cache_size + (join_buffer_size + sort_buffer_size) * max_connections < accessible RAM

The amount of accessible RAM for a 32-bit process is only 2 GB.

Related information