Ad Widget

**hwidjaja** · 21-09-2013, 17:55

My Zabbix for monitoring 1000 hosts is running fully on VMs (1 MySQL server, 1 master, 4 proxy and 4 GUI behind F5 BIG-IP). The DB size is around 80GB.

The performance is good (I still enable the hourly housekeeping).

You might need to tune up your MySQL.

**Pada** · 21-09-2013, 18:17

Hi,

10GB in total is still pretty small for a Zabbix MySQL DB, unless you only had it for like a month. Our Zabbix MySQL DB is about 60GB after 2 years. We're monitoring 500 hosts, 9000 items and ~ 250 values per second. Our Housekeeping is still set to every 1 hour.
The default MySQL server configuration is terrible, because it doesn't really utilize RAM / CPU fully.

Go to the Zabbix Dashboard and then you'll see the following values that are of interest: "Number of hosts", "Number of items", "Number of triggers" & "Required server performance"

These values give a much better indication, especially the last one on what kind of system requirements you'll need.

Also, another indication that your system is too slow when the Administration > Queue page has lots of outstanding items for more than 10 minutes - unless of course you have lots of dead hosts that you're still trying to monitor.

Could you perhaps tell us why you think the DB access is slow?
Also give us more information on your system specs, network layout, Zabbix version & your performance figures from the Zabbix Dashboard...

Proxies most likely won't reduce your load on your DB, but it should reduce the load on your Zabbix server when you have lots of SNMP of passive Zabbix agent items that you're monitoring.
Proxies are especially useful if you have multiple datacenters that have high latencies between then - like across continents. The proxies can also cache data when the link goes off between the datacenter and your main Zabbix server.

Our Zabbix setup is as follow and it works quite well:
* MySQL server with 4 cores, 16GB of RAM @ our office - however our sysadmin poorly chose RAID 5. RAID 10 is recommended for DBs.
* Zabbix server + frontend with 4 cores and 4GB of RAM @ our office
* 3 Zabbix proxies (all with local MySQL DBs) with 1 core and 1GB of RAM @ each of our data centers - high latency (200ms) to our Zabbix server

We replaced most of our SNMP items with active Zabbix Agents / applications that can push all the values in batches to the proxy/main server.

The following slide by Alexei (one of the Zabbix developers) should give you a good overview of how Zabbix works and what can be done to improve performance: http://www.slideshare.net/xsbr/alexe...formancetuning

**zabbixfk** · 24-09-2013, 09:19

Zabbix Database Access is very slow ( MySql 11GB + )

Hi All,

Thank you for the reply. Below is zabbix server status from dashboard.

The reason i said zabbix is slow is, while showing data items ( graphs/latest items ) it takes lot of time. Also in mysql prompt , select queries are slow.
Both db and zabbix server are hosted in same server.

Below is server configurations.
Uname :

Code:

Linux netmon 2.6.39-400.17.1.el6uek.x86_64 #1 SMP Fri Feb 22 18:16:18 PST 2013 x86_64 x86_64 x86_64 GNU/Linux
Oracle Linux Server release 6.4

RAM:

Code:

             total       used       free     shared    buffers     cached
Mem:            62         52         10          0          0         49
-/+ buffers/cache:          3         59
Swap:           31          0         31

CPU:

Code:

 Intel(R) Xeon(R) CPU E5-2630 0 @ 2.30GHz , 24 Processors with 6 Core

Mysqld.conf

Code:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#Slow query log details
slow_query_log_file=/var/log/mysql/slow-query.log
long_query_time = 30
log-queries-not-using-indexes=YES
#Replication Details.
log-bin=/var/log/mysql/mysql-bin.log
binlog-do-db=zabbix
server-id=1
#Everytime load the logs form memory to disk, so if power crashes then you will have recent data
sync_binlog=1

[mysqld_safe]
#log-error=/var/log/mysqld.log
log-error=/var/log/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I have created about 17 different types of templates as of now (server running from 2-3 months) and database size is increasing close 500-700MB per day.

