-- Check status
SHOW VARIABLES LIKE 'query_cache%';

-- Enable
SET GLOBAL query_cache_type = ON;
SET GLOBAL query_cache_size = 268435456;  -- 256MB

-- Permanent (my.cnf)
[mysqld]
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 4M
query_cache_min_res_unit = 4096

[mysqld]
# Enable/disable query cache
query_cache_type = ON  # 0=OFF, 1=ON, 2=DEMAND

# Total size untuk query cache
query_cache_size = 256M

# Maximum size untuk individual cached result
query_cache_limit = 4M

# Minimum block size (alokasi memory)
query_cache_min_res_unit = 4096

# Allocate entire blocks
query_cache_alloc_block_size = 8192

-- Type 1: Cache ALL queries (kecuali yang dimulai dengan SELECT SQL_NO_CACHE)
SET GLOBAL query_cache_type = 1;

-- Type 0: Don't cache any queries
SET GLOBAL query_cache_type = 0;

-- Type 2: Only cache queries dengan SELECT SQL_CACHE
SET GLOBAL query_cache_type = 2;

-- Query example dengan DEMAND mode
SELECT SQL_CACHE * FROM users WHERE id = 123;
SELECT SQL_NO_CACHE * FROM users WHERE id = 123;

SHOW STATUS LIKE 'Qcache%';

-- Key metrics:
-- Qcache_queries_in_cache: Number queries dalam cache
-- Qcache_hits: Number cache hits (good)
-- Qcache_inserts: Number queries dimasukkan ke cache
-- Qcache_not_cached: Number queries yang tidak di-cache
-- Qcache_lowmem_prunes: Number queries dihapus karena low memory

-- Calculate hit rate
SELECT 
    ROUND(Qcache_hits / (Qcache_hits + Qcache_inserts + Qcache_not_cached) * 100, 2) 
    AS hit_rate_percent,
    Qcache_hits,
    Qcache_inserts,
    Qcache_lowmem_prunes
FROM (
    SELECT 
        SUM(CASE WHEN VARIABLE_NAME = 'Qcache_hits' THEN VARIABLE_VALUE END) as Qcache_hits,
        SUM(CASE WHEN VARIABLE_NAME = 'Qcache_inserts' THEN VARIABLE_VALUE END) as Qcache_inserts,
        SUM(CASE WHEN VARIABLE_NAME = 'Qcache_not_cached' THEN VARIABLE_VALUE END) as Qcache_not_cached,
        SUM(CASE WHEN VARIABLE_NAME = 'Qcache_lowmem_prunes' THEN VARIABLE_VALUE END) as Qcache_lowmem_prunes
    FROM information_schema.global_status
    WHERE VARIABLE_NAME LIKE 'Qcache%'
) stats;

-- Comprehensive analysis
SELECT 
    VARIABLE_NAME,
    VARIABLE_VALUE,
    CASE 
        WHEN VARIABLE_NAME = 'Qcache_hits' THEN 'Cache hits - higher is better'
        WHEN VARIABLE_NAME = 'Qcache_inserts' THEN 'Queries added to cache'
        WHEN VARIABLE_NAME = 'Qcache_not_cached' THEN 'Queries not cached - investigate'
        WHEN VARIABLE_NAME = 'Qcache_lowmem_prunes' THEN 'Evicted due to low memory - increase size if high'
        WHEN VARIABLE_NAME = 'Qcache_queries_in_cache' THEN 'Current cached queries'
        ELSE 'Other metric'
    END as description
FROM information_schema.global_status
WHERE VARIABLE_NAME LIKE 'Qcache%'
ORDER BY VARIABLE_NAME;

-- Example: cache cleared untuk users table
SELECT * FROM users WHERE status = 'active';  -- Cached

-- Any modification clears cache untuk users
UPDATE users SET last_login = NOW() WHERE id = 1;

-- Query tidak lagi cached
SELECT * FROM users WHERE status = 'active';  -- Re-executed

-- Solusi: Disable cache untuk write-heavy tables dengan SQL_NO_CACHE
SELECT SQL_NO_CACHE * FROM logs ORDER BY created_at DESC LIMIT 100;

-- Atau set query_cache_type = 2 (DEMAND) dan hanya cache read-heavy tables

-- ✅ Read-heavy, write-light tables
-- ✅ Static lookup tables
-- ✅ Configuration tables
-- ✅ Product catalogs (jika tidak sering update)
-- ✅ Report tables yang di-generate periodically

-- Example: Static configuration
SELECT SQL_CACHE * FROM app_config WHERE config_key = 'theme';

