Latest Data Slow or Doesn't load at all (zabbix/latest.php)

[SDPCR]

Latest data page either doesn't load or takes 10+ minutes to load.

It worked in v2.4. However now that we're in v3.2.1 it no longer works.

We have been able to work around this by opening last values out of triggers to get information about item activity.

We tried setting the General : Dropdown first entry to None. per other threads with no success.

Other than this, our server performance, other page load times, monitoring checks, etc. have been great.

Any ideas in troubleshooting this and whether anyone else has seen/solved this would be appreciated.

Thanks in advance.

[JorisWillems]

The same here. I upgraded from 2.4 to 3.2.1. At first the "Latest data" page wouldn't load at all, just timeout. After heavy tuning of mysql and php I am getting a page after several minutes.

Should we file a bug?

[JorisWillems]

Raising the join buffer seems to improve the situation somewhat. Selecting a large host group worsens the load time and server load.

[SDPCR]

Yea, I'm guessing that if others are also having the same issue then a bug report is in order.

Thanks for the recomendation on join_buffer_size
Our join_buffer_size = 8M

I'm interested in hearing about the other my.conf and php.ini edits you may have put into place.

[JorisWillems]

I am not sure about join_buffer now. I read somewhere that you should disable it in recent versions of mysql. I did so and upgraded to 3.2.3. It's displaying the latest_data view now without crashing the whole server but still very slow.

[ingus.vilnis]

Hi Joris,

Can you post your full my.cnf file here?

Do you also see any slow queries in zabbix_server.log file (provided that you have enabled LogSlowQueries=3000 in zabbix_server.conf)?

[JorisWillems]

EDIT: I just noticed you are requesting zabbix slow log. JUST A SECOND!
EDIT2: I can now confirm that there are no slow queries according to mysql as well as zabbix (I enabled logging on both but nothing found)

MySQL 5.7 (different configuration directives for the log). I am not getting any slow queries in the logfile. I changed ownership to mysql and manually tried writing to it as user mysql so permissions should be OK. Is there is something else that could be missing?

# grep ^[^#] /etc/mysql/my.cnf

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0
[mysqld]
skip-name-resolve
skip-host-cache
innodb_file_per_table = 1
innodb_stats_on_metadata = OFF
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit=2
innodb_read_io_threads = 8
innodb_write_io_threads = 8
sync_binlog = 0
innodb_log_file_size = 512M
innodb_buffer_pool_size = 2048M
innodb_buffer_pool_instances = 1
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address            = 127.0.0.1
wait_timeout            = 300
tmp_table_size          = 128M
max_heap_table_size     = 128M
table_open_cache        = 1024
key_buffer_size         = 64M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam_recover_options  = BACKUP
max_connections        = 5000
query_cache_size        = 0
query_cache_type = 0
log_error = /var/log/mysql/error.log
slow_query_log = /var/log/mysql/mysql-slow.log
long_query_time = 3
min_examined_row_limit = 1
expire_logs_days        = 10
max_binlog_size         = 100M
[mysqldump]
quick
quote-names
max_allowed_packet      = 16M
[mysql]
[isamchk]
key_buffer_size         = 16M
!includedir /etc/mysql/conf.d/

[ingus.vilnis]

Here are some suggestions to the MySQL config. Looks like you have overtuned your database and not running the settings matching your DB version and hardware.
Note that many parameters are commented to set back to default. Please enable these setting only if you know for sure what you are doing!

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0
[mysqld]
# skip-name-resolve
# skip-host-cache
innodb_file_per_table = 1
# innodb_stats_on_metadata = OFF
innodb_flush_method = O_DIRECT
# innodb_flush_log_at_trx_commit=2
innodb_flush_log_at_trx_commit=0
innodb_read_io_threads = 8
innodb_write_io_threads = 8
sync_binlog = 0
innodb_log_file_size = 512M
# innodb_buffer_pool_size = 2048M
innodb_buffer_pool_size = 20G
# innodb_buffer_pool_instances = 1
innodb_buffer_pool_instances = 8
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
# skip-external-locking
# bind-address            = 127.0.0.1
# wait_timeout            = 300
tmp_table_size          = 128M
max_heap_table_size     = 128M
# table_open_cache        = 1024
# key_buffer_size         = 64M
# max_allowed_packet      = 16M
# thread_stack            = 192K
# thread_cache_size       = 8
# myisam_recover_options  = BACKUP
# max_connections        = 5000
# query_cache_size        = 0
# query_cache_type = 0
log_error = /var/log/mysql/error.log
slow_query_log = /var/log/mysql/mysql-slow.log
long_query_time = 3
# min_examined_row_limit = 1
expire_logs_days        = 10
max_binlog_size         = 100M

innodb_io_capacity    = 800

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M
[mysql]
[isamchk]
key_buffer_size         = 16M
!includedir /etc/mysql/conf.d/

Restart MySQL to apply the changes. Check MySQL error log for any issues. Uncomment a setting if that is showing up as error in the log.

EDIT. Check for available free memory (free -m) before setting innodb_buffer_pool_size=20G . With yours 38G RAM should be good though but make sure now you have so much available.

[JorisWillems]

I should urgently migrate to 64bit. I am not sure why I installed 32bits in the first place.

[ERROR] innodb_buffer_pool_size can't be over 4GB on 32-bit systems

[ingus.vilnis]

oh.. my... God...

You are definitely not using your hardware at its full potential. If i'm not wrong then 4GB is the absolute maximum you can allocate for everything in total not just a single process, right?

The best thing you could do for your Zabbix is do a backup, on the same hardware (which is decent) install fresh clean 64-bit CentOS 7 Minimal, Zabbix 3.0.6 from packages and MySQL 5.7 with the default settings plus a few recommendations from here. https://www.percona.com/blog/2014/11...-mysql-zabbix/

And you should see no issues whatsoever.

[JorisWillems]

I admit, a pretty stupid mistake :. I must say I started administering this server when it was already set up years ago. I just did the OS upgrades to start out. Then I started adding cpu cores since I saw lots of load.
Tomorrow I migrate this setup. I will try to give a confirmation that all is well.

[ingus.vilnis]

Sure, try to make some improvements there. But don't overthink the config settings. Defaults aren't enough for sure but changing too much configs also does not do any good. Seen this countless times. And these mistakes are very difficult to find and fix.

Let us know how it goes.

BTW, I did close the ZBX you created. Currently I don't see this as a bug on Zabbix side rather than a performance issue. We can return to that question once you'll have a proper setup again.

Best Regards,
Ingus

[JorisWillems]

it took some time and some patience but we are migrated and still running zabbix, with more hosts and triggers than ever. I am now looking into migrating to 3.4. Just remembered this thread and I thought it wouldn't be a bad idea to look at it again. The DB is running now a lot better. It's just using sooo little of it's memory. But as long as it does not overburden the cpu's its only a good sign.

[kernbug]

it took some time and some patience but we are migrated and still running zabbix, with more hosts and triggers than ever. I am now looking into migrating to 3.4. Just remembered this thread and I thought it wouldn't be a bad idea to look at it again. The DB is running now a lot better. It's just using sooo little of it's memory. But as long as it does not overburden the cpu's its only a good sign.

Great news, JorisWillems!

[kloczek]

Yea, I'm guessing that if others are also having the same issue then a bug report is in order.

Thanks for the recomendation on join_buffer_size
Our join_buffer_size = 8M

I'm interested in hearing about the other my.conf and php.ini edits you may have put into place.

https://dev.mysql.com/doc/refman/8.0...in_buffer_size
"The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans."
I don't think that you are affected by the latency of the queries which are not using indexes in case if using zabbix SQL database.