adrianTNT's questions

Como posso acelerar esta consulta abaixo? Tenho uma tabela ~grande com url_meta90 milhões de registros, 38 GB de dados, 6 GB de índice

O ID exclusivo principal de cada linha é url_hash(um md5 truncado para 16 caracteres)

Então criei um grande índice de texto completo chamado url_meta_index, contendo estas colunas:

• título_url
• url_descrição
• palavras-chave url
• url_paragrafos

A tabela também contém uma coluna indexada chamadaurl_total_links_in

1. Se eu selecionar apenas as URLs com muitos links, o processo será muito rápido e, o mais importante, há apenas 240 linhas com mais de 1.000 links:

SELECT * FROM url_meta WHERE url_total_links_in > 1000
240 rows in set (0.01 sec)

2. Mas se eu pesquisar no índice de texto grande DEPOIS da mesma consulta (selecionando as mesmas linhas acima) , isso demora uma eternidade:

    mysql> 
    SELECT  *
    FROM  url_meta
    WHERE  url_total_links_in > 1000
      AND  match(url_title, url_description, url_keywords, url_paragraphs)
        against('computer store' IN BOOLEAN MODE)
    LIMIT  500;
   
    10 rows in set (4 min 4.94 sec)
   

Eu esperava match againstque a segunda parte olhasse apenas os 240 registros que encontrou por url_total_links_in > 1000, não é assim que funciona ? Pelo longo tempo de consulta, acho que ele olha todos os 90 milhões de registros.

Por PHP, posso selecionar as 240 linhas na primeira consulta e, em seguida, fazer um loop sobre essas poucas linhas para "comparar" com o texto mostrado na segunda consulta. Mas como posso fazer isso pelo MySQL?

Ajudaria se eu ... incluísse o id de linha exclusivo ( url_hash) no índice de texto multicoluna grande url_meta_index? Ou ajuda se eu adicionar a coluna url_total_links_inao mesmo índice de texto multicoluna url_meta_index? Algum outro operador MySQL que ajudaria neste caso? Ou alguma variável MySQL que seria relevante neste caso?

Edição posterior, incluindo mais detalhes:

DESCRIBE url_meta;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| url_hash           | char(16)     | NO   | PRI | NULL    |       |
| url_sharding       | char(2)      | YES  | MUL | NULL    |       |
| url                | varchar(512) | NO   |     | NULL    |       |
| url_title          | varchar(128) | YES  | MUL | NULL    |       |
| url_description    | text         | YES  |     | NULL    |       |
| url_keywords       | varchar(128) | YES  |     | NULL    |       |
| url_paragraphs     | mediumtext   | YES  |     | NULL    |       |
| url_total_links_in | smallint     | NO   | MUL | 0       |       |
| url_meta_date      | int          | NO   |     | 0       |       |
| url_misc           | tinyint      | YES  |     | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+

EXPLAIN ANALYZE SELECT * FROM url_meta WHERE url_total_links_in > 1000 AND match(url_title, url_description, url_keywords, url_paragraphs) against('computer store' IN BOOLEAN MODE) LIMIT 500;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 500 row(s)  (cost=0.40 rows=0.05) (actual time=2582.991..21198.806 rows=10 loops=1)
    -> Filter: ((url_meta.url_total_links_in > 1000) and (match url_meta.url_title,url_meta.url_description,url_meta.url_keywords,url_meta.url_paragraphs against ('computer store' in boolean mode)))  (cost=0.40 rows=0.05) (actual time=2582.991..21198.800 rows=10 loops=1)
        -> Full-text index search on url_meta using url_meta_index (url_title='computer store')  (cost=0.40 rows=1) (actual time=312.284..21096.609 rows=1247864 loops=1)
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (23.36 sec)

