Any idea "MySQL client ran out of memory"

[Bulent Tekcan]

I received sometimes this error,any idea for this error ? Everytime same sql error.


Warning: mysql_query(): Unable to save result set in /includes/class_core.php on line 404


Database error in vBulletin 3.5.4:
Invalid SQL:
SELECT user.joindate, user.userid, user.membergroupids, user.posts, user.reputation,
user.usergroupid, user.displaygroupid, user.customtitle, user.username,
userpromotion.joinusergroupid, userpromotion.reputation AS jumpreputation, userpromotion.posts AS jumpposts,
userpromotion.date AS jumpdate, userpromotion.type, userpromotion.strategy,
usergroup.title, usergroup.usertitle AS ug_usertitle,
usertextfield.rank
FROM user AS user
INNER JOIN userpromotion AS userpromotion ON (user.usergroupid = userpromotion.usergroupid)
LEFT JOIN usergroup AS usergroup ON (userpromotion.joinusergroupid = usergroup.usergroupid)
LEFT JOIN usertextfield AS usertextfield ON (usertextfield.userid = user.userid);
MySQL Error : MySQL client ran out of memory
Error Number : 2008
Date : Sunday, July 9th 2006 @ 12:26:36 PM
Script : http://www.turkeyforum.com/satforum/xxxxxx/cronadmin.php?do=runcron&cronid=14
Referrer : http://www.turkeyforum.com/satforum/xxxxxx/index.php
IP Address : 81.215.81.13
Username : Bulent Tekcan
Classname : vb_database


Thanks

[Colin F]

Sounds like a RAM/Server problem.

[Bulent Tekcan]

Is it a defective ? Because I have a 4GB ram and optimize mysql,httpd.I find this error in error_log

[Colin F]

No, I don't think so.
Maybe this will help: http://dev.mysql.com/doc/refman/5.0/en/out-of-memory.html

[Bulent Tekcan]

Himmm I think this is query cache size,limit.I'll be check this.

Thanks

[eva2000]

run mysqlreport http://www.vbulletin.com/forum/showthread.php?t=175177 to see how your mysql memory buffers are being utilised.. sometimes you allocate way too much ram for some buffers which can be reallocated to other buffers

[Bulent Tekcan]

This is my report...any idea ?

MySQL 4.1.20-standard uptime 1 20:7:8 Fri Jun 30 18:18:06 2006
__ Key __________________________________________________ _______________
Buffer usage 209.32M of 256.00M %Used: 81.76
Write ratio 0.83
Read ratio 0.00
__ Questions __________________________________________________ _________
Total 15.18M 95.57/s
DMS 7.89M 49.70/s %Total: 52.00
QC Hits 4.89M 30.78/s 32.21
Com_ 1.21M 7.62/s 7.97
COM_QUIT 1.19M 7.48/s 7.82
-Unknown 1.33k 0.01/s 0.01
Slow 21 0.00/s 0.00 %DMS: 0.00
DMS 7.89M 49.70/s 52.00
SELECT 5.54M 34.87/s 36.49 70.17
UPDATE 1.52M 9.55/s 9.99 19.22
INSERT 725.40k 4.57/s 4.78 9.19
DELETE 89.77k 0.57/s 0.59 1.14
REPLACE 23.07k 0.15/s 0.15 0.29
Com_ 1.21M 7.62/s 7.97
change_db 1.19M 7.46/s 7.81
lock_tables 9.71k 0.06/s 0.06
unlock_tabl 9.71k 0.06/s 0.06
__ SELECT and Sort __________________________________________________ ___
Scan 957.55k 6.03/s %SELECT: 17.29
Range 1.18M 7.43/s 21.32
Full join 41 0.00/s 0.00
Range check 0 0.00/s 0.00
Full rng join 0 0.00/s 0.00
Sort scan 557.13k 3.51/s
Sort range 1.37M 8.61/s
Sort mrg pass 501 0.00/s
__ Query Cache __________________________________________________ _______
Memory usage 20.97M of 192.00M %Used: 10.92
Block Fragmnt 31.49%
Hits 4.89M 30.78/s
Inserts 5.45M 34.29/s
Prunes 51.04k 0.32/s
Insrt:Prune 106.71:1 33.97/s
Hit:Insert 0.90:1
__ Table Locks __________________________________________________ _______
Waited 259.79k 1.64/s %Total: 1.68
Immediate 15.16M 95.45/s
__ Tables __________________________________________________ ____________
Open 844 of 1800 %Cache: 46.89
Opened 850 0.01/s
__ Connections __________________________________________________ _______
Max used 175 of 500 %Max: 35.00
Total 1.19M 7.49/s
__ Created Temp __________________________________________________ ______
Disk table 1.18k 0.01/s
Table 199.22k 1.25/s
File 997 0.01/s
__ Threads __________________________________________________ ___________
Running 1 of 7
Cache 168 %Hit: 99.99
Created 175 0.00/s
Slow 0 0.00/s
__ Aborted __________________________________________________ ___________
Clients 2.68k 0.02/s
Connects 1 0.00/s
__ Bytes __________________________________________________ _____________
Sent 508.74G 3.20M/s
Received 5.01G 31.52k/s

[Marco van Herwaarden]

How much physical memory is in the server?
Could you please post the contents of your MySQL configuration file (my.cnf). Make sure that any passwords that might be there are edited out.

[Bulent Tekcan]

[mysqld]
safe-show-database
skip-locking
skip-innodb
max_connections = 500
key_buffer = 256M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 300
#wait_timeout = 30
connect_timeout = 10
tmp_table_size = 256M
max_heap_table_size = 256M
thread_concurrency = 4
max_allowed_packet = 64M
max_connect_errors = 10
bulk_insert_buffer_size = 8M
read_rnd_buffer_size = 524288
query_cache_limit = 12M
query_cache_size = 192M
query_cache_type = 1
query_prealloc_size = 16384
#query_prealloc_size = 65536
query_alloc_block_size = 32768
[mysqld_safe]
nice = -10
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
[mysqlhotcopy]
interactive-timeout


I have 4GB ram,and still continue this problem.

When I press in promotion cron run this error occured.

Warning: mysql_query(): Unable to save result set in /includes/class_core.php on line 404

[Bulent Tekcan]

I checked my schedule log,my User Promotions cron not working since 2 week.No record in log files.

When I press manuel this cron,few seconds later white screen coming and didn't work.

Any idea ?

[eva2000]

from ticket reply you fixed this right ?

[Bulent Tekcan]

Yes it is fixed,maybe other people referance this thread.

Warning: mysql_query(): Unable to save result set in /includes/class_core.php on line 404

This is CPanel suck...When Shell Fork Bomb is active this error occured,because many process limited working.

[eva2000]

ah thanks for the info :)