Empty graphs in the mysql plugin

1

Hi Everybody,

On Debian 8 / Mysql 5.6 with the last version of the plugin, those graphs are empty :

InnoDB Buffer Pool
InnoDB Buffer Pool Activity
InnoDB Insert Buffer
InnoDB IO
InnoDB IO Pending
InnoDB Log
InnoDB Row Operations
InnoDB semaphores
InnoDB Transactions

It seems that some variables are missing in the $keys array.

Here is my mysql> show global status;
±----------------------------------------------±-------------+
| Variable_name | Value |
±----------------------------------------------±-------------+
| Aborted_clients | 276 |
| Aborted_connects | 33778 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| Bytes_received | 8463958919 |
| Bytes_sent | 11505416296 |
| Com_admin_commands | 464521 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 1847 |
| Com_alter_tablespace | 0 |
| Com_alter_user | 0 |
| Com_analyze | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 13619 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 3718326 |
| Com_create_db | 0 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 200 |
| Com_create_procedure | 0 |
| Com_create_server | 0 |
| Com_create_table | 233 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 2162617 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 1 |
| Com_drop_procedure | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 105 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 0 |
| Com_flush | 2 |
| Com_get_diagnostics | 0 |
| Com_grant | 12 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 1751188 |
| Com_insert_select | 0 |
| Com_install_plugin | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_lock_tables | 73 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_release_savepoint | 0 |
| Com_rename_table | 0 |
| Com_rename_user | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_resignal | 0 |
| Com_revoke | 1 |
| Com_revoke_all | 0 |
| Com_rollback | 3169264 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 29 |
| Com_select | 13057156 |
| Com_set_option | 19747646 |
| Com_signal | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_create_db | 97 |
| Com_show_create_event | 0 |
| Com_show_create_func | 0 |
| Com_show_create_proc | 0 |
| Com_show_create_table | 33253 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 30 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 0 |
| Com_show_events | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 92065 |
| Com_show_function_code | 0 |
| Com_show_function_status | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 60969 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_code | 0 |
| Com_show_procedure_status | 0 |
| Com_show_processlist | 15 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_relaylog_events | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 15 |
| Com_show_status | 19 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 13569 |
| Com_show_tables | 40489 |
| Com_show_triggers | 13520 |
| Com_show_variables | 235 |
| Com_show_warnings | 1273165 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reprepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 96 |
| Com_update | 1383369 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 1 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 1289010 |
| Created_tmp_disk_tables | 173968 |
| Created_tmp_files | 16 |
| Created_tmp_tables | 318753 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 11798768 |
| Handler_delete | 1690338 |
| Handler_discover | 0 |
| Handler_external_lock | 21772528 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 604278 |
| Handler_read_key | 12355827 |
| Handler_read_last | 362 |
| Handler_read_next | 28682996 |
| Handler_read_prev | 1904438 |
| Handler_read_rnd | 1806012 |
| Handler_read_rnd_next | 25871916 |
| Handler_rollback | 526 |
| Handler_savepoint | 29 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 1386622 |
| Handler_write | 10445245 |
| Innodb_buffer_pool_dump_status | not started |
| Innodb_buffer_pool_load_status | not started |
| Innodb_buffer_pool_pages_data | 6720 |
| Innodb_buffer_pool_bytes_data | 110100480 |
| Innodb_buffer_pool_pages_dirty | 53 |
| Innodb_buffer_pool_bytes_dirty | 868352 |
| Innodb_buffer_pool_pages_flushed | 14682345 |
| Innodb_buffer_pool_pages_free | 1361 |
| Innodb_buffer_pool_pages_misc | 111 |
| Innodb_buffer_pool_pages_total | 8192 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 112 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 546378359 |
| Innodb_buffer_pool_reads | 3680 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 56304174 |
| Innodb_data_fsyncs | 7101690 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 62230528 |
| Innodb_data_reads | 4225 |
| Innodb_data_writes | 20344558 |
| Innodb_data_written | 490696034304 |
| Innodb_dblwr_pages_written | 14682345 |
| Innodb_dblwr_writes | 392922 |
| Innodb_have_atomic_builtins | ON |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 14237423 |
| Innodb_log_writes | 4840489 |
| Innodb_os_log_fsyncs | 5207051 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 9397317120 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 2927 |
| Innodb_pages_read | 3793 |
| Innodb_pages_written | 14682345 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 12372 |
| Innodb_row_lock_time_avg | 6 |
| Innodb_row_lock_time_max | 1790 |
| Innodb_row_lock_waits | 2015 |
| Innodb_rows_deleted | 1690338 |
| Innodb_rows_inserted | 1751188 |
| Innodb_rows_read | 52844334 |
| Innodb_rows_updated | 1386015 |
| Innodb_num_open_files | 430 |
| Innodb_truncated_status_writes | 0 |
| Innodb_available_undo_logs | 128 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 6698 |
| Key_blocks_used | 20 |
| Key_read_requests | 1893 |
| Key_reads | 488 |
| Key_write_requests | 10 |
| Key_writes | 10 |
| Last_query_cost | 0.000000 |
| Last_query_partial_plans | 0 |
| Max_used_connections | 50 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 0 |
| Open_streams | 0 |
| Open_table_definitions | 614 |
| Open_tables | 431 |
| Opened_files | 8258930 |
| Opened_table_definitions | 4935 |
| Opened_tables | 89369 |
| Performance_schema_accounts_lost | 0 |
| Performance_schema_cond_classes_lost | 0 |
| Performance_schema_cond_instances_lost | 0 |
| Performance_schema_digest_lost | 130789 |
| Performance_schema_file_classes_lost | 0 |
| Performance_schema_file_handles_lost | 0 |
| Performance_schema_file_instances_lost | 0 |
| Performance_schema_hosts_lost | 0 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_mutex_classes_lost | 0 |
| Performance_schema_mutex_instances_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Performance_schema_socket_classes_lost | 0 |
| Performance_schema_socket_instances_lost | 0 |
| Performance_schema_stage_classes_lost | 0 |
| Performance_schema_statement_classes_lost | 0 |
| Performance_schema_table_handles_lost | 0 |
| Performance_schema_table_instances_lost | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Performance_schema_users_lost | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031352 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 13029000 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
| Queries | 48252665 |
| Questions | 47788141 |
| Select_full_join | 7306 |
| Select_full_range_join | 0 |
| Select_range | 124329 |
| Select_range_check | 0 |
| Select_scan | 812834 |
| Slave_heartbeat_period | |
| Slave_last_heartbeat | |
| Slave_open_temp_tables | 0 |
| Slave_received_heartbeats | |
| Slave_retried_transactions | |
| Slave_running | OFF |
| Slow_launch_threads | 2 |
| Slow_queries | 1 |
| Sort_merge_passes | 5 |
| Sort_range | 367541 |
| Sort_rows | 1166295 |
| Sort_scan | 124058 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_server_not_after | |
| Ssl_server_not_before | |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | Unknown |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 10779543 |
| Table_locks_waited | 43 |
| Table_open_cache_hits | 10878724 |
| Table_open_cache_misses | 89367 |
| Table_open_cache_overflows | 86858 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 0 |
| Threads_connected | 38 |
| Threads_created | 19843 |
| Threads_running | 1 |
| Uptime | 2328099 |
| Uptime_since_flush_status | 2328099 |
±----------------------------------------------±-------------+
341 rows in set (0.00 sec)

