Mysql settings for query_cache_min_res_unit

MySQL

What is the best setting for query_cache_min_res_unit for these results:

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 35327     |
| Qcache_free_memory      | 295242976 |
| Qcache_hits             | 236913188 |
| Qcache_inserts          | 49557287  |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 7128902   |
| Qcache_queries_in_cache | 195659    |
| Qcache_total_blocks     | 426870    |
+-------------------------+-----------+

Do I need to change any other settings?

My website creates very large results. This is the current setting:

query_cache_min_res_unit = 4096

Info on the mysql dev website

If most of your queries have large results (check the Qcache_total_blocks and Qcache_queries_in_cache status variables), you can increase performance by increasing query_cache_min_res_unit. However, be careful to not make it too large (see the previous item).

Best Answer

Observations

Your question says you have 426870 for Qcache_total_blocks. Since each block is 1K, blocks are aligned to the nearest multiple of 1024. Thus, your query cache has about 417M.
If you divide Qcache_not_cached (7128902) by Qcache_inserts (49557287), this shows that about 14.4% (1 out of 7) of your queries could not be cached.
(Qcache_total_blocks (426870) - Qcache_free_blocks (35327)) / Qcache_queries_in_cache (195659) = 2K
On average, each query cache result hold about 2K, which is theoretically not possible. This could indicate some fragmentation in the query cache.

Recommendations

You need a bigger value for query_cache_size (perhaps 2G). If you are using InnoDB, you may get some performance increase if you disable the query cache altogether by setting query_cache_size to 0.
You need a smaller value for query_cache_min_res_unit (minimum value is 512 (0.5K)) if you want to hold small result sets.
You need a bigger value for query_cache_limit if you want to cap how big a result is too big.
See my earlier post : Is the overhead of frequent query cache invalidation ever worth it?

Related Solutions

Mysql – thesql fine tuning: open_tables, opened_tables

Three points:

I don't see the immediate connection between your "too many connections" and the table cache. There might be such connection, but you have not provided info which suggests the two relate.
I disagree with the advice about "If Open_tables climbs above (Opened_tables / Uptime), I have a cause for concern.."
You haven't listed the table_open_cache & table_definition_cache settings. The table_cache variable is an old one.

Some suggestions:

