I'm using mysqltuner.pl for some time now, and i'm confused by the
output of it, can someone give some more insight?

There is: "Variables to adjust: table_cache (> 32752)"
which contradicts (to me) with:
"[!!] Table cache hit rate: 11% (1K open / 11K opened)"

I whish to say thanks if you have a nice comment about this...


== START OF OUTPUT ==
-------- General Statistics
--------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.67-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics
-------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 1020)
[--] Data in InnoDB tables: 8M (Tables: 135)
[--] Data in MEMORY tables: 0B (Tables: 7)
[OK] Total fragmented tables: 0

-------- Performance Metrics
-------------------------------------------------
[--] Up for: 20d 13h 13m 36s (564K q [0.318 qps], 27K conn, TX: 6B, RX:
203M)
[--] Reads / Writes: 73% / 27%
[--] Total buffers: 706.0M global + 47.5M per thread (20 max threads)
[OK] Maximum possible memory usage: 1.6G (42% of installed RAM)
[OK] Slow queries: 1% (8K/564K)
[OK] Highest usage of available connections: 20% (4/20)
[OK] Key buffer size / total MyISAM indexes: 512.0M/1.4G
[OK] Key buffer hit rate: 99.6% (7M cached / 26K reads)
[OK] Query cache efficiency: 74.2% (293K cached / 395K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 30K sorts)
[OK] Temporary tables created on disk: 14% (11K on disk / 82K total)
[OK] Thread cache hit rate: 99% (4 created / 27K connections)
[!!] Table cache hit rate: 11% (1K open / 11K opened)
[OK] Open file limit used: 3% (2K/65K)
[OK] Table locks acquired immediately: 99% (469K immediate / 469K locks)
[OK] InnoDB data size / buffer pool: 8.7M/32.0M

-------- Recommendations
-----------------------------------------------------
General recommendations:
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
table_cache (> 32752)
== END OF OUTPUT ==

--
Luuk

On 2011-01-08 11:00, Luuk wrote:
My interpretation of the output [1], is that 11% (poor hit ratio) of the
data is found in the cache. If you increase the size of the cache (above
32752), the hit ratio will probably increase. That said, a poor hit
ratio is not necessary due to an insufficient size of the cache. If you
are doing table scans in your queries, you will effectively lower the
hit ratio.

/Lennart

[1] I have never used the tool, and my knowledge regarding tuning of
mysql is very limited.

On 08-01-11 11:27, Lennart Jonsson wrote:
If i have in my.cnf 'query_cache_size = 128M', and only 11% of it is
used, it might be that more will be used when i increase the value

But if the poor ratio is because of tablescans, i might as well change
the value to 14M or 15M (11% of 128M), which should give the same
performance as now, with less memory needs?

--
Luuk

Luuk <Luuk (AT) invalid (DOT) lan> wrote:
Where do you see a contradiction? I'm not sure what "(> 32752)"
is trying to tell us, but

[!!] Table cache hit rate: 11% (1K open / 11K opened)

is pretty clear. Seems you have 1K tables open (this would then
be your current value for table_open_cache) but MySQL had to do
11K table-open operations (in the last UPTIME seconds). Increasing
table_open_cache could reduce the number of those, rather costly,
operations.

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

IMHO it makes more sense to look at (tables_opened / uptime).
Unless you have >1 table-open op per second, this is not a
problem. Common values are even lower, below 1/min. But this
depends on your data model and usage pattern.


XL

On 08-01-11 11:41, Axel Schwenke wrote:
This seems to be true, only 'table_open_cache' was introduced in 5.1,
and i'm still using 5.0.x

opensuse:/etc # mysqladmin extended-status | grep -i open

When looking at:
http://dev.mysql.com/doc/refman/5.0/...ar_table_cache
it says:
"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.



tables_opened / uptime = 11416 / 1807169 = 0.0063

;-)


For now i will double the table_cache (from 128M to 256M) and look again
after the weekend..


--
Luuk

On 08-01-11 11:39, Luuk wrote:
I might be confusing 'table_cache' (40960)
with 'query_cache_size' (128M) here.

I dont see the need to increase the 'table_cache' see my reply to Axel.

--
Luuk

Luuk <Luuk (AT) invalid (DOT) lan> wrote:
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.

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

Yeah. Certainly not a problem at all.

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


XL

On 2011-01-08 12:37, Luuk wrote:
[...]
And I confused table_cache with bufferpool, you can safely ignore my
previous post :-)

[...]

On 08-01-11 14:04, Axel Schwenke wrote:
indeed, its 32752 according to 'SHOW GLOBAL VARIABLES'


Oops, i should have delete that sentence, but it happened when i found
out that i had a mix up between 'table_cache' and 'query_cache_size'....


--
Luuk

On 08-01-11 15:56, Luuk wrote:
en no mention of it in 'error.log'.....


--
Luuk