| Bug #114092 | WHERE statement on an indexed decimal column hangs | ||
|---|---|---|---|
| Submitted: | 22 Feb 2024 23:40 | Modified: | 24 Feb 2024 13:55 |
| Reporter: | Daniel Bircher | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S5 (Performance) |
| Version: | Ver 8.0.36-0 | OS: | Ubuntu (ubuntu 22.04.1) |
| Assigned to: | CPU Architecture: | x86 (Linux on x86_64 ((Ubuntu))) | |
| Tags: | decimal values, INDEX, MYISAM engine | ||
[22 Feb 2024 23:50]
Daniel Bircher
As a side note, var1_ generally represents a free-text field. The values contained within does not appear to be relevant.
[23 Feb 2024 8:22]
MySQL Verification Team
Hello Daniel Bircher,
Thank you for the report and test case.
I quickly tried to reproduce locally with the provided test case but not seeing any issues. Could you please share exact conf file used? Thank you.
--
-- Env
Platform | Linux
Release | Red Hat Enterprise Linux Server release 7.9 (Maipo)
Kernel | 5.4.17-2102.206.1.el7uek.x86_64
Architecture | CPU = 64-bit, OS = 64-bit
Threading | NPTL 2.17
Compiler | GNU CC version 4.8.5 20150623 (Red Hat 4.8.5-44.0.3).
SELinux | Disabled
Virtualized | VMWare
# Processor ##################################################
Processors | physical = 1, cores = 8, virtual = 16, hyperthreading = yes
Speeds | 16x2445.406
Models | 16xAMD EPYC 7J13 64-Core Processor
Caches | 16x512 KB
# Memory #####################################################
Total | 117.4G
Free | 8.6G
Used | physical = 1.4G, swap allocated = 8.0G, swap used = 5.3G, virtual = 6.6G
Shared | 3.6G
Buffers | 107.4G
Caches | 111.3G
Dirty | 72 kB
UsedRSS | 1.2G
Swappiness | 60
DirtyPolicy | 20, 10
-- build details
cat docs/INFO_SRC
commit: 4e7d422ffbb4fff9342a568b999e44eccc9630dd
date: 2023-12-12 19:05:31 +0100
build-date: 2023-12-12 18:11:09 +0000
short: 4e7d422ffbb
branch: mysql-8.0.36-release
MySQL source 8.0.36
-- startup
rm -rf 114092/
bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/114092 --log-error-verbosity=3
bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/114092 --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/114092/log.err --mysqlx=0 --log-error-verbosity=3 --secure-file-priv="" --local-infile=1 2>&1 &
bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create user bug@'ip';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on test.* to bug@'100.103.30.87';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE DATABASE IF NOT EXISTS test;
Query OK, 1 row affected (0.01 sec)
mysql> use test;
Database changed
mysql> DROP PROCEDURE IF EXISTS `prepare_data`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> DELIMITER $$
mysql> CREATE DEFINER=`bug`@`ip` PROCEDURE `prepare_data`()
-> BEGIN
-> DECLARE i INT DEFAULT 1;
-> WHILE i < 1000000 DO
-> INSERT INTO _TEST (dec_ , var1_) VALUES (ROUND(-1+2*RAND(),2), CONCAT(UUID(), UUID(), UUID(), UUID(), UUID()));
-> SET i = i + 1;
-> END WHILE;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> -- Create test data
mysql> DROP TABLE IF EXISTS _TEST;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> CREATE TABLE `_TEST` (
-> `dec_` decimal(15,2) DEFAULT NULL,
-> `var1_` varchar(250) DEFAULT NULL
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> -- call procedure
mysql>
mysql> CALL prepare_data();
Query OK, 1 row affected (21 min 28.43 sec)
mysql> pager grep less
PAGER set to 'grep less'
mysql>
mysql> -- Run tests
mysql>
mysql> SELECT * FROM _TEST WHERE dec_ < 0;
496743 rows in set (0.97 sec)
mysql> ALTER TABLE _TEST ADD INDEX dec_(dec_);
Query OK, 999999 rows affected (2.91 sec)
Records: 999999 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM _TEST WHERE dec_ < 0;
496743 rows in set (0.97 sec)
mysql> ALTER TABLE _TEST DROP INDEX dec_;
Query OK, 999999 rows affected (1.68 sec)
Records: 999999 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM _TEST WHERE dec_ < 0;
496743 rows in set (0.98 sec)
regards,
Umesh
[23 Feb 2024 14:49]
Daniel Bircher
Hi Umesh, I appreciate your quick response. I will attach my config file. Upon further testing this morning the issue appears to be related to read_rnd_buffer_size. When specifying a specific value the issue appears - 5GB in my instance. Please let me know if I can help with anything further. Thanks again!
[23 Feb 2024 14:53]
Daniel Bircher
When attempting to upload a file, I receive "You don't have permission to access "http://splash.oracle.com/bug.php?" on this server.". I will paste here: # # The MySQL database server configuration file. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # Here is entries for some specific programs # The following values assume you have at least 32M ram [mysqld] # # * Basic Settings # user = mysql # pid-file = /var/run/mysqld/mysqld.pid # socket = /var/run/mysqld/mysqld.sock # port = 3306 # datadir = /var/lib/mysql # If MySQL is running as a replication slave, this should be # changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir # tmpdir = /tmp # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address = 0.0.0.0 mysqlx-bind-address = 0.0.0.0 # # * Fine Tuning # ## ********* Server specifics ********** ## disable_log_bin skip-external-locking skip-name-resolve local_infile = 1 #sql_mode = '' lock_wait_timeout = 5 max_connections = 100 default_storage_engine = MyISAM default_tmp_storage_engine = MyISAM event_scheduler = OFF big_tables = ON ## ********* MyISAM specifics ********** ## key_buffer_size = 5368709120 ##5GB #read_rnd_buffer_size = 5368709120 ##5GB ## ********** InnoDB specifics (temp tables) *********** ## innodb_tmpdir = /var/temp innodb_temp_tablespaces_dir = /var/temp innodb_data_file_path = ibdata1:10M:autoextend innodb_buffer_pool_size = 48G #innodb_flush_log_at_trx_commit = 0 #innodb_flush_method = O_DIRECT #innodb_read_io_threads = 8 #innodb_write_io_threads = 8 innodb_buffer_pool_instances = 48 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover-options = BACKUP # max_connections = 151 # table_open_cache = 4000 # # * Logging and Replication # # Both location gets rotated by the cronjob. # # Log all queries # Be aware that this log type is a performance killer. # general_log_file = /var/log/mysql/query.log # general_log = 1 # # Error log - should be very few entries. # log_error = /var/log/mysql/error.log # # Here you can see queries with especially long duration # slow_query_log = 1 # slow_query_log_file = /var/log/mysql/mysql-slow.log # long_query_time = 2 # log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. # server-id = 1 # log_bin = /var/log/mysql/mysql-bin.log # binlog_expire_logs_seconds = 2592000 # max_binlog_size = 100M # binlog_do_db = include_database_name # binlog_ignore_db = include_database_name
[24 Feb 2024 12:05]
MySQL Verification Team
Thank you, Daniel for the details. I'm able to reproduce after setting read_rnd_buffer_size to 5G. Let me check with other storage engine to see if it affects just MyISAM or other also. Thank you once again for sharing the conf file. Sincerely, Umesh
[24 Feb 2024 13:55]
Daniel Bircher
Thank you, Umesh. Please let me know if there's anything else I can do to support the resolution of this big.
Description: Performing a WHERE filter on an indexed DECIMAL column hangs. Please see steps below to repeat the issue. I've tested on multiple servers directly through the CLI, as well as, through MySQL Workbench. Please let me know if I can provide any further details. Thanks. How to repeat: Please run the following: DROP PROCEDURE `prepare_data`; DELIMITER $$ CREATE DEFINER=`user`@`ip%` PROCEDURE `prepare_data`() BEGIN DECLARE i INT DEFAULT 1; WHILE i < 1000000 DO INSERT INTO _TEST (dec_ , var1_) VALUES (ROUND(-1+2*RAND(),2), CONCAT(UUID(), UUID(), UUID(), UUID(), UUID())); SET i = i + 1; END WHILE; END$$ DELIMITER ; -- Create test data DROP TABLE IF EXISTS _TEST; CREATE TABLE `_TEST` ( `dec_` decimal(15,2) DEFAULT NULL, `var1_` varchar(250) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CALL prepare_data(); -- Run tests SELECT * FROM _TEST WHERE dec_ < 0; -- Works -- 3 31 16:25:54 SELECT * FROM _TEST WHERE dec_ < 0 LIMIT 0, 1000 1000 row(s) returned 0.016 sec / 0.000 sec ALTER TABLE _TEST ADD INDEX dec_(dec_); SELECT * FROM _TEST WHERE dec_ < 0; -- Does not work -- Ran for 30+ minutes. Killed process. Process hung and did not clear until service was restarted. ALTER TABLE _TEST DROP INDEX dec_; SELECT * FROM _TEST WHERE dec_ < 0; -- Works -- 3 36 16:28:29 SELECT * FROM _TEST WHERE dec_ < 0 LIMIT 0, 1000 1000 row(s) returned 0.000 sec / 0.000 sec