MySQL Forums
Forum List  »  MyISAM

Re: Optimize Table and Flush Table contention on different databases
Posted by: Gabe Tucker
Date: December 09, 2009 12:56PM

Aftab,

Daily, we run a mysqlhotcopy on the server for all of the databases [about 3100]. This of course runs the FLUSH TABLES command. Every two weeks I execute mysqladmin extended on the server. Here is what I have for Opened_tables:
on 11.24 --> 696988
on 12.08 --> 3705047

I upgraded the server from 4.0.24 to 5.0.51a-log on 11.21. That's why I have the stats starting from 11.24.

The table_cache is set to 1024.

The FLUSH TABLES command is coming from mysqlhotcopy. I am using these switches: --noindices --allowold.

In my tests, I would just execute the FLUSH TABLE command on about 10 tables. I was not running FLUSH TABLES without any tables.

I restarted my test instance before running the tests again. After the re-start, I am not able to recreate the problem.

Does this imply that the fix was to "empty" the table cache? All memory/cache is freed on the restart of the instance. How could I know exactly what changed to correct the problem?

Restarting the instance to correct the problem is not an option. As you see, Opened_tables grows quickly. I would have to restart the instance almost daily. Is there another way to "reset" the table_cache or whatever it is I need reset? I know that the table_cache is by connection. Would just increasing this from the 1024 to some large value correct the problem?

What is also interesting to note is that the process of running OPTIMIZE TABLE while the mysqlhotcopy is running is not new. However, the contention started AFTER I upgraded to 5.0.51a-log. And, I did not have table_cache set to 1024, it was at the default [which I believe is 64].

Again, thanks for your assistance.

Regards,
Gabe

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Optimize Table and Flush Table contention on different databases
3081
December 09, 2009 12:56PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.