Zabbix Server Ram Usage seems very high.

[anonamoose]

Hi, sorry if this has been answered elsewhere. I didn't turn up anything with a search.

I know the parameters in the Zabbix documentation is just a general guideline but our environment is about 300 monitored hosts.

Medium

CentOS

2 CPU cores/2GB

MySQL InnoDB

500

So according to the above I should only need 2GB and our server is slammed up around 30GB and mysqltuner is recommending another 10 to 15GB but the last time I bumped up the RAM is just gobbled it all up and started recommending even more.

Here's my my.cnf [mysqld]

[mysqld]
basedir = /usr
bind-address = 0.0.0.0
binlog-format = MIXED
datadir = /var/lib/mysql/
default_storage_engine = InnoDB
expire_logs_days = 14
group_concat_max_len = 2048
innodb = FORCE
innodb_buffer_pool_size = 24G
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
interactive_timeout = 7200
key_buffer_size = 32M
lock_wait_timeout = 300
log-bin = /var/lib/mysql/mysql-bin
log-error = /var/log/mysqld.log
log-queries-not-using-indexes = 0
log-warnings = 0
max_allowed_packet = 64M
max_binlog_size = 100M
max_connect_errors = 1000000
max_connections = 4096
max_heap_table_size = 32M
myisam_recover_options = FORCE,BACKUP
open_files_limit = 65535
pid-file = /var/lib/mysql/mysql.pid
port = 3306
query_cache_size = 3M
query_cache_type = 0
skip-external-locking
slow-query-log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log
socket = /var/lib/mysql/mysql.sock
ssl = false
ssl-ca = /etc/mysql/cacert.pem
ssl-cert = /etc/mysql/server-cert.pem
ssl-key = /etc/mysql/server-key.pem
symbolic-links = 0
table_definition_cache = 2048
table_open_cache = 2048
thread_cache_size = 2000
thread_stack = 256K
tmp_table_size = 32M
tmpdir = /tmp
user = mysql
wait_timeout = 7200

I'm just wondering if splitting the db off onto it's own dedicated server might help. I also have the option to migrate the VM to flash storage but disk I/O doesn't appear to be an issue. I don't want to keep throwing memory at if there's other options to bring the resource usage down. Most of the items are a default 60 second interval for collecting data.

[anonamoose]

To follow up I've installed a bunch of monitoring of the zabbix server including setting up the MYSQL DB template.

zabbixtuner suggested an innoDB buffer of 98G and I ran this command:

SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1)) Recommended_InnoDB_Buffer_Pool_Size FROM ( SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw FROM ( SELECT SUM(data_length+index_length)*1.1*growth RIBPS FROM information_schema.tables AAA, (SELECT 1 growth) BBB WHERE ENGINE='InnoDB' ) AA ) A;

Seems massively excessive at least compared to the recommendations for CPU/RAM in the official documentation for a server that has 350 hosts.

Here's my dashboard:



The VM lives on cold storage and I do have the option of migrating it to Flash. Would that help much?

Does that buffer seem reasonable? The DB has grown to 150G as well. I've migrated it to new servers a couple of times and it always doubles in size when I do that after dumping/importing so I'm looking into cleaning up the DB and reducing it in size to see if that improves efficiency.

Thanks peeps.

I'm very limited in my SQL knowledge.

[geoffke]

Have you tried tuning your housekeeper settings? Most people have enough with 90 days of history instead of 365 days... Interval time of 60 seconds is nice for graphs, but you have to ask yourself if this is really required? For example capturing some hardware values might be enough having them set at 1h interval or more. Set other values maybe to 5m or 10m which will also have a positive effect on your storage. Also consider table partitioning which will downsize your database and make your whole installation more performant.

When you are running MySQL and Zabbix server on the same machine you should be aware that both consume memory. As your MYSQL pool size is now set to use 24G you only have 6GB left for all the other remaining processes. A rule of thumb is to set "innodb_buffer_pool_size" to use 50% of total RAM in case you are running Zabbix and MySQL on the same machine. On the other hand MySQL will perform best if you can store your poolsize in RAM as much as possible which is why mysqltuner is recommending this. But in your case I would leave it at 50% of total RAM.

[anonamoose]

Thanks Geoffke.

After doing much research I think I've got it at a place where's it's in equilibrium and I gained a better understanding of resource usage. One thing I hadn't thought of was I started monitoring a bunch of MySQL metrics and realized that the hourly dumps were crushing it (it's our standard setup) and after speaking to the team we agreed that once a day was fine as it's not that critical if we lose a bit of data if it ever crashes. After that one adjustment it seems to be humming along.

I do think in the future if I have the free time and I'm feeling ambitious I'll pull web off of it and setup a new server.

[LenR]

Some tips: 1) look at db partitioning for history aging, housekeeper is an issue as you grow 2) look at https://www.percona.com/software/mys...ona-xtrabackup as an alternative to mysqldump.

[anonamoose]

Thank you.