Total Number of Templates : 17
- For Linux servers : 6 ( Created to differentiate servers based on memory). Each template has close to 4-10 servers
- For windows servers 2 , has total 17 hosts.
- SNMP Based Templates 6 : For different types , firewall, routers and switches. Has Close to 17 firewalls + 35 routers + 22 switches. Items monitored basically are for 5 minutes ( 300 sec).
- Linux Desktops 1 : has about 9-10 hosts
- Windows Desktop 1 : has about 8-10 hosts.

And i am thinking to add more firewalls, routers , switches , and servers on zabbix. I am concerned on the DB size. Data kept duration is set to 180 days.

BTW, Administration -> Queue shows me this.

Any suggestions are greatly helpful.

Thanks.

**Pada** · 25-09-2013, 22:55

I'd be very careful with the "log-queries-not-using-indexes=YES" setting in MySQL ! Check your MySQL log and make sure that it isn't flooded due to that setting.

I'd also highly recommend that you look for MySQL server optimizations/settings, because the default (or close to default) one that you're using is pretty terrible.

What kind of storage do you have? eg. 4x 300GB 15000rpm in hardware RAID 10

Also, could you post your Zabbix server config?
If you have too many StartPollers (SNMP pollers), then that can put a heavy load on your DB. Having too few could cause measurements to be missed due to the SNMP pollers being unable to finish their portion in time.

I'd recommend that you add the Zabbix server and MySQL server templates to your host, because they should give you a fair amount of info into why the system may be slow!

Lastly, what is the unit for that output of "free"? Because those results look appalling if it is in MB. Could you perhaps run it as "free -m" so that the units are in MB?

**tchjts1** · 26-09-2013, 04:34

Pada is giving you good advice.

I would also recommend that you take a look at this post:

We’ll be back soon!

https://www.zabbix.com/forum/showthread.php?t=38575

I had the same issue as you, where viewing graphs and screens was horribly slow. I was able to get rid of that issue by tuning my settings, but mostly by getting rid of the nasty IO Wait I was experiencing.

Another post to look at, and which Pada makes reference to about attaching the template to your Zabbix server for for the Zabbix internal items... see this post. In particular the last paragraph and the graphs that are shown in it:

We’ll be back soon!

https://www.zabbix.com/forum/showthread.php?t=41219

**zabbixfk** · 26-09-2013, 12:53

Zabbix Database Access is very slow ( MySql 11GB + )

Thank you all for the reply. I will surely look at the links provided.

