Estou rodando MySQL 8.0.30em Rocky Linux 9, cpu é de 12 threads AMD Ryzen, 128 GB de RAM, um SSD NVME decente. E ... eu luto com bancos de dados relativamente grandes, atualmente em torno de 200 GB no total, cerca de 20 tabelas, 2-3 tabelas com mais de 50 GB cada.
Eu queria optimize2 tabelas principalmente para compará-las com diferentes tipos de dados de coluna, para ver qual delas ocuparia mais espaço, qual executaria consultas de pesquisa mais rapidamente etc. Caso contrário, presumo que seja uma operação cara que pode não ser necessária.
Agora parei todos os processos e consultas e executei OPTIMIZE tableem 2 tabelas menores que são de 1,8 GB cada, 1 milhão de registros em cada tabela.
Demorou 50 minutos para ser concluído e não entendo o porquê.
time 2949
state: copy to tmp table
info: OPTIMIZE TABLE `url_meta_TEST_1`, `url_meta_TEST_2`
Enquanto esta consulta estava em execução, executei iotopo comando e mostrou que estava constantemente gravando no SSD com cerca de 300 MB/s
O servidor tinha 52GB de memória "disponível" de 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
Talvez relevante ... a pasta /var/lib/mysql/db_nametem 354 GB no total (com fts_*_index_x.ibdarquivos enormes), mesmo que o PhpMyAdmin mostre o total de todas as tabelas como 182 GB, para o tamanho dos arquivos db, talvez seja necessário abrir outra pergunta.
- Por que é preciso tanto para otimizar?
- Algo parece errado com os índices, tipos de coluna, etc?
- Esse tempo de 50 minutos pode ser normal caso os dados estejam fragmentados?
EDIT: executei exatamente a mesma consulta novamente, depois que as 2 tabelas já estavam otimizadas, leva o mesmo tempo e, novamente, grava constantemente no disco 350-450 MB/s.
Editar: incluindo mais data:
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_general_ci DEFAULT NULL,
`url_description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
`url_keywords` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`url_paragraphs` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_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_general_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_general_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_general_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: adicionando uma consulta de amostra que seria boa para executar mais rapidamente:
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;
O tempo limite no PhpMyAdmin é de 15 a 20 segundos para ser executado. O url_hashé indexado, se eu apenas executar a primeira parte (antes da partida contra), ele será executado instantaneamente. Eu esperava que ele executasse a segunda consulta apenas dentro das linhas correspondentes na primeira parte. É porque eu não defini url_hashcomo PRMARYíndice?!
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)))"
}
}
}
innodb_buffer_pool_size = 64G-- Mude para 100G.Para
OPTIMIZEa tabela de 50 GB, a tabela será totalmente copiada. Isso pode exigir a leitura de 50 GB no buffer_pool e a gravação de mais de 50 GB no disco. Tanto a leitura quanto a gravação podem ser principalmente "transmitidas". Mas depois há os índices. Por favor, forneçaSHOW CREATE TABLEpara que possamos discutir outras coisas. Se houver muitos índices grandes, podem ser 10s de GB a mais para criar no buffer_pool e/ou enviar pelo disco.O tempo gasto para otimizar não depende muito da fragmentação; mas sim em outros fatores.
Como o InnoDB armazena os dados em
PRIMARY KEYordem, as linhas não são dispersas, mas podem ter lacunas -- ou seja, blocos de 16KB que não estão cheios.Vamos também ver as consultas que você espera otimizar; podemos ser capazes de fazer mais sugestões.
Em geral, desaconselho o uso
OPTIMIZE TABLEem tabelas InnoDB. Frequentemente não há redução de tamanho, nem melhora no desempenho das consultas subsequentes.Verdade, se você precisar ler a tabela inteira para fazer a pesquisa, o tamanho da tabela é importante. Espero que você tenha investigado
INDEXes,FULLTEXTeSPATIAL.Vamos ver
Ao contrário do que vejo em uma de suas capturas de tela,...
SMALLINTo padrão éSIGNED, com um máximo de 32767.SMALLINT UNSIGNEDmas a mas de 65525.Parece que 3 dos índices são inúteis (Cardinalidade = 1).
Como o 'hash' é calculado? Provavelmente é muito aleatório, o que significa que a construção desse índice envolverá muitos saltos.
"grava no disco 350-450 MB/s" --> é a velocidade com que o SSD é executado. A única maneira de acelerar as gravações é evitá -las. (Evite
OPTIMIZEe coma melhorINDEXes.)sort_buffer_size Parece provável que mude. Sugira 1G (em vez de 256M). Espero
Sort_merge_passesdiminuir significativamente. Essa métrica às vezes é um indicador de muitas E/S. (A conexão é complicada.) É um "contador", então verifique o valor antes e depois de uma otimização.Forçando ordem de filtragem
Como o Optimizer assume cegamente que
MATCHé mais rápido e fácil, ele faz isso primeiro. No entanto, seu caso prefere queINseja feito primeiro.Se você sabe que o
INdeve ser feito primeiro, então esta pode ser a única maneira de acelerá-lo. Atualmente:Proposta:
Isso buscará muito rapidamente as duas linhas e, lentamente, fará os LIKEs. (Como apenas 2 linhas estão envolvidas, a velocidade dos LIKEs não é um problema.)
Advertência: Isso não faz coisas que MATCH faz, como respeitar os limites das palavras.