-- Env and Build cat /etc/*release Oracle Linux Server release 7.8 NAME="Oracle Linux Server" VERSION="7.8" ID="ol" ID_LIKE="fedora" VARIANT="Server" VARIANT_ID="server" VERSION_ID="7.8" PRETTY_NAME="Oracle Linux Server 7.8" ANSI_COLOR="0;31" CPE_NAME="cpe:/o:oracle:linux:7:8:server" HOME_URL="https://linux.oracle.com/" BUG_REPORT_URL="https://bugzilla.oracle.com/" ORACLE_BUGZILLA_PRODUCT="Oracle Linux 7" ORACLE_BUGZILLA_PRODUCT_VERSION=7.8 ORACLE_SUPPORT_PRODUCT="Oracle Linux" ORACLE_SUPPORT_PRODUCT_VERSION=7.8 Red Hat Enterprise Linux Server release 7.8 (Maipo) Oracle Linux Server release 7.8 cat docs/INFO_SRC commit: f64af5bfff4c3a65eb44d9e513ca1c882850b030 date: 2021-07-01 07:17:38 +0200 build-date: 2021-07-01 07:54:49 +0000 short: f64af5bfff4 branch: mysql-8.0.26-release MySQL source 8.0.26 (1) Dummy data set for the table cat bug104421.pl #!/bin/perl for(my $i=1; $i <= 350000000; $i++) { my $txt = 'x' x 10; my $customer_id = 1 + int(rand(4294967295-1)); my $object_id = 1 + int(rand(4294967295-1)); my $type_id = 1 + int(rand(255 - 1)); print "$i,$customer_id,$object_id,$type_id,$txt\n"; } perl bug104421.pl > p.csv ls -lh p.csv -rw-r--r-- 1 umshastr common 15G Jul 28 09:31 p.csv (2) Speeding up for loading etc changed these from default - --innodb-flush-log-at-trx-commit=0 --innodb-buffer-pool-size=64G --innodb-log-file-size=2G --innodb-doublewrite=0 rm -rf 104421/ bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/104421 --log-error-verbosity=3 bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/104421 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --log-error=$PWD/104421/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3 --secure-file-priv="" --local-infile=1 --innodb-flush-log-at-trx-commit=0 --innodb-buffer-pool-size=64G --innodb-log-file-size=2G --innodb-doublewrite=0 2>&1 & bin/mysql -uroot -S /tmp/mysql_ushastry.sock --local-infile Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.0.26 MySQL Community Server - GPL Copyright (c) 2000, 2021, 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 database test; Query OK, 1 row affected (0.01 sec) mysql> use test Database changed mysql> CREATE TABLE `p` ( -> `id` bigint unsigned NOT NULL, -> `customer_id` int unsigned NOT NULL, -> `object_id` bigint unsigned NOT NULL, -> `type_id` tinyint unsigned NOT NULL, -> `details` text -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; Query OK, 0 rows affected (0.02 sec) (3) -- Load data created in (1) bin/mysqlsh MySQL Shell 8.0.26 Copyright (c) 2016, 2021, 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 '\?' for help; '\quit' to exit. MySQL JS > \c root@localhost:3333 Creating a session to 'root@localhost:3333' Please provide the password for 'root@localhost:3333': Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 9 Server version: 8.0.26 MySQL Community Server - GPL No default schema selected; type \use to set one. MySQL localhost:3333 ssl JS > MySQL localhost:3333 ssl JS > util.importTable("/export/home/tmp/ushastry/mysql-8.0.26/p.csv", {schema: "test",dialect: "csv-unix", skipRows: 0, showProgress: true, fieldsOptionallyEnclosed: true, fieldsTerminatedBy: ",", linesTerminatedBy: "\n",threads: 12}) Importing from file '/export/home/tmp/ushastry/mysql-8.0.26/p.csv' to table `test`.`p` in MySQL Server at localhost:3333 using 12 threads [Worker000] p.csv: Records: 1109697 Deleted: 0 Skipped: 0 Warnings: 0 . . [Worker004] p.csv: Records: 1085584 Deleted: 0 Skipped: 0 Warnings: 0 [Worker008] p.csv: Records: 1085594 Deleted: 0 Skipped: 0 Warnings: 0 100% (16.01 GB / 16.01 GB), 3.31 MB/s File '/export/home/tmp/ushastry/mysql-8.0.26/p.csv' (16.01 GB) was imported in 38 min 49.7265 sec at 6.87 MB/s Total rows affected in test.p: Records: 350000000 Deleted: 0 Skipped: 0 Warnings: 0 -- mysql> alter table p add index(id), modify column id bigint unsigned NOT NULL AUTO_INCREMENT, add index(customer_id, id), add primary key(`customer_id`,`object_id`,`id`); Query OK, 350000000 rows affected (1 hour 33 min 47.31 sec) Records: 350000000 Duplicates: 0 Warnings: 0 mysql> show create table p\G *************************** 1. row *************************** Table: p Create Table: CREATE TABLE `p` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `customer_id` int unsigned NOT NULL, `object_id` bigint unsigned NOT NULL, `type_id` tinyint unsigned NOT NULL, `details` text, PRIMARY KEY (`customer_id`,`object_id`,`id`), KEY `id` (`id`), KEY `customer_id` (`customer_id`,`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.01 sec) (4) - ls -lhtr 104421/test/ total 54G -rw-r----- 1 umshastr common 54G Jul 28 11:59 p.ibd -- restarted server, removed --innodb-flush-log-at-trx-commit=0 --innodb-buffer-pool-size=64G --innodb-log-file-size=2G --innodb-doublewrite=0 (rm ib_logfile*, ib_buffer_pool) bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/104421 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --log-error=$PWD/104421/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3 --secure-file-priv="" --local-infile=1 --innodb-buffer-pool-load-now=OFF 2>&1 & -- values masqueraded mysql> set @cid = xxxxxxx, @id=xxxxxxxxx; Query OK, 0 rows affected (0.00 sec) mysql> explain SELECT id, object_id, details FROM p WHERE customer_id = @cid ORDER BY id LIMIT 10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: p partitions: NULL type: ref possible_keys: PRIMARY,customer_id key: PRIMARY key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: Using filesort 1 row in set, 1 warning (0.00 sec) mysql> show status like "Handler_read%"; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 3 | | Handler_read_key | 15 | | Handler_read_last | 0 | | Handler_read_next | 4 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 6 | +-----------------------+-------+ 7 rows in set (0.00 sec) mysql> explain SELECT id, object_id, details FROM p use index (customer_id) WHERE customer_id = @cid ORDER BY id LIMIT 10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: p partitions: NULL type: ref possible_keys: customer_id key: customer_id key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) mysql>