Mysql Error: Out of memory (Needed 4325400 bytes)

MySQL & MariaDB Other MySQL® Questions
1

Hi there,

I’ve been nailing my head to the table trying to fix this one and well other than moving to innodb (I shall be, but I want to fix this in MYISAM first!!) I just can’t get it to work correctly!

I am running one of two queries around 60,000 times, changing values each time, the queries look like, this problem only seems to occur after multiple thousands of iterations and when performed on an UPDATE/INSERT with subqueries with COUNTs, please note I am on a production server so cannot install debugging tools:

update query:

UPDATE rank AS r SET r.yesterdays_rank = INTEGER, r.rank = INTEGER, r.platform = INTEGER, r.yesterdays_platform_rank = INTEGER, r.platform_rank = INTEGER, r.wishlists = (SELECT count(w.gid) FROM wishlists AS w WHERE w.gid = INTEGER), r.collections = (SELECT count(c.gid) FROM collections AS c WHERE c.gid = INTEGER), r.tracking = (SELECT count(t.gid) FROM track AS t WHERE t.gid = INTEGER)WHERE game = INTEGER

insert query:

INSERT INTO rank ( id, game, rank, yesterdays_rank, platform, platform_rank, yesterdays_platform_rank, wishlists, collections, tracking )VALUES ( ‘’, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, (SELECT count(w.gid) FROM wishlists AS w WHERE w.gid = INTEGER), (SELECT count(c.gid) FROM collections AS c WHERE c.gid = INTEGER), (SELECT count(t.gid) FROM track AS t WHERE t.gid = INTEGER) )

Rank “explain”:

id int(20) NO PRI (null) auto_incrementgame int(20) NO MUL rank int(20) NO MUL yesterdays_rank int(20) NO platform int(20) NO platform_rank int(20) NO yesterdays_platform_rank int(20) NO wishlists int(20) NO collections int(20) NO tracking int(20) NO

Wishlists “explain”:

uid int(11) unsigned NO MUL gid int(20) unsigned NO

Collections “explain”:

uid int(11) unsigned NO MUL gid int(20) unsigned NO

Track “explain”:

uid int(11) unsigned NO MUL gid int(20) unsigned NO MUL lastvisit int(20) NO

And finally the My.cnf

[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Default to using old password format for compatibility with mysql 3.x# clients (those using the mysqlclient10 compatibility package).old_passwords=1#skip-locking#skip-innodblog-slow-queries=sporilong_query_time=2log-long-formatinnodb_buffer_pool_size = 2048Minnodb_file_io_threads = 4key_buffer_size=128Mmax_allowed_packet=4Mtable_cache=2048sort_buffer=4Mrecord_buffer=4Mthread_cache=18thread_concurrency=8thread_cache_size=256myisam_sort_buffer_size=128Mmax_connections=6000max_connect_errors=10000tmp_table_size=512Mread_rnd_buffer_size=12Mquery_cache_limit=8Mquery_cache_size=384Mquery_cache_type=1join_buffer=4Msort_buffer_size=4Mread_buffer_size=4Mft_min_word_len=1[mysql.server]user=mysqlcharacter-set=asciicollation=ascii_general_ciconnection=ascii_general_cicharacter_set_connection=ascii_general_ci[mysqld_safe]err-log=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid[myisamchk]ft_min_word_len=1

I know the My.cnf is a total mess, but I’m not really sure what affects things so have tweaked a fair amount but any suggestions would be much appreciated.

Some specs:
Sun v20z 248 opteron
16gb ram
14x36gb disk 10 array (56% remaining)

Thanks in advance guys, would be forever in your favour if someone can manage to get this working again!

2

Could You paste results of:

[B]Quote:[/B]

explain SELECT count(w.gid) FROM wishlists AS w WHERE w.gid = INTEGER
explain SELECT count(c.gid) FROM collections AS c WHERE c.gid = INTEGER
explain SELECT count(t.gid) FROM track AS t WHERE t.gid = INTEGER

(put something as INTEGER, that number should exist in column "gid") ?

As for my.cnf, You could add “log-queries-not-using-indexes” after log-slow-queries.

[B]Quote:[/B]

log-slow-queries=spori

This should be something like log-slow-queries=/var/log/mysqld/slow.log ?
3

I should have clarified those tables are basically empty, but an explain on each one always displays:

id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE w ref gid gid 4 const 1 Using index

They have ~5 rows in each table at the moment but they can increase exponetially based on user activity, but it shouldnt effect it that badly.

My current version is 5.0.27-log, which I have requested an update to, to my Server Admins maybe this will sort my issues. I must note I have not experienced this before (have never used updates with nested subqueries though) and this server has been in a production state for well over 2 years!

4

Are You sure that those two queries (update/insert) are making this error ?

You could fetch count() in three separate queries and use fetched values in update/insert queries.

You could also ask admin about RAID. Maybe there is a problem.

5

It only seems to be the UPDATE that does the error, and it happens after query number 72086 of 78,000 queries (that was my last test, it is often after less queries and often after more queries but it is ALWAYS the same amount of bytes needed.

As for the seperate queries, this is my plan and is how I got around pretty much the same problem in a different area a couple of days ago, however I would like to understand where the problem originates so I can keep away/rectify it!

From your responses though, I am guessing this is not common behavior?

I have issued a request to my server admins for the MySQL version update and will report back here if this fixes the issue (probably tommorow).