Derek Downey's questions

背景

我正在为一个由大约 4 名程序员和 4 名设计师组成的小型 Web 团队创建一个新的开发流程，未来团队的发展潜力明显。我们的产品是一个中央应用程序，为我们设计和托管的客户网站提供支持。

以前，我们都通过 FTP 在开发服务器上工作，只有一个开发数据库。它“工作”了^一段时间，但我们正在朝着一个新的方向前进，所以是时候让我们的流程成熟起来了。

我们使用 Percona Server 5.5，但这应该与数据库无关，以保持低成本。

目标：

我正在考虑为数据库开发创建一个持续集成 (CI) 流程，并考虑以下内容：

1. 开发人员拥有数据的本地副本来运行开发代码
2. 能够将数据库结构回滚到以前的变更集
3. 能够区分新功能架构更改与架构设计修复更改
4. 能够在本地修改数据库结构以进行测试

初始概念

我在下面使用 SVN 和 LiquiBase 勾勒出了一个过程，尽管它完全删除了#4.

• 从主干创建一个“开发”分支
• 中央“开发”数据库服务器运行“开发”分支
• 本地开发人员被设置为开发分支的奴隶（#1上面提供）
• liquibase 变更集定期提交到开发分支，该分支执行提交后挂钩以更新中央开发数据库（这将渗透到作为该开发服务器的从属运行的本地计算机）（liquibase 在#2上面提供）
• 当功能或模式修复准备好进行质量检查时，DBA（我）会将开发分支中的适当更改合并到主干中。此操作将创建一个 sql 脚本以应用于临时数据库服务器。
• 暂存服务器应该反映 TRUNK，它应该具有与生产相同的结构，以及 QA 中的更改
• 在登台服务器上执行 sql 脚本后，对更改进行一些 QA。
• 如果一切看起来都不错，请标记结构。这将生成由 DBA 手动在生产环境中运行的 .sql 脚本（如果需要，可用于非高峰时段）

此过程要求所有开发人员运行相同的“开发”分支，这意味着在任何给定时间只有一个版本的数据库模式（不确定我是否想要这个）。

这也意味着对模式的任何更改都无法在本地进行测试，如果操作不当，可能会影响其他开发人员。在我们的环境中，开发人员可能会添加新表，但很少修改现有结构。作为 DBA，设计修复由我完成。但是无法在本地测试修复程序是我对该过程的最大阻碍。

如何调整上述过程以允许本地开发，同时仍保持相对最新的数据副本（由我提议的过程中的复制提供）？我不需要数据是最新的，即使是上周。

在将数据库转换为 UTF-8 时，我注意到关于控制字符 0x80-0x9F 的奇怪行为。例如，0x92（右撇号）不会转换为 UTF-8 并截断列的其余内容，使用以下方法：

CREATE TABLE `bar` (
 `content` text
) ENGINE=MyISAM DEFAULT CHARSET=latin1

INSERT INTO bar VALUES (0x8081828384858687898A8B8C8D8E8F909192939495969798999A9B9C9D9E9F);
Query OK, 1 row affected (0.06 sec)

SELECT content FROM bar;
+---------------------------------------------------------------------------------+
| content                                                                         |
+---------------------------------------------------------------------------------+
| €‚ƒ„…†‡‰Š‹ŒŽ‘’“”•–—˜™š›œžŸ                                                 |
+---------------------------------------------------------------------------------+
1 row in set (0.06 sec)

