Database Optimization

Optimize Database Performance and Storage

Database Performance Analysis

Step 1: Analyze Current Performance

`
SLOS → Advanced → Database → Performance Analysis
`

Database Performance Report:
`json
{
“database_size”: {
“totalsizemb”: 2450,
“consentlogsize_mb”: 1800,
“cookiesettingssize_mb”: 450,
“auditlogsize_mb”: 200
},
“query_performance”: {
“slowest_queries”: [
{
“querytype”: “consentlookup”,
“averagetimems”: 450,
“call_count”: 1250,
“optimization_potential”: “high”
},
{
“querytype”: “userconsent_history”,
“averagetimems”: 320,
“call_count”: 890,
“optimization_potential”: “medium”
}
],
“index_usage”: {
“indexes_used”: 8,
“unused_indexes”: 3,
“missing_indexes”: 2
}
},
“table_fragmentation”: {
“consentlogfragmentation”: “35%”,
“cookiesettingsfragmentation”: “12%”,
“recommendedoptimization”: “rebuildtables”
}
}
`

Step 2: Identify Optimization Opportunities

`sql
— Analyze slow queries
SELECT
sql_text,
exec_count,
avgtimerwait / 1000000000 as avgtimesec,
rows_examined,
rows_sent
FROM performanceschema.eventsstatementssummaryby_digest
WHERE schema_name = DATABASE()
AND avgtimerwait > 1000000000 — Queries taking > 1 second
ORDER BY avgtimerwait DESC
LIMIT 10;

— Check index usage
SELECT
object_schema,
object_name,
index_name,
count_read,
count_fetch,
count_insert,
count_update,
count_delete
FROM performanceschema.tableiowaitssummarybyindex_usage
WHERE object_schema = DATABASE()
AND objectname LIKE ‘wpslos_%’
ORDER BY count_read DESC;
`

Step 3: Database Configuration Check

`
SLOS → Advanced → Database → Configuration Check
`

Database Configuration Analysis:
`json
{
“innodb_settings”: {
“innodbbufferpool_size”: “1GB”,
“recommendedbufferpool”: “2GB”,
“innodblogfile_size”: “256MB”,
“innodbflushlogattrx_commit”: 1
},
“query_cache”: {
“querycachesize”: “256MB”,
“querycachetype”: “ON”,
“querycachelimit”: “1MB”
},
“connection_settings”: {
“max_connections”: 150,
“wait_timeout”: 28800,
“interactive_timeout”: 28800
},
“optimization_recommendations”: [
“Increase innodbbufferpool_size”,
“Enable query cache for read-heavy workload”,
“Add composite indexes for common queries”
]
}
`

Index Optimization

Step 1: Analyze Current Indexes

`
SLOS → Advanced → Database → Index Analysis
`

Index Analysis Report:
`json
{
“existing_indexes”: [
{
“table”: “wpslosconsent_log”,
“index”: “PRIMARY”,
“columns”: [“id”],
“usage”: “high”
},
{
“table”: “wpslosconsent_log”,
“index”: “idx_timestamp”,
“columns”: [“consent_timestamp”],
“usage”: “high”
},
{
“table”: “wpslosconsent_log”,
“index”: “idxuserid”,
“columns”: [“user_id”],
“usage”: “medium”
}
],
“missing_indexes”: [
{
“table”: “wpslosconsent_log”,
“suggestedindex”: “idxuser_timestamp”,
“columns”: [“userid”, “consenttimestamp”],
“benefit”: “Improves user consent history queries by 70%”
},
{
“table”: “wpslosconsent_log”,
“suggestedindex”: “idxip_address”,
“columns”: [“ip_address”],
“benefit”: “Speeds up IP-based consent lookups”
}
],
“unused_indexes”: [
{
“table”: “wpsloscookie_settings”,
“index”: “idxcategoryname”,
“last_used”: “2024-10-15”,
“recommendation”: “consider_removal”
}
]
}
`

