Googlebot's questions

Eu arquivo milhões de arquivos XML (1-100 MB cada) em uma tabela com a estrutura de

CREATE TABLE Data
(
    ID int(11) unsigned NOT NULL,
    XML longtext COMPRESSED,
    PRIMARY KEY(ID)
) ENGINE=Aria DEFAULT CHARSET=utf8 COLLATE utf8_general_ci ROW_FORMAT=DYNAMIC;

INSERT INTO Data (ID,XML) VALUES ($id,LOAD_FILE('file.xml'));

O processo é lento, cerca de 2 a 5 inserções/segundo. O banco de dados inteiro seria muito grande para uma unidade SDD, e eu crio o banco de dados em um HDD separado, mas movo os arquivos em lotes para uma unidade SDD para tornar a leitura mais rápida. Observe que a velocidade do disco não é a etapa determinante da taxa, pois os dados XML são enormemente reduzidos pela compactação.

Tentei InnoDBobter inserção simultânea, mas o tamanho do InnoDB ibd é três vezes maior que o ARIA/MyISAM, e o InnoDB é muito mais lento no HDD.

Eu tentei ROCKSDB, mas não pode ser criado em um disco separado, pois existe um único diretório para todas as tabelas. Além disso, o gerenciamento de memória do ROCKSDB é péssimo para tais cenários (ou não consegui encontrar a configuração adequada).

Não testei ARCHIVEo desempenho do motor, pois ele precisa IDestar em ordem.

Minha solução atual é INSERT simultaneamente em uma tabela temporária do InnoDB no SSD e depois INSERT INTO SELECTda tabela InnoDB para a tabela ARIA no HDD. O problema é a integridade e o atraso no esvaziamento do InnoDB e no início do processo INSERT simultâneo.

Agradeço qualquer solução possível.

Para uma tabela InnoDB com 500 milhões de linhas (em uma unidade NVMe separada), SELECT COUNT(*)leva cerca de 3 minutos.

SHOW ENGINE INNODB STATUS\Gmostra ROW OPERATIONScerca de 2 milhões de leituras/s, o que é consistente com o tempo que a consulta leva.

Também mostra FILE I/Ocerca de 3.000 leituras/s. Isso é semelhante ao read from iostat, que também mostra a velocidade de leitura de cerca de 50 MB/s.

O NVMe tem muito mais capacidade para ler os dados do disco.

Eu me pergunto qual é o gargalo aqui? Ainda I/Oé ou é o processamento do MySQL?

EXEMPLO

Fiz um teste reprodutível básico.

CREATE TABLE test
(
id int(11) unsigned NOT NULL AUTO_INCREMENT,
Number int(11) unsigned NOT NULL,
PRIMARY KEY(id)
) ENGINE=InnoDB

INSERT INTO test (Number) SELECT * FROM seq_1_to_500000000;
Query OK, 500000000 rows affected (20 min 2.846 sec)
Records: 500000000  Duplicates: 0  Warnings: 0

SELECT COUNT(*) FROM test;
+-----------+
| COUNT(*)  |
+-----------+
| 500000000 |
+-----------+
1 row in set (1 min 20.234 sec)

Depois de reiniciarMySQL

innodb_buffer_pool_load_at_startup=OFF
innodb_buffer_pool_dump_at_shutdown=OFF
query_cache_type=0
query_cache_size=0

na ausência de qualquer outra atividade, consegui

SELECT COUNT(*) FROM test;
+-----------+
| COUNT(*)  |
+-----------+
| 500000000 |
+-----------+
1 row in set (1 min 13.245 sec)

A pergunta-chave: é o mais rápido que você pode executar essa consulta em um NVMe típico?

Configurações:

• Conjunto de buffers de 50 GB. O ibdarquivo tem 17,7 GB.
• CPU é 16/32 núcleos/threads.
• innodb_io_threadsnão tem efeito. Eu tentei 4 (padrão) e 64 (máximo).

e

SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool%';
+-----------------------------------------+-------------+
| Variable_name                           | Value       |
+-----------------------------------------+-------------+
| Innodb_buffer_pool_dump_status          |             |
| Innodb_buffer_pool_load_status          |             |
| Innodb_buffer_pool_resize_status        |             |
| Innodb_buffer_pool_load_incomplete      | OFF         |
| Innodb_buffer_pool_pages_data           | 874527      |
| Innodb_buffer_pool_bytes_data           | 14328250368 |
| Innodb_buffer_pool_pages_dirty          | 0           |
| Innodb_buffer_pool_bytes_dirty          | 0           |
| Innodb_buffer_pool_pages_flushed        | 0           |
| Innodb_buffer_pool_pages_free           | 2351473     |
| Innodb_buffer_pool_pages_made_not_young | 0           |
| Innodb_buffer_pool_pages_made_young     | 0           |
| Innodb_buffer_pool_pages_misc           | 0           |
| Innodb_buffer_pool_pages_old            | 322843      |
| Innodb_buffer_pool_pages_total          | 3226000     |
| Innodb_buffer_pool_pages_lru_flushed    | 0           |
| Innodb_buffer_pool_read_ahead_rnd       | 682843      |
| Innodb_buffer_pool_read_ahead           | 0           |
| Innodb_buffer_pool_read_ahead_evicted   | 0           |
| Innodb_buffer_pool_read_requests        | 56901439    |
| Innodb_buffer_pool_reads                | 874396      |
| Innodb_buffer_pool_wait_free            | 0           |
| Innodb_buffer_pool_write_requests       | 515         |
+-----------------------------------------+-------------+
23 rows in set (0.001 sec)

Informações adicionais sobre a mesa

SHOW TABLE STATUS LIKE '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 | Max_index_length | Temporary |
+------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| test | InnoDB |      10 | Dynamic    | 499216334 |             25 | 12859736064 |               0 |            0 |   7340032 |      500000001 | 2022-07-29 00:10:49 | 2022-07-29 00:32:52 | NULL       | utf8_general_ci |     NULL |                |         |                0 | N         |
+------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
1 row in set (0.001 sec)

EXPLAIN FORMAT=JSON SELECT COUNT(*) FROM test;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                  |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "test",
      "access_type": "index",
      "key": "PRIMARY",
      "key_length": "4",
      "used_key_parts": ["id"],
      "rows": 499216334,
      "filtered": 100,
      "using_index": true
    }
  }
} |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

SHOW VARIABLES LIKE 'innodb_io%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_io_capacity     | 32000 |
| innodb_io_capacity_max | 64000 |
+------------------------+-------+

Eu tenho uma tabela de jogadores da equipe em que PlayerIDestá faltando em algumas linhas.

