Page 1 of 1

Serious MySQL loading problem

Posted: Mon Jun 14, 2004 3:43 pm
by Unipus
We moved to a new server last week and since then we're having some serious problems. The database has been slowing to a halt, giving "too many connection" errors (for which we simply increased the max_connections and that's solved that). One thing I have noticed is that mysqladmin status is showing LOTS of open tables. And it keeps growing. Apparently these tables are never being closed. The underlying code has not really changed. I have added some pages that require MORE queries but watching mytop, these queries don't appear to be the problem at all. I've disabled one particularly nasty query that was causing lots of trouble (running updates on potentially hundreds of tables) but this has only slowed the problem, not eliminated it.

We did upgrade to 4.0.20 with the server switch. This is on Linux Redhat ES 3.0.

So I guess my first question is: why would tables be opened and then never closed?

Posted: Mon Jun 14, 2004 4:05 pm
by feyd
using pconnect?

Posted: Mon Jun 14, 2004 4:15 pm
by Unipus
We were previously using pconnect. After the crashes started our IT guy thought that might have been the trouble and switched it to non-persistent. It doesn't seem to make a difference.

Posted: Mon Jun 14, 2004 4:16 pm
by Unipus
Here's a recent status report:

Uptime: 5355 Threads: 1 Questions: 2088068 Slow queries: 1 Opens: 1600 Flush tables: 2 Open tables: 471 Queries per second avg: 389.929

Posted: Mon Jun 14, 2004 5:13 pm
by feyd
cricky..

Posted: Mon Jun 14, 2004 6:06 pm
by Unipus
Umm?

Posted: Mon Jun 14, 2004 6:12 pm
by Weirdan
does FLUSH TABLES sql statement reduce the number of open tables?

Posted: Mon Jun 14, 2004 6:13 pm
by Unipus
Yes, at which point they immediately begin to build up again. Right now we've achieved equilibrium because our purges are taking care of the excessive open tables, but I'm still curious about how this is working.

Posted: Mon Jun 14, 2004 6:19 pm
by Weirdan
what does

Code: Select all

show variables like 'table_cache%'
show?

Posted: Mon Jun 14, 2004 6:37 pm
by Unipus
table_cache is 512. It was just changed this morning from 64.