OpenLM Database optimal configuration – recommendations

Our recommendations

  • If the binary log is enabled, the database user needs to have SUPER privileges in order to run some upgrade scripts.
    To verify if the binary log is enabled run the following command:

    SHOW VARIABLES LIKE 'log_bin';

     

  • To Verify if the current user has SUPER privileges run the following script:
    SELECT *
    FROM INFORMATION_SCHEMA.USER_PRIVILEGES
    WHERE PRIVILEGE_TYPE = 'SUPER'
    AND REPLACE(GRANTEE,'''','') = CURRENT_USER();
  • It is possible to allow functions/procedures creation by enabling the following option : log_bin_trust_function_creators. To check if it is enabled – run the following script :
    SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
  • Another option is to disable binary logging if replication is not enabled or point-in-time recovery is not needed.
  • For compilers that perform multiple checkouts/check-ins in a second, we recommend 25%-50% stronger hardware.
  • VM Administrators should make sure that the hosting server is capable of accommodating the required resources.
  • When seeing a low performance in DB queries, please check the disk queue.
  • We strongly recommend placing the DB in the same Data Center as OpenLM Server.
  • See recommendations for MS SQL Server below.
  • For MySQL we provide a sample configuration file for Windows (my.ini) & Linux (my.cnf) that should be revised by DBA.
  • VM network controller should be available for each network card.
  • In the case of a big DB (from 25GB and up) and big load, each DB should have 3 files and 3 VM disk controllers for the: db file, log file and tmp file.

Best practices for using MySQL

1. Use the latest 5.7/8 MySQL release.
2. In order to utilize the system’s resources MySQL requires its configuration file (my.cnf/my.ini) to be set with correct values. Otherwise, MySQL will not take advantage of its hosting machine’s resources. We recommend some settings – please see our suggestions for configuration files archived in .zip format according to your system size:

4GB_2Cores_Windows 

8GB_4Cores_Windows

16GB_8Cores_Linux

16GB_8Cores_Windows

24GB_8Cores_Windows

 

Best practices for using MS SQL Server

1. Customers need to apply a maintenance plan consisting of:
a) Periodic Statistics Update
b) Periodic Rebuild or Reorganization of Indexes
DBAs need to apply company maintenance policy also for OpenLM DB. In case such does not exist, a public package can be applied (Here is one).

2. Recommended memory allocation to MSSQL Server running (almost) exclusively on a windows machine is no more than 80% of total machine memory.

3. OpenLM database should have the is_read_committed_snapshot_on parameter set.

To check if it is set:

SELECT is_read_committed_snapshot_on FROM sys.databases
WHERE name= 'YourDatabase'

To set:

DECLARE @sqlCommand varchar(1000)
DECLARE @db_name varchar(50)

SET @db_name = 'YourDatabase'

SET @sqlCommand = 'ALTER DATABASE ' + @db_name + ' SET ALLOW_SNAPSHOT_ISOLATION ON '
EXEC (@sqlCommand)

SET @sqlCommand = 'ALTER DATABASE ' + @db_name + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE '
EXEC (@sqlCommand)

SET @sqlCommand = 'ALTER DATABASE ' + @db_name + ' SET READ_COMMITTED_SNAPSHOT ON '
EXEC (@sqlCommand)

SET @sqlCommand = 'ALTER DATABASE ' + @db_name + ' SET MULTI_USER '
EXEC (@sqlCommand)

 

4. For better performance, we recommend installing tempdb, databases and log files on separate logical (and in some cases – even physical) disks.
A solid installation would have:

a) 1- disk for tempdb data (ssd configuration is recommended)
b) 1- disk for system DBs (msdb, model, master)
c) 1- disk for all logs (including tempdb logs)
d) 1- disk for all DBs Data

5. tempdb has a critical role, having all parameters, temporary tables and executing sorts and aggregations. Number of tempdb data files is recommended to be as number of processors – up to 8 (more will have no effect or bad influence on performance).
6. Autogrowth units of database files is set by default to percent, which is dangerous. A good practice would be to use MB units, based on a predicted growth multiplied by record size. In any case, setting alerts on disk size is recommended.
7. It is recommended to set log size upfront.
8. A regular backup program is recommended in order to be able to resume crashes and control log files growth. Shrinking a database is a bad practice and is not recommended.

 

OpenLM Server Database Server
Number of users Number of ports Agents CPU Memory Network Card CPU Memory Disk Network Card
500 5 2 Cores 4 GB 1Gbit
1000 5 2 Cores 8 GB 1Gbit
5000 20 4 Cores 8 GB 1Gbit 2 Cores 8 GB 10K+ RPM HDD or SSD 1Gbit
5000 50 4 Cores 8 GB 1Gbit 4 Cores 12 GB 10K+ RPM HDD or SSD 1Gbit
10000 50 4 Cores 8 GB 1Gbit 4 Cores 16 GB 10K+ RPM HDD or SSD 1Gbit
15000 200 4 Cores 12 GB 10Gbit 8 Cores 16 GB 10K+ RPM HDD or SSD 10Gbit
30000 500 8 Cores 16 GB

10Gbit

8 Cores 24 GB 10K+ RPM HDD or SSD 10Gbit
250 5 50 2 Cores 6 GB 1Gbit
500 5 100 4 Cores 8 GB 1Gbit
1000 10 250 4 Cores 12 GB 1Gbit 4 Cores 8 GB 10K+ RPM HDD or SSD 1Gbit
5000 20 500 4 Cores 8 GB 1Gbit 4 Cores 12 GB 10K+ RPM HDD or SSD

1Gbit

10000 50 500 4 Cores 8 GB 1Gbit 8 Cores 16 GB 10K+ RPM HDD or SSD 1Gbit
15000 100 500 4 Cores 12 GB 10Gbit 8 Cores 16 GB 10K+ RPM HDD or SSD 10Gbit
15000 300 3000 8 Cores 12 GB 10Gbit 12 Cores 24 GB 10K+ RPM HDD or SSD 10Gbit
30000 500 15000 24 Cores 32 GB

10Gbit

16 Cores 64 GB 10K+ RPM HDD or SSD 10Gbit

Best practices for using MariaDB

Note: Backup the my.ini before changing the file

4GB :

innodb_buffer_pool_size=2G
innodb_io_capacity=1000
innodb_open_files=2000
max_allowed_packet=500M
max_connections=500
max_heap_table_size=500M
thread_cache_size=256
tmp_table_size=500M

8GB :

innodb_buffer_pool_size=5G
innodb_io_capacity=1500
innodb_open_files=3000
max_allowed_packet=1G
max_connections=500
max_heap_table_size=1G
thread_cache_size=500
tmp_table_size=1G

16GB :

innodb_buffer_pool_size=12G
innodb_io_capacity=2500
innodb_open_files=5000
max_allowed_packet=1G
max_connections=500
max_heap_table_size=2G
thread_cache_size=1000
tmp_table_size=2G

24GB :

innodb_buffer_pool_size=18G
innodb_io_capacity=3000
innodb_open_files=80000
max_allowed_packet=2G
max_connections=1000
max_heap_table_size=3G
thread_cache_size=2000

tmp_table_size=3G

What are your feelings
Skip to content