[MDEV-16461] MyISAM creates defect indexes on varchar, if rowcount is above some threshold between 480,000,000 and 490,000,000 Created: 2018-06-11  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Platform Debian, Storage Engine - MyISAM
Affects Version/s: 10.2.15, 10.2, 10.3
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: rawi Assignee: Rucha Deodhar
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Ubuntu Xenial Server
CPU Intel 6 core + HT, 64GB RAM

Attachments: File my.conf    
Issue Links:
Relates
relates to MDEV-17401 LOAD DATA from very big file into MyI... Closed
relates to MDEV-26552 MyISAM index corruption on 400M rows Closed

 Description    

DROP TABLE IF EXISTS test;
 
CREATE TABLE test (
 
rs	VARCHAR(20) PRIMARY KEY
 
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

rs is in the form "rs12364789", max. length of the string is 12; always "rs" before a number

# myisamchk --keys-used=0 -rq test.MYI
 
... FLUSH
 
# mysqlimport --local --fields_escaped_by=\\ -u root -p test_db test.tsv
 
... FLUSH
 
# myisamchk --analyze --update-state --sort_buffer_size=56G  --recover --quick --quick --tmpdir=/mnt/WORK/tmp test.MYI
 
... FLUSH

RESULTS:

  • with 480,000,000 rows everything is OK
  • with 490,000,000 rows nothing will be found.
    EXPLAIN says: "Impossible WHERE noticed after reading const tables"

The row can still be found with IGNORE INDEX (PRIMARY)

If I build another table with only a non unique index on `rs` , EXPLAIN says it's using the index but the query won't find anything..

A server version from 3 or 4 Months ago worked OK.

Hope it helps

Thanks

 Comments   
Comment by Alice Sherepa [ 2018-06-21 ]

What query returns "Impossible WHERE noticed after reading const tables"? Please add also your .cnf file, thanks!

MariaDB [test]> CREATE or replace TABLE t1 (
 
    -> rs VARCHAR(20) PRIMARY KEY
 
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [test]> insert into t1 select CONCAT("rs",seq) from seq_1_to_500000000;
 
Query OK, 500000000 rows affected (1 hour 50.04 sec)
 
Records: 500000000  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> select rs from t1 where rs= "rs490000006";
 
+-------------+
 
| rs          |
 
+-------------+
 
| rs490000006 |
 
+-------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> explain extended select rs from t1 where rs= "rs490000006";
 
+------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+
 
| id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
 
+------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+
 
|    1 | SIMPLE      | t1    | const | PRIMARY       | PRIMARY | 22      | const |    1 |   100.00 | Using index |
 
+------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+
 
1 row in set, 1 warning (0.00 sec)
 
 
 
MariaDB [test]> select version();
 
+-----------------+
 
| version()       |
 
+-----------------+
 
| 10.2.15-MariaDB |
 
+-----------------+
 
1 row in set (0.00 sec)

Comment by rawi [ 2018-06-26 ]

Sorry to come back that late.

I think now, that the culprit is myisamchk creating the indexes.
Like many recommendations on the inet for bulk import in MyIsam engine, I disable first index creation, then I create the indexes after import. Despite the manual, disabling index creation disables also the creation of the unique indexes and PRIMARYs.

As you'll see, it is myisamchk who builds the bad index.
Simply filling the table the classic way, with symultaneously index creation creates a good index, but takes a very long time compared with the first method. (This is due to another problem, mysqld not using the whole assigned myisam_sort_buffer_size to create indexes).

– FIRST METHOD (DISABLE INDEX CREATION == bad index == repair cannot repair)

MariaDB [test]> CREATE or REPLACE TABLE test (rs VARCHAR(20) PRIMARY KEY) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
...
 
MariaDB [test]> FLUSH TABLES ; FLUSH TABLE_STATISTICS ; FLUSH INDEX_STATISTICS ;
 
...
 
 
 
-- as_root_in_the_shell
 
# myisamchk --keys-used=0 -rq test.MYI
 
...
 
MariaDB [test]> FLUSH TABLES ; FLUSH TABLE_STATISTICS ; FLUSH INDEX_STATISTICS ;
 
...
 
MariaDB [test]> INSERT INTO test SELECT CONCAT("rs",seq) from seq_1_to_500000000;
 
Query OK, 500000000 rows affected (3 min 9.23 sec)
 
Records: 500000000  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> FLUSH TABLES ; FLUSH TABLE_STATISTICS ; FLUSH INDEX_STATISTICS ;
 
...
 
 
 
-- as_root_in_the_shell
 
time -p myisamchk --analyze --update-state --sort_buffer_size=56G  --recover --quick --quick --tmpdir=/mnt/WORK/tmp test.MYI
 
- check record delete-chain
 
- recovering (with sort) MyISAM-table 'test.MYI'
 
Data records: 500000000
 
- Fixing index 1
 
real 208.50
 
user 197.98
 
sys 10.49
 
 
 
MariaDB [test]> FLUSH TABLES ; FLUSH TABLE_STATISTICS ; FLUSH INDEX_STATISTICS ;
 
...
 
 
 
MariaDB [test]> SELECT rs FROM test WHERE rs= "rs490000006";
 
Empty set (0.00 sec)
 
 
 
MariaDB [test]> EXPLAIN EXTENDED SELECT rs FROM test WHERE rs= "rs490000006";
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                               |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
 
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE noticed after reading const tables |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
 
1 row in set, 1 warning (0.00 sec)
 
 
 
MariaDB [test]> CHECK TABLE test;
 
+-----------------------------------+-------+----------+------------------------------------------+
 
| Table                             | Op    | Msg_type | Msg_text                                 |
 
+-----------------------------------+-------+----------+------------------------------------------+
 
| homo_sapiens_variation_92_37.test | check | error    | Key in wrong position at page 1722248192 |
 
| homo_sapiens_variation_92_37.test | check | error    | Corrupt                                  |
 
+-----------------------------------+-------+----------+------------------------------------------+
 
2 rows in set (14.45 sec)
 
 
 
MariaDB [test]> REPAIR TABLE test;
 
+-----------------------------------+--------+----------+----------+
 
| Table                             | Op     | Msg_type | Msg_text |
 
+-----------------------------------+--------+----------+----------+
 
| homo_sapiens_variation_92_37.test | repair | status   | OK       |
 
+-----------------------------------+--------+----------+----------+
 
 
 
MariaDB [test]> CHECK TABLE test;
 
+-----------------------------------+-------+----------+------------------------------------------+
 
| Table                             | Op    | Msg_type | Msg_text                                 |
 
+-----------------------------------+-------+----------+------------------------------------------+
 
| homo_sapiens_variation_92_37.test | check | error    | Key in wrong position at page 1722248192 |
 
| homo_sapiens_variation_92_37.test | check | error    | Corrupt                                  |
 
+-----------------------------------+-------+----------+------------------------------------------+
 
2 rows in set (14.52 sec)

–
-- SECOND METHOD, SIMPLY INSERT DATA AND CREATE INDEX SYMULTANEOUSLY

INSERT INTO test SELECT CONCAT("rs",seq) from seq_1_to_500000000;
 
Query OK, 500000000 rows affected (1 hour 15 min 44.64 sec)
 
Records: 500000000  Duplicates: 0  Warnings: 0
 
 
 
FLUSH TABLES ; FLUSH TABLE_STATISTICS ; FLUSH INDEX_STATISTICS ;
 
 
 
SELECT rs FROM test WHERE rs= "rs490000006";
 
+-------------+
 
| rs          |
 
+-------------+
 
| rs490000006 |
 
+-------------+
 
1 row in set (0.00 sec)
 
 
 
EXPLAIN EXTENDED SELECT rs FROM test WHERE rs= "rs490000006";
 
+------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+
 
| id   | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
 
+------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+
 
|    1 | SIMPLE      | test  | const | PRIMARY       | PRIMARY | 22      | const |    1 |   100.00 | Using index |
 
+------+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+
 
1 row in set, 1 warning (0.01 sec)
 
 
 
CHECK TABLE test;+-----------------------------------+-------+----------+----------+
 
| Table                             | Op    | Msg_type | Msg_text |
 
+-----------------------------------+-------+----------+----------+
 
| homo_sapiens_variation_92_37.test | check | status   | OK       |
 
+-----------------------------------+-------+----------+----------+

– CONCLUSION?
Normal inserts/imports will build good tables and indexes but very slow. The time difference between the methods (75 vs. 7 minutes is important - this is a small table
During the simple insert with symultaneously index build - mysqld will not use the assigned myisam_sort_buffer_size = 55G (my.conf is now attached) but only...

  1. ps aux | grep mysqld
    mysql 1561 0.3 0.4 2938068 264604 ? Ssl Jun12 70:18 /usr/sbin/mysqld

Thank you
my.conf

Comment by Alice Sherepa [ 2018-06-26 ]

rawi, thanks! Reproduced the problem on 10.2.15

MariaDB [test]> SELECT * from t2 order by rs desc limit 3;
 
+-------------+
 
| rs          |
 
+-------------+
 
| rs500000000 |
 
| rs499999999 |
 
| rs499999998 |
 
+-------------+
 
3 rows in set (0.00 sec)
 
 
 
MariaDB [test]> SELECT rs FROM t2 WHERE rs= "rs500000000";
 
Empty set (0.00 sec)
 
 
 
MariaDB [test]> explain SELECT rs FROM t2 WHERE rs= "rs500000000";
 
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
 
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> check table t2;
 
+---------+-------+----------+------------------------------------------+
 
| Table   | Op    | Msg_type | Msg_text                                 |
 
+---------+-------+----------+------------------------------------------+
 
| test.t2 | check | error    | Key in wrong position at page 1722248192 |
 
| test.t2 | check | error    | Corrupt                                  |
 
+---------+-------+----------+------------------------------------------+
 
2 rows in set (12.59 sec)

Comment by rawi [ 2018-10-01 ]

The same happens with version 10.3.9.

And the same happens adding the primary key later with

ALTER TABLE test ADD CONSTRAINT test_pk PRIMARY KEY (rs);

Any chance this will be some time more closely examined?

Sorry to annoy you with it

Thanks

Comment by Alice Sherepa [ 2018-10-09 ]

rawi , sorry for the delay. Reproduced on 10.3.10 as well

 
 
use test;
 
CREATE  TABLE t1 (rs VARCHAR(20) PRIMARY KEY) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
 
 
shutdown the server
 
 
 
/10.3.10/bin/myisamchk --keys-used=0 -rq /10.3.10/dt/test/t1.MYI
 
 
 
-------
 
use test;
 
FLUSH TABLES ; FLUSH TABLE_STATISTICS ; FLUSH INDEX_STATISTICS ;
 
INSERT INTO t1 SELECT CONCAT("rs",seq) from seq_1_to_500000000;
 
FLUSH TABLES ; FLUSH TABLE_STATISTICS ; FLUSH INDEX_STATISTICS ;
 
--------
 
 
 
/10.3.10/bin/myisamchk --analyze --update-state --sort_buffer_size=56G  --recover --quick --quick --tmpdir=~/tmp /10.3.10/dt/test/t1.MYI
 
 
 
SELECT * from t2 order by rs desc limit 3;	
SELECT rs FROM t2 WHERE rs= "rs500000000";
 
explain SELECT rs FROM t2 WHERE rs= "rs500000000";
 



MariaDB [test]> SELECT * from t1 order by rs desc limit 3;
 
+-------------+
 
| rs          |
 
+-------------+
 
| rs500000000 |
 
| rs499999999 |
 
| rs499999998 |
 
+-------------+
 
3 rows in set (0.000 sec)
 
 
 
MariaDB [test]> SELECT rs FROM t1 WHERE rs= "rs500000000";
 
Empty set (0.000 sec)
 
 
 
MariaDB [test]> explain SELECT rs FROM t1 WHERE rs= "rs500000000";
 
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
 
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> select version();
 
+-----------------+
 
| version()       |
 
+-----------------+
 
| 10.3.10-MariaDB |
 
+-----------------+
 
1 row in set (0.000 sec)

Generated at Thu Feb 08 08:29:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.