Optimizing 2x 1.8GB tables takes 50 minutes

Question

I am running MySQL 8.0.30 on Rocky Linux 9, cpu is 12 threads AMD Ryzen, 128 GB ram, a decent NVME SSD. And ... I struggle with relatively large databases, currently around 200GB in total, around 20 tables, 2-3 tables are over 50GB each.

I wanted to optimize 2 tables mostly to then compare them with different column data types, to see which one would take more space, which one would run search queries faster, etc. Otherwise I assume this is a costly operation that might not be needed.

Now I stopped all processes and queries and ran OPTIMIZE table on 2 smaller tables that are 1.8 GB each, 1 million records in each table.

It took 50 minutes to complete and I don't udnerstand why.

time 2949
state: copy to tmp table
info: OPTIMIZE TABLE `url_meta_TEST_1`, `url_meta_TEST_2`

While this query was running, I ran iotop command and it shown that it was constantly writing to SSD with around 300MB/s

The server had 52GB memory "available" from 128GB:

free -h
               total        used        free      shared  buff/cache   available
Mem:           124Gi        72Gi       3.9Gi        84Mi        49Gi        52Gi

/etc/my.cnf

skip-log-bin
innodb_buffer_pool_size = 64G

Maybe relevant ... the folder /var/lib/mysql/db_name is 354GB in total (with huge fts_*_index_x.ibd files), even thou PhpMyAdmin shows all table's total as 182GB, for the size of the db files there I might need to open another question.

• Why does it take so much to optimize ?
• Does anything look wrong with the indexes, column types, etc ?
• Can this 50 minutes time be normal in case data was fragmented ?

EDIT: I ran the exact same query again, after the 2 tables were already optimized, it takes same amount of time, and again it constantly writes to disk 350-450MB/s.

Edit: including more date:

SHOW CREATE TABLE url_meta_TEST_1;

