From: Date: September 13 2001 4:16am Subject: MySQL getting backlogged List-Archive: http://lists.mysql.com/mysql/85386 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Right now MySQL is getting really backlogged but I have no idea why. There are 169 concurrently running queries, and 153 of them are in show the status as "Opening tables". A processlist looks something like this (formatted by Status, Seconds query has been running, and query): Opening tables 15 SELECT users.username, user... Opening tables 15 SELECT users.username, user... Opening tables 15 SELECT users.username, user... Opening tables 13 SELECT users.username, da_i... Opening tables 13 SELECT users.username, user... Opening tables 13 SELECT users.username, user... Opening tables 13 SELECT users.username, user... Opening tables 13 SELECT users.username, user... Opening tables 9 SELECT users.username, user... Opening tables 8 SELECT users.username, user... Opening tables 8 SELECT users.username, user... Opening tables 8 SELECT users.username, user... Opening tables 8 SELECT users.username, user... Opening tables 8 SELECT users.username, user... The status line from the mysql client looks like this: Threads: 190 Questions: 9272 Slow queries: 311 Opens: 868 Flush tables: 1 Open tables: 862 Queries per second avg: 31.753 Threads: 204 Questions: 11381 Slow queries: 311 Opens: 871 Flush tables: 1 Open tables: 865 Queries per second avg: 30.349 What I don't get is why all those queries are at Opening tables. My table_cache is currently set to 2048. It's a FreeBSD 3.4-RELEASE box. $ sysctl kern.maxfilesperproc kern.maxfilesperproc: 16424 I just reloaded the processlist and now half of the queries are in "closing tables" state (with running times from 16 to 48 seconds) and the other half are in "Opening tables" state, with running times all at 1 second. Why are threads closing tables if the table cache isn't even full?