搜索

[22373] 2014-02-01_安装完MySQL后必须调整的10项配置

文档创建者：s7ckTeam
浏览次数：6
最后更新：2025-01-18
2014-02-01_安装完MySQL后必须调整的10项配置 安 装 完   M y S Q L   后 必 须 调 整 的   1 0   项 配 置 L i n u x 中 国   2 0 1 4 - 0 2 - 0 1 当 我 们 被 人 雇 来 监 测 M y S Q L 性 能 时 ， 人 们 希 望 我 们 能 够 检 视 一 下 M y S Q L 配 置 然 后 给 出 一 些 提 高 建 议 。 许 多 人 在 事 后 都 非 常 惊 讶 ， 因 为 我 们 建 议 他 们 仅 仅 改 动 几 个 设 置 ， 即 使 是 这 里 有 好 几 百 个 配 置 项 。 这 篇 文 章 的 目 的 在 于 给 你 一 份 非 常 重 要 的 配 置 项 清 单 。 我 们 曾 在 几 年 前 在 博 客 里 给 出 了 这 样 的 建 议 ， 但 是 M y S Q L 的 世 界 变 化 实 在 太 快 了 ！ 写 在 开 始 前 … 即 使 是 经 验 老 道 的 人 也 会 犯 错 ， 会 引 起 很 多 麻 烦 。 所 以 在 盲 目 的 运 用 这 些 推 荐 之 前 ， 请 记 住 下 面 的 内 容 ： 一 次 只 改 变 一 个 设 置 ！ 这 是 测 试 改 变 是 否 有 益 的 唯 一 方 法 。 大 多 数 配 置 能 在 运 行 时 使 用 S E T   G L O B A L 改 变 。 这 是 非 常 便 捷 的 方 法 它 能 使 你 在 出 问 题 后 快 速 撤 销 变 更 。 但 是 ， 要 永 久 生 效 你 需 要 在 配 置 文 件 里 做 出 改 动 。 一 个 变 更 即 使 重 启 了 M y S Q L 也 没 起 作 用 ？ 请 确 定 你 使 用 了 正 确 的 配 置 文 件 。 请 确 定 你 把 配 置 放 在 了 正 确 的 区 域 内 ( 所 有 这 篇 文 章 提 到 的 配 置 都 属 于   [ m y s q l d ] ) 服 务 器 在 改 动 一 个 配 置 后 启 不 来 了 ： 请 确 定 你 使 用 了 正 确 的 单 位 。 例 如 ， i n n o d b _ b u f f e r _ p o o l _ s i z e 的 单 位 是 M B 而 m a x _ c o n n e c t i o n 是 没 有 单 位 的 。 不 要 在 一 个 配 置 文 件 里 出 现 重 复 的 配 置 项 。 如 果 你 想 追 踪 改 动 ， 请 使 用 版 本 控 制 。 不 要 用 天 真 的 计 算 方 法 ， 例 如 ” 现 在 我 的 服 务 器 的 内 存 是 之 前 的 2 倍 ， 所 以 我 得 把 所 有 数 值 都 改 成 之 前 的 2 倍 “ 。 基 本 配 置 你 需 要 经 常 察 看 以 下 3 个 配 置 项 。 不 然 ， 可 能 很 快 就 会 出 问 题 。 i n n o d b _ b u f f e r _ p o o l _ s i z e : 这 是 你 安 装 完 I n n o D B 后 第 一 个 应 该 设 置 的 选 项 。 缓 冲 池 是 数 据 和 索 引 缓 存 的 地 方 ： 这 个 值 越 大 越 好 ， 这 能 保 证 你 在 大 多 数 的 读 取 操 作 时 使 用 的 是 内 存 而 不 是 硬 盘 。 典 型 的 值 是 5 - 6 G B ( 8 G B 内 存 ) ， 2 0 - 2 5 G B ( 3 2 G B 内 存 ) ， 1 0 0 - 1 2 0 G B ( 1 2 8 G B 内 存 ) 。 i n n o d b _ l o g _ f i l e _ s i z e ： ： 这 是 r e d o 日 志 的 大 小 。 r e d o 日 志 被 用 于 确 保 写 操 作 快 速 而 可 靠 并 且 在 崩 溃 时 恢 复 。 一 直 到 M y S Q L   5 . 1 ， 它 都 难 于 调 整 ， 因 为 一 方 面 你 想 让 它 更 大 来 提 高 性 能 ， 另 一 方 面 你 想 让 它 更 小 来 使 得 崩 溃 后 更 快 恢 复 。 幸 运 的 是 从 M y S Q L   5 . 5 之 后 ， 崩 溃 恢 复 的 性 能 的 到 了 很 大 提 升 ， 这 样 你 就 可 以 同 时 拥 有 较 高 的 写 入 性 能 和 崩 溃 恢 复 性 能 了 。 一 直 到 M y S Q L   5 . 5 ， r e d o 日 志 的 总 尺 寸 被 限 定 在 4 G B ( 默 认 可 以 有 2 个 l o g 文 件 ) 。 这 在 M y S Q L   5 . 6 里 被 提 高 。 一 开 始 就 把 i n n o d b _ l o g _ f i l e _ s i z e 设 置 成 5 1 2 M ( 这 样 有 1 G B 的 r e d o 日 志 ) 会 使 你 有 充 裕 的 写 操 作 空 间 。 如 果 你 知 道 你 的 应 用 程 序 需 要 频 繁 的 写 入 数
据 并 且 你 使 用 的 时 M y S Q L   5 . 6 ， 你 可 以 一 开 始 就 把 它 这 是 成 4 G 。 m a x _ c o n n e c t i o n s : 如 果 你 经 常 看 到 ‘ T o o   m a n y   c o n n e c t i o n s ’ 错 误 ， 是 因 为 m a x _ c o n n e c t i o n s 的 值 太 低 了 。 这 非 常 常 见 因 为 应 用 程 序 没 有 正 确 的 关 闭 数 据 库 连 接 ， 你 需 要 比 默 认 的 1 5 1 连 接 数 更 大 的 值 。 m a x _ c o n n e c t i o n 值 被 设 高 了 ( 例 如 1 0 0 0 或 更 高 ) 之 后 一 个 主 要 缺 陷 是 当 服 务 器 运 行 1 0 0 0 个 或 更 高 的 活 动 事 务 时 会 变 的 没 有 响 应 。 在 应 用 程 序 里 使 用 连 接 池 或 者 在 M y S Q L 里 使 用 进 程 池 有 助 于 解 决 这 一 问 题 。 I n n o D B 配 置 从 M y S Q L   5 . 5 版 本 开 始 ， I n n o D B 就 是 默 认 的 存 储 引 擎 并 且 它 比 任 何 其 他 存 储 引 擎 的 使 用 都 要 多 得 多 。 那 也 是 为 什 么 它 需 要 小 心 配 置 的 原 因 。 i n n o d b _ f i l e _ p e r _ t a b l e ： 这 项 设 置 告 知 I n n o D B 是 否 需 要 将 所 有 表 的 数 据 和 索 引 存 放 在 共 享 表 空 间 里 （ i n n o d b _ f i l e _ p e r _ t a b l e   =   O F F ）   或 者 为 每 张 表 的 数 据 单 独 放 在 一 个 . i b d 文 件 （ i n n o d b _ f i l e _ p e r _ t a b l e   =   O N ） 。 每 张 表 一 个 文 件 允 许 你 在 d r o p 、 t r u n c a t e 或 者 r e b u i l d 表 时 回 收 磁 盘 空 间 。 这 对 于 一 些 高 级 特 性 也 是 有 必 要 的 ， 比 如 数 据 压 缩 。 但 是 它 不 会 带 来 任 何 性 能 收 益 。 你 不 想 让 每 张 表 一 个 文 件 的 主 要 场 景 是 ： 有 非 常 多 的 表 （ 比 如 1 0 k + ） 。 M y S Q L   5 . 6 中 ， 这 个 属 性 默 认 值 是 O N ， 因 此 大 部 分 情 况 下 你 什 么 都 不 需 要 做 。 对 于 之 前 的 版 本 你 必 需 在 加 载 数 据 之 前 将 这 个 属 性 设 置 为 O N ， 因 为 它 只 对 新 创 建 的 表 有 影 响 。 i n n o d b _ f l u s h _ l o g _ a t _ t r x _ c o m m i t ： 默 认 值 为 1 ， 表 示 I n n o D B 完 全 支 持 A C I D 特 性 。 当 你 的 主 要 关 注 点 是 数 据 安 全 的 时 候 这 个 值 是 最 合 适 的 ， 比 如 在 一 个 主 节 点 上 。 但 是 对 于 磁 盘 （ 读 写 ） 速 度 较 慢 的 系 统 ， 它 会 带 来 很 巨 大 的 开 销 ， 因 为 每 次 将 改 变 f l u s h 到 r e d o 日 志 都 需 要 额 外 的 f s y n c s 。 将 它 的 值 设 置 为 2 会 导 致 不 太 可 靠 （ r e l i a b l e ） 因 为 提 交 的 事 务 仅 仅 每 秒 才 f l u s h 一 次 到 r e d o 日 志 ， 但 对 于 一 些 场 景 是 可 以 接 受 的 ， 比 如 对 于 主 节 点 的 备 份 节 点 这 个 值 是 可 以 接 受 的 。 如 果 值 为 0 速 度 就 更 快 了 ， 但 在 系 统 崩 溃 时 可 能 丢 失 一 些 数 据 ： 只 适 用 于 备 份 节 点 。 i n n o d b _ f l u s h _ m e t h o d :   这 项 配 置 决 定 了 数 据 和 日 志 写 入 硬 盘 的 方 式 。 一 般 来 说 ， 如 果 你 有 硬 件 R A I D 控 制 器 ， 并 且 其 独 立 缓 存 采 用 w r i t e - b a c k 机 制 ， 并 有 着 电 池 断 电 保 护 ， 那 么 应 该 设 置 配 置 为 O _ D I R E C T ； 否 则 ， 大 多 数 情 况 下 应 将 其 设 为 f d a t a s y n c （ 默 认 值 ） 。 s y s b e n c h 是 一 个 可 以 帮 助 你 决 定 这 个 选 项 的 好 工 具 。 i n n o d b _ l o g _ b u f f e r _ s i z e :   这 项 配 置 决 定 了 为 尚 未 执 行 的 事 务 分 配 的 缓 存 。 其 默 认 值 （ 1 M B ） 一 般 来 说 已 经 够 用 了 ， 但 是 如 果 你 的 事 务 中 包 含 有 二 进 制 大 对 象 或 者 大 文 本 字 段 的 话 ， 这 点 缓 存 很 快 就 会 被 填 满 并 触 发 额 外 的 I / O 操 作 。 看 看 I n n o d b _ l o g _ w a i t s 状 态 变 量 ， 如 果 它 不 是 0 ， 增 加 i n n o d b _ l o g _ b u f f e r _ s i z e 。 其 他 设 置 q u e r y _ c a c h e _ s i z e :   q u e r y   c a c h e （ 查 询 缓 存 ） 是 一 个 众 所 周 知 的 瓶 颈 ， 甚 至 在 并 发 并 不 多 的 时 候 也 是 如 此 。   最 佳 选 项 是 将 其 从 一 开 始 就 停 用 ， 设 置 q u e r y _ c a c h e _ s i z e   =   0 （ 现 在 M y S Q L   5 . 6 的 默 认 值 ） 并 利 用 其 他 方 法 加 速 查 询 ： 优 化 索 引 、 增 加 拷 贝 分 散 负 载 或 者 启 用 额 外 的 缓 存 （ 比 如 m e m c a c h e 或 r e d i s ） 。 如 果 你 已 经 为 你 的 应 用 启 用 了 q u e r y   c a c h e 并 且 还 没 有 发 现 任 何 问 题 ， q u e r y   c a c h e 可 能 对 你 有 用 。 这 是 如 果 你 想 停 用 它 ， 那 就 得 小 心 了 。 l o g _ b i n ： ： 如 果 你 想 让 数 据 库 服 务 器 充 当 主 节 点 的 备 份 节 点 ， 那 么 开 启 二 进 制 日 志 是 必 须 的 。 如 果 这 么 做 了 之 后 ， 还 别 忘 了 设 置 s e r v e r _ i d 为 一 个 唯 一 的 值 。 就 算 只 有 一 个 服 务 器 ， 如 果 你 想 做 基 于 时 间 点 的 数 据 恢 复 ， 这 （ 开 启 二 进 制 日 志 ） 也 是 很 有 用 的 ： 从 你 最 近 的 备 份 中 恢 复 （ 全 量 备 份 ） ， 并 应 用 二 进 制 日 志 中 的 修 改 （ 增 量 备 份 ） 。 二 进 制 日 志 一 旦 创 建 就 将 永 久 保 存 。 所 以 如 果 你 不 想 让 磁 盘 空 间 耗 尽 ， 你 可 以 用   P U R G E   B I N A R Y   L O G S   来 清 除 旧 文 件 ， 或 者 设 置 e x p i r e _ l o g s _ d a y s   来 指 定 过 多 少 天 日 志 将 被 自 动 清 除 。 记 录 二 进 制 日 志 不 是 没 有 开 销 的 ， 所 以 如 果 你 在 一 个 非 主 节 点 的 复 制 节 点 上 不 需 要 它 的 话 ， 那 么 建 议 关 闭 这 个 选 项 。 s k i p _ n a m e _ r e s o l v e ： ： 当 客 户 端 连 接 数 据 库 服 务 器 时 ， 服 务 器 会 进 行 主 机 名 解 析 ， 并 且 当 D N S 很 慢 时 ， 建 立 连 接 也 会 很 慢 。 因 此 建 议 在 启 动 服 务 器 时 关 闭 s k i p _ n a m e _ r e s o l v e 选 项 而 不 进 行 D N S 查 找 。 唯 一 的 局 限 是 之 后 G R A N T 语 句 中 只 能 使 用 I P 地 址 了 ， 因 此 在 添 加 这 项 设 置 到 一 个 已 有 系 统 中 必 须 格 外 小 心 。
总 结 总 结 当 然 还 有 其 他 的 设 置 可 以 起 作 用 ， 取 决 于 你 的 负 载 或 硬 件 ： 在 慢 内 存 和 快 磁 盘 、 高 并 发 和 写 密 集 型 负 载 情 况 下 ， 你 将 需 要 特 殊 的 调 整 。 然 而 这 里 的 目 标 是 使 得 你 可 以 快 速 地 获 得 一 个 稳 健 的 M y S Q L 配 置 ， 而 不 用 花 费 太 多 时 间 在 调 整 一 些 无 关 紧 要 的 M y S Q L 设 置 或 读 文 档 找 出 哪 些 设 置 对 你 来 说 很 重 要 上 。 英 文 原 文 ： 1 0   M y S Q L   s e t t i n g s   t o   t u n e   a f t e r   i n s t a l l a t i o n 参 与 翻 译 ( 3 人 ) ： B o y d W a n g ,   美 好 的 2 0 1 4 ,   D r Z v i a   :   h t t p : / / w w w . o s c h i n a . n e t / t r a n s l a t e / 1 0 - m y s q l - s e t t i n g s - t o - t u n e - a f t e r - i n s t a l l a t i o n   本 文 地 址 ： h t t p : / / l i n u x . c n / a r t i c l e - 2 5 3 3 - 1 . h t m l   阅 读 原 文
回复

举报

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

使用Markdown编辑器编辑 使用富文本编辑器编辑

Archiver| 手机版| 小黑屋|
Powered by Discuz! X3.4 Copyright © 2001-2020, Tencent Cloud. 商业源码建议获取授权，如侵犯您的权益，请联系客服处理