Step 2: Create Optimal Indexes

`sql
— Add performance indexes
CREATE INDEX idxusertimestamp ON wpslosconsentlog (userid, consent_timestamp DESC);
CREATE INDEX idxiptimestamp ON wpslosconsentlog (ipaddress, consent_timestamp);
CREATE INDEX idxcategories ON wpslosconsentlog (consent_categories(100));
CREATE INDEX idxsource ON wpslosconsentlog (source);

— Composite indexes for complex queries
CREATE INDEX idxusercategorytimestamp ON wpslosconsentlog
(userid, consentcategories(50), consent_timestamp DESC);

— Cookie settings indexes
CREATE INDEX idxcookiedomain ON wpsloscookiesettings (cookiedomain);
CREATE INDEX idxcategorystatus ON wpsloscookie_settings (category, status);

— Audit log indexes
CREATE INDEX idxaudittimestamp ON wpslosauditlog (createdat DESC);
CREATE INDEX idxauditaction ON wpslosauditlog (action, createdat DESC);
`

Step 3: Remove Unused Indexes

`sql
— Identify and remove unused indexes
SELECT
‘DROP INDEX ‘ + i.name + ‘ ON ‘ + o.name + ‘;’ as drop_statement
FROM sys.indexes i
INNER JOIN sys.objects o ON i.objectid = o.objectid
WHERE o.name LIKE ‘wpslos%’
AND i.name LIKE ‘idx_%’
AND i.isprimarykey = 0
AND NOT EXISTS (
SELECT 1
FROM sys.dmdbindexusagestats s
WHERE s.objectid = i.objectid
AND s.indexid = i.indexid
AND s.databaseid = DBID()
AND (s.userseeks > 0 OR s.userscans > 0 OR s.user_lookups > 0)
);
`

Table Optimization and Maintenance

Step 1: Table Fragmentation Analysis

`
SLOS → Advanced → Database → Table Maintenance
`

Table Maintenance Report:
`json
{
“fragmented_tables”: [
{
“table”: “wpslosconsent_log”,
“fragmentation_percent”: 35,
“page_count”: 1250,
“recommended_action”: “rebuild”
},
{
“table”: “wpslosaudit_log”,
“fragmentation_percent”: 28,
“page_count”: 450,
“recommended_action”: “rebuild”
}
],
“table_statistics”: {
“consentlogrows”: 154200,
“avgrowlength”: 245,
“datafreemb”: 120,
“autoincrementnext”: 154201
}
}
`

Step 2: Optimize Table Structure

`sql
— Rebuild fragmented tables
ALTER TABLE wpslosconsent_log ENGINE = InnoDB;
ALTER TABLE wpslosaudit_log ENGINE = InnoDB;

— Optimize table storage
OPTIMIZE TABLE wpslosconsent_log;
OPTIMIZE TABLE wpsloscookie_settings;
OPTIMIZE TABLE wpslosaudit_log;

— Update table statistics
ANALYZE TABLE wpslosconsent_log;
ANALYZE TABLE wpsloscookie_settings;
ANALYZE TABLE wpslosaudit_log;
`

Step 3: Implement Partitioning Strategy

`sql
— Partition consent log by date for better performance
ALTER TABLE wpslosconsent_log
PARTITION BY RANGE (YEAR(consent_timestamp)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026),
PARTITION p_future VALUES LESS THAN MAXVALUE
);

— Partition audit log by month
ALTER TABLE wpslosaudit_log
PARTITION BY RANGE (MONTH(created_at)) (
PARTITION p_jan VALUES LESS THAN (2),
PARTITION p_feb VALUES LESS THAN (3),
PARTITION p_mar VALUES LESS THAN (4),
PARTITION p_apr VALUES LESS THAN (5),
PARTITION p_may VALUES LESS THAN (6),
PARTITION p_jun VALUES LESS THAN (7),
PARTITION p_jul VALUES LESS THAN (8),
PARTITION p_aug VALUES LESS THAN (9),
PARTITION p_sep VALUES LESS THAN (10),
PARTITION p_oct VALUES LESS THAN (11),
PARTITION p_nov VALUES LESS THAN (12),
PARTITION p_dec VALUES LESS THAN (13)
);
`

