search

major / MySQLTuner-perl

MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability.
GNU General Public License v3.0
8.93k stars 1.29k forks source link

division by zero #603

Closed wywerne closed 1 year ago

wywerne commented 2 years ago

On RHEL 7 with MariaDB 10.8.3 fresh installation with last mysqltuner.pl, i have this error :

Illegal division by zero at /usr/local/bin/mysqltuner line 6137 (#1) (F) You tried to divide a number by 0. Either something was wrong in your logic, or you need to put a conditional in to guard against meaningless input.

Uncaught exception from user code: Illegal division by zero at /usr/local/bin/mysqltuner line 6137. main::mysql_innodb() called at /usr/local/bin/mysqltuner line 6881

Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 926 Server version: 10.8.3-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show variables like 'Innodb_%'; +------------------------------------------+------------------------+ | Variable_name | Value | +------------------------------------------+------------------------+ | innodb_adaptive_flushing | ON | | innodb_adaptive_flushing_lwm | 10.000000 | | innodb_adaptive_hash_index | OFF | | innodb_adaptive_hash_index_parts | 8 | | innodb_autoextend_increment | 64 | | innodb_autoinc_lock_mode | 1 | | innodb_buf_dump_status_frequency | 0 | | innodb_buffer_pool_chunk_size | 393347072 | | innodb_buffer_pool_dump_at_shutdown | ON | | innodb_buffer_pool_dump_now | OFF | | innodb_buffer_pool_dump_pct | 25 | | innodb_buffer_pool_filename | ib_buffer_pool | | innodb_buffer_pool_load_abort | OFF | | innodb_buffer_pool_load_at_startup | ON | | innodb_buffer_pool_load_now | OFF | | innodb_buffer_pool_size | 25174212608 | | innodb_change_buffer_max_size | 25 | | innodb_change_buffering | none | | innodb_checksum_algorithm | full_crc32 | | innodb_cmp_per_index_enabled | OFF | | innodb_compression_algorithm | zlib | | innodb_compression_default | OFF | | innodb_compression_failure_threshold_pct | 5 | | innodb_compression_level | 6 | | innodb_compression_pad_pct_max | 50 | | innodb_data_file_path | ibdata1:12M:autoextend | | innodb_data_home_dir | | | innodb_deadlock_detect | ON | | innodb_deadlock_report | full | | innodb_default_encryption_key_id | 1 | | innodb_default_row_format | dynamic | | innodb_defragment | OFF | | innodb_defragment_fill_factor | 0.900000 | | innodb_defragment_fill_factor_n_recs | 20 | | innodb_defragment_frequency | 40 | | innodb_defragment_n_pages | 7 | | innodb_defragment_stats_accuracy | 0 | | innodb_disable_sort_file_cache | OFF | | innodb_doublewrite | ON | | innodb_encrypt_log | OFF | | innodb_encrypt_tables | OFF | | innodb_encrypt_temporary_tables | OFF | | innodb_encryption_rotate_key_age | 1 | | innodb_encryption_rotation_iops | 100 | | innodb_encryption_threads | 0 | | innodb_fast_shutdown | 1 | | innodb_fatal_semaphore_wait_threshold | 600 | | innodb_file_per_table | ON | | innodb_fill_factor | 100 | | innodb_flush_log_at_timeout | 1 | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | O_DIRECT | | innodb_flush_neighbors | 1 | | innodb_flush_sync | ON | | innodb_flushing_avg_loops | 30 | | innodb_force_primary_key | OFF | | innodb_force_recovery | 0 | | innodb_ft_aux_table | | | innodb_ft_cache_size | 8000000 | | innodb_ft_enable_diag_print | OFF | | innodb_ft_enable_stopword | ON | | innodb_ft_max_token_size | 84 | | innodb_ft_min_token_size | 3 | | innodb_ft_num_word_optimize | 2000 | | innodb_ft_result_cache_limit | 2000000000 | | innodb_ft_server_stopword_table | | | innodb_ft_sort_pll_degree | 2 | | innodb_ft_total_cache_size | 640000000 | | innodb_ft_user_stopword_table | | | innodb_immediate_scrub_data_uncompressed | OFF | | innodb_instant_alter_column_allowed | add_drop_reorder | | innodb_io_capacity | 200 | | innodb_io_capacity_max | 2000 | | innodb_lock_wait_timeout | 50 | | innodb_log_buffer_size | 8388608 | | innodb_log_file_size | 536870912 | | innodb_log_group_home_dir | ./ | | innodb_lru_flush_size | 32 | | innodb_lru_scan_depth | 1536 | | innodb_max_dirty_pages_pct | 90.000000 | | innodb_max_dirty_pages_pct_lwm | 0.000000 | | innodb_max_purge_lag | 0 | | innodb_max_purge_lag_delay | 0 | | innodb_max_purge_lag_wait | 4294967295 | | innodb_max_undo_log_size | 10485760 | | innodb_monitor_disable | | | innodb_monitor_enable | all | | innodb_monitor_reset | | | innodb_monitor_reset_all | | | innodb_old_blocks_pct | 37 | | innodb_old_blocks_time | 1000 | | innodb_online_alter_log_max_size | 134217728 | | innodb_open_files | 8192 | | innodb_optimize_fulltext_only | OFF | | innodb_page_size | 16384 | | innodb_prefix_index_cluster_optimization | OFF | | innodb_print_all_deadlocks | OFF | | innodb_purge_batch_size | 300 | | innodb_purge_rseg_truncate_frequency | 128 | | innodb_purge_threads | 4 | | innodb_random_read_ahead | OFF | | innodb_read_ahead_threshold | 56 | | innodb_read_io_threads | 4 | | innodb_read_only | OFF | | innodb_read_only_compressed | OFF | | innodb_rollback_on_timeout | OFF | | innodb_sort_buffer_size | 1048576 | | innodb_spin_wait_delay | 4 | | innodb_stats_auto_recalc | ON | | innodb_stats_include_delete_marked | OFF | | innodb_stats_method | nulls_equal | | innodb_stats_modified_counter | 0 | | innodb_stats_on_metadata | OFF | | innodb_stats_persistent | ON | | innodb_stats_persistent_sample_pages | 20 | | innodb_stats_traditional | ON | | innodb_stats_transient_sample_pages | 8 | | innodb_status_output | OFF | | innodb_status_output_locks | OFF | | innodb_strict_mode | ON | | innodb_sync_spin_loops | 30 | | innodb_table_locks | ON | | innodb_temp_data_file_path | ibtmp1:12M:autoextend | | innodb_tmpdir | | | innodb_undo_directory | ./ | | innodb_undo_log_truncate | OFF | | innodb_undo_tablespaces | 0 | | innodb_use_atomic_writes | ON | | innodb_use_native_aio | ON | | innodb_version | 10.8.3 | | innodb_write_io_threads | 4 | +------------------------------------------+------------------------+ 131 rows in set (0.003 sec)

regard,

sdn-dev commented 2 years ago

Facing a similar issue on Ubuntu 22.04 with MariaDB 10.9.2:

Illegal division by zero at ./mysqltuner.pl line 6138 (#1) (F) You tried to divide a number by 0. Either something was wrong in your logic, or you need to put a conditional in to guard against meaningless input.

Uncaught exception from user code: Illegal division by zero at ./mysqltuner.pl line 6138. main::mysql_innodb() called at ./mysqltuner.pl line 6881

brunom63 commented 2 years ago

Same here but with innodb_log_files_in_group on Docker container version:

mariadb  Ver 15.1 Distrib 10.9.3-MariaDB, for debian-linux-gnu (aarch64) using  EditLine wrapper
brunom63 commented 2 years ago

Oops, sorry, my bad. This issue was fixed on version 1.8.

bperel commented 2 years ago

In my case, the error still occurs in version 2.0.9

muuvmuuv commented 2 years ago

Seeing this as well, on 16GB with Xeon E5-2640:

[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[!!] InnoDB Read buffer efficiency: -516.98% (-8737 hits / 1690 total)
[!!] InnoDB Write Log efficiency: 0% (0 hits / 0 total)
Illegal division by zero at mysqltuner.pl line 6157 (#1)
    (F) You tried to divide a number by 0.  Either something was wrong in
    your logic, or you need to put a conditional in to guard against
    meaningless input.

Uncaught exception from user code:
    Illegal division by zero at mysqltuner.pl line 6157.
    main::mysql_innodb() called at mysqltuner.pl line 6900
[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_520_ci
port = 3306
bind-address = 0.0.0.0
skip_name_resolve = On
datadir = /var/lib/mysql
tmpdir = /tmp
log_error = /var/log/mysql/mysqld.log
socket = /run/mysqld/mysqld.sock
max_connections = 200 # as required per situation
# wait_timeout = 1800
max_connect_errors = 1000
connect_timeout = 20
performance_schema = On
innodb_stats_on_metadata = On
table_open_cache = 8192
table_definition_cache = 4496
join_buffer_size = 256
max_allowed_packet = 512M
key_buffer_size = 4M
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_fast_shutdown = 0
innodb_file_per_table
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1 # needed for ACID
innodb_lock_wait_timeout = 120 # default 50 sec
innodb_log_buffer_size = 32M
innodb_log_files_in_group = 2
innodb_log_file_size = 512M
innodb_max_dirty_pages_pct = 75
default_storage-engine = InnoDB

[mysqldump]
single_transaction = 1
extended_insert = 1
quick
max_allowed_packet = 512M

[client]
socket = /run/mysqld/mysqld.sock
mikevoets commented 2 years ago

As a temporary workaround, I went to line 6157 in the file and added in a condition to check that the denominator is higher than zero ($mystat{'Innodb_log_writes'} > 0.000001), like this:

if (    defined( $mystat{'Innodb_log_waits'} )
        and defined( $mystat{'Innodb_log_writes'} )
        and $mystat{'Innodb_log_writes'} > 0.000001 )
{
 ...
jmrenouard commented 1 year ago

MySQLtuner.pl 2.1.1+ Line 6192:

` # InnoDB Log Waits $mystat{'Innodb_log_waits_computed'} = 0;

if (    defined( $mystat{'Innodb_log_waits'} )
    and defined( $mystat{'Innodb_log_writes'} ) and $mystat{'Innodb_log_writes'} > 0.000001 )
{
    $mystat{'Innodb_log_waits_computed'} =
      $mystat{'Innodb_log_waits'} / $mystat{'Innodb_log_writes'};
}
else {
    undef $mystat{'Innodb_log_waits_computed'};
}

`

This issue seems to be fixed :)