5.8 Measurement result storage options PVSR has several more or less different ways to store the collected data in the Oracle database. Every storage option has advantages and disadvantages. This section gives a detailed desription about these modes and the way they can be configured The options: 1. Simple data loading: in this case PVSR uses always the PVSR_MON_RES table to load the measurement result files into. The loading is done by the SQLLDR module. The Data migration module runs once a day and moves the rows which are older than couple of days to the PVSR_MON_RES_ARCHIV table. The Data compression module runs once a day and calculates hourly averages from the data older than several days, stores the averages in the PVSR_MON_RES_ARCHIV_DAILY table and deletes them from the PVSR_MON_RES_ARCHIV table. The number of days after which this "compression" is done can be set separately for every measurement or even can be turned off 2. Simple data loading with automatic deletion: the same as option 1 except that the hourly averages are deleted from the database after a while 3. Data loading with several tables: in this case PVSR uses several tables to load the meaurement result files into. The number of the tables used can be configured and the following tables can be used: PVSR_MON_RES, PVSR_MON_RES2 … PVSR_MON_RES8. The loading is done by the SQLLDR module. The Data migration module runs every couple of hours and moves every row from some of the tables mentioned before into the PVSR_MON_RES_ARCHIV table. The Data compression module runs once a day and calculates hourly averages from the data older than several days, stores the averages in the PVSR_MON_RES_ARCHIV_DAILY table and deletes them from the PVSR_MON_RES_ARCHIV table. The number of days after which this "compression" is done can be set separately for every measurement or even can be turned off 4. Data loading with several tables and automatic deletion: the same as option 3 except that the hourly averages are deleted from the database after a while 5. Data loading with several tables and time partitioned tables: in this case PVSR uses several tables to load the meaurement result files into. The number of the tables used can be configured and the following tables can be used: PVSR_MON_RES, PVSR_MON_RES2 … PVSR_MON_RES8. The loading is done by the SQLLDR module. The Data migration module runs every couple of hours and moves every row from some of the tables mentioned before into the PVSR_MON_RES_ARCHIV table. Besides this it checks the time when the PVSR_MON_RES_ARCHIV table was created and if it is older than a certain number of days then it renames it to PVSR_MON_RES_A_YYYYMMDDHH, so for example at 11am on the 15th of April 2010 it renames it to PVSR_MON_RES_A_2010041511. The Data compression does not do any operation, so the data retention value set for each measurement is ignored and the application preserves the raw collected data indefinitely 6. Data loading with several tables and time partitioned tables and automatic deletion: the same as option 5 except that the Data compression drops the PVSR_MON_RES_A_... tables from the database after a while. The data retention value set for each measurement is ignored and the application preserves the raw collected data for every measurement for the same amount of time and never calculates hourly averages 7. Data loading with several tables and time partitioned tables II.: in this case PVSR only uses the PVSR_MON_RES and PVSR_MON_RES2 tables. The Data migration module runs every couple of hours and renames the currently used table to PVSR_MON_RES_A_YYYYMMDDHH, so for example at 11am on the 15th of April 2010 it renames it to PVSR_MON_RES_A_2010041511. The main difference between this and option 5 is that in this case PVSR does not even use the PVSR_MON_RES_ARCHIV table 8. Data loading with several tables and time partitioned tables and automatic deletion II: the same as option 7 except that the Data compression drops the PVSR_MON_RES_A_... tables from the database after a while. The data retention value set for each measurement is ignored and the application preserves the raw collected data for every measurement for the same amount of time and never calculates hourly averages 9. Oracle Partitioning Option: PVSR can make use of the Oracle Partitioning Option as well (if USE_PARTITONS is set to 1), even in a way when it only uses the PVSR_MON_RES table (if DONT_MOVE_PARTITION_TO_ARCHIV is set to 1). This configuration mode is only allowed if done by NETvisor Ltd Professional Services, since it requires database schema modifications The different modes and their behaviour are controlled with the USE_PARTITONS, MOVE_MON_RES, DELETE_DAILY_DATA_AFTER, CREATE_PP_TABLE_AFTER, DROP_DATA_AFTER configuration parameters and the data retention set for the measurements. The parameter settings are: 1. Simple data loading: the USE_PARTITONS parameter has to be set to 0. The Data migration module move the data older than MOVE_MON_RES days from the PVSR_MON_RES table into the PVSR_MON_RES_ARCHIV table. The DELETE_DAILY_DATA_AFTER must not be set, the other configuration parameters are ignored 2. Simple data loading with automatic deletion: the USE_PARTITONS parameter has to be set to 0. The Data migration module move the data older than MOVE_MON_RES days from the PVSR_MON_RES table into the PVSR_MON_RES_ARCHIV table. The DELETE_DAILY_DATA_AFTER parameter controls how the old data is deleted, the other configuration parameters are ignored. For every measurement a number of days can be configured and PVSR preserves the raw data for that timeperiod and calculates daily averages after that. If this timeperiod is smaller than the value of the DELETE_DAILY_DATA_AFTER parameter then the hourly averages are deleted after that. It it is greater then PVSR preserves the raw data according to the measurement retention setting and then deletes the data without calculating hourly averages 3. Data loading with several tables: the USE_PARTITONS parameter has to be 2, 4 or 5. The Data migration runs every MOVE_MON_RES hours and moves the data from one half of the table set PVSR_MON_RES, PVSR_MON_RES2…8 used for data loading, while the SQLLDR modul uses the other half of the table set. The DELETE_DAILY_DATA_AFTER and CREATE_PP_TABLE_AFTER parameters must not be set, the DROP_DATA_AFTER parameter is ignored. The number of tables used for data loading depends on the value of the USE_PARTITONS parameter: a. If its value is 2 then the SQLLDR module uses the PVSR_MON_RES table while the Data migration moves the data from the PVSR_MON_RES2 table and vice versa b. If its value is 4 then the SQLLDR uses the PVSR_MON_RES3 and PVSR_MON_RES5 tables for data loading while the Data migration moves the data from the PVSR_MON_RES4 and PVSR_MON_RES6 tables into the PVSR_MON_RES_ARCHIV table and vice versa. The PVSR_MON_RES and PVSR_MON_RES2 tables are only temporaly used during the data migration c. If its value is 5 then the SQLLDR uses the PVSR_MON_RES3, PVSR_MON_RES5 and PVSR_MON_RES7 tables for data loading while the Data migration moves the data from the PVSR_MON_RES4, PVSR_MON_RES6 and PVSR_MON_RES8 tables into the PVSR_MON_RES_ARCHIV table and vice versa. The PVSR_MON_RES and PVSR_MON_RES2 tables are only temporaly used during the data migration 4. Data loading with several tables and automatic deletion: the same as option 3, except the DELETE_DAILY_DATA_AFTER parameter which has to be set. The DELETE_DAILY_DATA_AFTER parameter controls how the old data is deleted, the other configuration parameters are ignored. For every measurement a number of days can be configured and PVSR preserves the raw data for that timeperiod and calculates daily averages after that. If this timeperiod is smaller than the value of the DELETE_DAILY_DATA_AFTER parameter then the hourly averages are deleted after that. It it is greater then PVSR preserves the raw data according to the measurement retention setting and then deletes the data without calculating hourly averages 5. Data loading with several tables and time partitioned tables: the meaning and usage of the USE_PARTITONS and MOVE_MON_RES parameters are the same as for option 3. The Data migration module creates a new PVSR_MON_RES_ARCHIV table after CREATE_PP_TABLE_AFTER days. The DROP_DATA_AFTER parameter must not be set. The DELETE_DAILY_DATA_AFTER parameter is ignored 6. Data loading with several tables and time partitioned tables and automatic deletion: the same as option 5, except the DELETE_DAILY_DATA_AFTER parameter: the Data compression drops the PVSR_MON_RES_A_... tables after they have been created DELETE_DAILY_DATA_AFTER days ago 7. Data loading with several tables and time partitioned tables II: USE_PARTITONS has to be set to 2, MOVE_MON_RES parameter is the same as for option 3. CREATE_PP_TABLE_AFTER has to be set to 0. The DROP_DATA_AFTER parameter must not be set. The DELETE_DAILY_DATA_AFTER parameter is ignored 8. Data loading with several tables and time partitioned tables and automatic deletion: the same as option 7, except the DELETE_DAILY_DATA_AFTER parameter: the Data compression drops the PVSR_MON_RES_A_... tables after they have been created DELETE_DAILY_DATA_AFTER days ago These eigth options can be grouped together into four groups, since only the automatic deletion is the difference between the options in the same group. The advantages and disadvantages of the groups and their recommended use cases are: · 1-2: its advantage is that if the report and/or threshold calculation modules were not running for a while and the measurement files were deleted from the tmp directory then after they are started they can "go back" and calculate the data for the last MOVE_MON_RES days. The disadvantage is that this options are only suitable for a couple of ten thousand measurements, after that it becomes too slow to load the measurement data into the database and to move the rows · 3-4: option 3 is the default after the installation. Its advantage is that this way PVSR can handle couple of hundred thousand measurements. Its disadvantage is that this way if the threshold and/or report calculation modules were not running then they are only able to calculate data for the last couple of hours after they have been started, or they might not be able to do that either. Another disadvantage is that it cannot handle several hundred thousand or million measurements · 5-6: its advantage is that it can even handle couple of million measurements. Its disadvantage is that the user cannot control on a per measurement level the number of days the data is stored in the database and PVSR does not do hourly average based "compression" · 7-8: its advantage is that PVSR “handles” the data just once: after it is loaded into the initial table, it does not delete, move or update the rows and so the whole loading-moving cycle takes less time (since there is no moving). Its disadvantage is that PVSR creates a new table more often and so selecting from the database becomes somewhat slower after a while Option 5 has another advantages which is not dependent on the number of measurements, so it can be favorable even for small installations. Since PVSR periodically creates new tables and never drops them, a very simple but effective data archiving can be implemented. For example let's set the value of the CREATE_PP_TABLE_AFTER to 7: PVSR creates a new table every week, which can be exported, archived and dropped after a while. If the data is needed later on then it can be easily imported and used again, because PVSR won't drop the data automatically (that's why option 6 is not suitable in this case). The exporting, dropping and importing is outside of the scope of PVSR documentation, standard Oracle utilities can be used for these purposes: · exp and imp utilities for exporting and importing · Data pump utility for exporting and importing · DROP TABLE statement for dropping the table |