url_meta_TEST_1 | CREATE TABLE `url_meta_TEST_1` (
  `url_hash` char(16) NOT NULL,
  `url_sharding` char(2) DEFAULT NULL,
  `url` varchar(512) NOT NULL,
  `url_title` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `url_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `url_keywords` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `url_paragraphs` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `url_total_links_in` smallint NOT NULL DEFAULT '0' COMMENT 'max SMALLINT is 65535',
  `url_meta_date` int NOT NULL DEFAULT '0',
  `url_misc` tinyint DEFAULT NULL,
  UNIQUE KEY `url_hash` (`url_hash`),
  KEY `url_total_links_in` (`url_total_links_in`) USING BTREE,
  KEY `url_sharding` (`url_sharding`),
  KEY `url_misc` (`url_misc`),
  FULLTEXT KEY `url_meta_index` (`url_title`,`url_description`,`url_keywords`,`url_paragraphs`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

mysql> SHOW VARIABLES LIKE '%size';
+----------------------------------------------------------+----------------------+
| Variable_name                                            | Value                |
+----------------------------------------------------------+----------------------+
| binlog_cache_size                                        | 32768                |
| binlog_row_event_max_size                                | 8192                 |
| binlog_stmt_cache_size                                   | 32768                |
| binlog_transaction_dependency_history_size               | 25000                |
| bulk_insert_buffer_size                                  | 8388608              |
| connection_memory_chunk_size                             | 8912                 |
| delayed_queue_size                                       | 1000                 |
| histogram_generation_max_mem_size                        | 20000000             |
| host_cache_size                                          | 279                  |
| innodb_buffer_pool_chunk_size                            | 134217728            |
| innodb_buffer_pool_size                                  | 68719476736          |
| innodb_change_buffer_max_size                            | 25                   |
| innodb_ddl_buffer_size                                   | 1048576              |
| innodb_doublewrite_batch_size                            | 0                    |
| innodb_ft_cache_size                                     | 8000000              |
| innodb_ft_max_token_size                                 | 84                   |
| innodb_ft_min_token_size                                 | 3                    |
| innodb_ft_total_cache_size                               | 640000000            |
| innodb_log_buffer_size                                   | 16777216             |
| innodb_log_file_size                                     | 50331648             |
| innodb_log_write_ahead_size                              | 8192                 |
| innodb_max_undo_log_size                                 | 1073741824           |
| innodb_online_alter_log_max_size                         | 134217728            |
| innodb_page_size                                         | 16384                |
| innodb_purge_batch_size                                  | 300                  |
| innodb_sort_buffer_size                                  | 1048576              |
| innodb_sync_array_size                                   | 1                    |
| join_buffer_size                                         | 262144               |
| key_buffer_size                                          | 8388608              |
| key_cache_block_size                                     | 1024                 |
| large_page_size                                          | 0                    |
| max_binlog_cache_size                                    | 18446744073709547520 |
| max_binlog_size                                          | 1073741824           |
| max_binlog_stmt_cache_size                               | 18446744073709547520 |
| max_heap_table_size                                      | 16777216             |
| max_join_size                                            | 18446744073709551615 |
| max_relay_log_size                                       | 0                    |
| myisam_data_pointer_size                                 | 6                    |
| myisam_max_sort_file_size                                | 9223372036853727232  |
| myisam_mmap_size                                         | 18446744073709551615 |
| myisam_sort_buffer_size                                  | 8388608              |
| ngram_token_size                                         | 2                    |
| optimizer_trace_max_mem_size                             | 1048576              |
| parser_max_mem_size                                      | 18446744073709551615 |
| performance_schema_accounts_size                         | -1                   |
| performance_schema_digests_size                          | 10000                |
| performance_schema_error_size                            | 5153                 |
| performance_schema_events_stages_history_long_size       | 10000                |
| performance_schema_events_stages_history_size            | 10                   |
| performance_schema_events_statements_history_long_size   | 10000                |
| performance_schema_events_statements_history_size        | 10                   |
| performance_schema_events_transactions_history_long_size | 10000                |
| performance_schema_events_transactions_history_size      | 10                   |
| performance_schema_events_waits_history_long_size        | 10000                |
| performance_schema_events_waits_history_size             | 10                   |
| performance_schema_hosts_size                            | -1                   |
| performance_schema_session_connect_attrs_size            | 512                  |
| performance_schema_setup_actors_size                     | -1                   |
| performance_schema_setup_objects_size                    | -1                   |
| performance_schema_users_size                            | -1                   |
| preload_buffer_size                                      | 32768                |
| profiling_history_size                                   | 15                   |
| query_alloc_block_size                                   | 8192                 |
| query_prealloc_size                                      | 8192                 |
| range_alloc_block_size                                   | 4096                 |
| range_optimizer_max_mem_size                             | 8388608              |
| read_buffer_size                                         | 131072               |
| read_rnd_buffer_size                                     | 262144               |
| rpl_read_size                                            | 8192                 |
| select_into_buffer_size                                  | 131072               |
| sort_buffer_size                                         | 262144               |
| thread_cache_size                                        | 9                    |
| tmp_table_size                                           | 16777216             |
| transaction_alloc_block_size                             | 8192                 |
| transaction_prealloc_size                                | 4096                 |
+----------------------------------------------------------+----------------------+
75 rows in set (0.00 sec)


mysql> SHOW GLOBAL STATUS LIKE '%sort%';
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| Sort_merge_passes | 6964    |
| Sort_range        | 0       |
| Sort_rows         | 5129121 |
| Sort_scan         | 12573   |
+-------------------+---------+
4 rows in set (0.00 sec)


mysql> SHOW GLOBAL STATUS LIKE 'Uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 78313 |
+---------------+-------+
1 row in set (0.00 sec)



mysql> SHOW TABLE STATUS LIKE 'url_meta_TEST%';
+-----------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name            | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+-----------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| url_meta_TEST_1 | InnoDB |      10 | Dynamic    | 818729 |           2175 |  1781530624 |               0 |    108331008 |   4194304 |           NULL | 2022-12-22 01:03:20 | NULL        | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
| url_meta_TEST_2 | InnoDB |      10 | Dynamic    | 872950 |           2040 |  1781530624 |               0 |    108331008 |   4194304 |           NULL | 2022-12-22 01:03:20 | NULL        | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
+-----------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
2 rows in set (0.00 sec)


mysql> SHOW VARIABLES LIKE '%ft%';
+---------------------------------+----------------+
| Variable_name                   | Value          |
+---------------------------------+----------------+
| ft_boolean_syntax               | + -><()~*:""&| |
| ft_max_word_len                 | 84             |
| ft_min_word_len                 | 4              |
| ft_query_expansion_limit        | 20             |
| ft_stopword_file                | (built-in)     |
| 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   |                |
+---------------------------------+----------------+
17 rows in set (0.00 sec)

EDIT: adding a sample query that would be nice to run faster:

SELECT  *
    FROM  url_meta_cache
    WHERE  url_hash IN ('1a0b004256a5c7fd','1fcc4489f4195c95')
      AND  match(url_title, url_description, url_keywords, url_paragraphs)
           against('contact' IN BOOLEAN MODE)
    LIMIT  2000;

It times out in PhpMyAdmin, over 15-20 seconds to run. The url_hash is indexed, if I just run first part (before match against) it runs instantly. I was expecting it to run second query only inside rows that matched in first part. Is it because I didn't set url_hash as PRMARY index ?!

EXPLAIN SELECT * FROM url_meta_cache WHERE url_hash IN ('1a0b004256a5c7fd','1fcc4489f4195c95') AND match(url_title, url_description, url_keywords, url_paragraphs) against('contact' IN BOOLEAN MODE);
+----+-------------+----------------+------------+----------+-------------------------+----------------+---------+-------+------+----------+-----------------------------------+
| id | select_type | table          | partitions | type     | possible_keys           | key            | key_len | ref   | rows | filtered | Extra                             |
+----+-------------+----------------+------------+----------+-------------------------+----------------+---------+-------+------+----------+-----------------------------------+
|  1 | SIMPLE      | url_meta_cache | NULL       | fulltext | url_hash,url_meta_index | url_meta_index | 0       | const |    1 |   100.00 | Using where; Ft_hints: no_ranking |
+----+-------------+----------------+------------+----------+-------------------------+----------------+---------+-------+------+----------+-----------------------------------+
1 row in set, 1 warning (0.50 sec)


{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.49"
    },
    "table": {
      "table_name": "url_meta_cache",
      "access_type": "fulltext",
      "possible_keys": [
        "url_hash",
        "url_meta_index"
      ],
      "key": "url_meta_index",
      "used_key_parts": [
        "url_title"
      ],
      "key_length": "0",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "ft_hints": "no_ranking",
      "cost_info": {
        "read_cost": "0.39",
        "eval_cost": "0.10",
        "prefix_cost": "0.49",
        "data_read_per_join": "3K"
      },
      "used_columns": [
        "url_hash",
        "url_sharding",
        "url",
        "url_title",
        "url_description",
        "url_keywords",
        "url_paragraphs",
        "url_total_links_in",
        "url_meta_date",
        "url_misc"
      ],
      "attached_condition": "((`crawlson`.`url_meta_cache`.`url_hash` in ('1a0b004256a5c7fd','1fcc4489f4195c95')) and (match `crawlson`.`url_meta_cache`.`url_title`,`crawlson`.`url_meta_cache`.`url_description`,`crawlson`.`url_meta_cache`.`url_keywords`,`crawlson`.`url_meta_cache`.`url_paragraphs` against ('contact' in boolean mode)))"
    }
  }
} 

Answer 1

innodb_buffer_pool_size = 64G -- Change to 100G.

To OPTIMIZE 50GB table, the table will be completely copied over. This may require reading 50GB into the buffer_pool and writing upwards of 50GB to disk. Both the read and write may be mostly "streamed". But then there are the indexes. Please provide SHOW CREATE TABLE so that we can discuss other things. If there are lots of big indexes, that could be 10s of GB more to build in the buffer_pool and/or send through the disk.

The time taken for Optimize does not depend much on fragmentation; but rather on other factors.

Since InnoDB stores the data in PRIMARY KEY order, the rows are not scattered, but they may have gaps -- that is, 16KB blocks that are less than full.

Let's also see the queries you are hoping to optimize; we may be able to make more suggestions.

In general, I advise against ever using OPTIMIZE TABLE on InnoDB tables. Often there is no shrinkage in size, nor any improvement in the performance of subsequent queries.

True, if you need to read the entire table to do the search, then table size is important. I would hope that you have investigated INDEXes, FULLTEXT, and SPATIAL.

Let's see

SHOW VARIABLES LIKE '%size';
SHOW GLOBAL STATUS LIKE '%sort%';
SHOW GLOBAL STATUS LIKE 'Uptime';
SHOW TABLE STATUS LIKE 'url_meta_TEST%';
SHOW VARIABLES LIKE '%ft%';

Contrary to what I see on one of your screenshots,...
SMALLINT defaults to SIGNED, with a max of 32767.
SMALLINT UNSIGNED mas a mas of 65525.

It looks like 3 of the indexes are useless (Cardinality = 1).

How is the 'hash' calculated? It is probably very random, meaning that building that index will involve jumping around a lot.

"writes to disk 350-450MB/s" --> that's how fast the SSD runs. The only way to speed up writes is to avoid writes. (Avoid OPTIMIZE and have better INDEXes.)

sort_buffer_size Seems like a likely thing to change. Suggest 1G (instead of 256M). I expect Sort_merge_passes to decrease significantly. That metric is sometimes an indicator of lots of I/O. (The connection is complicated.) It is a "counter", so check the value before and after an Optimize.

Forcing filtering order

Since the Optimizer blindly assumes that MATCH is both faster and easier, it does that first. However, your case would prefer the IN to be done first.

If you know that the IN should be done first, then this may be the only way to speed it up. Currently:

WHERE  url_hash IN ('1a0b004256a5c7fd','1fcc4489f4195c95')
  AND  match(url_title, url_description, url_keywords, url_paragraphs)
       against('contact' IN BOOLEAN MODE)

Proposed:

WHERE  url_hash IN ('1a0b004256a5c7fd','1fcc4489f4195c95')
  AND  ( url_title LIKE '%contact%' )
         OR url_title       LIKE '%contact%'
         OR url_description LIKE '%contact%'
         OR url_keywords    LIKE '%contact%'
         OR url_paragraphs  LIKE '%contact%' )

That will very rapidly fetch the two rows, then slowly do the LIKEs. (Since only 2 rows are involved, the speed of the LIKEs is not an issue.)

Caveat: This does not do things that MATCH does such as respecting word boundaries.