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:
- Open the following file for editing: <Server_install>/config/kwmysql.ini
- Edit as required. Relevant parameters and suggested values are described in kwmysql.ini parameters.
- Save the file.
- Restart the Klocwork servers with validate service (or with Windows Service administration):
validate service --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.