Database Debugging

Enable Magento Query Logging

// app/etc/env.php
return [
    'db' => [
        'logger' => [
            'enabled' => true,
            'log-file' => 'var/log/db.log',
            'types' => ['\Magento\Framework\DB\Logger\File'],
        ],
    ],
];

MySQL Query Log

# my.cnf / my.ini
[mysqld]
general_log = 1
general_log_file = /var/log/mysql/query.log

# Or enable per session
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/query.log';

Magento Query Logger

use Magento\Framework\DB\Logger\File as QueryLogger;

class CustomQueryLogger extends QueryLogger
{
    public function logQuery($sql, $params = [])
    {
        $logEntry = sprintf(
            "[%s] Query: %s\nParams: %s\nTime: %sms\n\n",
            date('Y-m-d H:i:s'),
            $sql,
            json_encode($params),
            $this->getQueryTime()
        );

        file_put_contents('var/log/custom_queries.log', $logEntry, FILE_APPEND);
    }
}

Key Points

• Query logging helps debug database issues
• Enable in development, disable in production
• Log includes SQL, parameters, and execution time
• Review logs for optimization opportunities

Enable MySQL Slow Query Log

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

Analyze Slow Queries

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

# Analyze with pt-query-digest
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

Common Slow Queries

-- Missing index
SELECT * FROM catalog_product_entity WHERE sku = 'TEST-001';
-- Add index: CREATE INDEX idx_sku ON catalog_product_entity(sku);

-- Full table scan
SELECT * FROM sales_order WHERE status = 'pending';
-- Add index: CREATE INDEX idx_status ON sales_order(status);

-- N+1 query problem
-- Loading products in loop instead of using JOIN or subquery

Magento Query Optimization

// Optimize collection queries
$collection = $this->productCollectionFactory->create();
$collection->addAttributeToSelect(['name', 'price', 'sku']); // Select specific fields
$collection->addFieldToFilter('status', 1); // Use indexed fields
$collection->setPageSize(20); // Limit results
$collection->load();

// Use subquery instead of loop
$subSelect = $connection->select()
    ->from('sales_order_item', ['order_id'])
    ->group('order_id');

$select = $connection->select()
    ->from('sales_order')
    ->where('entity_id IN (?)', $subSelect);

Key Points

• Enable slow query log to identify bottlenecks
• Use EXPLAIN to analyze query execution
• Add indexes for frequently queried columns
• Avoid SELECT * in production code

MySQL Profiling

-- Enable profiling
SET profiling = 1;

-- Run query
SELECT * FROM catalog_product_entity WHERE entity_id = 1;

-- Show profiles
SHOW PROFILES;

-- Show query profile
SHOW PROFILE FOR QUERY 1;

Magento Profiler

use Magento\Framework\Profiler;

// Start profiler
Profiler::start('DB_QUERY');

// Database operation
$products = $collection->load();

// Stop profiler
Profiler::stop('DB_QUERY');

// Get profiler data
$profiler = new Profiler();
$timer = $profiler->fetchTimers('DB_QUERY');
echo 'Query time: ' . $timer->getSecondsElapsed() . 's';

Connection Pooling

// 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',
            ],
            'slave' => [
                'host' => 'slave-db.example.com',
                'username' => 'readonly',
                'password' => '',
                'active' => '1',
            ],
        ],
    ],
];

Key Points

• MySQL profiling shows query execution details
• Magento profiler tracks code execution time
• Connection pooling reduces connection overhead
• Use read replicas for scaling reads

Test Database Connection

// Test connection manually
$connection = new \PDO(
    'mysql:host=localhost;dbname=magento',
    'root',
    '',
    [
        \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
        \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
    ]
);

echo 'Connected successfully';

Connection Issues

// Common connection errors
// 1. Access denied
// - Check username/password
// - Check host permissions
// - Check database exists

// 2. Too many connections
// - Increase max_connections in my.cnf
// - Use connection pooling
// - Close unused connections

// 3. Connection refused
// - Check MySQL service status
// - Verify port (default 3306)
// - Check firewall rules

// Debug connection
try {
    $db->getConnection();
} catch (\Exception $e) {
    $logger->error('Database connection failed', [
        'error' => $e->getMessage(),
        'host' => $config['host'],
        'dbname' => $config['dbname'],
    ]);
}

Connection Monitoring

-- Show current connections
SHOW PROCESSLIST;

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

-- Kill stuck connection
KILL <process_id>;

-- Show connection limits
SHOW VARIABLES LIKE 'max_connections';

Key Points

• Test connections before operations
• Monitor connection count
• Use connection pooling for high traffic
• Log connection errors for debugging