Re: Server just hanging (Copying to tmp table)

[Shawn Hogan]

I've been having what appears to be the exact same problem, this morning. It's a relatively moderate traffic DB server. I have 1000 MySQL connections setup on my DB server and the process list was showing 1 query copying to tmp table (for 16834 seconds) and 999 that were locked.

I'm thinking it might be a platform/OS issue because I'm running the same OS and a similar hardware setup. I'm running a dual 2.3Ghz G5 (Xserve) with 5GB of RAM, hardware RAID-5 and Mac OS X Server 10.3.8 with MySQL 4.1.14

This was the status when I reset the server:

Uptime: 209289 Threads: 1000 Questions: 18954914 Slow queries: 6919 Opens: 89696 Flush tables: 1 Open tables: 1912 Queries per second avg: 90.568

The thing that seems odd to be is the Open tables. Seems strange to have 1,912 tables open, when there are only 136 tables actual tables on the server.

Even now it hasn't been up that long since I restarted it, and I'm showing 280 open tables:

Uptime: 6044 Threads: 26 Questions: 1358719 Slow queries: 3 Opens: 330 Flush tables: 1 Open tables: 280 Queries per second avg: 224.805

This has been a problem since it started as a MySQL db server in February, but it's getting worse as it serves more queries (it's to the point that I need to restart MySQL every few days).