Eu tenho uma mesalista de observação contendo hoje quase 3 mil registros.

mysql>  select count(*) from watchlist;
+----------+
| count(*) |
+----------+
|  2957994 |
+----------+

É usado como um registro para registrar visualizações de páginas de produtos em um grande site de comércio eletrônico (mais de 50.000 produtos). Ele registra o productID do produto visualizado, o endereço IP e USER_AGENT do visualizador. E um timestamp de quando isso acontece:

mysql> show columns from watchlist;
+-----------+--------------+------+-----+-------------------+-------+
| Field     | Type         | Null | Key | Default           | Extra |
+-----------+--------------+------+-----+-------------------+-------+
| productID | int(11)      | NO   | MUL | 0                 |       |
| ip        | varchar(16)  | YES  |     | NULL              |       |
| added_on  | timestamp    | NO   | MUL | CURRENT_TIMESTAMP |       |
| agent     | varchar(220) | YES  | MUL | NULL              |       |
+-----------+--------------+------+-----+-------------------+-------+

Os dados são então relatados em várias páginas do site, tanto no back-end (por exemplo, verificando o que o GoogleBot está indexando), quanto no front-end (por exemplo, uma barra lateral para "Produtos visualizados recentemente" e uma página mostrando aos usuários " As pessoas da sua região também gostaram "etc.).

Para que essas páginas de "relatórios" e barras laterais sejam carregadas rapidamente, coloco índices em campos relevantes:

mysql> show indexes from watchlist;
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| watchlist |          1 | added_on  |            1 | added_on    | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| watchlist |          1 | productID |            1 | productID   | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| watchlist |          1 | agent     |            1 | agent       | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Sem o INDEXES, páginas com a barra lateral, por exemplo, gastariam cerca de 30-45sec executando uma consulta para obter os 7 ProductIDs mais recentes. Com os índices, leva <0.2s.

O problema é quecom o INDEXES as páginas do produto estão demorando mais e mais para carregar, porque à medida que a tabela cresce, as operações de gravação estão levando mais de 5 segundos. Além disso, há um pico nomysqld processo no valor de 10 a 15% da CPU disponível cada vez que uma página do produto é visualizada (aproximadamente uma vez a cada 2 segundos). Nós já tínhamos que atualizar o hardware do servidor porque em um servidor anterior ele estava atingindo 100% e fazia o mysqld travar.

Meu plano é tentar uma solução de 2 mesas. Uma tabela para operações INSERT e outra para operações SELECT. eu planeiopurga a tabela INSERT sempre que atingir 1.000 registros usando um TRIGGER e copiar os 900 registros mais antigos para a tabela SELECT. As páginas do relatório são uma mistura de análise em tempo real (vista recentemente) e (qual região), mas as páginas em tempo real tendem a precisar apenas de um punhado de registros novos, enquanto as páginas analíticas não precisam saber sobre as mais recentes. tendência (ou seja, últimas mil visualizações). Então eu posso usar a pequena mesa para o primeiro e a grande mesa para os últimos relatórios.

Minha pergunta: Esta é uma solução ideal para este problema?

Além disso: Com TRIGGERS no MySQL é possívellegal o trigger_statement para que demore mais, mas não consome muito CPU? Correria umtrabalho cron a cada 30min que é picado, e que executa a limpeza, se necessário, ser uma solução melhor?