Início / user-4098

Putnik's questions

Martin Hope
Putnik
Asked: 2023-08-10 00:51:24 +0800 CST
  • 5

Eu tenho uma consulta simples em um banco de dados Postgres 15. Sem junções ou outras coisas sofisticadas:

selecione * da minha_tabela onde col='val'

O objetivo é agilizar a consulta. Eu diria que preciso de um índice, mas os valores colnão são únicos. Existe alguma maneira de especificar algo como índice não exclusivo ou qualquer outra maneira de acelerá-lo usando apenas a estrutura da tabela ?

Presumo que todos os ajustes de memória/cache/hardware já tenham sido feitos.

postgresql
Martin Hope
Putnik
Asked: 2023-07-26 20:49:19 +0800 CST
  • 5

Há chances de eu ter selecionado a ferramenta errada para esta tarefa, sinta-se à vontade para sugerir se você conhece uma melhor.

A tarefa: preciso armazenar pares chave-valor.

  • Cada par tem 60 bytes. As chaves são IDs e valores - ambos são bastante aleatórios ou pelo menos não estruturados.
  • Existem ~ 2 bilhões desses pares, o que perfaz ~ 110 GB de dados puros no total. Boas chances de crescimento no futuro.
  • A carga de gravação é pesada e a de leitura é bastante leve.
  • Seria bom ter um desempenho de 1K IOPS para escrever, mas talvez seja apenas um sonho e terei que escolher algo mais lento, mas não tão caro.
  • Posso gravar em lote, mas as chaves não serão sequenciais (como 123,7,13565em vez de 1,2,3)
  • Nenhuma pesquisa sofisticada é necessária, apenas forneça o valor para a chave completa.
  • Estou na AWS, se for importante, mas posso mudar para uma solução realmente boa.
  • O custo importa.

O Redis é um armazenamento de valor-chave, então pensei em usá-lo, mas manter um banco de dados tão grande na memória tem um custo proibitivo, então gostaria de configurar o Redis de uma maneira que pegue os dados da memória como um cache e quando há uma falta de cache - do disco.

Portanto, não se trata da persistência do Redis como backup.

Além do Redis, tentei:

  • arquivos simples na árvore de diretórios, como key='abcdef' => ab/cd/ef. Ext2, BtrFS, tentou distribuir gravações em 16 partições (desempenho terrível após ~ 0,5 milhões de pares)
  • MySQL (morreu silenciosamente)

Também pensei no AWS Elasticache Redis com Data Tiering, mas a instância mais barata tem um custo insano para mim (~$ 600/mês)

Como posso conseguir isso?

redis
Martin Hope
Putnik
Asked: 2017-08-01 05:40:17 +0800 CST
  • 2

Temos duas tabelas:

 CREATE TABLE `messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` int(10) unsigned DEFAULT '0',
  `user_id` int(11) DEFAULT '0',
