Re: Server just hanging (Copying to tmp table)

[Dmitriy Kropivnitskiy]

I just want to report that I am having exactly the same problem (tables stuck in the "Copying to tmp table" position. I am running MySQL 5.0.18 on Debian linux (testing branch). I have read this thread, and I see you haven't found a solution yet (if you did PLEASE!!! tell me). The queries I have are SELECTs with UNION. I didn't yet try to flush tables and I do have a hard limit of 1024 open files set (trying to find where is it getting set to change it). On of the things that seem to be prolonging the life of my server so far is the fact that I brought my settings from

read_rnd_buffer = 16M
sort_buffer = 8M
join_buffer = 8M
read_buffer = 2M
table_cache = 5000

to

read_rnd_buffer = 2M
sort_buffer = 4M
join_buffer = 2M
read_buffer = 2M
table_cache = 512

Now the server has been up (under close to peak loads) for 3 hours and still holding. If it gives out I will try increasing open files ulimit (as well as use lsof to find out if this actually is causing the problem).