mysql报内存不足！！！！

027xiatian

data表一天的数据有1000多万条，
机器的内存是16G,
运行select id,INET_ATON(peer) as ip,time from data where left(Logtime,10)='$date' order by ip 报错

ERROR 5 (HY000) at line 1: Out of memory (Needed 357392255 bytes)
None get 0 (0) ip data from mysql db

mysql的配置文件如下：
[mysql]
port                = 3306
default-character-set=gbk
socket                = /tmp/my.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
#log = commonlog
#log-update
user=mysql
datadir=/usr/mysql
default-character-set=gbk
port                = 3306
socket                = /tmp/my.sock
skip-innodb
skip-bdb
skip-locking
key_buffer_size = 1024M
max_allowed_packet = 92M
net_buffer_length = 8M
#net_buffer_length = 32M
table_cache = 4096

# per connect use 256M , 4G memory allow 16 process
sort_buffer_size = 2048M

# per connect use  256M
read_buffer_size = 2048M

# per connect use 256M
join_buffer_size = 1024M

read_rnd_buffer_size = 300M
#read_rnd_buffer_size = 800M
bulk_insert_buffer = 1024M
myisam_sort_buffer_size = 1024M
tmp_table_size = 4096M
#tmp_table_size = 2048M
thread_cache_size = 32
query_cache_size = 1024M
binlog_cache_size = 64M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 16

tmpdir                = /tmp/               

[mysqldump]
quick
socket                = /tmp/my.sock
max_allowed_packet = 16M

[mysql]
#quick
no-auto-rehash
socket                = /tmp/my.sock
defult-character-set=gbk
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[mysqladmin]
socket         = /tmp/my.sock

[mysqlshow]
socket         = /tmp/my.sock

[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 4M
write_buffer = 4M

[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 40M
write_buffer = 40M

[mysqlhotcopy]
interactive-timeou

但重新启动mysql就不会报错了，
请高手指教..

[ 本帖最后由 027xiatian 于 2008-9-19 16:05 编辑 ]

027xiatian

在线等。。。。
咋没有人回答啊？？

yejr

1. 加内存
2. 减小内存相关参数配置

akebono

# memory=key_buffer+(sort_buffer_size+read_buffer_size)*max_connections

your sort_buffer_size = 2048M??
too large?


how many connections then?

yueliangdao0608

http://bbs.chinaunix.net/thread-1157458-1-3.html

wildlily980

select id,INET_ATON(peer) as ip,time from data where left(Logtime,10)='$date' order by ip
顺便把sql也改一下
where logtime like "$date%" order by ip

027xiatian

今天才上来看到，多谢高手指点，明天再去服务器测试一下，

027xiatian

目前修改为
sort_buffer_size = 256M

read_buffer_size = 100M

目前数据统计程序运行正常，以后还待观察。。。。