With regard "too many connections", try the following: Set max_connections to some reasonable value, like 300, throwing around a number. Set open_files_limit to a very high values, like 8192. By default you are only allowed 1024 open files on linux. Sockets, threads, table descriptors -- all map to files. Do you have some connection pool which holds many open (idle) connections, with some keepalive? How many processes do you get on a typical SHOW PROCESSLIST?
With regard table cache: I suggest set a high value. With 3,000 queries per hour you're not remotely near the problem of having a problem with that. Why don't you set: table_open_cache=2048 table_definition_cache=512 (even though you may only have a couple tables)
With regard the advice about open tables, you should be more interested in the number of tables opened per second (or minute, whatever) in any given time period, rather than looking at the overall number of tables opened since the server started to run. So you should monitor your opened_tables status variable every few minutes, and do the math. You would typically want to see less than 1 table opened in a second. Again, I'm throwing numbers around, but just to give you proportion. The open_tables is nice to know, as well. On good usage it can be very high, and that means your server is actively reading from tables. Fine! As long as the number of opened_tables/sec does not rise too high, this is no worry.
There could be other reasons for "too many connections". Are the queries very long? Such that they just accumulate? Are some background tasks failing to complete? (You do have a wait_timeout=300 which is good; however some PHP job might be issuing new queries every few seconds, failing to close properly?
Noting again: watch over SHOW PROCESSLIST and see what comes up.

MySQL – Difference Between Show Status and Show Global Status

There is most definitely a difference between SHOW STATUS; and SHOW GLOBAL STATUS;

SHOW GLOBAL STATUS; will give you status variables that have updated since mysqld started for all sessions that are connected or have ever been connected.
SHOW STATUS; will give you status variables that have updated within your session. The command can also be expressed as SHOW SESSION STATUS; (As the MySQL Documentation says, it displays the status values for the current connection).

To physically show the difference, the information_schema database has them separated as

INFORMATION_SCHEMA.GLOBAL_STATUS
INFORMATION_SCHEMA.SESSION_STATUS

These information_schema tables have been around since MySQL 5.1.12.

Why the difference?

To thoroughly demonstrate the difference, let me run an INNER JOIN of these tables to show which values are different. Here is the query:

SELECT a.variable_name,a.variable_value,b.variable_value
FROM information_schema.global_status A INNER join information_schema.session_status B
USING (variable_name) WHERE A.variable_value <> B.variable_value;

Please note the output:

mysql> SELECT A.variable_name,A.variable_value,B.variable_value
    -> FROM information_schema.global_status A INNER join information_schema.session_status B
    -> USING (variable_name) WHERE A.variable_value <> B.variable_value;
+---------------------------+----------------+----------------+
| variable_name             | variable_value | variable_value |
+---------------------------+----------------+----------------+
| BYTES_RECEIVED            | 123641576598   | 7757           |
| BYTES_SENT                | 149888451047   | 300001         |
| COM_ADMIN_COMMANDS        | 121915         | 0              |
| COM_ALTER_TABLE           | 111            | 0              |
| COM_BEGIN                 | 1              | 0              |
| COM_CALL_PROCEDURE        | 530            | 0              |
| COM_CHANGE_DB             | 1623           | 1              |
| COM_COMMIT                | 19220          | 0              |
| COM_CREATE_FUNCTION       | 4              | 0              |
| COM_CREATE_PROCEDURE      | 126            | 0              |
| COM_CREATE_TABLE          | 354            | 0              |
| COM_DEALLOC_SQL           | 924            | 0              |
| COM_DELETE                | 64668          | 0              |
| COM_DELETE_MULTI          | 19             | 0              |
| COM_DROP_FUNCTION         | 4              | 0              |
| COM_DROP_PROCEDURE        | 148            | 0              |
| COM_DROP_TABLE            | 238            | 0              |
| COM_EXECUTE_SQL           | 945            | 0              |
| COM_INSERT                | 1182379        | 0              |
| COM_INSERT_SELECT         | 40673          | 0              |
| COM_KILL                  | 68             | 0              |
| COM_LOAD                  | 22386          | 0              |
| COM_LOCK_TABLES           | 2              | 0              |
| COM_OPTIMIZE              | 2              | 0              |
| COM_PREPARE_SQL           | 948            | 0              |
| COM_REPAIR                | 8              | 0              |
| COM_REPLACE               | 34737          | 0              |
| COM_ROLLBACK              | 13             | 0              |
| COM_SELECT                | 1107225018     | 65             |
| COM_SET_OPTION            | 602159         | 0              |
| COM_SHOW_BINLOGS          | 8              | 0              |
| COM_SHOW_CHARSETS         | 12             | 0              |
| COM_SHOW_COLLATIONS       | 100            | 0              |
| COM_SHOW_CREATE_DB        | 6              | 0              |
| COM_SHOW_CREATE_FUNC      | 2453           | 0              |
| COM_SHOW_CREATE_PROC      | 5684           | 0              |
| COM_SHOW_CREATE_TABLE     | 1313           | 0              |
| COM_SHOW_DATABASES        | 275            | 0              |
| COM_SHOW_EVENTS           | 1              | 0              |
| COM_SHOW_FIELDS           | 13666          | 1              |
| COM_SHOW_FUNCTION_STATUS  | 362            | 0              |
| COM_SHOW_KEYS             | 494            | 0              |
| COM_SHOW_PLUGINS          | 2              | 0              |
| COM_SHOW_PROCEDURE_STATUS | 361            | 0              |
| COM_SHOW_PROCESSLIST      | 488943         | 15             |
| COM_SHOW_SLAVE_STATUS     | 4              | 0              |
| COM_SHOW_STATUS           | 12315          | 10             |
| COM_SHOW_STORAGE_ENGINES  | 30             | 0              |
| COM_SHOW_TABLE_STATUS     | 320            | 0              |
| COM_SHOW_TABLES           | 584            | 0              |
| COM_SHOW_TRIGGERS         | 2              | 0              |
| COM_SHOW_VARIABLES        | 190            | 1              |
| COM_STMT_CLOSE            | 924            | 0              |
| COM_STMT_EXECUTE          | 945            | 0              |
| COM_STMT_PREPARE          | 948            | 0              |
| COM_TRUNCATE              | 522            | 0              |
| COM_UNLOCK_TABLES         | 2              | 0              |
| COM_UPDATE                | 496041         | 0              |
| COM_UPDATE_MULTI          | 625            | 0              |
| CREATED_TMP_DISK_TABLES   | 16772          | 40             |
| CREATED_TMP_TABLES        | 34336          | 63             |
| HANDLER_COMMIT            | 1109540769     | 0              |
| HANDLER_DELETE            | 12775993       | 0              |
| HANDLER_EXTERNAL_LOCK     | 2228108102     | 24             |
| HANDLER_PREPARE           | 2155764        | 0              |
| HANDLER_READ_FIRST        | 23586          | 9              |
| HANDLER_READ_KEY          | 18285349400    | 0              |
| HANDLER_READ_LAST         | 13000          | 0              |
| HANDLER_READ_NEXT         | 72142303428    | 0              |
| HANDLER_READ_PREV         | 3000146        | 0              |
| HANDLER_READ_RND          | 1261418742     | 156            |
| HANDLER_READ_RND_NEXT     | 12320861765    | 7845           |
| HANDLER_ROLLBACK          | 269376         | 0              |
| HANDLER_UPDATE            | 2596924399     | 0              |
| HANDLER_WRITE             | 8200421074     | 8241           |
| LAST_QUERY_COST           | 0.000000       | 21.399123      |
| LAST_QUERY_PARTIAL_PLANS  | 0              | 3              |
| OPENED_TABLE_DEFINITIONS  | 2482           | 0              |
| OPENED_TABLES             | 3619           | 0              |
| QUESTIONS                 | 1110214247     | 97             |
| SELECT_FULL_JOIN          | 615            | 9              |
| SELECT_RANGE              | 243635         | 0              |
| SELECT_SCAN               | 47851          | 53             |
| SLOW_QUERIES              | 29290          | 50             |
| SORT_MERGE_PASSES         | 6              | 0              |
| SORT_RANGE                | 179956         | 0              |
| SORT_ROWS                 | 321609927      | 156            |
| SORT_SCAN                 | 1829           | 39             |
| TABLE_OPEN_CACHE_HITS     | 1109365721     | 13             |
| TABLE_OPEN_CACHE_MISSES   | 1669           | 0              |
+---------------------------+----------------+----------------+
90 rows in set (0.03 sec)

mysql>

Look at four variables:

| BYTES_RECEIVED            | 123641576598   | 7757           |
| BYTES_SENT                | 149888451047   | 300001         |
| COM_ADMIN_COMMANDS        | 121915         | 0              |
| COM_SHOW_PROCESSLIST      | 488955         | 15             |

What does this tell you?

mysqld received 123,641,576,598 bytes (115.15GB) from all DB Connections since mysqld started
The session I ran the query with received 7,757 bytes (a little over 7K) in my current session
mysqld sent 149,888,451,047 bytes (139.59GB) from all DB Connections since mysqld started
The session I ran the query with sent 300,001 bytes (a little under 297K) in my current session
There have been 121,915 administrative commands that have run since mysqld started
There have benn 0 administrative commands that have run in my current session
The command SHOW PROCESSLIST has been run 488,955 times since mysqld started
The command SHOW PROCESSLIST has been run 15 times in my current session

You can compare the other 86 varibales and interpret them the same way.