MYSQL錯誤:Out of memory (Needed 1046596 bytes)

Out of memory根據我們對英文了理解是超出的內存空間，也就是說你mysql占用了大量的內存了，或是沒有配置好，下面小編來給各位同學介紹一下。

MYSQL內存設置問題，可調整tmp_table_size大小解決

增大query_cache_limit 的值。
還有max_heap_table_size
和tmp_table_size的值。
因為我們的存儲過程中用了好多的預處理語句。而且語句的結果都是非常大的。
起初我的結果：

 代碼如下 復制代碼

mysql> show variables like 'max_heap_table_size';
+---------------------------------+----------------------+
| Variable_name | Value |
+---------------------------------+----------------------+
| max_heap_table_size | 16777216 |
+---------------------------------+----------------------+
mysql> show variables like 'tmp_table_size';
+---------------------------------+----------------------+
| Variable_name | Value |
+---------------------------------+----------------------+
| tmp_table_size | 16777216 |
+---------------------------------+----------------------+

我改了my.cnf文件

mysql> show variables like 'max_heap_table_size';
+---------------------------------+----------------------+
| Variable_name | Value |
+---------------------------------+----------------------+
| max_heap_table_size | 67108864 |
+---------------------------------+----------------------+
mysql> show variables like 'tmp_table_size';
+---------------------------------+----------------------+
| Variable_name | Value |
+---------------------------------+----------------------+
| tmp_table_size | 67108864 |
+---------------------------------+----------------------+

我的配置方法如下

[client]
#password       = your_password
port            = 3306
socket          = /var/lib/mysql/mysql.sock

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking

# Cache & Buffer Size
max_allowed_packet = 1G
key_buffer_size =768M
table_cache =256M
sort_buffer_size =64M
read_buffer_size =64M
read_rnd_buffer_size =64M
myisam_sort_buffer_size = 64M
tmp_table_size=256M  ====================對這裡進行修改即可
query_cache_type=1
query_cache_limit=32M
connect_timeout=100000

# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_buffer_pool_size = 768M
innodb_additional_mem_pool_size = 512M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 32M
sort_buffer_size = 32M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout