Like file systems, databases also fragment, causing the entire system to lose performance.
In a high performance system like Pandora FMS it is very important that the health of the database does not affect the proper functioning of the system. In overloaded systems, the database can become blocked, causing the whole system to crash.
A good MySQL configuration could make Pandora FMS work a hundred times faster, so if you experience performance problems, it is probably not correctly configured, or there is some problem related to the database.
The first step to optimize MySQL is the modification of the /etc/my.cnf file.
With a 4 GB Random Access Memory (RAM) system and medium-powered hardware, the configuration of my.cnf should be similar to the following.
The file itself will contain some comments to help you parameterize your database correctly.
Check that all parameters are correctly entered in the section [mysqld], as many times the configuration parameters are not loaded correctly because they are outside this section:
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql character-set-server=utf8 skip-character-set-client-handshake key_buffer = 400M max_allowed_packet = 100M thread_stack = 64K thread_cache_size = 8 max_connections = 100 skip-locking innodb_file_per_table innodb_flush_log_at_trx_commit = 0 innodb_flush_method = O_DIRECT innodb_thread_concurrency = 16 innodb_buffer_pool_size = 500M innodb_additional_mem_pool_size = 32M innodb_log_file_size = 64M innodb_log_buffer_size = 16M innodb_lock_wait_timeout = 90 # IO: 100 for 7500RPM disk, 180 for 15K RPM disk, 1500 for SSD disks innodb_io_capacity = 100 key_buffer_size = 32M read_buffer_size = 128K read_rnd_buffer_size = 128K sort_buffer_size = 128K join_buffer_size = 64M # Enable query cache query_cache_type = 1 query_cache_limit = 128M query_cache_size = 128M
Any changes made to the my.cnf file will require a MySQL restart.
Check the end of the /var/log/mysqld.log file to see if any errors have occurred.
One of the most frequent errors is when setting new values for the transaction logs.
So, if the error is:
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes InnoDB: than specified in the .cnf file 0 67108864 bytes!
You must delete these logs and restart MySQL:
rm /var/lib/mysql/ib_logfile* /etc/init.d/mysqld restart
Once this is done, you should check that the changes have been correctly applied.
This is checked with the SHOW VARIABLES command in the MySQL line.
In the Pandora FMS documentation you will find more and very detailed information about the optimization of the database, in a chapter dedicated to this topic:
https://pandorafms.com/manual/en/documentation/05_big_environments/08_optimization