1. Home
  2. Knowledge Base
  3. Articles (EN/ES/FR)
  4. How to optimize MySQL for a better performance of Pandora FMS?

How to optimize MySQL for a better performance of Pandora FMS?

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:

key_buffer = 400M
max_allowed_packet = 100M
thread_stack = 64K
thread_cache_size = 8
max_connections = 100
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:


Was this article helpful?

Related Articles

Need Support?

Can't find the answer you're looking for?
Contact Support