SHOW INDEX FROM url_meta;
+----------+------------+--------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name           | Seq_in_index | Column_name        | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+--------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| url_meta |          0 | PRIMARY            |            1 | url_hash           | A         |    81358144 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| url_meta |          1 | url_total_links_in |            1 | url_total_links_in | A         |        2790 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| url_meta |          1 | url_sharding       |            1 | url_sharding       | A         |        1590 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| url_meta |          1 | url_meta_index     |            1 | url_title          | NULL      |    81358147 |     NULL |   NULL | YES  | FULLTEXT   |         |               | YES     | NULL       |
| url_meta |          1 | url_meta_index     |            2 | url_description    | NULL      |    81358147 |     NULL |   NULL | YES  | FULLTEXT   |         |               | YES     | NULL       |
| url_meta |          1 | url_meta_index     |            3 | url_keywords       | NULL      |    81358147 |     NULL |   NULL | YES  | FULLTEXT   |         |               | YES     | NULL       |
| url_meta |          1 | url_meta_index     |            4 | url_paragraphs     | NULL      |    81358147 |     NULL |   NULL | YES  | FULLTEXT   |         |               | YES     | NULL       |
+----------+------------+--------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
7 rows in set (0.00 sec)

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)))"
    }
  }
} 

Eu estou MySQL 8.0.30correndo Rocky Linux 9.

Ao importar alguns arquivos sql de ~ 100 GB, notei que /var/lib/mysqlestá cheio de binlog.000123arquivos, com mais de 100 GB.

Presumo que sejam criados porque o servidor está de alguma forma configurado para fazer parte da replicação, mas não me lembro de ter ativado nada relacionado a isso.

Mas em PHP MyAdmin 5.2.0-1.el9replicação eu vejo:

Primary replication
This server is configured as primary in a replication process.

e

Show connected replicas
Server ID   Host
[empty]

• Portanto, este servidor está configurado para fazer parte de uma replicação?
• Isso é ativado por padrão no MySQL 8.0.30?
• Como faço para interromper com segurança a replicação neste caso?

Pode ser relevante: eu tenho esta configuração em 4 servidores na mesma rede local e eles se conectam um ao outro por código/consultas PHP mysqli, mas não usei/configure nenhuma função de replicação no mysql, apenas usando normal no código php ( mysqli_select_db(s1/s2/etc)entre os 4 servidores) como conectar a um banco de dados externo. E ao configurá-los, espelhei os HDDs de um servidor para outro, isso é um problema porque a configuração do MySQL para todos os servidores é exatamente a mesma server ide server uuid?

Em MySQL 5.7.34eu tenho essa estrutura simples de banco de dados. Colunas importantes são domain name, domain total links in, mas as entradas são duplicadas (uma vez para cada link encontrado de várias fontes), preciso selecionar o domínio apenas uma vez ( link_to_domain) e classificá-lo por link_to_domain_total_links_inDESC; Não tenho certeza de como renderizá-lo aqui, aqui está a consulta:

CREATE TABLE IF NOT EXISTS `domain_to_domain_links` (
  `id` int(11) NOT NULL,
  `link_to_domain_hash` varchar(16) NOT NULL,
  `link_to_domain_total_links_in` int(11) NOT NULL DEFAULT '0',
  `link_to_domain` varchar(128) NOT NULL,
  `link_from_domain` varchar(128) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=270245 DEFAULT CHARSET=utf8;


INSERT INTO `domain_to_domain_links` (`id`, `link_to_domain_hash`, `link_to_domain_total_links_in`, `link_to_domain`, `link_from_domain`) VALUES
(1, 'c9b13094745bae79', 3, 'example.com', 'from-other-site1.com'),
(1, 'c9b13094745bae79', 3, 'example.com', 'from-other-site2.com'),
(1, 'c9b13094745bae79', 3, 'example.com', 'from-other-site3.com'),
(2, 'c43f16c897f72994', 2, 'foo.com', 'from-other-site4.com'),
(3, 'c43f16c897f72994', 2, 'foo.com', 'from-other-site5.com');

Isso retorna exatamente o que eu preciso, mas pelo que eu entendi ele realmente conta as entradas (como links) na hora da consulta, eu preciso pegar do link_to_domain_total_links_inpara que ele rode mais rápido:

SELECT link_to_domain, COUNT(*) AS my_links_counter 
FROM domain_to_domain_links 
GROUP BY link_to_domain 
ORDER BY COUNT(*) DESC;

Também link_to_domain_hashé indexado, se puder tirar proveito disso ao selecionar deve ser mais rápido que link_to_domain, mas isso não é crítico.