Query Optimization

Step 1: Optimize Slow Queries

`
SLOS → Advanced → Database → Query Optimization
`

Query Optimization Analysis:
`json
{
“slow_queries”: [
{
“originalquery”: “SELECT * FROM wpslosconsentlog WHERE userid = ? ORDER BY consenttimestamp DESC LIMIT 10″,
“execution_time”: “450ms”,
“optimizedquery”: “SELECT id, consentcategories, consenttimestamp, ipaddress FROM wpslosconsentlog WHERE userid = ? ORDER BY consent_timestamp DESC LIMIT 10″,
“improvement”: “60% faster”
},
{
“originalquery”: “SELECT COUNT(*) FROM wpslosconsentlog WHERE consenttimestamp >= ? AND consentcategories LIKE ‘%analytics%'”,
“execution_time”: “320ms”,
“optimizedquery”: “SELECT COUNT(*) FROM wpslosconsentlog WHERE consenttimestamp >= ? AND FINDINSET(‘analytics’, consentcategories)”,
“improvement”: “75% faster”
}
],
“query_patterns”: {
“consent_history”: “High frequency, needs optimization”,
“bulk_exports”: “Memory intensive, needs chunking”,
“realtimelookups”: “Latency sensitive, needs caching”
}
}
`

Step 2: Implement Query Caching

`php
// Query result caching
class DatabaseQueryCache {
private $cachegroup = ‘slosdb_queries’;
private $cache_expiration = 300; // 5 minutes

public function getCachedQuery($query, $params = []) {
$cache_key = $this->generateCacheKey($query, $params);
return wpcacheget($cachekey, $this->cachegroup);
}

public function setCachedQuery($query, $params = [], $results) {
$cache_key = $this->generateCacheKey($query, $params);
wpcacheset($cachekey, $results, $this->cachegroup, $this->cache_expiration);
}

private function generateCacheKey($query, $params) {
return ‘query_’ . md5($query . serialize($params));
}

public function invalidateCache($table = null) {
if ($table) {
// Invalidate cache for specific table
wpcachedelete(“table{$table}”, $this->cachegroup);
} else {
// Clear all query cache
wpcacheflushgroup($this->cachegroup);
}
}
}

// Usage example
$cache = new DatabaseQueryCache();
$results = $cache->getCachedQuery(“userconsenthistory”, [$user_id]);

if (!$results) {
$results = $wpdb->get_results($wpdb->prepare(
“SELECT id, consentcategories, consenttimestamp
FROM wpslosconsent_log
WHERE user_id = %d
ORDER BY consent_timestamp DESC
LIMIT 10″,
$user_id
));
$cache->setCachedQuery(“userconsenthistory”, [$user_id], $results);
}
`

Step 3: Database Connection Optimization

`php
// Connection pooling and optimization
class DatabaseConnectionManager {
private static $instance = null;
private $connections = [];

public static function getInstance() {
if (self::$instance === null) {
self::$instance = new self();
}
return self::$instance;
}

public function getConnection($type = ‘read’) {
if (!isset($this->connections[$type])) {
$this->connections[$type] = $this->createConnection($type);
}
return $this->connections[$type];
}

private function createConnection($type) {
global $wpdb;

// For read operations, use replica if available
if ($type === ‘read’ && defined(‘DBREADHOST’)) {
return new wpdb(DBUSER, DBPASSWORD, DBNAME, DBREAD_HOST);
}

return $wpdb;
}

public function optimizeConnection($connection) {
// Set optimal connection settings
$connection->query(“SET SESSION sqlmode = ‘STRICTTRANS_TABLES'”);
$connection->query(“SET SESSION innodblockwait_timeout = 50″);
$connection->query(“SET SESSION maxexecutiontime = 30000″);
}
}
`

