This is more general, but you need to make sure the system IO is not a bottleneck (disk). Execute the following command to collect the system information:
+
This is more general, but you need to make sure the system IO is not a bottleneck (disk). Run the following command to collect the system information:
Usually, **SWAP-SI** and **SWAP-SO** should have value zero, if not, it means the system is using SWAP memory, which degrades performance. Increase RAM or decrease RAM usage in your applications (Pandora FMS server threads, Buffers in MySQL, et cetera).
+
Usually, **SWAP-SI** and **SWAP-SO** should have value zero, if not, it means the system is using SWAP memory, which degrades performance. Increase RAM or decrease RAM usage in your applications (Pandora FMS server threads, Buffers in MySQL, etc.).
To use MySQL table partitioning, you should also use the //multiples tablespaces// system [[#KS1_3|described above]] (''innodb_file_per_table'').
+
To use MySQL table partitioning, you should also use the //multiples tablespaces// system [[#ks1_3|described above]] (''innodb_file_per_table'').
</WRAP>
</WRAP>
行 897:
行 902:
<WRAP center round tip 90%>
<WRAP center round tip 90%>
-
MySQL テーブルパーティショニングを使用するには、[[#KS1_3|上記で説明した]] //複数のテーブルスペース// システム (''innodb_file_per_table'') も使用する必要があります。
+
MySQL テーブルパーティショニングを使用するには、[[#ks1_3|上記で説明した]] //複数のテーブルスペース// システム (''innodb_file_per_table'') も使用する必要があります。
</WRAP>
</WRAP>
行 905:
行 910:
MySQL はテーブルパーティショニングをサポートしており、これにより、非常に大きなテーブルを論理的なサブディビジョンなどの小さなチャンクに分散できます (詳細については、[[http://dev.mysql.com/doc/refman/5.1/en/partitioning-overview.html|MySQL マニュアル]] を参照してください)。
MySQL はテーブルパーティショニングをサポートしており、これにより、非常に大きなテーブルを論理的なサブディビジョンなどの小さなチャンクに分散できます (詳細については、[[http://dev.mysql.com/doc/refman/5.1/en/partitioning-overview.html|MySQL マニュアル]] を参照してください)。
-
If you have large amounts of data in the database (main and [[[:en:documentation:pandorafms:complex_environments_and_optimization:09_pandorafms_engineering#KS1_5|historical]]) Pandora FMS and estimates that many Console operations that refer to that data (for example //drawing graph//) are slow, then it will improve its performance using **table partitioning**.
+
If you have large amounts of data in Pandora FMS database (main and [[:en:documentation:pandorafms:complex_environments_and_optimization:09_pandorafms_engineering#ks1_5|history]]) and it is estimated that many Console operations that refer to that data (for example //drawing graph//) are slow, then it will improve its performance using **table partitioning**.
Pandora FMS automatically performs a monthly partitioning in the historical database if it is configured from the Web Console, section **Historical database**, of the general configuration. For more details [[en:documentation:pandorafms:management_and_operation/12_console_setup#ks1_4|consult this link]].
+
Pandora FMS automatically performs a monthly partitioning in the history database if it is configured from the Web Console, section **History database**, of the general configuration. For more details [[:en:documentation:pandorafms:management_and_operation:12_console_setup#ks1_4|check this link]].
After having installed Pandora FMS and enabled its historical database, this will be the one that holds the largest amount of data **and it is the recommended one to perform a table partitioning ** Precisely the tables indicated for this are ''tagente_datos'', ''tagente_datos_string'', ''tagente_datos_inc'' and ''tevento''. The manual practical process for the table ''tagente_datos'' is described below (see also "[[#ks1_9_1|Automatic partitioning]]").
+
After having installed Pandora FMS and enabled its history database, this will be the one that holds the largest amount of data **and it is the recommended one to perform a table partitioning ** Precisely the tables indicated for that are ''tagente_datos'', ''tagente_datos_string'', ''tagente_datos_inc'' and ''tevento''. The manual practical process for the table ''tagente_datos'' is described below (see also "[[#ks1_9_1|Automatic partitioning]]").
You should check at first that the directory ''/var/lib/mysql/pandora_history/*.ibd'' has many files (one per table). If this is not the case, you will need to do a //dump// of the database, change the configuration of the ''my.cnf'' file, restart MySQL, delete the current database and recreate it from the //dump//.
You should check at first that the directory ''/var/lib/mysql/pandora_history/*.ibd'' has many files (one per table). If this is not the case, you will need to do a //dump// of the database, change the configuration of the ''my.cnf'' file, restart MySQL, delete the current database and recreate it from the //dump//.
* This operation will need enough disk space to be completed. You will need to check how big the ''tagente_datos.ibd'' file is. If for example it occupies 10 gigabytes, you will need 15 GB of free space to start the operation.
+
* This operation will need enough disk space to be completed. You will need to check how big the ''tagente_datos.ibd'' file is. If for example it occupies 10 gigabytes, you will need 15 GB of free space to start the operation.
-
* This operation can take a long time, depending on the size of the table. For example, **it would take an hour and a half** to split a table with approximately 7500 //modules data// for 100 days (more than 50,000,000 rows).
+
* This operation may take a long time, depending on the size of the table. For example, **it would take an hour and a half** to split a table with approximately 7500 //modules data// for 100 days (more than 50,000,000 rows).
This is an example for partitioning all of 2023 so far and for future months. To start the process you will need to execute the following query in the MySQL CLI:
+
This is an example for partitioning all of 2023 so far and for future months. To start the process, you will need to execute the following query in MySQL CLI:
PARTITION Jan24 VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01 00:00:00')),
PARTITION Jan24 VALUES LESS THAN (UNIX_TIMESTAMP('2024-02-01 00:00:00')),
PARTITION pActual VALUES LESS THAN MAXVALUE);
PARTITION pActual VALUES LESS THAN MAXVALUE);
+
</code>
</code>
行 976:
行 985:
"Jan24" を当月に変更します。
"Jan24" を当月に変更します。
-
Remember again that this operation could take hours, depending on how big the ''tagente_datos'' table is. You can check the process by watching the size of the partitioning files by running:
+
Remember again that this operation could take hours, depending on how big the ''tagente_datos'' table is. You may check the process by watching the size of the partitioning files by running:
[root@histdb pandora_history]# ls -lah | grep "#sql"
[root@histdb pandora_history]# ls -lah | grep "#sql"
行 1000:
行 1009:
<wrap #ks1_10 />
<wrap #ks1_10 />
+
==== データベースの再構成 ====
==== データベースの再構成 ====
行 1027:
行 1037:
=== 部分的再構成 ===
=== 部分的再構成 ===
-
MySQL database management system, same as other SQL engines, such as Oracle® is degraded with time due to causes such as data fragmentation produced by deleting and continuous insertion in large tables. In large environments, with a lot traffic volume, there is a very easy way to improve the performance and prevent performance from degrading. This is rebuilding the DB from time to time.
+
MySQL database management system, same as other SQL engines, such as Oracle®, is degraded with time due to causes such as data fragmentation produced by deleting and continuous insertion in large tables. In large environments, with a lot traffic volume, there is a very easy way to improve performance and prevent performance from degrading. This is rebuilding the DB from time to time.
In this service stop, stop the Pandora FMS WEB console and the server too. **Attention**: you may leave the Tentacle server so that it can still receive data and these will be processed as soon as the server works again.
+
In this service stop, stop the Pandora FMS WEB console and the server too. **Attention**: you may leave the Tentacle server, so that it can still receive data and these will be processed as soon as the server works again.
Unfortunately, MySQL is degraded with time, and this affects the global performance of the system.There is no other solution that does not involve rebuilding all the database schemes from scratch, rebuilding the data binary file that MySQL uses to store all the information and the files used to rebuild the transactions.
+
<code bash>
+
systemctl stop pandora_ha
-
時間ともに MySQL のパフォーマンスが落ちると、システム全体のパフォーマンスに影響します。この場合は、すべてのデータベーススキーマをゼロから再構築する以外に解決策はありません。MySQL がデータの保存に利用しているバイナリファイルを利用し、またそれを再構築します。
+
</code>
-
If you take a look at the ''/var/lib/mysql'' directory, you can see that there are three files, that have always the same name, and that are, depending on the severity of the case, huge. In this example:
+
Secondly, an export of all schemas and data must be done. Choose a directory of the machine, in a partition that has enough space to store the backup of the database in case it is a large database. The following command is executed:
-rw-rw---- 1 mysql mysql 5.0M 2012-01-12 14:00 ib_logfile0
+
-
-rw-rw---- 1 mysql mysql 5.0M 2012-01-12 14:00 ib_logfile1
+
-
</file>
+
</code>
-
The ''ibdata1'' file is the one that stores all the system Innobd data. In a very fragmented system that has not been "rebuilt" or "installed" for a long time, this system will be big but little efficient. The ''innodb_file_per_table'' parameter, that has been [[#ks1_3|mentioned before]], regulates part of this performance.
+
After finishing the process we will have in the file ''backup.sql'' the data, in the directory where we are.
- Dump (via **mysqldump**) all the schemes to the disk:
+
Access the MySQL installation directory in ''/var/lib/mysql'' and delete the files named ''binlog.000001'', ''binlog.000002'' … and ''binlog.index''. In addition, we will delete the directory with the same name as the database ''pandora''.
-
- すべてのスキーマをディスクにダンプします (**mysqldump** 経由)。
+
MySQL インストールディレクトリ (''/var/lib/mysql'') にアクセスし、''binlog.000001''、''binlog.000002''、… および ''binlog.index'' という名前のファイルを削除します。さらに、データベース ''pandora'' と同じ名前のディレクトリも削除します。
<code bash>
<code bash>
-
mysqldump -u root -p -A> alldata.sql
+
rm -rf /var/lib/mysql/pandora
+
rm -rf /var/lib/mysql/binlog.0*
+
rm -rf /var/lib/mysql/binlog.index
</code>
</code>
-
* Stop MySQL.
+
Once these steps are done, we will start MySQL again with:
-
* Delete ''ibdata1'', ''ib_logfile0'', ''ib_logfile1'' and the InnoDB database directories
+
-
* Start MySQL.
+
-
* Create ''pandora'' database again (''create database pandora;'')
Access the MySQL terminal with the following command:
+
+
次のコマンドで MySQL ターミナルにアクセスします。
+
+
<code bash>
+
mysql -uroot -p pandora
+
+
</code>
+
+
The ''pandora'' database will be selected, but it will be empty. The following command is used to import in the database the //backup// that we did from the Pandora FMS database:
In some systems, depending on the type of information you have, you can find some "slow queries" that make the system work worse. You may enable logs of this type of queries **over a short period of time** (since it harms the system performance) in order to consider trying to optimize queries to tables with indexes. To enable these settings, do the following:
+
In some systems, depending on the type of information you have, you may find some "slow queries" that make the system work worse. You may enable logs of this type of queries **over a short period of time** (since it harms the system performance) in order to consider trying to optimize queries to tables with indexes. To enable these settings, do the following:
* The number established in the ''verbose'' parameter refers to the amount of information that is written in the logs, being advisable not to exceed 3. The higher the number, **the lower the performance** of Pandora FMS due to the large amount of information to write in the logs.
+
* The number established in the ''verbose'' parameter refers to the amount of information that is written in the logs, being advisable not to exceed 3. The higher the number, **the lower the performance** of Pandora FMS due to the large amount of information to write in the logs.
* A high value (15) of the ''server_threshold'' parameter causes the DB to suffer less, while the increase in the maximum number of files processed causes the server to //seek files// and fill the buffers every time. **These two elements of the configuration are closely linked. In the case of optimizing the network server, it may be interesting to lower the ''server_threshold'' to 5 or 10**.
-
* A high value (15) of the ''server_threshold'' parameter causes the DB to suffer less, while the increase in the maximum number of files processed causes the server to //seek files// and fill the buffers every time. **These two elements of the configuration are closely linked. In the case of optimizing the network server it may be interesting to lower the ''server_threshold'' to 5 or 10**.
* The very high number of threads (more than 5) set in ''[[:en:documentation:pandorafms:installation:04_configuration#dataserver_threads|xxxxserver_threads]]'' ' only benefits processes with long I/O waits, such as the //network server// or //plugin server.// In the case of the Dataserver, which is in process all the time, setting too many threads **can even hurt performance**. On systems with a slow DB: try different combinations between 1 and 10; with faster disks and multi-core CPUs, it could be increased. In the case of optimizing the system for the Networkserver, the number can be much higher, between 10 and 30.
-
+
-
* The very high number of threads (more than 5) set in ''[[[:en:documentation:pandorafms:installation:04_configuration#dataserver_threads|xxxxserver_threads]]''' only benefits processes with long I/O waits, such as the //network server// or //plugin server.// In the case of the Dataserver, which is in process all the time, setting too many threads **can even hurt performance**. On systems with a slow DB: try different combinations between 1 and 10; with faster disks and multi-core CPUs it could be increased. In the case of optimizing the system for the Networkserver, the number can be much higher, between 10 and 30.
Pandora FMS has several tools that can help you to measure properly its hardware and software for the amount of data that it expects to obtain. One of them is useful to "attack" directly the database with fictitious data (**dbstress**) and the other generates fictitious XML files (**xml_stress**).
+
Pandora FMS has several tools that may help you to measure properly its hardware and software for the amount of data it expects to obtain. One of them is useful to "attack" directly the database with fictitious data (**dbstress**) and the other generates fictitious XML files (**xml_stress**).
Pandora FMS には、ハードウエアおよびソフトウエアで、取得可能なデータ量を適切に計測できるいくつかのツールがあります。一つは、ダミーデータで直接データベースへアクセスするもの (**dbstress**)、もう一つは、ダミーの XML ファイルを生成するもの (**xml_stress**) です。
Pandora FMS には、ハードウエアおよびソフトウエアで、取得可能なデータ量を適切に計測できるいくつかのツールがあります。一つは、ダミーデータで直接データベースへアクセスするもの (**dbstress**)、もう一つは、ダミーの XML ファイルを生成するもの (**xml_stress**) です。
**Send and Receive the Agent Local Configuration**
+
**Send and Receive Agent Local Configuration**
**エージェントのローカル設定の送受信**
**エージェントのローカル設定の送受信**
-
If you activate in your ''pandora_xml_stress.conf'' the ''get_and_send_agent_conf'' configuration value to 1, you can make the test load agents work as normal agents, so that they send their configuration file and also the MD5 hash. From the PFMS Web Console you can modify the remote configuration for subsequent runs of the ''pandora_xml_stress''.
+
If you activate in your ''pandora_xml_stress.conf'' the ''get_and_send_agent_conf'' configuration value to 1, you may make the test load agents work as normal agents, so that they send their configuration file and also the MD5 hash. From PFMS Web Console you may modify the remote configuration for subsequent runs of ''pandora_xml_stress''.
''pandora_xml_stress.conf'' で、''get_and_send_agent_conf'' を 1 に設定して実行した場合、テスト負荷エージェントで、通常のエージェントのように設定ファイルおよび md5 を送ることができます。PFMS Web コンソールから、''pandora_xml_stress'' の以降の実行のリモート設定を変更できます。
''pandora_xml_stress.conf'' で、''get_and_send_agent_conf'' を 1 に設定して実行した場合、テスト負荷エージェントで、通常のエージェントのように設定ファイルおよび md5 を送ることができます。PFMS Web コンソールから、''pandora_xml_stress'' の以降の実行のリモート設定を変更できます。
行 1503:
行 1535:
* **max_threads**: Number of threads where the script will be executed. This improves the E/S.
* **max_threads**: Number of threads where the script will be executed. This improves the E/S.
* **agent_file**: Path of the name list file path, separated by a new line.
* **agent_file**: Path of the name list file path, separated by a new line.
-
* **temporal**: Path of the directory where the made-up XML data files are generated.
+
* **temporal**: Path of the directory where made-up XML data files are generated.
* **log_file**: Path of the log where it will report about its execution script.
* **log_file**: Path of the log where it will report about its execution script.
* **xml_version**: Version of the XML data file (by default 1.0).
* **xml_version**: Version of the XML data file (by default 1.0).
行 1512:
行 1544:
* **time_from**: Time from which made-up XML data files are generated, in format ''yyyy-MM-dd HH:mm:ss''.
* **time_from**: Time from which made-up XML data files are generated, in format ''yyyy-MM-dd HH:mm:ss''.
* **time_to**: Time until which made-up XML data files are generated, in format ''yyyy-MM-dd HH:mm:ss''.
* **time_to**: Time until which made-up XML data files are generated, in format ''yyyy-MM-dd HH:mm:ss''.
-
* **get_and_send_agent_conf**: Boolean value 0 or 1. When it is active the made-up agents will try to download by remote configuration a more updated version of the standard configuration file of an agent. And they can be edited through the Pandora FMS Web console.
+
* **get_and_send_agent_conf**: Boolean value 0 or 1. When it is active, the made-up agents will try to download by remote configuration a more updated version of the standard configuration file of an agent. And they can be edited through Pandora FMS Web console.
* **startup_delay**: Time numeric value in seconds before each agent starts to generate files. It is used to avoid //race conditions//.
* **startup_delay**: Time numeric value in seconds before each agent starts to generate files. It is used to avoid //race conditions//.
* **timezone_offset**: Numeric value of the time zone offset.
* **timezone_offset**: Numeric value of the time zone offset.
行 1519:
行 1551:
* **longitude_base**: Numeric value. It is the longitude geographic area to be used to define fictitious agents.
* **longitude_base**: Numeric value. It is the longitude geographic area to be used to define fictitious agents.
* **altitude_base**: Numeric value. It is the altitude geographic area to be used to define fictitious agents.
* **altitude_base**: Numeric value. It is the altitude geographic area to be used to define fictitious agents.
-
* **position_radius**: Numeric value. Defines the radius of the circumference in which the geographic position of the fictitious agent will be set (randomly and based on the parameters **latitude_base** and **longitude_base**).
+
* **position_radius**: Numeric value. It defines the radius of the circumference in which the geographic position of the fictitious agent will be set (randomly and based on parameters **latitude_base** and **longitude_base**).
-
+
* **max_threads** スクリプトの実行スレッド数。処理率を改善します。
-
* **max_threads** スクリプトの実行スレッド数。処理率を改善します。
+
* **agent_file** 行ごとに名前を書いたファイルのパス。
-
* **agent_file** 行ごとに名前を書いたファイルのパス。
+
* **temporal** 架空の XML データファイルを生成するディレクトリのパス。
-
* **temporal** 架空の XML データファイルを生成するディレクトリのパス。
+
* **log_file** スクリプトの実行について情報を出力するログのパス。
-
* **log_file** スクリプトの実行について情報を出力するログのパス。
+
* **xml_version** XML データファイルのバージョン。(デフォルトは 1.0)
-
* **xml_version** XML データファイルのバージョン。(デフォルトは 1.0)
+
* **encoding** XML データファイルのエンコーディング。(デフォルトは ISO-8859-1)
-
* **encoding** XML データファイルのエンコーディング。(デフォルトは ISO-8859-1)
+
* **os_name** 仮想エージェントの OS 名。(デフォルトは Linux)
-
* **os_name** 仮想エージェントの OS 名。(デフォルトは Linux)
+
* **os_version** 仮想エージェントの OS バージョン。(デフォルトは 2.6)
-
* **os_version** 仮想エージェントの OS バージョン。(デフォルトは 2.6)
+
* **agent_interval** 仮想エージェントの実行秒間隔。(デフォルトは 300)
-
* **agent_interval** 仮想エージェントの実行秒間隔。(デフォルトは 300)
+
* **time_from** 仮想 XML データの開始時間。"年-月-日 時間:分:秒" というフォーマットにて。
-
* **time_from** 仮想 XML データの開始時間。"年-月-日 時間:分:秒" というフォーマットにて。
+
* **time_to** 仮想 XML データの終了時間。"年-月-日 時間:分:秒" というフォーマットにて。
-
* **time_to** 仮想 XML データの終了時間。"年-月-日 時間:分:秒" というフォーマットにて。
module_exec type =< xml_stress_type_generation >;< another_option >;< another_option > …
+
module_exec type =<xml_stress_type_generation>;<another_option>;<another_option> …
-
module_unit < units >
+
module_unit <units>
module_min_critical <value>
module_min_critical <value>
module_max_critical <value>
module_max_critical <value>
行 1557:
行 1587:
module_max_warning <value>
module_max_warning <value>
module_end
module_end
+
</file>
</file>
行 1571:
行 1602:
module_max_warning <値>
module_max_warning <値>
module_end
module_end
+
</file>
</file>
行 1577:
行 1609:
それぞれの項目は次のように設定可能です。
それぞれの項目は次のように設定可能です。
-
* **type_generation_xml_stress**: It can have the values [[#ks2_2_1_1|RANDOM]], [[#ks2_2_1_2|SCATTER]], [[#ks2_2_1_3|CURVE]].
+
* **type_generation_xml_stress**: It can have values [[#ks2_2_1_1|RANDOM]], [[#ks2_2_1_2|SCATTER]], [[#ks2_2_1_3|CURVE]].
* **module_attenuation <value>**: The generated module value is multiplied by the specified value, usually between 0.1 and 0.9.
* **module_attenuation <value>**: The generated module value is multiplied by the specified value, usually between 0.1 and 0.9.
* **module_attenuation_wdays <value> <value> … <value>**: The module value is only attenuated during the given days, ranging from Sunday (0) to Saturday (6). For example, the following module simulates a 50 % drop in network traffic on Saturdays and Sundays:
* **module_attenuation_wdays <value> <value> … <value>**: The module value is only attenuated during the given days, ranging from Sunday (0) to Saturday (6). For example, the following module simulates a 50 % drop in network traffic on Saturdays and Sundays:
Sometimes there are problems for which direct help from Pandora FMS support is needed. To facilitate the communication with the Support team, Pandora FMS servers have some tools for this.
+
Sometimes, there are problems for which direct help from Pandora FMS support is needed. To fmake the communication with the Support team easier, Pandora FMS servers have some tools for this.
This tool is located in the <wrap :en>**Management → Admin tools → Diagnostic Info**</wrap> section, and is designed to provide the most important information about Pandora FMS configuration and its database.
+
This tool is located in the <wrap :en>**Management → Admin tools → Diagnostic Info**</wrap> section, and is conceived to provide the most important information about Pandora FMS configuration and its database.