CREATE TABLE TeamPlayers
(
ID int(11) unsigned NOT NULL AUTO_INCREMENT,
TeamID int(11) unsigned NOT NULL,
PlayerID int(11) unsigned,
Name varchar(255),
INDEX(PlayerID),
INDEX(Name),
PRIMARY KEY(ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

Eu tento combinar o PlayerIDpara cada nome. No entanto, como os nomes não são únicos (duas pessoas podem ter o mesmo nome), devo confiar em algumas aproximações.

A primeira tentativa é assumir que os nomes dos co-jogadores são únicos. Por exemplo,

ID    TeamID    PlayerID    Name
1     15        9           Tim Cook
2     15        NULL        John West
3     16        NULL        Tim Cook
4     16        5           John West
5     17        11          John West
6     18        NULL        John West

Assumindo que o Team Cook jogou com apenas uma pessoa chamada John West e vice-versa.

Saída esperada:

ID    TeamID    PlayerID    Name
1     15        9           Tim Cook
2     15        5           John West
3     16        9           Tim Cook
4     16        5           John West
5     17        11          John West
6     18        NULL        John West

eu comecei com

SELECT a.Name,MAX(a.PlayerID),MAX(b.PlayerID),
    GROUP_CONCAT(a.ID SEPARATOR ','),    GROUP_CONCAT(b.ID SEPARATOR ',') 
  FROM TeamPlayers a JOIN TeamPlayers b 
  ON a.TeamID=b.TeamID AND a.Name<>b.Name
  GROUP BY a.Name

mas qual é o caminho certo para UPDATEa mesa?

SQL Fiddle

Capturo Usuários que comentam os Artigos de outros Usuários, como

INSERT IGNORE INTO AuthorCommentators (AuthorID,CommentatorID,Freq) 
    SELECT AuthorID,CommentatorID,C FROM 
        (SELECT b.UserID AS AuthorID,c.UserID AS CommentatorID,COUNT(*) AS C 
            FROM ArticleComments a  
                JOIN ArticleMap b ON a.ArticleID=b.ArticleID 
                JOIN CommentMap c ON a.CommentID=c.CommentID 
                GROUP BY b.UserID,c.UserID
         ) c 
ON DUPLICATE KEY UPDATE Freq=c.C

mas como ArticleCommentsa tabela é muito grande, a consulta é lenta.

Existe alguma maneira de melhorar esta consulta?

As tabelas são simples,

CREATE TABLE AuthorCommentators
(
AuthorID int(11) unsigned NOT NULL,
CommentatorID int(11) unsigned NOT NULL,
Freq mediumint(7) unsigned,
INDEX(CommentatorID),
INDEX(Freq),
PRIMARY KEY(AuthorID,CommentatorID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

CREATE TABLE ArticleComments
(
ArticleID int(11) unsigned NOT NULL,
CommentID int(11) unsigned NOT NULL,
INDEX(CommentID),
PRIMARY KEY(ArticleID,CommentID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

CREATE TABLE ArticleMap
(
ArticleID int(11) unsigned NOT NULL,
UserID int(11) unsigned NOT NULL,
INDEX(UserID),
PRIMARY KEY(ArticleID,UserID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

CREATE TABLE CommentMap
(
CommentID int(11) unsigned NOT NULL,
UserID int(11) unsigned NOT NULL,
INDEX(UserID),
PRIMARY KEY(CommentID,UserID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

Como ambos Articlese Commentspodem ter vários autores, tenho que normalizar a arquitetura.

Eu fiz INSERT INTO SELECTde uma mesa enorme (1TB). Quando o processo termina, o MySQL/InnoDB ainda faz o pós-processamento ( criando atualização de índices secundários, etc.) por horas.

Como esses são processos internos, eles não aparecem em SHOW PROCESSLISTou SHOW ENGINE INNODB STATUS \G.

Só posso verificar o processo por atividades de E/S.

Existe alguma maneira de monitorar adequadamente os processos em andamento?

Eu tenho o MariaDB 10.5 na minha área de trabalho com vários discos (SSD e HDD) para projetos de gravação intensiva. A gravação em uma única tabela é rápida e a porcentagem de páginas sujas permanece próxima de zero com 1000-3000 writes/s.

No entanto, quando escrevo ativamente em várias tabelas ao mesmo tempo, a porcentagem de páginas sujas aumenta rapidamente. O problema é que a descarga para o disco cai para o nível de 100 writes/se permanece nesse nível.

Esse comportamento permanece até uma reinicialização.

Acho que o problema está de alguma forma relacionado (não exatamente) ao identificado por Percona há 10 anos.

Existe algum truque para manter a velocidade de descarga?

key_buffer_size     = 20M
max_allowed_packet  = 5G
thread_stack        = 256K
thread_cache_size       = 8
innodb_buffer_pool_size = 70G
innodb_log_buffer_size = 512M
innodb_log_file_size = 20G
innodb_thread_concurrency = 0
innodb_flush_log_at_trx_commit = 0
innodb_compression_level = 6
innodb_io_capacity=2000
innodb_io_capacity_max=30000
innodb_max_dirty_pages_pct=0
innodb_doublewrite = 0
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth=128
innodb_purge_threads=8
innodb_purge_batch_size=600
innodb_flush_neighbors=0
innodb_change_buffer_max_size=50
innodb_buffer_pool_load_at_startup=OFF
innodb_buffer_pool_dump_at_shutdown=OFF
innodb-ft-result-cache-limit=4G
innodb_fatal_semaphore_wait_threshold=7200
innodb_compression_default=ON
innodb_random_read_ahead=1

ATUALIZAÇÃO: Solução possível

Eu não posto isso, pois não tenho certeza se é a solução real. Depois de muitas experiências, descobri que o problema é a descarga adaptativa. Eu abordei o problema por

innodb_adaptive_flushing=0
innodb_adaptive_flushing_lwm=70

Aparentemente, quando a descarga adaptativa é acionada para evitar E/S alta, ela permanece por um longo tempo.

UPDATE2: compactação de página vs coluna

Identifiquei o problema a ser

innodb_compression_default=ON

Seguindo uma sugestão de Rick James , criei tabelas semelhantes com compactação de coluna em vez de compactação de página. A compactação é de cerca de 300% para ambos os métodos (10-20% melhor com compactação de página, como se aplica a toda a tabela em vez de colunas seletivas), mas o desempenho foi significativamente diferente no HDD.

Acho que o problema é ao gravar em vários arquivos esparsos criados pela compactação de página ao mesmo tempo em um HDD (não deve ser um problema no SSD).

Eu preciso recriar todas as tabelas para ter certeza, e o processo é dolorosamente demorado.

Neste sqlfiddle ,

CREATE TABLE t1
(
id int(11) unsigned NOT NULL,
val int(11) unsigned NOT NULL,
rank mediumint(7) unsigned,
INDEX(id),
INDEX(rank),
PRIMARY KEY(id,val)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

INSERT INTO t1 (id,val,rank) VALUES (1,2,2), (1,1,1), (1,9,1), (1,5,23), 
(1,3,2), (2,2,1), (2,9,8), (2,5,0);

SELECT id,GROUP_CONCAT(CONCAT(val,'|',rank) ORDER BY rank DESC SEPARATOR ',') 
FROM t1 GROUP BY id;

Como posso LIMITAR o número de linhas retornadas por GROUP_BY?

Meu problema é que ORDERdentro GROUP_BYdeve ser idêntico a ORDERdentro GROUP_CONCAT.

Por exemplo, se estiver usando LIMIT 3, a saída deve ser

id  GROUP_CONCAT(CONCAT(val,'|',rank) ORDER BY rank DESC SEPARATOR ',')
1   5|23,3|2,2|2
2   9|8,2|1,5|0

O valor padrão de innodb_read_io_threadse innodb_write_io_threadsé 4. Como você verifica se a carga do servidor precisa de um número maior de threads?

Como verifico regularmente show engine innodb status \G, não há atividade rastreável:

--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
...
I/O thread 32 state: waiting for completed aio requests (write thread)
I/O thread 33 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] ,

Além disso, qual é a desvantagem de aumentá-lo para o valor máximo de 64? Se não estiver usando, eles são threads ociosos inofensivos.

I SELECTn linhas para cada uma ArticleIDda TagMaptabela

SELECT a.ArticleID,a.TagID FROM TagMap a
LEFT JOIN TagMap b ON a.ArticleID = b.ArticleID AND a.TagID < b.TagID
GROUP BY a.ArticleID,a.TagID
HAVING COUNT(b.TagID) < 3
ORDER BY a.ArticleID,a.TagID

Quero adicionar uma condição para limitar a consulta a tags específicas,

SELECT a.ArticleID,a.TagID FROM TagMap a
  LEFT JOIN TagMap b ON a.ArticleID = b.ArticleID AND a.TagID < b.TagID
    WHERE a.TagID IN(SELECT TagID FROM Tags WHERE Status=1)
    AND b.TagID IN(SELECT TagID FROM Tags WHERE Status=1)
  GROUP BY a.ArticleID,a.TagID
  HAVING COUNT(b.TagID) < 3
  ORDER BY a.ArticleID,a.TagID

Se eu usar a condição como subconsulta, precisaria fazer subconsultas. Se eu usar a condição as JOIN, preciso de dois JOINs já que o acima JOINé LEFT JOIN.

Existe uma maneira mais fácil de fazer isso?

Eu quero encontrar o ID pai mais alto para cada entrada. Por exemplo,

CREATE TABLE t1
(
ID int(11) unsigned NOT NULL,
ParentID int(11) unsigned,
PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

INSERT INTO t1 (ID,ParentID) VALUES (1,NULL),(2,1),(3,2),(4,3);

eu pretendo obter

ID     Highest ParentID
1      NULL
2      1
3      1
4      1

Planejei criar uma consulta recursiva (MySQL 8 ou MariaDB 10.5) adicionando uma condição para quebrar a recursiva quando o Parent ID for um valor específico (por exemplo, NULL no caso acima). eu comecei com

WITH RECURSIVE cte (ID, ParentID) as (
  SELECT ID,ParentID FROM t1
  UNION ALL
  SELECT t2.ID,t2.ParentID FROM t1 t2
  INNER JOIN cte on t2.ParentID = cte.ID
)
SELECT * FROM cte;

mas não funciona como pretendo, pois obtém o próximo ParentID em vez de recursivamente.

Amostra de violino .

tenho a consulta de

INSERT IGNORE INTO t2 (ID,P1,P2,Items) 
    SELECT ID,P1,P2,SUM(Items) 
    FROM t1 GROUP BY ID,P1,P2;

Como preciso executar essa consulta com frequência para inspecionar alterações em t1, preciso adicionar

ON DUPLICATE KEY UPDATE t2.Items=SUM(t1.Items)

mas dá ERROR 1111 (HY000): Invalid use of group functionerro.

Minhas tabelas foram criadas com compressão de linha InnoDB ( ENGINE=InnoDB ROW_FORMAT=COMPRESSED). Agora estou alterando-os para compactação de página. De acordo com a documentação oficial do MariaDB, habilitar a compactação de página afeta apenas as tabelas recém-criadas.

Assim, crio uma tabela de réplicas e uso INSERT INTO SELECT.

Gostaria de saber se é possível ALTERuma tabela alterar o tipo de compactação (de linha para página) ?

Podemos ALTERusar uma tabela comum para usar a compactação de página usando

ALTER TABLE t1 PAGE_COMPRESSED=1;

Minha pergunta é sobre uma maneira segura de remover ROW_FORMAT=COMPRESSEDe adicionar arquivos PAGE_COMPRESSED=1.

Eu uso SphinxSE em vez de FULLTEXT. As configurações do Sphinx são para usuários finais de um mecanismo de pesquisa. De acordo com a documentação oficial ,

Cláusula LIMIT. Ambos os formulários LIMIT N e LIMIT M,N são suportados. Ao contrário do SQL normal (mas como na API Sphinx), um LIMIT implícito 0,20 está presente por padrão.

Portanto, qualquer consulta retorna apenas 20 linhas

MariaDB [sphinx]> SELECT * FROM t1 WHERE query='test;mode=any';
+------+--------+---------------+
| id   | weight | query         |
+------+--------+---------------+
|  556 |      1 | test;mode=any |
|  864 |      1 | test;mode=any |
| 1329 |      1 | test;mode=any |
| 1781 |      1 | test;mode=any |
| 1832 |      1 | test;mode=any |
| 2157 |      1 | test;mode=any |
| 2388 |      1 | test;mode=any |
| 2889 |      1 | test;mode=any |
| 3118 |      1 | test;mode=any |
| 3155 |      1 | test;mode=any |
| 3255 |      1 | test;mode=any |
| 3485 |      1 | test;mode=any |
| 3495 |      1 | test;mode=any |
| 3623 |      1 | test;mode=any |
| 4034 |      1 | test;mode=any |
| 4525 |      1 | test;mode=any |
| 4563 |      1 | test;mode=any |
| 4659 |      1 | test;mode=any |
| 4736 |      1 | test;mode=any |
| 4807 |      1 | test;mode=any |
+------+--------+---------------+
20 rows in set (0.027 sec)

Eu experimentei várias configurações no sphinx.conf, mas não consigo alterar a configuração para retornar mais resultados em uma consulta SQL.

O padrão do meu servidor CHARSETé UTF-8(definido em my.cnf). Eu crio tabelas como

CREATE TABLE t1
(
ID int(11) unsigned NOT NULL AUTO_INCREMENT,
Title varchar(255),
PRIMARY KEY(ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=2

mas algumas tabelas têm apenas intcolunas. É benéfico usar latincharset para eles? Ou é melhor ter todas as tabelas com UTF-8charset para consistência?

CREATE TABLE t2
(
ID int(11) unsigned NOT NULL,
TagID int(11) unsigned NOT NULL,
PRIMARY KEY(ID,TagID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1

vs.

CREATE TABLE t2
(
ID int(11) unsigned NOT NULL,
TagID int(11) unsigned NOT NULL,
PRIMARY KEY(ID,TagID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE latin1_swedish_ci KEY_BLOCK_SIZE=1

Eu JOINuma mesa para si mesma como

SELECT t1.TagID AS TagID1
    ,t2.TagID AS TagID2
    ,COUNT(1)
FROM TagMap AS t1
JOIN TagMap AS t2 ON t1.ArticleID = t2.ArticleID AND t1.TagID <> t2.TagID
GROUP BY t1.TagID, t2.TagID

Como posso limitar as linhas recuperadas para cada tabela (t1 e t2) por

WHERE TagID IN(SELECT TagID FROM Tags WHERE Articles>50)

Observe que a tabela TagMapé enorme com mais de 200 milhões de linhas e desejo limitar seu tamanho antes JOINde .

Na verdade, a razão pela qual quero limitar o número de linhas de TagMapé que a tabela é muito grande e não posso executar o JOIN.

Eu tenho uma tabela tag_map simples

CREATE TABLE TagMap
(
TagID mediumint(7) unsigned,
ArticleID int(11) unsigned,
FOREIGN KEY(TagID) REFERENCES Tags(TagID) ON DELETE CASCADE,
FOREIGN KEY(ArticleID) REFERENCES Articles(ArticleID) ON DELETE CASCADE,
PRIMARY KEY(TagID,ArticleID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci KEY_BLOCK_SIZE=1

Recebo as co-tags (tags, que aparecem junto com uma tag específica):

SELECT TagID AS TagID2,COUNT(*) FROM TagMap WHERE ArticleID IN(
SELECT ArticleID FROM TagMap WHERE TagID=1 // This is TagID1
) 
GROUP BY TagID

Como posso fazer essa consulta para todas as tags para obter

TagID1,TagID2,COUNT(*)

A tabela é enorme (10 a 50 milhões de linhas) e cada artigo tem dezenas de tags. Assim, o desempenho é fundamental.

Eu tenho um banco de dados de itens, pois cada item tem um conjunto de pares chave-valor como

place => london
field => engineering

já que frequentemente preciso encontrar itens com os mesmos dois conjuntos de valores-chave. Criei tabelas de suporte armazenando ids designados para cada keye value.

CREATE TABLE ItemKeyValues
(
ItemID int(11) unsigned,
KeyID mediumint(7) unsigned,
ValueID mediumint(7) unsigned,
PRIMARY KEY (ItemID,KeyID,ValueID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1

CREATE TABLE Pairs
(
PairID int(11) unsigned NOT NULL AUTO_INCREMENT,
Key1 mediumint(7) unsigned,
Value1 mediumint(7) unsigned,
Key2 mediumint(7) unsigned,
Value2 mediumint(7) unsigned,
UNIQUE INDEX(Key1,Value1,Key2,Value2),
PRIMARY KEY (ItemID,KeyID,ValueID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1

CREATE TABLE ItemPairs
(
PairID int(11) unsigned NOT NULL,
ItemID int(11) unsigned,
PRIMARY KEY (PairID,ItemID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci KEY_BLOCK_SIZE=1

(Eu removi índices e FKs por simplicidade)

Por exemplo, uma linha da tabela Pairsé

PairID    Key1             Value1            Key2              Value2
1         3 (for place)    88 (for London)   10 (for field)    9 (for engineering)

Eu preciso preencher as duas últimas tabelas.

1. Como posso INSERTtodas as combinações da Key1,Value1,Key2,Value2tabela ItemKeyValues (KeyID,ValueID)?
2. Como posso UPDATEmesa ItemPairsda mesa ItemPairs?

O desempenho é de grande importância, pois a tabela ItemKeyValuestem mais de 100 milhões de linhas (embora esta seja uma operação única).

Eu tento atualizar uma tabela contando linhas de outra tabela como

UPDATE t1 SET c1 = (
    SELECT COUNT(*) FROM t2 WHERE t2.id=t1.id GROUP BY id
    )

estranhamente, essa consulta ficou no Sending dataestado por horas.

SELECT COUNT(*) FROM t2

retorna ~16M em 2 segundos.

SELECT id FROM t2 GROUP BY id

retorna 50 mil linhas em 5 segundos.

As estruturas da tabela são muito simples

CREATE TABLE t2
(
item int(11) unsigned NOT NULL,
id mediumint(7) unsigned NOT NULL,
PRIMARY KEY (item,id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci

Ao configurar

innodb_fast_shutdown=0

O MySQL descarta todas as transações incompletas antes de desligar/travar. O valor padrão é 1, o que é compreensível para preservar transações incompletas.

No entanto, no modo de recuperação (por exemplo, innodb_force_recovery = 6), o MySQL trata todas as transações incompletas como confirmadas. Isso obviamente danifica a mesa, pois as transações estão incompletas.

Qual é o propósito de preservar transações incompletas quando danifica a tabela inteira?

Se for necessário em alguns casos, por que não innodb_force_recoverytem a opção de simplesmente descartar todas as transações incompletas (quando não podem ser confirmadas) como innodb_fast_shutdown=0salvar a tabela? Ou eu estou esquecendo de alguma coisa?

Eu tento fazer um backup de um enorme banco de dados MySQL via mysqldump. O banco de dados tem milhões de linhas com um tamanho total de 1,3 TB.

CREATE TABLE PageContents
(
ID int(11) unsigned,
Added date,
Text MEDIUMTEXT,
FOREIGN KEY(ID) REFERENCES AllPages(ID) ON DELETE CASCADE,
PRIMARY KEY(ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci KEY_BLOCK_SIZE=1;

mas recebo um erro após 8 horas de despejo:

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `PageContents` at row: 20946782

eu tinha aumentado max_allowed_packetemmysql.cnf

max_allowed_packet  = 16G