....
  `subject_id` int(11) unsigned DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `user_id` (`user_id`),
  KEY `created` (`created`),
  KEY `text_id` (`text_id`) USING BTREE,
  KEY `subject_id` (`subject_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=237542180 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

O segundo:

CREATE TABLE `users` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `email` char(150) DEFAULT NULL,
  `reg_time` int(10) unsigned DEFAULT '0',
  `password` char(255) DEFAULT NULL,
...................
  `moderation` int(1) unsigned NOT NULL DEFAULT '0',
  `tag` varchar(255) DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `created` (`reg_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=123585 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

mensagens tem ~ 49 milhões de registros, usuários tem 13 mil. Mecanismo de banco de dados: Aurora (compatível com MySQL) 5.6.10a

O pedido terrivelmente longo é

SELECT messages.*, users.administrator_group_id FROM messages 
    LEFT JOIN users ON messages.user_id = users.id 
    ORDER BY messages.id desc LIMIT 0,20

Se eu executar essa solicitação sem order by, levará de 14 a 16 segundos. Com orderele leva mais de 5 min.

Estou considerando alterar a lógica de negócios para evitar essa solicitação e limitar o conjunto de registros, por messagesexemplo, pela data das mensagens, mas gostaria de saber se existe alguma maneira de acelerá-lo no mesmo hardware que está.

mysql optimization
Martin Hope
Putnik
Asked: 2013-01-07 20:33:22 +0800 CST
  • 1

Após o acidente:

mysql> repair table lot_not_sold;
+------------------------+--------+----------+---------------------------------------------------------------+
| Table                  | Op     | Msg_type | Msg_text                                                      |
+------------------------+--------+----------+---------------------------------------------------------------+
| coinprice.lot_not_sold | repair | info     | Found block with too small length at 126188; Skipped          |
| coinprice.lot_not_sold | repair | info     | Wrong block with wrong total length starting at 147688        |
| coinprice.lot_not_sold | repair | info     | Wrong bytesec:   4-253-223 at 9784004; Skipped                |
| coinprice.lot_not_sold | repair | info     | Wrong bytesec:  48- 32-208 at 11538216; Skipped               |
| coinprice.lot_not_sold | repair | info     | Delete link points outside datafile at 4036632                |
| coinprice.lot_not_sold | repair | info     | Wrong bytesec:   0-  0-  0 at 1405636; Skipped                |
.... rows were skipped a lot ....
| coinprice.lot_not_sold | repair | warning  | Number of rows changed from 113619 to 115277                  |
| coinprice.lot_not_sold | repair | status   | OK                                                            |
+------------------------+--------+----------+---------------------------------------------------------------+
616 rows in set (1.77 sec)

Mais saída aqui , no entanto, também tive que pular muitas linhas devido ao limite de tamanho.

SELECT * FROM coinprice.lot_not_sold  ORDER BY site DESC limit 2;
+------------+----------------------------+---------------------+--------+------+------------+------------+------+
| id         | fulltitle                  | added               | finish | site | startprice | blitzprice | url  |
+------------+----------------------------+---------------------+--------+------+------------+------------+------+
| 2924913561 | tttt 10 tttttttt 2001 ttttt| ▒338-14-08 91:97:60 | NULL   |   78 |      0.000 |      4.379 | NULL |
| 2926483072 | Zamek w Krniku - 2z - 1998 | 2013-01-06 06:40:01 | NULL   |    5 |      2.839 |      0.000 | NULL |
+------------+----------------------------+---------------------+--------+------+------------+------------+------+
2 rows in set (0.00 sec)

Observe que addedestá obviamente incorreto para a primeira linha.

mysql> delete from lot_not_sold where site>5;
ERROR 1032 (HY000): Can't find record in 'lot_not_sold'

Tente verificar:

mysql> check table lot_not_sold extended;
+------------------------+-------+----------+----------+
| Table                  | Op    | Msg_type | Msg_text |
+------------------------+-------+----------+----------+
| coinprice.lot_not_sold | check | status   | OK       |
+------------------------+-------+----------+----------+
1 row in set (2.95 sec)

Em seguida, tente selecionar novamente, excluir, reparar - e sempre obter o mesmo resultado.

mysql> show create table lot_not_sold;

 CREATE TABLE `lot_not_sold` (
  `id` int(11) unsigned NOT NULL,
  `fulltitle` varchar(120) NOT NULL,
  `added` datetime NOT NULL,
  `finish` datetime DEFAULT NULL COMMENT 'дата закрытия торгов',
  `site` tinyint(4) NOT NULL,
  `startprice` float(8,3) unsigned NOT NULL,
  `blitzprice` float(8,3) unsigned DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`,`site`,`added`),
  KEY `IX_lot_not_sold_added` (`added`),
  KEY `IX_lot_not_sold_finish` (`finish`),
  KEY `IX_lot_not_sold_site` (`site`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=114
/*!50100 PARTITION BY HASH (DAY(added))
PARTITIONS 4 */

É possível corrigir esse erro? É possível que haja muitos erros porque repairadicionou ~ 2k linhas?

Pesquisei no Google algumas páginas que mencionaram um bug muito antigo, mas minha instância não é muito antiga:

mysql  Ver 14.14 Distrib 5.1.66, for redhat-linux-gnu (x86_64) using readline 5.1

MyISAM foi escolhido por motivos de desempenho. Agora estou pensando em mudar para o InnoDB - é o caminho certo? Esta tabela é constantemente modificada.

Obrigada.

mysql mysql-5.1
Martin Hope
Putnik
Asked: 2012-04-17 09:52:01 +0800 CST
  • 1

Enfrentamos as restaurações do banco de dados do MySQL MUITO lentas: ~ 3 min para despejo de 15 MB. Este é um novo servidor dedicado com muita RAM.

Disseram-nos que o motivo mais possível é que o GUID é usado como chave primária em muitas tabelas:

CREATE TABLE `acl_actions` (
  `id` char(36) NOT NULL,
  `date_entered` datetime NOT NULL,
  `name` varchar(150) default NULL,
  ........
  `category` varchar(100) default NULL,
  `deleted` tinyint(1) default '0',
  PRIMARY KEY  (`id`),
  KEY `idx_aclaction_id_del` (`id`,`deleted`),
  KEY `idx_category_name` (`category`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

A explicação:

Isso certamente afetará o desempenho de inserção em massa, como no caso de uma importação de despejo, pois o arquivo MYI que contém os índices fará E/S aleatória, em vez do aumento que receberia para E/S sequencial no caso de um número inteiro de incremento monotônico. Seu key_buffer_size também entra em jogo, mas não parece ser subdimensionado neste caso com base nos blocos de chave livres e nos blocos de chave de marca d'água usados.

Não tenho certeza com essa pessoa e, além disso, não tenho experiência suficiente, então tenho que perguntar aqui: por que diabos haverá IO aleatório? Como o id é quase aleatório, o conteúdo do índice não será tão linear?

É possível diminuir o desempenho tão drasticamente por causa dos GUIDs? Existe alguma solução alternativa, exceto substituir UIDs para ID autoincremental globalmente?

UPD: afinal encontramos o principal motivo da lentidão: binlog. Desativamos o binlog e isso reduz o tempo de importação x12!

mysql restore
Martin Hope
Putnik
Asked: 2012-04-07 05:08:27 +0800 CST
  • 2

A tarefa: escrever um gatilho para registrar todas as atualizações na tbl1tabela para futuras ações dos usuários de auditoria.

A tbl1mesa:

id INT(11) 
y SMALLINT(6)
country SMALLINT(6)
-- really here ~20 fields but only some of them have to be monitored

Vou armazenar os valores antigos e novos, uma linha para cada campo que foi alterado. O lugar para log (simplificado para este exemplo):

 CREATE TABLE z_log(
   id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
   table_name CHAR(20) NOT NULL,
   row_id INT(11) UNSIGNED NOT NULL,
   field CHAR(20) NOT NULL,
   old_val VARCHAR(255) NOT NULL DEFAULT '',
   new_val VARCHAR(255) NOT NULL DEFAULT '',
   PRIMARY KEY (id)
 )

O gatilho que criei:

CREATE TRIGGER trigger1
    AFTER UPDATE
    ON tbl1
    FOR EACH ROW
BEGIN
  -- I'm interested in two fields only and if they were changed only
  IF new.y != old.y THEN
    SET @y = "('','tbl1',new.id,'y',old.y,new.y)";
  END IF;

  IF new.country != old.country THEN
    SET @country = "('','tbl1',new.id,'country',old.country,new.country)";
  END IF;

  if @y != '' or @country != '' THEN
    set @my_sql = concat('insert into z_lot values ', concat_ws(',', @y, @country), ';');
    prepare stmt1 from @my_sql;
    EXECUTE stmt1;
  end if;

END

Mas recebi o erro "o sql dinâmico não é permitido na função ou gatilho armazenado". Posso evitar 'executar' usando muitas inserções separadas: uma para o campo 'y', uma para o campo 'país' e ~ 10 inserções a mais para outros campos, mas haverá penalidade de desempenho.

Existe alguma outra maneira rápida de registrar apenas as alterações? Obrigada.

mysql trigger
Martin Hope
Putnik
Asked: 2011-10-26 11:59:16 +0800 CST
  • 1

SQL Server 2008 web edition x64 Estou abrindo: (conexão)/Segurança/Logins/John. Selecione "Mapeamento de usuário". No painel direito, há uma caixa de seleção marcada na linha DB1. Ativo a caixa de seleção na linha DB2, o nome de usuário e o esquema corretos aparecem. Pressione 'ok'. Abrindo (conexão)/Segurança/Logins/John mais uma vez e há mo marca na caixa de seleção do DB2, está claro. Nenhum nome de usuário/esquema nessa linha, é claro. Marque a caixa de seleção novamente e assim por diante até ficar louco.

Nenhuma mensagem de erro, nenhum erro no log.

Não consigo desativar a caixa de seleção do DB1 por causa de 'Não é possível descartar o dbo do usuário'

O DB1 realmente não importa para mim, mas preciso do mapeamento do DB2. Como posso consertar isso?

sql-server-2008