Data Archiving Strategy

Step 1: Analyze Data Retention Needs

`
SLOS → Advanced → Database → Data Archiving
`

Data Archiving Analysis:
`json
{
“retention_requirements”: {
“consentdata”: “7years_gdpr”,
“auditlogs”: “3years”,
“cookiescans”: “2years”,
“performancelogs”: “1year”
},
“datavolumeanalysis”: {
“oldest_record”: “2020-01-15”,
“recordsolderthan2years”: 45000,
“recordsolderthan5years”: 12000,
“estimatedarchivesize_mb”: 850
},
“archiving_strategy”: {
“method”: “partition_based”,
“frequency”: “quarterly”,
“compression”: “gzip”,
“storage”: “separate_database”
}
}
`

Step 2: Implement Automated Archiving

`sql
— Create archive tables
CREATE TABLE wpslosconsentlogarchive LIKE wpslosconsent_log;
CREATE TABLE wpslosauditlogarchive LIKE wpslosaudit_log;

— Add archive metadata columns
ALTER TABLE wpslosconsentlogarchive
ADD COLUMN archivedate TIMESTAMP DEFAULT CURRENTTIMESTAMP,
ADD COLUMN archivereason VARCHAR(100) DEFAULT ‘retentionpolicy’;

— Automated archiving procedure
DELIMITER //

CREATE PROCEDURE archiveoldconsent_data()
BEGIN
DECLARE archive_cutoff DATE;
SET archivecutoff = DATESUB(CURDATE(), INTERVAL 2 YEAR);

— Move old data to archive
INSERT INTO wpslosconsentlogarchive
SELECT *, NOW(), ‘retentionpolicy2_years’
FROM wpslosconsent_log
WHERE consenttimestamp < archivecutoff;

— Remove archived data from main table
DELETE FROM wpslosconsent_log
WHERE consenttimestamp < archivecutoff;

— Optimize main table after archiving
OPTIMIZE TABLE wpslosconsent_log;
END //

DELIMITER ;
`

Step 3: Set Up Archive Maintenance

`php
// Archive maintenance scheduler
function scheduleArchiveMaintenance() {
if (!wpnextscheduled(‘slosarchivemaintenance’)) {
wpscheduleevent(time(), ‘weekly’, ‘slosarchivemaintenance’);
}
}

addaction(‘slosarchive_maintenance’, ‘performArchiveMaintenance’);

function performArchiveMaintenance() {
global $wpdb;

// Compress old archive partitions
$wpdb->query(“ALTER TABLE wpslosconsentlogarchive PARTITION p2020, p2021 COMPRESS”);

// Update archive statistics
updateArchiveStatistics();

// Clean up temporary archive files
cleanupTemporaryArchives();

// Send maintenance report
sendMaintenanceReport();
}

function updateArchiveStatistics() {
global $wpdb;

$stats = $wpdb->get_row(“
SELECT
COUNT(*) as totalarchivedrecords,
MIN(consenttimestamp) as oldestrecord,
MAX(consenttimestamp) as newestrecord,
SUM(LENGTH(consentcategories)) as datasize_bytes
FROM wpslosconsentlogarchive
“);

updateoption(‘slosarchive_stats’, $stats);
}
`

Performance Monitoring Setup

Step 1: Configure Performance Monitoring

`
SLOS → Advanced → Database → Performance Monitoring
`

Performance Monitoring Configuration:
`json
{
“monitoring_metrics”: [
“queryexecutiontime”,
“connection_count”,
“tablelockwaits”,
“slowquerycount”,
“cachehitratio”,
“indexusagestats”
],
“alert_thresholds”: {
“slowquerythreshold_ms”: 1000,
“maxconnectionsthreshold”: 80,
“lockwaitthreshold_ms”: 5000
},
“monitoring_schedule”: {
“realtime”: “queryperformance”,
“hourly”: “connection_stats”,
“daily”: “index_analysis”,
“weekly”: “table_maintenance”
}
}
`

