Skip to main content
edited title
Link
RolandoMySQLDBA
  • 184.4k
  • 33
  • 323
  • 531

query_cache tunningtuning for a database server to keep db from slowing

added 625 characters in body
Source Link
Quantum
  • 115
  • 4

retuned

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| have_query_cache             | YES       |
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 524288000 |
| query_cache_type             | DEMAND    |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
6 rows in set (0.00 sec)

to see if that is better

retuned

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| have_query_cache             | YES       |
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 524288000 |
| query_cache_type             | DEMAND    |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
6 rows in set (0.00 sec)

to see if that is better

new info as requested
Source Link
Quantum
  • 115
  • 4

UDPADTED INFO

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| have_query_cache             | YES       |
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 268435456 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
6 rows in set (0.00 sec)

mysql> SHOW STATUS LIKE '%qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 5         |
| Qcache_free_memory      | 264569784 |
| Qcache_hits             | 10826     |
| Qcache_inserts          | 2706      |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 260       |
| Qcache_queries_in_cache | 1508      |
| Qcache_total_blocks     | 3118      |
+-------------------------+-----------+
8 rows in set (0.00 sec)

mysql> show status like 'qcache_hits';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 11027 |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW global status LIKE '%com_select%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 2987  |
+---------------+-------+
1 row in set (0.00 sec)

So the current ratio is 0.786% .. still ok?

UDPADTED INFO

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| have_query_cache             | YES       |
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 268435456 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
6 rows in set (0.00 sec)

mysql> SHOW STATUS LIKE '%qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 5         |
| Qcache_free_memory      | 264569784 |
| Qcache_hits             | 10826     |
| Qcache_inserts          | 2706      |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 260       |
| Qcache_queries_in_cache | 1508      |
| Qcache_total_blocks     | 3118      |
+-------------------------+-----------+
8 rows in set (0.00 sec)

mysql> show status like 'qcache_hits';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 11027 |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW global status LIKE '%com_select%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 2987  |
+---------------+-------+
1 row in set (0.00 sec)

So the current ratio is 0.786% .. still ok?

added 418 characters in body
Source Link
Quantum
  • 115
  • 4
Loading
more complete picture
Source Link
Quantum
  • 115
  • 4
Loading
more complete picture
Source Link
Quantum
  • 115
  • 4
Loading
added 681 characters in body
Source Link
Quantum
  • 115
  • 4
Loading
Source Link
Quantum
  • 115
  • 4
Loading