主页 / user-4098

Putnik's questions

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

我在 Postgres 15 数据库中有一个简单的查询。没有连接或其他花哨的东西：

从 my_table 中选择 *，其中 col='val'

目的是加快查询速度。我想说我需要一个索引，但 的值col不是唯一的。有没有什么方法可以指定非唯一索引之类的东西，或者有任何其他方法可以仅使用表结构来加速它？

我假设所有内存/缓存/硬件调整已经完成。

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

我有可能为这项任务选择了错误的工具，如果您知道更好的工具，请随时提出建议。

任务：我需要存储键值对。

  • 每对为 60 字节。键是 ID 和值 - 两者都是相当随机的或至少是非结构化的。
  • 这样的对大约有 2 百万，总共产生大约 110 GB 的纯数据。未来有良好的成长机会。
  • 写入负载较重，读取负载较轻。
  • 如果能有 1K IOPS 的写入性能就好了，但也许这只是一个梦想，我将不得不选择速度较慢但不那么昂贵的东西。
  • 我可以批量写入，但键不会是连续的（例如123,7,13565而不是1,2,3）
  • 不需要花哨的搜索，只需给出完整键的值即可。
  • 如果重要的话，我使用的是 AWS，但可以切换到一个非常好的解决方案。
  • 成本很重要。

Redis 是一个键值存储，所以我想使用它，但是在内存中保留这么大的数据库成本太高，所以我想以一种将从内存中获取数据作为缓存的方式配置 Redis，并且当缓存未命中时 - 来自磁盘。

所以，这与Redis作为备份的持久性无关。

除了 Redis 我还尝试过：

  • 目录树中的纯文件，例如key='abcdef' => ab/cd/ef. Ext2、BtrFS，尝试将写入分布到 16 个分区（约 0.5M 对后性能非常糟糕）
  • MySQL（悄然死去）

我还考虑过带有数据分层的 AWS Elasticache Redis，但最便宜的实例对我来说成本高昂（约 600 美元/月）

我怎样才能做到这一点？

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

我们有两个表：

 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

第二个：

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

消息有 ~49M 记录，用户有 13k。数据库引擎：Aurora（兼容 MySQL）5.6.10a

非常长的请求是

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

如果我不运行此请求，order by则需要 14-16 秒。它order需要超过 5 分钟的时间。

我正在考虑更改业务逻辑以避免此请求并限制记录集，messages例如按消息日期，但想知道是否有任何方法可以在与原样相同的硬件上加速它。

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

崩溃后：

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)

这里有更多输出，但是由于大小限制，我也不得不跳过很多行。

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)

请注意added，第一行显然不正确。

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

尝试检查：

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)

接下来尝试再次选择、删除、修复 - 并始终得到相同的结果。

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 */

是否可以修复此错误？repair由于添加了〜2k行，是否可能有很多错误？

我用谷歌搜索了一些提到非常老的错误的页面，但我的实例并不太旧：

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

选择 MyISAM 是因为性能原因。现在我正在考虑切换到 InnoDB - 这是正确的方法吗？此表不断修改。

谢谢你。

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

我们遇到 MySQL 恢复数据库非常慢：15 MB 转储约 3 分钟。这是具有大量 RAM 的新专用服务器。

我们被告知最可能的原因是 GUID 在许多表中被用作主键：

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;

说明：

这肯定会影响批量插入性能，因为在转储导入的情况下，保存索引的 MYI 文件将执行随机 I/O，而不是在单调递增整数的情况下它会收到顺序 I/O 的提升。您的 key_buffer_size 也可以发挥作用，但在这种情况下，根据免费的密钥块和使用的水印密钥块，它似乎并没有过小。

我对那个人不太确定，而且我自己也没有足够的经验，所以我不得不在这里问：为什么会有随机IO？因为 id 几乎是随机的，所以索引的内容不会那么线性？

是否有可能因为 GUID 而大幅降低性能？除了将 UID 全局替换为自动增量 ID 之外，是否有任何解决方法？

UPD：毕竟我们找到了缓慢的主要原因：binlog。我们禁用了 binlog，这减少了 12 倍的导入时间！

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

任务：编写一个触发器来记录tbl1表中的所有更新，以供将来审计用户的操作。

表tbl1：

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

我将存储旧值和新值，每个更改的字段一行。日志的位置（在此示例中进行了简化）：

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

我创建的触发器：

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

但我收到错误“存储函数或触发器中不允许使用动态 sql”。我可以通过使用大量分隔的插入来避免“执行”：一个用于字段“y”，一个用于字段“国家”，另外约 10 个用于其他字段，但会降低性能。

有没有其他快速的方法来只记录更改？谢谢你。

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

SQL Server 2008 web edition x64 我正在打开：（连接）/安全/登录/约翰。选择“用户映射”。在右侧窗格中标记了 DB1 行的复选框。我打开 DB2 行的复选框，出现正确的用户名和模式。按“确定”。再次打开（连接）/安全/登录/约翰，有在 DB2 复选框处标记，很清楚。当然，那行没有用户名/模式。再次标记复选框，依此类推，直到变得疯狂。

没有错误消息，日志中没有错误。

由于“无法删除用户 dbo”，我无法关闭 DB1 复选框

DB1 对我来说真的不重要，但我需要 DB2 映射。我怎样才能解决这个问题？

sql-server-2008