Skip to content
intermediate Phase 77 · Performance Fundamentals

Database Performance

45m
1 problems
Topic Progress 0%

Slow Query Log Analysis

Enable Slow Query Log

# my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_replica_statements = 1

Analyze Slow Queries

# Find top slow queries
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# Detailed analysis with pt-query-digest
pt-query-digest /var/log/mysql/slow.log > report.txt

# Check query count
grep -c 'Query_time' /var/log/mysql/slow.log

Common Magento Slow Queries

-- 1. Product listing without index
SELECT * FROM catalog_product_entity WHERE status = 1;
-- Fix: Add index on status column

-- 2. EAV query without optimization
SELECT e.*, v.* FROM catalog_product_entity e
JOIN catalog_product_entity_varchar v ON e.entity_id = v.entity_id
WHERE v.attribute_id = 71 AND v.value LIKE '%test%';
-- Fix: Use fulltext index

-- 3. Sales order with complex joins
SELECT o.*, oi.* FROM sales_order o
JOIN sales_order_item oi ON o.entity_id = oi.order_id
WHERE o.status = 'pending' AND o.created_at > '2024-01-01';
-- Fix: Add composite index

Key Points

  • Monitor slow query log regularly
  • Use pt-query-digest for analysis
  • Focus on most frequent slow queries
  • Add indexes for commonly queried columns

Index Optimization

Check Index Usage

-- Show table indexes
SHOW INDEX FROM catalog_product_entity;

-- Check index usage
SELECT * FROM sys.schema_table_statistics
WHERE table_name = 'catalog_product_entity';

-- Find unused indexes
SELECT * FROM sys.schema_unused_indexes;

-- Find missing indexes
SELECT * FROM sys.schema_redundant_indexes;

Magento Index Management

# Reindex all
bin/magento indexer:reindex

# Check index status
bin/magento indexer:status

# Specific index
bin/magento indexer:reindex catalog_product_price

Index Best Practices

-- 1. Composite indexes for multiple WHERE conditions
CREATE INDEX idx_status_created ON sales_order(status, created_at);

-- 2. Covering indexes for SELECT with specific columns
CREATE INDEX idx_sku_name ON catalog_product_entity(sku, name);

-- 3. Indexes for JOIN conditions
CREATE INDEX idx_order_id ON sales_order_item(order_id);

-- 4. Indexes for ORDER BY
CREATE INDEX idx_created_at ON sales_order(created_at);

Key Points

  • Regularly check index usage
  • Remove unused indexes
  • Add indexes for slow queries
  • Reindex after data changes

Query Caching

MySQL Query Cache

# my.cnf (deprecated in MySQL 8.0)
[mysqld]
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

Magento Query Cache

// Enable query caching
use Magento\Framework\Cache\FrontendInterface;

class QueryCache
{
    private $cache;

    public function __construct(FrontendInterface $cache)
    {
        $this->cache = $cache;
    }

    public function getCachedQuery($key, $callback, $ttl = 3600)
    {
        $cached = $this->cache->load($key);
        if ($cached) {
            return unserialize($cached);
        }

        $result = $callback();
        $this->cache->save(serialize($result), $key, [], $ttl);
        return $result;
    }
}

Redis Cache Configuration

// app/etc/env.php
return [
    'cache' => [
        'frontend' => [
            'default' => [
                'backend' => 'Magento\Framework\Cache\Backend\Redis',
                'backend_options' => [
                    'server' => '127.0.0.1',
                    'port' => '6379',
                    'database' => '0',
                ],
            ],
        ],
    ],
];

Key Points

  • Use Redis for query caching
  • Cache frequently accessed queries
  • Set appropriate TTL values
  • Monitor cache hit ratio

Connection Pooling

Connection Pool Configuration

// app/etc/env.php
return [
    'db' => [
        'connection' => [
            'default' => [
                'host' => 'localhost',
                'dbname' => 'magento',
                'username' => 'root',
                'password' => '',
                'model' => 'mysql4',
                'engine' => 'innodb',
                'initStatements' => ['SET NAMES utf8'],
                'active' => '1',
            ],
            'read' => [
                'host' => 'read-replica.example.com',
                'username' => 'readonly',
                'password' => '',
                'active' => '1',
            ],
            'write' => [
                'host' => 'primary.example.com',
                'username' => 'writeuser',
                'password' => '',
                'active' => '1',
            ],
        ],
    ],
];

Connection Monitoring

-- Show connection status
SHOW STATUS LIKE 'Threads%';

-- Show max connections
SHOW VARIABLES LIKE 'max_connections';

-- Show current connections
SHOW PROCESSLIST;

-- Kill idle connections
KILL <process_id>;

Connection Optimization

# my.cnf
[mysqld]
max_connections = 500
wait_timeout = 600
interactive_timeout = 600
thread_cache_size = 16

Key Points

  • Use read/write splitting
  • Configure connection limits properly
  • Monitor connection usage
  • Close idle connections

Practice Problems

0 / 1 solved
Optimize Slow Query

Identify and optimize a slow database query in Magento.

Solution 
// 1. Enable slow query log
slow_query_log = 1
long_query_time = 1

// 2. Analyze with EXPLAIN
EXPLAIN SELECT o.*, c.* FROM sales_order o
JOIN customer_entity c ON o.customer_id = c.entity_id
WHERE o.status = 'pending'
AND o.created_at > '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 100;

// 3. Add indexes
CREATE INDEX idx_status_created ON sales_order(status, created_at);
CREATE INDEX idx_customer_id ON sales_order(customer_id);

// 4. Optimize query
SELECT o.entity_id, o.increment_id, o.status, o.created_at,
       c.email, c.firstname, c.lastname
FROM sales_order o
JOIN customer_entity c ON o.customer_id = c.entity_id
WHERE o.status = 'pending'
AND o.created_at > '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 100;

Quiz

1. What is long_query_time?

Question 1 options

2. When should you reindex?

Question 2 options

3. What is connection pooling?

Question 3 options

4. What cache backend is recommended?

Question 4 options

Flashcards

Question

long_query_time?

Click to reveal answer

Answer

Threshold for logging slow queries

Question

Reindex when?

Click to reveal answer

Answer

After data changes

Question

Connection pooling?

Click to reveal answer

Answer

Reusing existing database connections

Question

Recommended cache?

Click to reveal answer

Answer

Redis or Memcached

Revision Notes

Key Takeaways

  • 1. Monitor slow query log for optimization opportunities
  • 2. Use indexes for frequently queried columns
  • 3. Cache queries with Redis/Memcached
  • 4. Implement read/write splitting for scaling

Interview Tips

  • • Explain how to identify slow queries
  • • Discuss index optimization strategies
  • • Know connection pooling benefits

Cheat Sheet

Database Performance

  • Slow log: long_query_time in my.cnf
  • Indexes: add for WHERE/JOIN/ORDER BY
  • Cache: Redis/Memcached
  • Connections: pooling + read/write split