Below is my zabbix server conf file. ( removed which are commented out , i.e #)

Code:

LogFile=/var/log/zabbix/zabbix_server.log
LogFileSize=50
DebugLevel=4
PidFile=/var/run/zabbix/zabbix_server.pid
DBName=zabbix
DBUser=UNAME
DBPassword=PASSWORD
StartTrappers=10
StartPingers=1
 StartDiscoverers=1
 SNMPTrapperFile=/tmp/zabbix_traps.tmp
StartSNMPTrapper=1
ListenIP=127.0.0.1
HousekeepingFrequency=10
MaxHousekeeperDelete=5000
SenderFrequency=3600
Timeout=15
AlertScriptsPath=/etc/zabbix/alert.d/
FpingLocation=/usr/local/sbin/fping

And zabbix agent conf ( for zabbix server )

Code:

PidFile=/var/run/zabbix/zabbix_agentd.pid 
LogFile=/var/log/zabbix/zabbix_agentd.log
LogFileSize=25
DebugLevel=3
EnableRemoteCommands=1
LogRemoteCommands=1
Server=127.0.0.1
ServerActive=127.0.0.1
Hostname=HOSTNAME
UserParameter=net.ping[*],  /bin/ping -c 5 $1 | grep -E "time=" | awk '{print $7}' | sed s/time=//g | xargs echo 
UserParameter=mysqld_cpu.pc,ps auxw|grep mysqld|grep -v grep| awk '{s+=$3} END {print s}'
UserParameter=mysql.threads.sleep,mysqladmin -uUNAME -pPASSWORD processlist | grep Sleep | wc -l
UserParameter=mysql.threads.active,mysqladmin -uUNAME -pPASSWORD processlist | grep -v Sleep | wc -l

Again, i am not sure why SNMPTrappers are 10, all i have to do was to monitor/get data from firewalls, which are sending data using snmp, hence i enabled them. Does it even matter? I mean is it Trapper or poller, i am kind of confused.

RAM is shared , which is in GB ( yes , i have 64GB of ram)

Hard disk is RAID1, 15K rpm with 146.2GB of space.

I just ensured that, no slow queries are logged. Also for the option log-queries-not-using-indexes=YES, i don't see any entry in the log files. Am i looking in wrong place?

Thanks.

**tchjts1** · 26-09-2013, 15:14

Originally posted by zabbixfk

I just ensured that, no slow queries are logged. Also for the option log-queries-not-using-indexes=YES, i don't see any entry in the log files. Am i looking in wrong place?

Thanks.

mysqld.conf

(Edit) Which I have never used, by the way. I have always used my.cnf

**zabbixfk** · 26-09-2013, 15:21

Zabbix Database Access is very slow ( MySql 11GB + )

Thanks for the reply.

Sorry, if i am sounding monotones/repetitive, but i am kind of newbie to this field, and trying to figure what's wrong

Yes. I have entry of log-queries-not-using-indexes=YES, but i am not sure because of that, mysql db size is growing ( since i looked at both mysqld.log and slow-query.log , both has normal entries. And the size of both logs are in KB's.

All i wanted to do is to make sure db doesn't grow like this. ( I am afraid of blind delete of table entries as i need to make sure i have data of 6 months).

Request you to please elaborate your reply.

Thanks again for your patience.

**steveboyson** · 26-09-2013, 15:41

We have in a test environment (2.0.6 appliance on SuSE) running for ~5 months.

Having 26 hosts with 66 templates, almost any item is templated. We use ~30 discovery rules giving in summary 5335 active items.

History is set to max. 90 days, trends to max. 180 days.
Mimimum item interval is 60sec (less than 100 items), while most of the items have 120sec or longer, up to one day (86400s)

mysql is using "file per table" parameter since I recreated all tables the other day from backup (the Zabbix folks "delivered" the appliance without "file per table" set).

The zabbix database is ~ 30 GB and is increasing slowly.
I see no problems so far on filesystem usage gain.

Attached Files

**zabbixfk** · 26-09-2013, 15:46

Zabbix Database Access is very slow ( MySql 12GB + )

Thanks for the reply.

Can you share your zabbix_server/zabbix_agent and mysqld conf files, also do you have any snmp related items?

Thanks

**steveboyson** · 26-09-2013, 16:00

There's nothing special in there.

zabbix_server:

Code:

StartPollers=25
StartPollersUnreachable=2
StartPingers=2
StartDiscoverers=2
StartHTTPPollers=2
CacheSize=16M
Timeout=30
UnreachableDelay=30

mysqld:

Code:

[mysqld]
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
server-id       = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 32M
default-storage-engine=INNODB

And yes, we have a lot of SNMP items, I wrote a bunch of custom discovery rules for "HP Insight Agents" (temperature, fan, PhysicalDrv, LogicalDrv, SoftwareList, ControllerStats etc.)

**tchjts1** · 26-09-2013, 17:15

Originally posted by zabbixfk

All i wanted to do is to make sure db doesn't grow like this. ( I am afraid of blind delete of table entries as i need to make sure i have data of 6 months).

Ok, I understand what you are saying. I can give you a visual of how ours has grown over a year. I have history set at either 7 or 14 for the majority of items, with trends set at 365. My update interval is 60 for the majority of items.

At the beginning of the year we started out with about 30 monitored hosts and steadily added and are now at about 600 hosts. Current size of the DB is about 120GB.

As long as you have housekeeper running, which you do, obsolete data will be cleaned up as needed.

This is a 1 year view.

Attached Files

**mickey17-93** · 06-03-2017, 01:22

hi, i need help in zabbix

Originally posted by hwidjaja

My Zabbix for monitoring 1000 hosts is running fully on VMs (1 MySQL server, 1 master, 4 proxy and 4 GUI behind F5 BIG-IP). The DB size is around 80GB.

The performance is good (I still enable the hourly housekeeping).

You might need to tune up your MySQL.

Hello, a question which configuration do you have for 100 host? I have 100 and I do not know what changes to apply in database and server.

Ad Widget

Zabbix Database Access is very slow ( MySql 10GB + )

Zabbix Database Access is very slow ( MySql 10GB + )

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment