Re: Server just hanging (Copying to tmp table)

[Daniel Nichter]

An interesting idea. Though, if open files were the problem, you'd think the status of the thread would be "Copying to tmp table on disk" instead. Perhaps MySQL is switching from a memory table to a disk table, can't get the file handle, then freezes and so the thread status doesn't get updated. For this to be the case, table_cache would have to be set to ulimit -n for the system and be at 100% usage. In my case it was I believe (I know it was set to 1028 for some weird reason, and I know the server hit 100% table cache usage). Thomas and Shawn: Is this true for your servers too?

In Thomas's last post of a mysqlreport report, his table_cache was only 1,000 though, and the problem still occurred (Open 1.00k of 1000 %Cache: 100.00); that would seem to dismiss or change the theory. Perhaps it's just a general problem with 100% table cache usage and temp tables, though I don't see how the two are related if not by open file limits and disk tmp tables.