mysqltuner.pl

#11

On 08-01-11 14:04, Axel Schwenke wrote:

Quote:

Luuk <Luuk (AT) invalid (DOT) lan> wrote:
On 08-01-11 11:41, Axel Schwenke wrote:

http://dev.mysql.com/doc/refman/5.1/...ble_open_cache

This seems to be true, only 'table_open_cache' was introduced in 5.1,
and i'm still using 5.0.x

Right. The variable is called table_cache then, but the meaning is the
same. The new name was introduced because in 5.1 there are now two
table related caches: table_open_cache and table_definition_cache and
the new naming schema is more clear.

"If the value of Opened_tables is large and you do not use FLUSH TABLES
often (which just forces all tables to be closed and reopened), then you
should increase the value of the table_cache variable."

When i see this value 1311, and my table_cache is at 40960, i dont see
the need to increase that value.

Yeah. In fact it seems to be quite large already. If there were 11K
table-open operations, they could have been for at most 11K tables.
But since only 1K of your cache is used, it's pretty clear that you
never had more than said 1K tables open concurrently and that you can
in fact reduce the table_cache without adverse effects.

As Lennart said: a bad cache hit ratio not necessarily means that the
cache is too small.

However: you should check (SHOW GLOBAL VARIABLES) that MySQL indeed
uses the 40K from your my.cnf. Table cache size is related to file
descriptors. If MySQL is unable to set the file handle limit
(calculated as 2*table_cache + max_connections + 10), it will
automatically decrease table_cache and/or max_connections and write
a warning to the error log.

I believe now that indeed MySQL could not reserve enough file handles
and thus decreased table_cache to 32752. However this is still very big

IMHO it makes more sense to look at (tables_opened / uptime).

tables_opened / uptime = 11416 / 1807169 = 0.0063

Yeah. Certainly not a problem at all.

For now i will double the table_cache (from 128M to 256M)

For heavens sake, no! I suggest to decrease table cache to ~4K.

i will change tha table_cache to 4K, and look again at it in 2 or 3 days...

--
Luuk

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

mysqltuner.pl

comp.databases.mysql comp.databases.mysql

Re: mysqltuner.pl - 01-08-2011 , 09:02 AM