MySQL Table_Cache_Percent_Used > 90.

94% cache usage seems rather high

but you said :


seems the more it hold the faster MySQL is ? MySQL read from cache, right?

and repeat until there are no more cases where utilization goes over 90%.

why we don't want to see cache higher than 90%, this is my concern.

You either (A) have a ton of tables, or (B) a ton of connections, or (C) a lot of both, or (D) connections that aren't closing properly.

If it's A, B, or C, then all you have to do is increase your table_cache.

If it's none of those, then you'll have to try to find out what connections are staying open (e.g. usage of persistent connections in your code) and fix that.

Cache utilization is like other resources allocated on demand rather than statically.  You don't know exactly how much is needed unless the application is very well behaved and no humans are involved, so you must overprovision - but not too much.

When cache utilization goes over 90% the system is running too close to its limits.  You don't want to be running 20% utilization (much overprovisioned) but you don't want to be running 90-plus% either (the next request may demand resources that aren't available.)

And as gr8gonzo says, an investigation of the system would pay back dividends in knowing where those resources are going, and how much of them, and how long.

seems the more it hold the faster MySQL is

Sort of. Table caches speed up initial access to table, but that is just one small part of the overall performance. It's usually one of the final performance tweaks.

Dr. Klahn,

ut you don't want to be running 90-plus% either (the next request may demand resources that aren't available.)

so this means next request will say out of memory ?

and the resource keep using it is one of the problem showing why the resource never release back to MySQL? then it is more on application design issue ?

and as you said, if we don't have that information at hand, bump the cache by 10%, run in production and watch the cache utilization, and repeat until there are no more cases where utilization goes over 90%.

Using https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl will identify tunings which will effect your SQL statement performance.

So if the warning you mentioned, is a negatively effects your MySQL version, mysqltuner will likely suggest some tuning.

Hi,

I recommend (as David points out) that you periodically run mysqltuner on your server to monitor and tune the server parameter according to those results that the script returns.

Regards,
     Tomas Helgi

ok tks.

hi,

any complete guide on how to use mysqltuner.pl?

David,

1) Run this on the command line...

so it is
# ./mysqltuner.pl ?

5) Better to just use the tool, rather than following any usage guides, because mysqltuner updates occur frequently.

usually this tools is not installed by default ?
so I have to dnf mysqltuner ?
or just copy the script and save as mysqltuner.pl , then use it all the time ?

which mysqltuner will base on your MySQL variant, determined each time mysqltuner executes.

it will suggest both MySQL configuration and query level problem right ? we run it when MySQL is slow .... 

gr8gonzo ,

 (D) connections that aren't closing properly.

so for this one no need to adjust table cache ? then in order to fix it? usually must be from application side ? any URL to coach developement team how to solve it?

David Favor ,

For any usage guide to be of value, you'd  have to find a guide matching the current version of mysqltuner (all new features), then also matching the exact MySQL variant you're using.

excellent ! tks.
but based on your experience, any one-stop best practive/configuration guide for MySQL 5.7 and 8.0 ?

You also asked, "based on your experience, any one-stop best practive/configuration guide for MySQL 5.7 and 8.0 ?"

Yes.

Run mysqltuner.

The problem with guides is they really work very poorly.

As the combination of data access pattern, storage engine(s) used, data volume provide a matrix just to complex to cover in a guide.

Said another way, the starting point for writing a guide is complex + for a guide to be useful, your project would have to match exactly all the criteria use to write a guide.

This is why most guides (there are many) fail to produce good results.

Using mysqltuner provides better results... for way less time (few seconds to install + run), than any guide I've ever read.

Hi,

Every dbms system has different workload and thus needs to be tuned/configured diffrently through constant monitoring.
The recommendations from mysqltuner gives you pretty good advice that you can use.
As each workload vary from time to time you need to run mysqltuner regularly to establish your dbms workload boundaries and adjust the configuration accordingly.

Regards,
    Tomas Helgi

As the combination of data access pattern, storage engine(s) used, data volume provide a matrix just to complex to cover in a guide.

Said another way, the starting point for writing a guide is complex + for a guide to be useful, your project would have to match exactly all the criteria use to write a guide

so the guide is ONLY build upon on a specify situtation/configuration ?

For any usage guide to be of value, you'd  have to find a guide matching the current version of mysqltuner (all new features), then also matching the exact MySQL variant you're using.

what news letter to know this mysqltuner.pl has an update?

Tomas Helgi Johannsson ,'

Every dbms system has different workload and thus needs to be tuned/configured diffrently through constant monitoring.

what I am looking for is sth like MS SQL, 64k allocation unit is better, no matter what worload is.

so MySQLtuner.pl is better than slow query log: https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html ? MySQL tuner is more on real time monitoring ?

also can this MySQLtuner.pl suggest query level problem ? and how to rewrite that ?

David Favor,

for the .pl files, it will suggest query level tuning, right? in MySQL, what kind of proactive tools . other than slow query log, can we see real time slow query? show processlist ?

These tables and the slow-query log tables should give you clear view on statements running on your database.

so has to turn on slow-query log anyway ? from my point of view this kind of log show outdated information and that's why I mention show processlist, much up to date, right ?

The INFORMATION_SCHEMA and PERFORMANCE_SCHEMA tables provide you with a lot of performance and system information data both realtime and history.

what should be the query to gathering useful information from these 2 x tables ?

I strongly recommend turning on the slow-query-log and send the result to tables so that you can query that table and join with the tables in the INFORMATION_SCHEMA and PERFORMANCE_SCHEMA catalog databases.

ok slow query log anyway. any link/URL tell guide me thought this? this is a key part on MySQL query tuning I guess !

other than this, any other expert option we can use and offer more than what you said ? e.g. suggest how to tune it !

this one: ./mysqltuner.pl can't tell right ?

I will read more and come back if needed.