-- ❌ Write-heavy tables
-- ❌ Large result sets (> query_cache_limit)
-- ❌ Queries dengan functions like NOW(), RAND()
-- ❌ Queries dengan user variables
-- ❌ Tables yang sering di-update

-- Query dengan function tidak di-cache
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL 1 DAY;

-- Large result set
SELECT * FROM large_table;  -- Jika > query_cache_limit

# Redis example
import redis
import json

r = redis.Redis(host='localhost', port=6379, db=0)

# Cache query result
def get_user(user_id):
    # Check cache
    cached = r.get(f'user:{user_id}')
    if cached:
        return json.loads(cached)
    
    # Query database
    user = db.query(f"SELECT * FROM users WHERE id = {user_id}")
    
    # Cache untuk 5 menit
    r.setex(f'user:{user_id}', 300, json.dumps(user))
    
    return user

-- MySQL 8.0 dengan HeatWave untuk analytics acceleration
-- Bukan query cache, tapi in-memory analytics accelerator

-- Untuk MariaDB 10.2+
INSTALL SONAME 'query_cache_info';

-- Cek cache status dengan plugin
SELECT * FROM information_schema.QUERY_CACHE_INFO;

[mysqld]
# Untuk server dengan mixed workload
query_cache_type = 2  # DEMAND mode - cache hanya SQL_CACHE queries
query_cache_size = 64M  # Conservative size
query_cache_limit = 2M  # Don't cache large results
query_cache_min_res_unit = 2048

[mysqld]
# Untuk read-heavy server
query_cache_type = 1  # Cache semua queries
query_cache_size = 512M  # Large cache
query_cache_limit = 8M  # Cache larger results
query_cache_min_res_unit = 4096

[mysqld]
# Untuk write-heavy atau MySQL 8.0+
query_cache_type = 0
query_cache_size = 0

#!/bin/bash
# query_cache_monitor.sh

# Get metrics
METRICS=$(mysql -e "
SELECT 
    SUM(CASE WHEN VARIABLE_NAME = 'Qcache_hits' THEN VARIABLE_VALUE END) as hits,
    SUM(CASE WHEN VARIABLE_NAME = 'Qcache_inserts' THEN VARIABLE_VALUE END) as inserts,
    SUM(CASE WHEN VARIABLE_NAME = 'Qcache_lowmem_prunes' THEN VARIABLE_VALUE END) as prunes,
    SUM(CASE WHEN VARIABLE_NAME = 'Qcache_queries_in_cache' THEN VARIABLE_VALUE END) as cached
FROM information_schema.global_status
WHERE VARIABLE_NAME LIKE 'Qcache%';
")

# Calculate hit rate
HITS=$(echo "$METRICS" | awk 'NR==2{print $1}')
INSERTS=$(echo "$METRICS" | awk 'NR==2{print $2}')
PRUNES=$(echo "$METRICS" | awk 'NR==2{print $3}')

if [ $HITS -gt 0 ] && [ $INSERTS -gt 0 ]; then
    HIT_RATE=$(echo "scale=2; $HITS / ($HITS + $INSERTS) * 100" | bc)
    echo "Query Cache Hit Rate: ${HIT_RATE}%"
    
    if [ $PRUNES -gt 100 ]; then
        echo "WARNING: High number of lowmem prunes: $PRUNES"
        echo "Consider increasing query_cache_size"
    fi
fi

-- Check if enabled
SHOW VARIABLES LIKE 'query_cache%';

-- Check query cache status
SHOW STATUS LIKE 'Qcache%';

-- Common issues:
-- 1. query_cache_size = 0
-- 2. query_cache_type = OFF
-- 3. Query contains non-deterministic functions
-- 4. Table has been modified (cache cleared)

-- Analyze cache usage
SELECT 
    SUM(CASE WHEN VARIABLE_NAME = 'Qcache_hits' THEN VARIABLE_VALUE END) as hits,
    SUM(CASE WHEN VARIABLE_NAME = 'Qcache_inserts' THEN VARIABLE_VALUE END) as inserts,
    SUM(CASE WHEN VARIABLE_NAME = 'Qcache_not_cached' THEN VARIABLE_VALUE END) as not_cached
FROM information_schema.global_status
WHERE VARIABLE_NAME LIKE 'Qcache%';

-- If not_cached high, queries tidak eligible untuk cache
-- Check: user variables, functions, temporary tables

-- Check free blocks
SHOW STATUS LIKE 'Qcache_free_blocks';

-- If high, defragment dengan FLUSH
FLUSH QUERY CACHE;

-- Atau restart MySQL untuk defragment complete