Serious MySQL loading problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

Serious MySQL loading problem

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

using pconnect?
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

Post 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.
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

cricky..
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

Post by Unipus »

Umm?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

does FLUSH TABLES sql statement reduce the number of open tables?
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

what does

Code: Select all

show variables like 'table_cache%'
show?
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

Post by Unipus »

table_cache is 512. It was just changed this morning from 64.
Post Reply