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的值.

因为我们的存储过程中用了好多的预处理语句,而且语句的结果都是非常大的,起初我的结果,代码如下:

1. mysql>showvariableslike'max_heap_table_size';
2. +---------------------------------+----------------------+
3. |Variable_name|Value|
4. +---------------------------------+----------------------+
5. |max_heap_table_size|16777216|
6. +---------------------------------+----------------------+
7. mysql>showvariableslike'tmp_table_size';
8. +---------------------------------+----------------------+
9. |Variable_name|Value|
10. +---------------------------------+----------------------+
11. |tmp_table_size|16777216|
12. +---------------------------------+----------------------+

我改了my.cnf文件,代码如下:

1. mysql>showvariableslike'max_heap_table_size';
2. +---------------------------------+----------------------+
3. |Variable_name|Value|
4. +---------------------------------+----------------------+
5. |max_heap_table_size|67108864|
6. +---------------------------------+----------------------+
7. mysql>showvariableslike'tmp_table_size';
8. +---------------------------------+----------------------+
9. |Variable_name|Value|
10. +---------------------------------+----------------------+
11. |tmp_table_size|67108864|
12. +---------------------------------+----------------------+

我的配置方法如下:

1. [client]
2. #password=your_password
3. port=3306
4. socket=/var/lib/mysql/mysql.sock
5. #TheMySQLserver
6. [mysqld]
7. port=3306
8. socket=/var/lib/mysql/mysql.sock
9. skip-locking
10. #Cache&BufferSize
11. max_allowed_packet=1G
12. key_buffer_size=768M
13. table_cache=256M
14. sort_buffer_size=64M
15. read_buffer_size=64M
16. read_rnd_buffer_size=64M
17. myisam_sort_buffer_size=64M
18. tmp_table_size=256M====================对这里进行修改即可
19. query_cache_type=1
20. query_cache_limit=32M
21. connect_timeout=100000
22. #binaryloggingisrequiredforreplication
23. log-bin=mysql-bin
24. #binaryloggingformat-mixedrecommended
25. binlog_format=mixed
26. #requireduniqueidbetween1and2^32-1
27. #defaultsto1ifmaster-hostisnotset
28. #butwillnotfunctionasamasterifomitted
29. server-id=1
30. #UncommentthefollowingifyouareusingInnoDBtables
31. innodb_data_home_dir=/var/lib/mysql/
32. innodb_data_file_path=ibdata1:10M:autoextend
33. innodb_log_group_home_dir=/var/lib/mysql/
34. innodb_buffer_pool_size=768M
35. innodb_additional_mem_pool_size=512M
36. innodb_log_file_size=5M
37. innodb_log_buffer_size=8M
38. [mysqldump]
39. quick
40. max_allowed_packet=16M
41. [mysql]
42. no-auto-rehash
43. #RemovethenextcommentcharacterifyouarenotfamiliarwithSQL
44. #safe-updates//phpfensi.com
45. [myisamchk]
46. key_buffer_size=32M
47. sort_buffer_size=32M
48. read_buffer=8M
49. write_buffer=8M
50. [mysqlhotcopy]
51. interactive-timeout