A lot of things can be done, when it comes to performance tuning of the temBoard monitoring tool for PostgreSQL. The temBoard tool itself is very lightweight and already quite fast per default, however, if your environment grows you might need to tweak it a little bit.
In this blog post I am going to highlight the things that I have done. The tuning tasks can be divided in 3 catagories.
- OS tuning for temBoard
- temBoard server tuning
- temBoard agent tuning
On the OS I did the following changes. I implemented HugePages for the temBoard repository database, changed the XFS mount option to noatime,nodiratime and changed the Nice value of the temBoard systemd service.
OS tuning for temBoard
Implementing HugePages for the temBoard repository database
To implement HugePages (and to disable Transparent HugePages) is a general good practice when it comes to PostgreSQL. There are several different ways to implement them. You can do it via tuned profiles, via grub, via sysctl.conf files and maybe some more. The configuration of HugePages is documented very well in the following blog posts and presentations, and so I am not going to repeat them.
https://blog.dbi-services.com/configuring-huge-pages-for-your-postgresql-instance-redhatcentos-version/
https://franckpachot.medium.com/did-you-forget-to-allocate-huge-pages-on-your-postgresql-server-7a97e7727b03
https://archive.fosdem.org/2019/schedule/event/hugepages_databases/
I just want to point out, that it is important to have them, and to check if they are active.
$ cat postgresql.conf | grep shared_buffers shared_buffers = 8192MB $ cat /proc/meminfo | grep -i huge AnonHugePages: 0 kB HugePages_Total: 4320 HugePages_Free: 85 HugePages_Rsvd: 2 HugePages_Surp: 0 Hugepagesize: 2048 kB $ cat /etc/sysctl.d/91-hugepages.conf vm.nr_hugepages=4320 $ pmap $(pgrep postgres) | grep -E -- "-s- .*deleted" | sort -u 00002aaaaac00000 8677376K rw-s- anon_hugepage (deleted) $ ipcs -m ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x03828f41 0 postgres 600 56 13
Another important thing, is that the PostgreSQL parameter huge_pages is set to on (default is try).
$ cat postgresql.conf | grep huge_pages huge_pages = on # on, off, or try
Change the XFS mount option to noatime,nodiratime
The noatime mount option prevents access timestamps being updated when a file is read and the nodiratime mount option will stop directory inode access times being updated, which in turn leads to a increase performance by limiting the number of writes to the filesystem journal. Implementing the noatime,nodiratime can be done online and does not require a reboot.
To setup the new XFS mount option, do the following steps.
$ sudo vi /etc/fstab ... /dev/mapper/vg01-alib /app/lib xfs defaults,noatime,nodiratime,nofail 0 0 $ sudo systemctl daemon-reload $ sudo mount -o remount /app/lib $ mount | grep /app/lib /dev/mapper/vg01-alib on /app/lib type xfs (rw,noatime,nodiratime,seclabel,attr2,inode64,noquota)
In case you want to learn more about about different tuning techniques on Red Hat, I would recommend to check out the following link.
Changing the Nice value of the temBoard systemd service
The process priority or Niceness values range from -20 (most favorable to the process) to 19 (least favorable to the process). By configuring the Nice value of -19 you can give the temBoard service a very high priority on the system. I did this because the temBoard service is running on a dedicated host for that service only. In case you have other important services running on the same host, you might want to consider different Nice values or just go with the default.
To implement the new Nice value, simply edit the service file, reload systemd, and stop/start temBoard.
$ cat /etc/systemd/system/multi-user.target.wants/temboard.service [Unit] Description=temBoard Web UI After=network.target [Service] Type=simple User=temboard Group=temboard ExecStart=/usr/bin/env SYSTEMD=1 temboard -c /etc/temboard/temboard.conf Nice=-19 [Install] WantedBy=multi-user.target $ sudo systemctl daemon-reload $ sudo systemctl stop temboard.service $ sudo systemctl start temboard.service
By running the top command, you can verify that the temboard worker has a nice value of -19.
$ top -c
...
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
4918 postgres 20 0 8907908 12924 4708 S 59.1 0.0 0:03.55 postgres: temboard temboard (16184) SELECT
4917 temboard 1 -19 475224 121596 2864 R 34.6 0.4 0:02.63 temboard: worker pool
9 root 20 0 0 0 0 S 0.3 0.0 21:44.92 [rcu_sched]
470 root 20 0 0 0 0 S 0.3 0.0 3:06.95 [xfsaild/dm-0]
temBoard server tuning
When it comes to the temBoard server tuning itself, there are not too many options to tune. The most important one is to keep your temBoard up2date. Check out regularly the github site for new versions.
https://github.com/dalibo/temboard
New releases include not only new features and bug fixes. Every now and then new performance improvements are implemented as well. For example, with temBoard version 7.0, the performance for the home page (instance list) improved dramatically. Check out the change log list, to get an overview of the most important changes with each release.
These are for example, the changes that came with 7.0.
https://temboard.readthedocs.io/en/v7/CHANGELOG/#70-2020-09-28
[7.0] – 2020-09-28
Added
- Statements plugin by @pgiraud and @dlax.
- Load PG* vars, by @bersace.
Changed
- Improved performance of the home page (instances list), by @pgiraud.
- Activity filters are kept, by @pgiraud.
- Better support for history navigation in monitoring, by @pgiraud.
- Don’t highlight short idle_in_transaction queries, by @pgiraud.
- Added comment field for instance settings, by @pgiraud.
- Allow users to choose the refresh interval (whenever a date range picker is used), by @pgiraud.
- Agent: support for RHEL/CentOS 6 has been dropped.
- Agent: support for RHEL/CentOS 8 has been added.
Fixed
- Agent scripts now use the Python interpreter of their installation, not the first found in env, by @bersace.
The next very important tuning task are the adjustment of the purging policies, which can be configured in the /etc/temboard/temboard.conf file. The purging of the monitoring data have no default value, so they are kept forever, and the history data in regards to the statements plugin are kept per default for 7 days.
[monitoring] # Set the amount of data to keep, expressed in days purge_after = 186 [statements] # Set the amount of data to keep, expressed in days purge_after = 14
Obviously, the smaller the amount of days you keep the data, the smaller the repository database is, and the faster it gets.
Besides that, make sure that you don’t run temBoard in DEBUG mode by accident. That will have a quite significant impact as well.
[logging] method = file destination = /var/log/temboard/temboard.log level = INFO # level = DEBUG
And last but not least, make sure that you tune your PostgreSQL temBoard repository database according to your needs. The most important settings are shared_buffer (default is 128MB) and work_mem (default is 4MB). The defaults are way too small in case you want to monitor e.g. over 100 PostgreSQL instances. I set shared_buffers = 8192MB, so that it just fits into my huge page pool, and work_mem = 32MB. With those settings it runs very smoothly.
temBoard agent tuning
The temBoard agent tuning is quite important as well. Besides keeping the version up2date, the most important part is the monitoring section in the agent temboard-agent.conf file.
Per default, all metrics for all available PostgreSQL databases are collected in a scheduling interval of 60 seconds.
[monitoring] # dbnames = * dbnames = mydb # scheduler_interval = 60 scheduler_interval = 300 # probes = * probes = locks,process,db_size,tblspc_size,sessions,blocks,xacts,replication,loadavg,filesystems_size,cpu,bgwriter,memory
If you collecting data for the development instance, you might not want a scheduling interval of 1 minute. Maybe 5 minutes is enough. The same applies to the databases. If you e.g. 5 databases in your instances, but you are interested only in database mydb, then you might want to change this as well. And last but not least, you might not want to collect data from all probes. Maybe collecting only locks,sessions and cpu is enough for you. Than you can change the probes parameter to probes = locks,sessions,cpu.
Conclusion
As mentioned beforehand, the temBoard monitoring tool is quite lightweight and fast per default, however, there are of course a few things that can be done to speed it up. If someone would ask me, what are the three most important changes is regards of temBoard tuning, I would say: Keep your temBoard version up2date, adjust the temBoard purging policies, and the agent scheduling interval.