主页 / dba / 问题 / 1927
Accepted
Michael McGowan
Asked: 2011-03-28 11:47:38 +0800 CST

为什么MySQL说我内存不足？

  • 772

我试图INSERT...SELECT在 MySQL 中使用 JDBC 执行相当大的任务，但出现以下异常：

Exception in thread "main" java.sql.SQLException: Out of memory (Needed 1073741824 bytes)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)

因为我实际上并没有返回 ResultSet 对象，所以我认为 Java 堆空间应该不是问题。但是，无论如何，我都尝试将其提高，但效果不佳。然后我尝试在 MySQL Workbench 中执行该语句，我得到了基本相同的结果：

Error Code 5: Out of memory (Needed 1073741816 bytes)

我应该有足够的 RAM 来完成这些操作（足以容纳我从中选择的整个表），但我猜我需要调整各种设置以利用我所有的内存。我正在使用Windows Server 2008 AMI运行 Amazon EC2高内存双超大型实例。我试过摆弄 my.ini 文件以使用更好的设置，但据我所知，我可能会让事情变得更糟。这是该文件的转储：

[client]
port=3306
[mysql]
default-character-set=latin1
[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
character-set-server=latin1
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size=1024M
table_cache=256
tmp_table_size=25G
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_repair_threads = 2
myisam_sort_buffer_size=10G
key_buffer_size=5000M
bulk_insert_buffer_size = 4000M
read_buffer_size=8000M
read_rnd_buffer_size=8000M
sort_buffer_size=1G
innodb_additional_mem_pool_size=26M
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=13M
innodb_buffer_pool_size=23G
innodb_log_file_size=622M
innodb_thread_concurrency=18
innodb_file_per_table=TRUE
join_buffer_size=4G
max_heap_table_size = 10G

那么这只是更改上述设置以更好地适应我的环境的问题吗？如果是这样，我应该使用什么设置？我是唯一一个使用过这个实例的人；我将它用于我的个人爱好项目，该项目涉及对大型数据集的统计分析。因此，我可以自由地让它为我自己的查询消耗所有可用资源。

如果这不是更改这些设置的问题，那么问题是什么？感谢您为如何更好地配置所有内容提供的任何帮助。

mysql windows

4 个回答

  1. Best Answer

    鉴于这是一个 Windows 安装，@DTest 仍然提供了最初的正确方向。

    应用以下公式：

    大多数人使用这个：

    Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + (read_buffer_size + sort_buffer_size) X max_connections

    我更喜欢这个：

    Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) X max_connections)

    这些变量是您需要调整的变量，直到公式产生已安装 RAM 的 80% 或更少。

    sort_buffer_size
