- For compilers that perform multiple checkouts/checkins 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 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:
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'
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. A number of tempdb data files are recommended to be as a 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.9. In the case when data loss of 1 day is acceptable – it is OK to use a daily full backup and Simple recovery model. In case when a point in time restore is needed, use a full recovery model and proper schedule for log backups (every 1 hour or every 30 or even 15 minutes depending on the volume of inserted data to avoid log file growth).
9.In the case when data loss of 1 day is acceptable – it is OK to use a daily full backup and Simple recovery model. In case when the point in time restore is needed use a full recovery model and proper schedule for log backups (every 1 hour or every 30 or even 15 minutes depending on the volume of inserted data to avoid log file growth).
|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||
|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||
|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||
|16 Cores||64 GB||10K+ RPM HDD or SSD||10Gbit|