My IBM DB2 Express-C administration experience when used with 1C: Enterprise
Had a chance to work with IBM DB2. Both on 1C and the server on Django used this DBMS at one time, OLAP requests processed quite quickly (though it was necessary to manually configure the indexes, and of the web server, of course, so that the response was within 2 seconds). In 2015, I prepared this short instruction for myself, so as not to forget. And as an addition to the resume, maybe someone read on paper, the remaining years of work lay idle. Some generalization of my experience with DB2. I corrected it a bit and suggest reading it here to broaden my horizons. Maybe someone will be interested. I must say right away that since then I have not worked with DB2, everything has been forgotten, but I still remember something. Criticism and clarifications are welcome, but since I no longer work, maybe not for me, but for someone else they will be useful.
There are many instructions on the Internet how to organize the work of 1C: Enterprise with the IBM DB2 DBMS. For starters, this is not bad at all, but not enough for use in production. I previously recommended starting out with IBM DB2 with the Big Data University DB101RU training course. I myself took this course, passed the exam in 2012, I think it is very useful. It is a pity that the resource ceased to exist. I didn’t find anything like it on the new platform. In production, IBM DB2 requires additional configuration and maintenance, the most necessary of which will be briefly described here. We are considering a free edition of IBM DB2 Express-C for Windows versions 10.1fp2 and 10.5fp4 (the first is supported by 1C for working in test mode, the second is officially supported, it is a pity that newer versions are only paid). It makes sense to install 64-bit 10.5 where there are high requirements for RAM (buffer size for speed) or recording size (EXTENDED_ROW_SZ = ENABLE) when using composite types containing long strings of fixed size.
The very first thing that should be done is to switch to using archived journals in order to make backups without interrupting the operation of 1C: Enterprise, and to be able to restore the database at any point in time (restoring to 10.1fp2 has its own peculiarities due to an uncorrected bug in the free version - manual movement of log files is required). Unlike MS SQL, archiving of logs is not carried out at certain predetermined time points (in MS SQL it is not strong, perhaps there is something else), and when the log file reaches a certain, predetermined size, it is not necessary to back up the log before the restore operation, just deactivate the base. Archiving logs in two directions is easily configured, one of which is on a network drive, for example. Moreover, in the case of short network outages, the increase in the space occupied by active logs is not significant. For active logs, you must provide enough free space to be able to restore the database at any given time. If during the programmer’s work with the 1C base, frequent returns are required at various close points in time, one backup is enough to restore, the choice of log files for recovery is very simple. Be sure to activate the database at the start of the instance, otherwise we get a large number of small log files with implicit activation. Obviously, you should set the backup storage time (it seems to me that you need to store logs for at least two months) and configure automatic deletion. The base and backups (logs) must be on different physical disks, in extreme cases, you can make backups to another computer on the local network.
Base activation is also needed for another reason. For normal operation, you should install windows online and offline maintenance. At this time, the base should be active. Periodically, you should look at the history of the database to find out what actions were performed during the offline window. The offline maintenance window should most likely be set between 22:00 - 0:00, as there are no heavy 1C maintenance tasks at this time. Perhaps for small bases a window lasting 1 hour is enough.
Periodically, you need to run a check for the need for reorganization in manual mode and, after analyzing the state of tables and indexes, perform a reorganization of individual objects. Manual reorganization of several thousand tables and indexes can take a long time. The analysis is easily accelerated by a simple filter (on .js, for example) using regexps.
AUTOCONFIGURE, of course, needs to be done periodically, manually correcting individual settings, for example, setting your own log file size.
After a day, it is possible and more often to perform online backup of the database (it does not require a shutdown and the presence of a DBA), the frequency depends on the required recovery time, which in turn depends on the number of archived log files after the last backup, that is, the load on the database. For medium, large and heavily loaded databases, it makes sense to use various types of incremental backups in order to reduce the space occupied by backups and reduce the recovery time from backups. After each backup and before restoring, check the health of the backups. The backup storage time is at the discretion of the DBA, but not less than the storage time of the logs.
At least once a month, perform offline and online database health checks (in offline mode, work with the database is suspended for several minutes) and, if necessary, perform repairs (most important for “servers” without a UPS). Perform offline backup of the database on a monthly basis, only offline backups will be stored for a long time, because when you change the version of DB2, online backup will not be possible to deploy. If “1C: Enterprise” does not allow even a short-term translation of the database offline for verification or backup, it is possible to perform the indicated actions in an expanded copy of the database. The database is easily restored from the backup to another location, for example, to another disk on another server. It should be noted that both backups and archive logs can be compressed using DB2 tools (in this case, the backup verification tool remains functional and the archive log verification tool does not work).
Before offline checking the database and offline backup, you should set the database and maintenance tasks to be locked. In an emergency, you can do with stabilization of the database, but since the user under which the 1C: Enterprise server is running is included in the SYSADM_GROUP group, this will not cancel the ability to connect 1C to the database at the wrong time and, as a result, will necessitate a second job launch.
If the database does not work fast, you should, after updating the statistics, get the most severe query plans, experiment with 1C indexes in a copy of the database, monitoring changes to the query plan in IBM Data Studio (in this case, using eclipse is justified, in others it’s enough to do with the command interface lines), or use the recommendations of the DB2 Design Advisor and, if necessary, create indexes manually outside 1C. At the same time, start collecting detailed performance information using DB2 tools (more than a dozen SQL scripts) and analyze the load with the system monitor. To reduce the load on the disk system, the database must be installed on a separate high-speed disk of sufficient size (unless, of course, a normal RAID 10 server disk array is available), it is possible to place active logs on the SSD together with the OS. Probably, a change in the location of the 1C: Enterprise server tempos will also be required. If the purchase of a disk is only planned, for small organizations it is permissible to temporarily use a single physical disk for the database.
Review db2diag.log daily for errors, as well as the results of actions with the database. Archive upon reaching a size of several tens of megabytes. Far Manager can be a convenient means of viewing the log (it is assumed that there are few errors in the database), it will also help if you need to manually move archive logs to restore at a point in time.
One of the competitive advantages of IBM DB2, as it seems to me, can be considered that in cases when MS SQL Server requires a 64-bit “1C: Enterprise” server for normal operation, you can do with 32-bit for IBM DB2.
UPD: Perhaps, it was not careful when checking the versions of DB2 supported by 1C: Enterprise before publishing. In the original of this text of 2015, about 10.5fp4 it was said: when used with 1C: Enterprise, "no errors have yet been detected." That is, at the moment, of the new Express-C, only 10.1 is possible (with its features and limitations). Of today's paid, only 11.1 is officially supported. It is possible that someone will have enough Developer-C with a database size of up to 100 GB. I did not change the link to the documentation - it’s easy to switch there.
The basic information, except that which was obtained at courses that no longer exist, can be found here (then the link was different).
Much has already been forgotten, but a link to the once-saved pdf document “Best practices. Tuning and monitoring database system performance ”was found .
There are many instructions on the Internet how to organize the work of 1C: Enterprise with the IBM DB2 DBMS. For starters, this is not bad at all, but not enough for use in production. I previously recommended starting out with IBM DB2 with the Big Data University DB101RU training course. I myself took this course, passed the exam in 2012, I think it is very useful. It is a pity that the resource ceased to exist. I didn’t find anything like it on the new platform. In production, IBM DB2 requires additional configuration and maintenance, the most necessary of which will be briefly described here. We are considering a free edition of IBM DB2 Express-C for Windows versions 10.1fp2 and 10.5fp4 (the first is supported by 1C for working in test mode, the second is officially supported, it is a pity that newer versions are only paid). It makes sense to install 64-bit 10.5 where there are high requirements for RAM (buffer size for speed) or recording size (EXTENDED_ROW_SZ = ENABLE) when using composite types containing long strings of fixed size.
The very first thing that should be done is to switch to using archived journals in order to make backups without interrupting the operation of 1C: Enterprise, and to be able to restore the database at any point in time (restoring to 10.1fp2 has its own peculiarities due to an uncorrected bug in the free version - manual movement of log files is required). Unlike MS SQL, archiving of logs is not carried out at certain predetermined time points (in MS SQL it is not strong, perhaps there is something else), and when the log file reaches a certain, predetermined size, it is not necessary to back up the log before the restore operation, just deactivate the base. Archiving logs in two directions is easily configured, one of which is on a network drive, for example. Moreover, in the case of short network outages, the increase in the space occupied by active logs is not significant. For active logs, you must provide enough free space to be able to restore the database at any given time. If during the programmer’s work with the 1C base, frequent returns are required at various close points in time, one backup is enough to restore, the choice of log files for recovery is very simple. Be sure to activate the database at the start of the instance, otherwise we get a large number of small log files with implicit activation. Obviously, you should set the backup storage time (it seems to me that you need to store logs for at least two months) and configure automatic deletion. The base and backups (logs) must be on different physical disks, in extreme cases, you can make backups to another computer on the local network.
Base activation is also needed for another reason. For normal operation, you should install windows online and offline maintenance. At this time, the base should be active. Periodically, you should look at the history of the database to find out what actions were performed during the offline window. The offline maintenance window should most likely be set between 22:00 - 0:00, as there are no heavy 1C maintenance tasks at this time. Perhaps for small bases a window lasting 1 hour is enough.
Periodically, you need to run a check for the need for reorganization in manual mode and, after analyzing the state of tables and indexes, perform a reorganization of individual objects. Manual reorganization of several thousand tables and indexes can take a long time. The analysis is easily accelerated by a simple filter (on .js, for example) using regexps.
AUTOCONFIGURE, of course, needs to be done periodically, manually correcting individual settings, for example, setting your own log file size.
After a day, it is possible and more often to perform online backup of the database (it does not require a shutdown and the presence of a DBA), the frequency depends on the required recovery time, which in turn depends on the number of archived log files after the last backup, that is, the load on the database. For medium, large and heavily loaded databases, it makes sense to use various types of incremental backups in order to reduce the space occupied by backups and reduce the recovery time from backups. After each backup and before restoring, check the health of the backups. The backup storage time is at the discretion of the DBA, but not less than the storage time of the logs.
At least once a month, perform offline and online database health checks (in offline mode, work with the database is suspended for several minutes) and, if necessary, perform repairs (most important for “servers” without a UPS). Perform offline backup of the database on a monthly basis, only offline backups will be stored for a long time, because when you change the version of DB2, online backup will not be possible to deploy. If “1C: Enterprise” does not allow even a short-term translation of the database offline for verification or backup, it is possible to perform the indicated actions in an expanded copy of the database. The database is easily restored from the backup to another location, for example, to another disk on another server. It should be noted that both backups and archive logs can be compressed using DB2 tools (in this case, the backup verification tool remains functional and the archive log verification tool does not work).
Before offline checking the database and offline backup, you should set the database and maintenance tasks to be locked. In an emergency, you can do with stabilization of the database, but since the user under which the 1C: Enterprise server is running is included in the SYSADM_GROUP group, this will not cancel the ability to connect 1C to the database at the wrong time and, as a result, will necessitate a second job launch.
If the database does not work fast, you should, after updating the statistics, get the most severe query plans, experiment with 1C indexes in a copy of the database, monitoring changes to the query plan in IBM Data Studio (in this case, using eclipse is justified, in others it’s enough to do with the command interface lines), or use the recommendations of the DB2 Design Advisor and, if necessary, create indexes manually outside 1C. At the same time, start collecting detailed performance information using DB2 tools (more than a dozen SQL scripts) and analyze the load with the system monitor. To reduce the load on the disk system, the database must be installed on a separate high-speed disk of sufficient size (unless, of course, a normal RAID 10 server disk array is available), it is possible to place active logs on the SSD together with the OS. Probably, a change in the location of the 1C: Enterprise server tempos will also be required. If the purchase of a disk is only planned, for small organizations it is permissible to temporarily use a single physical disk for the database.
Review db2diag.log daily for errors, as well as the results of actions with the database. Archive upon reaching a size of several tens of megabytes. Far Manager can be a convenient means of viewing the log (it is assumed that there are few errors in the database), it will also help if you need to manually move archive logs to restore at a point in time.
One of the competitive advantages of IBM DB2, as it seems to me, can be considered that in cases when MS SQL Server requires a 64-bit “1C: Enterprise” server for normal operation, you can do with 32-bit for IBM DB2.
UPD: Perhaps, it was not careful when checking the versions of DB2 supported by 1C: Enterprise before publishing. In the original of this text of 2015, about 10.5fp4 it was said: when used with 1C: Enterprise, "no errors have yet been detected." That is, at the moment, of the new Express-C, only 10.1 is possible (with its features and limitations). Of today's paid, only 11.1 is officially supported. It is possible that someone will have enough Developer-C with a database size of up to 100 GB. I did not change the link to the documentation - it’s easy to switch there.
Links to mentioned resources / files
The basic information, except that which was obtained at courses that no longer exist, can be found here (then the link was different).
Much has already been forgotten, but a link to the once-saved pdf document “Best practices. Tuning and monitoring database system performance ”was found .
All Articles