Suggestions to my environment

[tts00]

Dear Friends,

I would like to discuss about my environment.

Number of hosts: 2143
Number of itens: 21482
Number of triggers: 14393
Required server performance, new values per second: 199.0457

Zabbix Version: 1.6.1
OS: Ubuntu 10.04
Hardware: Dell PowerEdge 1950
MEM: 16GB

Conf:
################################################## #####################
NodeID=0
StartPollers=32
StartPollersUnreachable=8
StartTrappers=30
StartPingers=32
StartDBSyncers=1
StartHTTPPollers=16
#
CacheSize=512M
CacheUpdateFrequency=60
HistoryCacheSize=256M
HistoryTextCacheSize=1024M
TrendCacheSize=64M
#
ListenPort=10051
HousekeepingFrequency=12
SenderFrequency=30
DebugLevel=3
Timeout=8
UnreachablePeriod=30
UnavailableDelay=60
PidFile=/var/tmp/zabbix_server.pid
LogFile=/tmp/zabbix_server.log
LogFileSize=500
AlertScriptsPath=/data/zabbix/scripts/alerts
ExternalScripts=/data/zabbix/scripts
FpingLocation=/usr/bin/fping
Fping6Location=/usr/bin/fping6
DBHost=host_ip
DBName=zabbix
DBUser=user
DBPassword=pass
################################################## ######################

SGBD: MySQL 5.0.86
OS: FreeBSD 8.0
Hardware: Dell R710
MEM: 8GB
Disk: SAS

Conf
################################################## #############
[client]
port = 3306
socket = /tmp/mysqld.sock

[mysqld]
log-bin
binlog-do-db=zabbix
server-id=1
auto_increment_increment=1
auto_increment_offset=1

#################################################

port = 3306
socket = /tmp/mysqld.sock
datadir = /var/db/mysql
back_log = 50
max_connections = 1500
max_connect_errors = 10
table_cache = 4096
max_allowed_packet = 16M
binlog_cache_size = 10M
max_heap_table_size = 64M
sort_buffer_size = 64M
join_buffer_size = 64M
thread_cache_size = 8
thread_concurrency = 32
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default_table_type = INNODB
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log_warnings
#log_slow_queries
long_query_time = 2
log_long_format
key_buffer_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 64M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 4
myisam_recover
skip-bdb
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 6G
innodb_data_file_path = ibdata1:100M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 20
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 8192
################################################## #####################

My problems:

history_uint.ibd: 148GB
history.ibd: 36GB
trends_uint.ibd: 10GB

Queue
Zabbix Agent (Active) - More than 10 minutes: 16601

Questions:
- Backup strategies; my database don't have backup because of data size. I have some ideas to discuss and I want some opinions.
- I followed zabbix and mysql tuning docs that I found in this forum.
- The queue is too high for "values per second" and I don't know why.

Next work
- Migrate Zabbix to 1.8
- Migrate Mysql to 5.1 and do partitioning
- Use innodb_file_per_table.
- Suggestions?

Thank you for you time.

[xsbr]

Next work
- Migrate Zabbix to 1.8
- Migrate Mysql to 5.1 and do partitioning
- Use innodb_file_per_table.
- Suggestions?

MySQL
- Migrate to MySQL Percona 5.5 (stable) instead community 5.1

http://www.percona.com/software/percona-server/benchmarks/

- Partitioning

http://zabbixzone.com/zabbix/partitioning-tables/

Hardware
- Swap Zabbix Server (16GB RAM) with MySQL (8GB RAM) and up the innodb_buffer_pool_size to 14GB. Because MySQL uses more memory than Zabbix Server

Proxy
- Use proxy - this feature will give you flexibility and reduce load of Zabbix Server

[richlv]

i'd probably start by upgrading to 1.8
1.6.1 is pretty old and 1.8 will have much better performance, especially regarding the db usage

[tts00]

Thank you for the replies.

We migrated to 1.8.2 using partitioning with MySQL 5.5 and it´s fine until now. Please, if you have new suggestions put here. It will always welcome.

[zalex_ua]

Thank you for the replies.

We migrated to 1.8.2 using partitioning with MySQL 5.5 and it´s fine until now. Please, if you have new suggestions put here. It will always welcome.

Related to the Queue it better to upgrade to 1.8.5 and to read this awesome article.

[zalex_ua]

MySQL
- Migrate to MySQL Percona 5.5 (stable) instead community 5.1

http://www.percona.com/software/percona-server/benchmarks/

Why you used Percona namely? Why not MariaDB?
Are Percona is more "suitable" for Zabbix database than MariaDB?
It seems last time Percona is very similar to MariaDB and vice versa.
And i'm interested is the Percona better then MySQL community for the little-middle Zabbix installation in the performance regarding on the not power hardware?

One note: MariaDB is ported to FreeBSD ports collection, and Percona isn't.
I know it's trifle, but it's some indicator for me.

[xsbr]

Why you used Percona namely? Why not MariaDB?
Are Percona is more "suitable" for Zabbix database than MariaDB?

It's based on my experience. I can't suggest something that I don't know deeply, like MariaDB.

And i'm interested is the Percona better then MySQL community for the little-middle Zabbix installation in the performance regarding on the not power hardware?

I think that XtraDB from Percona is more reliable and have more performance than InnoDB's community engine.

http://www.mysqlperformanceblog.com/2010/04/21/mysql-5-5-4-in-tpcc-like-workload/

http://www.mysqlperformanceblog.com/2010/12/20/mysql-5-5-8-and-percona-server-being-adaptive/

I work in a shared hosting provider and we maintain about 1200 MySQL servers. Before with MySQL community we had a lot of incidents from InnoDB corruptions.

[zalex_ua]

Just for case: log of starting MariaDB 5.2.6 (on the FreeBSD8.1):

110620 01:00:04 mysqld_safe Starting mysqld daemon with databases from /DB/mysql
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
110620  1:00:04  InnoDB: Initializing buffer pool, size = 128.0M
110620  1:00:04  InnoDB: Completed initialization of buffer pool
110620  1:00:04  InnoDB: highest supported file format is Barracuda.
110620  1:00:04 Percona XtraDB (http://www.percona.com) 1.0.15-12.7 started; log sequence number 6161271313
110620  1:00:04 [Note] Event Scheduler: Loaded 0 events
110620  1:00:04 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.2.6-MariaDB'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

proof of presence a Percona XtraDB feature in the MariaDB.
i can be wrong in my interpretation
Zabbix works Ok after upgrading to MariaDB.

[tts00]

Backup

What strategy are you using for backup of Zabbix DB on a large enviroment?

I´m thinking to run a daily backup without trends, history tables and a full backup at the end of month.

What do you think?