Optimal DB Engine(s) for Zabbix

[SplitIce]

You could shard based on itemid. Ive been considering doing just that for a while, however instead have held out hope that the Zabbix team will modularize the backend or introduce Cassandra support.

The main problem I see with mysql proxy is complex select queries that involve multiple items. How would they be handed? Is there even any in Zabbix.

Its certainly not something to jump into without appropriate research and budget.

[banzayats]

Does anyone tried historygluon? It was originally developed for storing Zabbix history data into NoSQL databases (HBase, Cassandra)

Link: https://github.com/miraclelinux/HistoryGluon

[SplitIce]

As far as I know the HistoryGluon code for integrating with Zabbix is not open source or released. If it is feel free to link it.

[banzayats]

We already have a prototype developed and actually used in production for some of our customers. Now we are working on making Cassandra and more general API for history storage part of the core product, I believe it will be available in Zabbix 2.6.

Zabbix 2.6 is cancelled now. Are you still planing to introduce an API for storage of historical data? Will it be implemented in the 3.0 version?

[pc99096]

any news on this?

[lcondado]

Any news ?

[SplitIce]

We have been using Mysql + TokuDB now at X4B for years. Since setting our database up in this way we have been laughing, less than <25% utilisation on a not that highspec server.

Our setup is a 3 core (VPS) running with a 2TB NAS hard drive and 120GB (overkill) SSD cache. We never need more than one core, and IO is negligible.

vps of 2.5k currently, storing a full years history.

[OKyHb]

"Mysql + TokuDB" - Percona MySQL? Do you use partitioning?
Could you share your my.cnf?

[SplitIce]

Yes, Partitioned. We store ~1TB of data using MariaDB (mysqld 10.1.11-MariaDB-1~jessie).

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /mnt/storage/mysql
tmpdir          = /tmp
open_files_limit = 1024000
lc-messages-dir = /usr/share/mysql
skip-external-locking

default_tmp_storage_engine=aria
aria_pagecache_buffer_size=128M
key_cache_segments = 1
aria_group_commit = none
aria_group_commit_interval = 0
aria_log_file_size = 64M
aria_log_purge_type = immediate
aria_sort_buffer_size = 16M
innodb_log_group_home_dir=/var/lib/mysql
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
#
# * Fine Tuning
#
key_buffer              = 32M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
table_cache            = 4096
thread_concurrency     = 10

tmp_table_size=80M
max-heap-table-size=96M
join_buffer_size=512K
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#slow_query_log_file = /var/log/mysql/mysql-slow.log
#slow_query_log      = 1
#long_query_time = 2
#log_queries_not_using_indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

innodb_log_buffer_size=16M

event_scheduler=ON
concurrent_insert=2
wait_timeout=18000000
max_connect_errors = 1000000000
innodb_thread_concurrency=3
innodb_file_format=barracuda
innodb_file_per_table
innodb_buffer_pool_size              = 64M
innodb_buffer_pool_instances         = 1
innodb_additional_mem_pool_size      = 1m
#innodb_autoextend_increment          = 16
innodb_support_xa                    = 0
innodb_io_capacity                   = 400
innodb_io_capacity_max               = 1000
#innodb_max_dirty_pages_pct          = 90
innodb_write_io_threads              = 2
innodb_log_file_size                 = 512M
innodb_locks_unsafe_for_binlog       = ON
#transaction-isolation               = REPEATABLE-READ
transaction-isolation                = READ-UNCOMMITTED
skip-name-resolve


tokudb_log_dir=/var/log/mysql
tokudb_directio = 1
tokudb_cache_size=1024M
optimizer_switch=index_condition_pushdown=off
tokudb_read_buf_size=4K
tokudb_commit_sync = 0
tokudb_fsync_log_period = 60000


[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

We use flashcache for the SSD cache.

[OKyHb]

SplitIce, thanks

And how did you change mysql schema for TokuDB? Did you use use the same variant as mentioned in ZBXNEXT-2363 ?

[SplitIce]

You can't use clustering index with partitions, and we had disk space issues without partitions doing a final cleanup after a year or so.

The only change from stock we do is that we have a primary key in our history_uint & history table that is just key & time (no nanosec). But we have a modified zabbix_server for that. This gave us sizeable improvements. The discussion & schema for which can be found: https://support.zabbix.com/browse/ZBXNEXT-3089

We do a bunch of end service monitoring where the checks are per 3 second and aggregated for the group (aggregated check item). So we push it pretty hard.

[OKyHb]

Great, thank you for sharing

[syepes]

@SplitIce, Are you partitioning or optimized the events table?