Re: my.cnf file with mysql tuner

[Rick James]

> [!!] Total fragmented tables: 179
> Run OPTIMIZE TABLE to defragment tables for better performance

Ignore

> [OK] Highest usage of available connections: 1% (21/2000)

max_connections = 2000 is unreasonable; consider lowering it.

> [!!] Query cache prunes per day: 208318

Perhaps the Query cache should be turned off. Or at least decrease the size, so that pruning won't be as costly. (I recommend 50M, not 128M.)

> [!!] Joins performed without indexes: 18586
> [!!] Temporary tables created on disk: 31% (316K on disk / 1M total)
> Adjust your join queries to always utilize indexes

Let's see some of the slow queries.

> table_open_cache = 3000
> [--] Data in InnoDB tables: 1G (Tables: 3076)
> Increase table_cache gradually to avoid file descriptor limits

Instead, let's look into why there are so many tables.
(Note: That tool is old; "table_cache" was renamed "table_open_cache" in 2005 - ver 5.1.3.)
The table cache is inefficient when it is large. The only fix I have heard of came with 5.6.8 - table_open_cache_instances.

> log_queries_not_using_indexes = 1
> long_query_time= 0.1

In my opinion, the not-using-indexes unnecessarily clutters the slowlog. A one-row (or even 100-row) table is not going to run noticeably faster with an index. Simply look at slow queries and verify whether there are good indexes.

> [!!] Temporary tables created on disk: 31% (316K on disk / 1M total)

Let's see some of them. Note that TEXT and BLOB force temp tables to go to disk, as do several other things. Using "SELECT *" may be grabbing a TEXT or BLOB that you don't then use. TINYTEXT may as well be replaced by VARCHAR(255). Etc.

> query_cache_size (> 128M)
> join_buffer_size (> 1.0M, or always use indexes with joins)
> tmp_table_size (> 128M)
> max_heap_table_size (> 128M)
> table_cache (> 3000)

I disagree with all of those suggestions; I have explained some of them. (Yes, there are rare cases where their advice is better than mine.)

Back to your question...
> I have been receiving numerous "down server" warnings

Where do you see that message?
Almost nothing my.cnf (and nothing in your my.cnf) can cause mysqld to shutdown. Perhaps some _really_ excessive memory usage (which you are not doing) could cause mysqld not to start, or to crash.

If you would like further my.cnf critique, please provide
SHOW VARIABLES;
SHOW GLOBAL STATUS;

Use pt_query_digest to find the worst queries in the slowlog; let's discuss a couple of them.