Thanks for your help.

Regards,

1 Like
2

Hi,

Same issue for me.
Cacti script is now in the percona-monitoring-plugins, but it has to be modified to works with librenms.

If anyone as the last version :slight_smile:
++

3

OK thanks for your answer.

4

Hi,

Same here with a CentOS 7 installation, all graphs work except the InnoDB ones.
Anyone with a solution?

Cheers,
Patrick

5

+1 :thumbsup:

6

I’ve just checked my servers and one is also showing empty graphs for InnoDB:

  • MySQL 5.5: works
  • MariaDB 10.0: graphs empty

Can anybody confirm that it might have to do with the server version? Then I’ll have a look at the check script and see if we can work it out.

7

I had an issue like this, it was because the data wasn’t being collected by librenms. Verify you have php-cli and php-mysql installed, as well as a correctly configured mysql.cnf in the same directory as the extend/agent script. If you are trying to pull from snmp and not the agent, verify you’ve added the “extend mysql /etc/snmp/mysql” to your snmpd.conf and have the sql check script in the /etc/snmp directory.

Remember this needs to be done on each host you want to pull the data from, not just the librenms server.

8

I think the issue we are talking here (at least in my case) is a different one. Data is being collected and all the other graphs are ok, only the InnoDB tab has empty graphs. And (again at least in my case) it only occurs on 1 out of 5 servers where I have the MySQL app activated.

Btw. I am using SNMP to collect the data.