MySQL Forums
Forum List  »  Memory Storage Engine

tmp_table_size ignored on x86_64 when copying to tmp table?
Posted by: Thomas Rattei
Date: January 13, 2007 04:27PM

Dear all,

we experience a very strange behaviour of our MySQL server (x86_64 Fedora Core 5, recent mysql-server package 5.0.27, dual opteron 248, 8 GB RAM).

When selecting distinct values from a very long table (400 million records) like in this example "select distinct col1 from tablename where col2 in (select col2 from othertable)" the server starts to create a temporary table which is indicated by "copying to tmp table" in the processlist. Immediately the mysql server process starts to grow in memory. It takes so much memory that it exceeds both physical and swap memory (8GB+8GB on our server). In the end the Linux kernel kills the mysqld or the whole server hangs completely.

How can this happen? Our buffers are not the problem, I even tried to set them to extremely small values. But the tmp_table_size of 128M we configured is just ignored by the server. It never switches from "copying to tmp table" to "copying to tmp table on disc" as I would expect if the temporary table gets too large.

The bad this is that every user is able to kill our mysql server by sending a similar query like in my example...

Cheers
Thomas

Options: ReplyQuote


Subject
Views
Written By
Posted
tmp_table_size ignored on x86_64 when copying to tmp table?
9822
January 13, 2007 04:27PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.