PostgreSQL performance and size

[benaus]

Hi All,

My Zabbix 2.2.5 environment is using PostgreSQL for its database

Currently it is having a noticeable performance impact on our SAN as well as being a lot larger than I would have thought necessary

The size of the database is well over 1 TB (which I think is a little excessive)

I was wondering if anyone knew of any audits or cleaning that could be done in order to reduce the size and make the database perform better

Specs:
The DB server runs on ESXi
2 vCPU
8GB RAM

Zabbix server is running Yes
Number of hosts (monitored/not monitored/templates) 424 322 / 21 / 81
Number of items (monitored/disabled/not supported) 51886 48683 / 855 / 2348
Number of triggers (enabled/disabled) [problem/ok] 10745 9659 / 1086 [112 / 9547]
Number of users (online) 40 5
Required server performance, new values per second 692.87 -

Please let me know what other information needs to be provided

[ILIV]

One Zabbix setup that I work with has two times fewer NVP's and a lot more total items, about 8 times more than you have. Our DB size is 309 GB.

You may need to tune housekeeper process as well as revise your how much historical data you keep. Which affects size of DB significantly.

[Slash]

We currently have a database of 652GB on postgresql.

3 things are essentials from my point of view for performance:
- use a recent postgresql version (9.5 was just released, you may want to wait a little to switch to it, but I strongly advice you to use 9.4)
- disable housekeeping and use partitioning for critical table like history instead, we had terrible performance drop with thedefault housekeeping before this. A thread talking about it: https://www.zabbix.com/forum/showthread.php?t=33583
- use pgbouncer to limit the number of connection from the server/frontend to postgres, see this thread: https://www.zabbix.com/forum/showthread.php?p=152257

Also 2vCPU is very low, you may want to boost that to at least 4 and preferably even more.

We have one VM for the frontend and the Zabbix server with 4GB RAM and 8vCPU and the postgresql is on a bare metal machine doing a lot of things, main postgres parameters that we currently use:

max_connections = 50 # configure pgbouncer before setting that!
shared_buffers = 1GB
work_mem = 40MB
maintenance_work_mem = 512MB
effective_cache_size = 2GB

Current Zabbix status (very similar to your setup):

Number of hosts (enabled/disabled/templates)	458	223 / 113 / 122
Number of items (enabled/disabled/not supported)	85601	68967 / 7474 / 9160
Number of triggers (enabled/disabled [problem/ok])	18567	17992 / 575 [56 / 17936]
Required server performance, new values per second	680.1	-

[kloczek]

Specs:
The DB server runs on ESXi
2 vCPU
8GB RAM

Zabbix server is running Yes
Number of hosts (monitored/not monitored/templates) 424 322 / 21 / 81
Number of items (monitored/disabled/not supported) 51886 48683 / 855 / 2348
Number of triggers (enabled/disabled) [problem/ok] 10745 9659 / 1086 [112 / 9547]
Number of users (online) 40 5
Required server performance, new values per second 692.87 -

Please let me know what other information needs to be provided

With such number of monitored items and nvps 8GB or RAM is causing that almost noting can be cached in memory. this 8GB must be decreased by memory used by kernel and system processes.
Your system should have enough RAM to have at least ration between physical write and read IOs like 10:1.
In your case by giving so little memory you are causing that almost all read IOs must be done by reading storage.

[benaus]

Hi All,

Many thanks for the suggestions

I am currently investigating upgrading to PostgreSQL 9.4
I am also investigating the autovacuum process as well as housekeeping

Increasing the vCPU is the next step, just require approval before proceeding

Learning a lot more about PostgreSQL after this issue!

I will keep you updated once I have completed the investigation into the autovacuum and housekeeping

[kloczek]

Again: your problem is not a CPU power but lack of memory to hold as much as possible in memory data most tfrequently selected to have lower latency of selects and less preasure on storage.
Adding more vCPUs will change nothing if those vCPUs will be waiting on delivery data from physical storage.
Postgres upgrade as well will change nothing.
Dou you have partitioned history* tabes?