Every since I upgraded our Zabbix server to 6.0 last year, we've had an issue with intermittent flapping alerts for tmp tables being generated in MySQL . The alerts are being generated by the official Zabbix MySQL by Zabbix Agent template.
MySQL: Created tmp tables on memory per second
MySQL: Created tmp tables on disk per second
For both of which the Template's advice is very helpfully "Possibly the application using the database is in need of query optimization."
This doesn't appear to be service affecting, but it does generate noise in the form of alert e-mails, so I would like to make another try at getting to the bottom of it:
As you can see there's no obvious pattern from the last week of data:
Consolidation runs hourly which is in general the only regular task I've noticed which adds a minor bump to resource usage, otherwise usage is pretty steady 24-7.
The server itself has 80GBytes of RAM and tends to use a fairly steady 45GBytes of RAM with a pretty generous InnoDB buffer pool allocation, so I'm not currently aware of any bottlenecks at a resource or MySQL level
Here is my MySQL config:
.
Has anybody else encountered this, or can anybody give me any pointers in how to investigate further?
Thanks.
MySQL: Created tmp tables on memory per second
MySQL: Created tmp tables on disk per second
For both of which the Template's advice is very helpfully "Possibly the application using the database is in need of query optimization."
This doesn't appear to be service affecting, but it does generate noise in the form of alert e-mails, so I would like to make another try at getting to the bottom of it:
As you can see there's no obvious pattern from the last week of data:
Consolidation runs hourly which is in general the only regular task I've noticed which adds a minor bump to resource usage, otherwise usage is pretty steady 24-7.
The server itself has 80GBytes of RAM and tends to use a fairly steady 45GBytes of RAM with a pretty generous InnoDB buffer pool allocation, so I'm not currently aware of any bottlenecks at a resource or MySQL level
Here is my MySQL config:
.
Code:
[mysqld] lc_messages_dir = /usr/share/mysql lc_messages = en_US skip-external-locking skip-name-resolve = 1 bind-address = 0.0.0.0 max_connections = 500 connect_timeout = 600 wait_timeout = 600 max_allowed_packet = 256M thread_cache_size = 128 sort_buffer_size = 16M bulk_insert_buffer_size = 16M tmp_table_size = 32M max_heap_table_size = 32M table_open_cache = 400 concurrent_insert = 2 read_buffer_size = 2M read_rnd_buffer_size = 1M #Logging log_warnings = 2 slow_query_log=1 slow_query_log_file = /var/log/mysql/mariadb-slow.log long_query_time = 10 log_slow_verbosity = query_plan server-id = 1 log_bin = /var/log/mysql/mariadb-bin log_bin_index = /var/log/mysql/mariadb-bin.index expire_logs_days = 1 max_binlog_size = 100M binlog_format = row relay_log = /var/log/mysql/relay-bin relay_log_index = /var/log/mysql/relay-bin.index relay_log_info_file =/var/log/mysql/relay-bin.info log_error =/var/log/mysql/error.log # Required for 6.0 Upgrade log_bin_trust_function_creators = 1 #InnoDB default_storage_engine = InnoDB innodb_log_file_size = 1G innodb_buffer_pool_size = 50G innodb_log_buffer_size = 8M innodb_file_per_table = 1 innodb_open_files = 400 innodb_io_capacity = 400 innodb_flush_method = O_DIRECT performance_schema = ON proxy-protocol-networks=::1, 10.39.2.0/23 ,localhost
Has anybody else encountered this, or can anybody give me any pointers in how to investigate further?
Thanks.
Comment