ALTER TABLE bar CHANGE content content TEXT CHARACTER SET UTF8;
Query OK, 1 row affected, 1 warning (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 1

SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                             |
+---------+------+-------------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\x80\x81\x82\x83\x84\x85...' for column 'content' at row 1 |
+---------+------+-------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

SELECT * FROM bar;
+---------+
| content |
+---------+
|         |
+---------+
1 row in set (0.06 sec)

虽然通常在 Latin1 中不允许使用 0x80-0x9F，但 MySQL 似乎以不同的方式处理它：

MySQL 的 latin1 与 Windows cp1252 字符集相同。这意味着它与官方 ISO 8859-1 或 IANA（互联网数字分配机构）latin1 相同，除了 IANA latin1 将 0x80 和 0x9f 之间的代码点视为“未定义”，而 cp1252 以及 MySQL 的 latin1 分配字符对于那些职位。[源]

但是 MySQL 似乎无法将上述值范围从其 latin1 字符集转换为 UTF-8 字符集。

这些字符是通过从 word 文档 (cp1252) 复制/粘贴而进入我的数据库的，虽然我可能已经找到一种方法让应用程序为新条目强制使用正确的 UTF-8 值，但我需要确保旧的 get正确转换。

MySQL 中是否有一种方法可以将它们转换为等效的 UTF-8，而无需遍历每个文本列的每一行并用 ASCII 友好的版本替换它们？

我正在考虑将我所有服务器的日志记录设置到集中式 postgresql 数据库中。能够按日期或主机删除日志是有意义的，所以我想用partitioning来设置它，但是一个多层分区：host-service inherits service, host-service-yyyymm inherits host-service.

我已经为特定服务制定了一个示例方案（在这种情况下为 php 错误），并且正在寻找一些 PostgreSQL 专家来批评它是否存在明显的性能瓶颈。

// SET UP MASTER PHP LOG TABLE //
CREATE TABLE php (
 log_id     int not null,
 host       char(5),    
 logdate     date not null,
 message        text
);

// SET UP HOST-SPECIFIC 'PARTITIONS' //
CREATE TABLE host1_php (
 CHECK ( host = 'host1' )
) INHERITS (php);

CREATE TABLE host2_php (
 CHECK ( host = 'host2' )
) INHERITS (php);

// SET UP HOST-SPECIFIC TIME 'PARTITIONS' //
CREATE TABLE host1_php_2011m12 (
 CHECK ( logdate >= DATE '2011-12-01' AND logdate < DATE '2012-01-01' )
) INHERITS (host1_php);

CREATE TABLE host1_php_2012m01 (
 CHECK ( logdate >= DATE '2012-01-01' AND logdate < DATE '2012-02-01' )
) INHERITS (host1_php);

CREATE TABLE host2_php_2011m12 (
 CHECK ( logdate >= DATE '2011-12-01' AND logdate < DATE '2012-01-01' )
) INHERITS (host2_php);

CREATE TABLE host2_php_2012m01 (
 CHECK ( logdate >= DATE '2012-01-01' AND logdate < DATE '2012-02-01' )
) INHERITS (host2_php);

CREATE INDEX host1_php_2011m12_logdate ON host1_php_2011m12 (logdate);
CREATE INDEX host1_php_2012m01_logdate ON host1_php_2012m01 (logdate);
CREATE INDEX host2_php_2011m12_logdate ON host2_php_2011m12 (logdate);
CREATE INDEX host2_php_2012m01_logdate ON host2_php_2012m01 (logdate);

我还将添加诸如 apache 访问/错误之类的服务。

我想我必须触发插入到 host_service（以利用主机检查约束），然后每个 host_service 必须触发插入到 host_service_yyyymm 表中。

我对这种触发器/分区方案的性能有什么期望？

聊天中讨论的一些额外信息：

• PostgreSQL 版本 9.1.2
• 对数据的查询不会很频繁，而且主要是针对多个主机的当月数据。
• PHP 每分钟大约只有 1 个插入，但所有主机上的总 apache 可能大约为每秒 300-500 个。

来自 MySQL 背景，存储过程的性能（较早的文章）和可用性是有问题的，我正在评估 PostgreSQL 是否适合我公司的新产品。

我想做的一件事是将一些应用程序逻辑移动到存储过程中，所以我在这里询问有关在 PostgreSQL (9.0) 中使用函数的 DO 和 DON'T （最佳实践），特别是关于性能缺陷的问题。

MySQL不能在WHERE条件不包括形成最左侧前缀的列的查找中使用复合索引：

如果列不构成索引的最左前缀，则 MySQL 无法使用索引执行查找。

PostgreSQL 上这个答案的引述引起了我的注意：

这在 Oracle 11 中有些不同，它有时也可以使用不在索引定义开头的列。

在什么情况下，Oracle（至少在 11g 中）可以在查询中不存在最左边的前缀列的情况下进行查找？

我最近遇到了 Percona Server 对普通 MySQL InnoDB 实施的更改。新功能是innodb_fast_checksum

XtraDB 可以使用基于 4 字节字的更 CPU 效率的算法，这对某些工作负载可能是有益的（例如，可以执行大量 IO 的服务器上的写入繁重的工作负载）。

听起来不错，但我想知道是否有任何工作负载在这种更改中的性能会比旧方法差。

文档继续说：

在新算法之后检查原始算法，因此您可以拥有具有旧校验和的数据页和具有新校验和的数据页。但是在这种情况下，您可能会遇到从具有旧校验和的页面读取缓慢的情况。

因此，假设您innodb_fast_checksum按照建议启用并重建所有 InnoDB 表以删除旧校验和，是否存在对旧校验和进行额外检查会导致性能下降的情况？

总之，有什么理由不跑innodb_fast_checksum？

我可能心胸狭隘，但如果我创建了外键约束并且一行被更新或删除，如果子表的列设置为 NULL，我将失去该连接。

故意保留这些孤立行的目的是什么？

直到最近，我都将查询缓存视为提高查询性能的一个非常重要的工具。今天，我正在收听一个播客，其中讨论了将查询缓存调整为 0，并使用更好的内存缓存解决方案（例如 memcache.d）。

但他们也提到在少数情况下 query_cache 很有帮助。因此，一般建议是按需启用它（使用SELECT SQL_CACHE, 和 query_cache_type = 2 配置设置）。

我的问题是，假设你有一个像 memcache.d 这样的缓存解决方案，什么类型的情况会使 query_cache 更优化？

编辑：添加链接

我最近遇到了一个问题，我注意到 MySQL 在发出 GROUP BY 时会忽略尾随空格。这是表格：

CREATE TABLE `myTable` (
 `id` int(10) unsigned NOT NULL,
 `foo` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

这是我运行的示例查询（不是生产用途，但显示了我要修复的内容）：

SELECT foo, CHAR_LENGTH(foo) FROM myTable
WHERE foo='bar'
GROUP BY foo

这将返回一个结果集：

| foo  | CHAR_LENGTH(foo)  |
+------+-------------------+
| Bar  |                 4 |

一切都很好，除了“酒吧”的长度是三，而不是四。所以我去检查个人条目（没有分组）：

SELECT foo, CHAR_LENGTH(foo) FROM myTable
WHERE foo='bar'

| foo  | CHAR_LENGTH(foo)  |
+------+-------------------+
| Bar  |                 3 |
| Bar  |                 3 |
| Bar  |                 3 |
| Bar  |                 4 |
| Bar  |                 3 |

现在，Bar 有五个条目，其中四个正确报告长度为 3。但有一个报告长度为 4。

事实证明，长度为 4 的末尾有一个空格：“Bar”。我本来希望GROUP BY将其视为完全不同的值，而不是将其与其他“酒吧”混为一谈。

如果它们有尾随（或前导）空格，有没有办法告诉 MySQL 考虑不同的值？

（在 mac osx 上运行 mysql 5.1.55 64 位）

首先让我承认我对硬盘的内部工作原理一无所知。因此，当我阅读变量innodb_flush_method的手册时，我感到很困惑。我可以用外行的术语解释 O_DSYNC 和 O_DIRECT 的区别，以及如何知道这是否是数据库服务器上的性能问题。

我设置的一些统计数据：Mac OSX 10.6（32 位内核，因为架构已过时）运行 MySQL 5.1.49-64 位（希望它允许我使用内存）。8GB RAM，约 6GB innodb 数据/索引。

所以我有这个审计表（跟踪我数据库中任何表的操作）：

CREATE TABLE `track_table` (
  `id` int(16) unsigned NOT NULL,
  `userID` smallint(16) unsigned NOT NULL,
  `tableName` varchar(255) NOT NULL DEFAULT '',
  `tupleID` int(16) unsigned NOT NULL,
  `date_insert` datetime NOT NULL,
  `action` char(12) NOT NULL DEFAULT '',
  `className` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userID` (`userID`),
  KEY `tableID` (`tableName`,`tupleID`,`date_insert`),
  KEY `actionDate` (`action`,`date_insert`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

我需要开始归档过时的项目。该表已增长到大约 5000 万行，因此我删除行的最快方法是一次删除一个表（基于tableName）。

这工作得很好，但在一些写繁重的表上，它不会完成。delete我的查询删除了对 tupleID/tableName 组合具有关联操作的所有项目：

DELETE FROM track_table WHERE tableName='someTable' AND tupleID IN (
  SELECT DISTINCT tupleID FROM track_table
  WHERE tableName='someTable' AND action='DELETE' AND date_insert < DATE_SUB(CURDATE(), INTERVAL 30 day)
)

我让它在我的服务器上运行了 3 天，但它从未完成最大的表。解释输出（如果我将删除切换为选择：

| id | select_type        | table       | type | possible_keys      | key     | key_len | ref        | rows    | Extra                        |
|  1 | PRIMARY            | track_table | ref  | tableID            | tableID | 257     | const      | 3941832 | Using where                  |
|  2 | DEPENDENT SUBQUERY | track_table | ref  | tableID,actionDate | tableID | 261     | const,func |       1 | Using where; Using temporary |

因此，我认为 400 万行不应该需要 3 天才能删除。我将 innodb_buffer_pool_size 设置为 3GB，并且服务器未设置为使用 one_file_per_table。还有哪些其他方法可以提高 InnoDB 删除性能？（在 Mac OSX 上运行 MySQL 5.1.43）

所以我对调整 InnoDB 还是很陌生。我正在慢慢地将表（如有必要）从 MyIsam 更改为 InnoDB。我在 innodb 中有大约 100MB，所以我将innodb_buffer_pool_size变量增加到 128MB：

mysql> show variables like 'innodb_buffer%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)

当我去更改innodb_log_file_size值时（例如mysql的innodb配置页面上的my.cnf注释将日志文件大小更改为缓冲区大小的25％。所以现在我的my.cnf看起来像这样：

# innodb
innodb_buffer_pool_size = 128M
innodb_log_file_size = 32M

当我重新启动服务器时，我收到此错误：

110216 9:48:41 InnoDB：正在初始化缓冲池，大小 = 128.0M
110216 9:48:41 InnoDB：已完成缓冲池
InnoDB 的初始化：错误：日志文件 ./ib_logfile0 的大小不同 0 5242880 字节
InnoDB：比在.cnf 文件 0 33554432 字节！
110216 9:48:41 [错误] 插件“InnoDB”初始化函数返回错误。
110216 9:48:41 [错误] 插件“InnoDB”注册为存储引擎失败。

所以我的问题是：删除旧的 log_files 是否安全，或者是否有另一种方法来更改innodb_log_file_size变量？

我将我的从属设置为不复制“mysql”数据库，如此处所述SHOW SLAVE STATUS\G;

           Slave_IO_State: Waiting for master to send event
              Master_Host: 127.0.0.1
              Master_User: replication
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: master-bin.000001
      Read_Master_Log_Pos: 1660
           Relay_Log_File: mysql-relay-bin.000004
            Relay_Log_Pos: 478
    Relay_Master_Log_File: master-bin.000001
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
          Replicate_Do_DB: 
      **Replicate_Ignore_DB: mysql**
       Replicate_Do_Table: 
   Replicate_Ignore_Table: 
  Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
               Last_Errno: 0
               Last_Error: 
             Skip_Counter: 0
      Exec_Master_Log_Pos: 1660
          Relay_Log_Space: 633
          Until_Condition: None
           Until_Log_File: 
            Until_Log_Pos: 0

现在，如果我去MASTER服务器并发出一个GRANTand FLUSH PRIVILEGES：

GRANT SELECT ON *.* TO `foo`@`localhost` IDENTIFIED BY 'bar';
FLUSH PRIVILEGES;

然后我回到SLAVE服务器并发出：

SHOW GRANTS FOR `foo`@`localhost`;

并收到回复：

+-------------------------------------------------------------------------------------------------------------+
| Grants for foo@localhost                                                                                    |
+-------------------------------------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'foo'@'localhost' IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

如何阻止从属服务器复制 mysql 数据库的更改？我认为“replicate_ignore_db”就足够了。

我正在考虑为我的数据库设置主从复制。从服务器将用于冗余，也可能用作报告服务器。然而，我遇到的最大问题之一是我们的数据中心已经用尽了电力。因此，添加另一台物理服务器不是一种选择。

就 cpu 而言，我们现有的数据库服务器的利用率相当低（在四核上的平均负载从未真正超过 1）。所以主要的想法是折腾一些新驱动器并将内存加倍（从 8GB 到 16）并在同一台物理机器上运行第二个 mysql 实例。每个实例都有单独的数据库磁盘。

这个想法有什么问题吗？

编辑（更多信息）：我（幸运的是）从来没有发生过任何糟糕的事情来关闭服务器，但我正在努力提前计划。我们当然有可以恢复的每晚备份。但我认为，如果主服务器的驱动器发生故障（如果整台机器出现故障，显然不会），将冗余数据放在单独的磁盘上会提供更快的解决方案。

至于报告方面，我们要报告的任何表格都是 MyIsam。因此，对正在写入的同一个表进行昂贵的读取可能会使服务器陷入困境。我的假设是，只要我们向主服务器投入足够的 RAM（因为 cpu 负载还不是问题），有一个从服务器来报告就不会影响主服务器。

在 5.0.3 版之后（允许 VARCHAR 为 65,535 字节并停止截断尾随空格），这两种数据类型之间是否有任何重大区别？

我正在阅读差异列表，唯一值得注意的两个是：

对于 BLOB 和 TEXT 列上的索引，您必须指定索引前缀长度。对于 CHAR 和 VARCHAR，前缀长度是可选的。请参阅第 7.5.1 节，“列索引”。

和

BLOB 和 TEXT 列不能有 DEFAULT 值。

那么，由于对 TEXT 数据类型的这两个限制，为什么要在 varchar(65535) 上使用它呢？两者之间是否存在性能影响？

我在具有 8GB RAM 的服务器上运行了一个大约 4.5GB 的数据库。绝大多数表是 MyIsam（大约 4.3GB），但我很快会将其中一些转换为 InnoDB。（这将是一个缓慢的过程，首先关注写入最密集的表）。

在存在两种类型的存储引擎的情况下运行专用服务器有什么问题吗？

根据存储过程和触发器的限制，不能使用动态sql（5.0.13及以后版本的存储过程限制解除）。为什么会有这个限制？为什么要为程序而不是函数或触发器取消它？

我有一个 mysql 5.1 服务器，它的数据库大约有 450 个表，占用 4GB 。这些表中的绝大多数（除了 2 个）都是 MyIsam。这在大多数情况下都很好（不需要事务），但是应用程序一直在获得流量，并且某些表由于更新时的表锁定而受到影响。这就是现在有 2 个表是 InnoDB 的原因。

较小表（100k 行）上的转换根本不需要很长时间，导致停机时间最短。然而，我的一些跟踪表接近 5000 万行。有没有办法加快ALTER TABLE...ENGINE InnoDB大桌子的速度？如果没有，是否还有其他方法可以将这些写入繁重的表的停机时间最小化？