read_buffer_size
read_rnd_buffer_size
join_buffer_size
max_connections
    • 9

  2. 我会尝试降低你的缓冲区大小。使它们与您拥有的一样大会引起问题。您有多少内存可用于运行这些值：

    query_cache_size=1024M
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=10G
key_buffer_size=5000M
bulk_insert_buffer_size = 4000M
read_buffer_size=8000M
read_rnd_buffer_size=8000M
sort_buffer_size=1G
innodb_buffer_pool_size=23G

    一些缓冲区大小是按线程分配的，例如 10G 的 myisam_sort_buffer_size 为每个线程分配 10G。

    我将首先大幅减少这些值，然后调查您真正需要哪些值来分配这么多 RAM（如果有的话）。

    • 4

  3. 确定 MySQL 认为它可以分配多少内存的快速方法如下：

    wget mysqltuner.pl

    perl mysqltuner.pl

    当你运行这个脚本时，它会告诉你 MySQL 认为它可以安全分配的已安装 RAM 的百分比。如果给出的答案超过 100%，那么您肯定需要降低缓冲区大小。主要需要关注的是：

    sort_buffer_size
    read_buffer_size read_rnd_buffer_size join_buffer_size max_connections
    key_buffer_size （在4G之后不是很有效）



    @DTest 已经在他的回答中为您设定了方向，因此为他的回答者 +1。perl 脚本会告诉您如果不设置它或更改任何值会发生什么。这是一个例子：

    我的一个客户端有
    read_buffer_size=128K
    read_rnd_buffer_size=256K sort_buffer_size
    =2M
    join_buffer_size=128K
    max_connections=1050

    这是 mysqltuner.pl 的输出：

    MySQLTuner 1.2.0 - 主要 Hayden
    错误报告、功能请求和下载http://mysqltuner.com/
    使用“--help”运行以获取其他选项和输出过滤
    请输入您的 MySQL 管理登录名：lwdba
    请输入您的 MySQL 管理密码：

    -------- 一般统计 ---------------------------------------- ----------
    [--] 跳过 MySQLTuner 脚本的版本检查
    [OK] 当前运行支持的 MySQL 版本 5.0.51a-community-log
    [!!] 切换到 64 位操作系统 - MySQL 当前无法使用你所有的内存

    -------- 存储引擎统计 ---------------------------------------- ----
    [--] 状态：+Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
    [--] MyISAM 表中的数据：319M（表：108）
    [--] InnoDB 表中的数据：2M（表： 5)
    [!!] 碎片表总数：22

    - - - - 性能指标 - - - - - - - - - - - - - - - - - - - - ---------
    [--] Up for: 52d 23h 15m 57s (72M q [15.875 qps], 241K conn, TX: 2B, RX: 1B)
    [--] 读取/写入：59% / 41%
    [--] 总缓冲区：34.0M 全局 + 2.7M 每个线程（1050 个最大线程）
    [!!] 在 32 位系统上分配 > 2GB RAM 会导致系统不稳定
    [!!] 最大可能内存使用量：2.8G （已安装 RAM 的 72%）
    [OK] 慢查询：0%（54/72M）
    [OK] 可用连接的最高使用率：6%（65/1050）
    [OK] 密钥缓冲区大小/MyISAM 索引总数：8.0M/ 82.1M
    [OK] Key buffer hit rate: 100.0% (4B cached / 1M reads)
    [!!] Query cache is disabled
    [OK] 需要临时表的排序: 0% (0 temp sorts / 948K sorts)
    [OK] 在磁盘上创建的临时表：3%（磁盘上 11K / 总共 380K）
    [!!] 线程缓存被禁用
    [!!] 表缓存命中率：0%（64 打开 / 32K 打开）
    [OK] 打开文件使用的限制：2% (125/5K)
    [OK] 立即获取的表锁：99%（30M 立即/30M 锁）
    [OK] InnoDB 数据大小/缓冲池：2.7M/8.0M

    -------- 建议 ----------------------------------------- ------------
    一般建议：
    运行 OPTIMIZE TABLE 对表进行碎片整理以获得更好的性能
    启用慢查询日志以解决不良查询
    将 thread_cache_size 设置为 4 作为起始值
    逐渐增加 table_cache 以避免文件描述符限制
    变量调整：
    query_cache_size (>= 8M)
    thread_cache_size (从 4 开始)
    table_cache (> 64)

    请注意性能指标下

    [--] 总缓冲区：全局 34.0M + 每个线程 2.7M（最大 1050 个线程）

    MySQL 可以根据 /etc/my.cnf 中的设置分配多达 72% 的已安装 RAM。

    34M是基于innodb_buffer_pool_size和key_buffer_size结合的

    每个线程 2.7M 是基于 read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size。

    2.7M 的倍数基于 max_connections。

    因此，您必须调整这些参数，直到性能指标报告显示您的已安装 RAM 低于 100%（最好低于 80%）。

    • 4

  4. 你没有说你有多少内存？我认为它至少是 32GB。

    innodb_buffer_pool_size - 23G

    对那么多内存有好处。

    query_cache_size = 1G

    太大了。大的时候效率低下。建议不超过50M。

    key-buffer_size = 5G

    Windows 上可能有 4G 的硬限制（仍然），有 4G 的硬限制。你的 5G 可能已经变成了 1G。无论如何，如果你所有的表都是 InnoDB，为什么要浪费内存。将其设置为 50M。

    由于错误消息正好有 1G，所以闻起来像sort_buffer_size. 32M可能是合理的。

    • 1

相关问题