mysql報內(nèi)存不足�。。�！

027xiatian

data表一天的數(shù)據(jù)有1000多萬條，
機(jī)器的內(nèi)存是16G,
運(yùn)行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. 加內(nèi)存
2. 減小內(nèi)存相關(guān)參數(shù)配置

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://www.72891.cn/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

今天才上來看到，多謝高手指點(diǎn)，明天再去服務(wù)器測試一下，

027xiatian

目前修改為
sort_buffer_size = 256M

read_buffer_size = 100M

目前數(shù)據(jù)統(tǒng)計程序運(yùn)行正常，以后還待觀察。。。。