Step 2: Implement Performance Alerts

`php
// Database performance monitoring
class DatabasePerformanceMonitor {
private $alerts = [];

public function monitorQueryPerformance() {
global $wpdb;

// Check for slow queries
$slowqueries = $wpdb->getresults(“
SELECT
sql_text,
exec_count,
avgtimerwait / 1000000000 as avgtimesec
FROM performanceschema.eventsstatementssummaryby_digest
WHERE schema_name = DATABASE()
AND avgtimerwait > 1000000000
ORDER BY avgtimerwait DESC
LIMIT 5
“);

foreach ($slow_queries as $query) {
if ($query->avgtimesec > 1) { // Alert on queries > 1 second
$this->addAlert(‘slow_query’, [
‘query’ => substr($query->sql_text, 0, 100) . ‘…’,
‘avgtime’ => $query->avgtime_sec,
‘execcount’ => $query->execcount
]);
}
}
}

public function monitorConnections() {
global $wpdb;

$connectioncount = $wpdb->getvar(“
SELECT COUNT(*)
FROM information_schema.processlist
WHERE db = DATABASE()
“);

$maxconnections = iniget(‘mysqli.max_connections’) ?: 150;

if ($connectioncount > ($maxconnections * 0.8)) {
$this->addAlert(‘highconnectioncount’, [
‘current’ => $connection_count,
‘max’ => $max_connections,
‘percentage’ => ($connectioncount / $maxconnections) * 100
]);
}
}

private function addAlert($type, $data) {
$this->alerts[] = [
‘type’ => $type,
‘data’ => $data,
‘timestamp’ => current_time(‘mysql’),
‘severity’ => $this->calculateSeverity($type, $data)
];
}

private function calculateSeverity($type, $data) {
switch ($type) {
case ‘slow_query’:
return $data[‘avg_time’] > 5 ? ‘critical’ : ‘warning’;
case ‘highconnectioncount’:
return $data[‘percentage’] > 90 ? ‘critical’ : ‘warning’;
default:
return ‘info’;
}
}

public function getAlerts() {
return $this->alerts;
}

public function sendAlerts() {
$alerts = $this->getAlerts();
if (empty($alerts)) return;

$email_content = “Database Performance Alerts:\n\n”;
foreach ($alerts as $alert) {
$email_content .= “[{$alert[‘severity’]}] {$alert[‘type’]}: ” .
json_encode($alert[‘data’]) . “\n”;
}

wpmail(getoption(‘adminemail’), ‘SLOS Database Performance Alert’, $emailcontent);
}
}
`

Step 3: Database Backup Optimization

`bash

Optimized backup script

#!/bin/bash

Database backup with compression and progress

mysqldump \
–user=${DB_USER} \
–password=${DB_PASSWORD} \
–host=${DB_HOST} \
–single-transaction \
–quick \
–compress \
–databases ${DB_NAME} \
–tables wpslosconsentlog wpsloscookiesettings wpslosaudit_log \
–where=”consenttimestamp >= DATESUB(NOW(), INTERVAL 1 YEAR)” \
| gzip > slosbackup$(date +%Y%m%d_%H%M%S).sql.gz

Verify backup integrity

gunzip -c slosbackup*.sql.gz | mysql \
–user=${DB_USER} \
–password=${DB_PASSWORD} \
–host=${DB_HOST} \
-e “SELECT COUNT(*) FROM wpslosconsentlog;” ${DBNAME}

echo “Backup completed and verified”
`

Support Resources

Documentation

1. Database Schema Reference
2. Performance Tuning Guide
3. Backup Procedures

Help


4. Database optimization specialists
5. Performance tuning consultation
6. Emergency database recovery
7. Query optimization experts