✏️ Editing: performance.php Read Only

<?php
$host = '127.0.0.1';
$db = 'dbtest';
$user = 'dbtester';
$pass = 'DbTesterTruehost191';
$charset = 'utf8mb4';

header('Content-Type: text/html; charset=utf-8'); // Optional: ensures readable output in browser

define('EXCELLENT_THRESHOLDS', [
    'connection' => 0.02,
    'insert' => 0.2,
    'bulk_insert' => 0.15,
    'select_no_index' => 0.05,
    'select_indexed' => 0.01,
    'update' => 0.02,
    'delete' => 0.02,
    'transactions' => 150,
    'complex_query' => 0.05
]);

define('GOOD_THRESHOLDS', [
    'connection' => 0.05,
    'insert' => 0.3,
    'bulk_insert' => 0.25,
    'select_no_index' => 0.1,
    'select_indexed' => 0.03,
    'update' => 0.05,
    'delete' => 0.05,
    'transactions' => 75,
    'complex_query' => 0.1
]);

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false
];

echo "<pre>";

$metrics = [];
$score = 0;
$max_score = 0;

function calculateScore($value, $excellent, $good, $weight = 1) {
    global $max_score;
    $max_score += $weight;

if ($value <= $excellent) return $weight;
    if ($value <= $good) return $weight * 0.8;
    return $weight * 0.5;
}

function formatBytes($bytes, $precision = 2) {
    $units = ['B', 'KB', 'MB', 'GB', 'TB'];
    $bytes = max($bytes, 0);
    $pow = floor(($bytes ? log($bytes) : 0) / log(1024));
    $pow = min($pow, count($units) - 1);
    $bytes /= pow(1024, $pow);
    return round($bytes, $precision) . ' ' . $units[$pow];
}

try {
    // Connection
    $start = microtime(true);
    $pdo = new PDO($dsn, $user, $pass, $options);
    $conn_time = microtime(true) - $start;
    $metrics['connection_time'] = $conn_time;
    echo "Connection Time: " . round($conn_time * 1000, 2) . " ms\n";
    $score += calculateScore($conn_time, EXCELLENT_THRESHOLDS['connection'], GOOD_THRESHOLDS['connection'], 1.5);

// Server Info
    $server_version = $pdo->getAttribute(PDO::ATTR_SERVER_VERSION);
    $buffer_pool = $pdo->query("SHOW VARIABLES LIKE 'innodb_buffer_pool_size'")->fetch()['Value'];
    echo "MySQL Version: $server_version\n";
    echo "InnoDB Buffer Pool: " . formatBytes($buffer_pool) . "\n\n";

// Create test table
    echo "Preparing test table...\n";
    $pdo->exec("DROP TABLE IF EXISTS test_perf");
    $pdo->exec("CREATE TABLE test_perf (
        id INT AUTO_INCREMENT PRIMARY KEY, 
        data VARCHAR(255),
        numeric_data DECIMAL(10,2),
        timestamp_data TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB");

// Insert test
    echo "Testing individual inserts (1000 rows)...\n";
    $insert_start = microtime(true);
    $stmt = $pdo->prepare("INSERT INTO test_perf (data, numeric_data) VALUES (?, ?)");

$batch_size = 100;
    for ($batch = 0; $batch < 10; $batch++) {
        $pdo->beginTransaction();
        for ($i = 0; $i < $batch_size; $i++) {
            $stmt->execute([str_repeat('x', 100), mt_rand(100, 10000) / 100]);
        }
        $pdo->commit();
    }

$insert_time = microtime(true) - $insert_start;
    $metrics['insert_time'] = $insert_time;
    echo "Individual inserts completed in " . round($insert_time, 4) . " seconds ";
    echo "(" . round(1000 / $insert_time) . " inserts/sec)\n";
    $score += calculateScore($insert_time, EXCELLENT_THRESHOLDS['insert'], GOOD_THRESHOLDS['insert'], 1.5);

// Bulk insert
    echo "\nTesting bulk inserts (1000 rows)...\n";
    $bulk_start = microtime(true);
    $values = [];
    for ($i = 0; $i < 1000; $i++) {
        $values[] = [str_repeat('y', 100), mt_rand(100, 10000) / 100];
    }

$stmt = $pdo->prepare("INSERT INTO test_perf (data, numeric_data) VALUES (?, ?)");
    $pdo->beginTransaction();

try {
        foreach (array_chunk($values, 150) as $chunk) {
            foreach ($chunk as $row) {
                $stmt->execute($row);
            }
            $pdo->commit();
            $pdo->beginTransaction(); // Start next batch
        }
        $pdo->commit(); // Final commit
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }

$bulk_time = microtime(true) - $bulk_start;
    $metrics['bulk_insert_time'] = $bulk_time;
    echo "Bulk inserts completed in " . round($bulk_time, 4) . " seconds ";
    echo "(" . round(1000 / $bulk_time) . " inserts/sec)\n";
    $score += calculateScore($bulk_time, EXCELLENT_THRESHOLDS['bulk_insert'], GOOD_THRESHOLDS['bulk_insert'], 1.5);

// Select no index
    echo "\nTesting SELECT without index...\n";
    $query_start = microtime(true);
    $stmt = $pdo->query("SELECT * FROM test_perf WHERE data LIKE 'x%'");
    $results = $stmt->fetchAll();
    $query_time = microtime(true) - $query_start;
    $metrics['select_no_index_time'] = $query_time;
    echo "Retrieved " . count($results) . " rows in " . round($query_time, 4) . " seconds\n";
    $score += calculateScore($query_time, EXCELLENT_THRESHOLDS['select_no_index'], GOOD_THRESHOLDS['select_no_index'], 1.5);

// Complex query
    echo "\nTesting complex query...\n";
    $complex_start = microtime(true);
    $stmt = $pdo->query("
        SELECT 
            AVG(numeric_data) as avg_val,
            MAX(numeric_data) as max_val,
            MIN(numeric_data) as min_val,
            COUNT(*) as total
        FROM test_perf
        WHERE timestamp_data > DATE_SUB(NOW(), INTERVAL 1 DAY)
        GROUP BY DATE(timestamp_data)
        ORDER BY avg_val DESC
    ");
    $complex_results = $stmt->fetchAll();
    $complex_time = microtime(true) - $complex_start;
    $metrics['complex_query_time'] = $complex_time;
    echo "Complex query completed in " . round($complex_time, 4) . " seconds\n";
    $score += calculateScore($complex_time, EXCELLENT_THRESHOLDS['complex_query'], GOOD_THRESHOLDS['complex_query'], 1);

// Indexing
    echo "\nCreating indexes...\n";
    $pdo->exec("CREATE INDEX idx_data ON test_perf (data)");
    $pdo->exec("CREATE INDEX idx_numeric ON test_perf (numeric_data)");

// Select with index
    echo "Testing SELECT with index...\n";
    $query_start = microtime(true);
    $stmt = $pdo->query("SELECT * FROM test_perf WHERE data LIKE 'x%'");
    $results = $stmt->fetchAll();
    $query_time_indexed = microtime(true) - $query_start;
    $metrics['select_indexed_time'] = $query_time_indexed;
    echo "Retrieved " . count($results) . " rows in " . round($query_time_indexed, 4) . " seconds\n";
    $score += calculateScore($query_time_indexed, EXCELLENT_THRESHOLDS['select_indexed'], GOOD_THRESHOLDS['select_indexed'], 1.5);

// Update
    echo "\nTesting UPDATE operations...\n";
    $update_start = microtime(true);
    $stmt = $pdo->prepare("UPDATE test_perf SET data = ?, numeric_data = ? WHERE id <= 100");
    $stmt->execute([str_repeat('z', 100), mt_rand(500, 5000) / 100]);
    $update_time = microtime(true) - $update_start;
    $metrics['update_time'] = $update_time;
    echo "Updated 100 rows in " . round($update_time, 4) . " seconds ";
    echo "(" . round(100 / $update_time) . " updates/sec)\n";
    $score += calculateScore($update_time, EXCELLENT_THRESHOLDS['update'], GOOD_THRESHOLDS['update'], 1);

// Delete
    echo "\nTesting DELETE operations...\n";
    $delete_start = microtime(true);
    $pdo->exec("DELETE FROM test_perf WHERE id <= 100");
    $delete_time = microtime(true) - $delete_start;
    $metrics['delete_time'] = $delete_time;
    echo "Deleted 100 rows in " . round($delete_time, 4) . " seconds ";
    echo "(" . round(100 / $delete_time) . " deletes/sec)\n";
    $score += calculateScore($delete_time, EXCELLENT_THRESHOLDS['delete'], GOOD_THRESHOLDS['delete'], 1);

// Transactions
    echo "\nTesting transaction performance...\n";
    $trans_start = microtime(true);
    $transactions = 0;
    $max_transactions = 100;
    while ($transactions < $max_transactions) {
        $pdo->beginTransaction();
        try {
            $stmt = $pdo->prepare("UPDATE test_perf SET numeric_data = ? WHERE id = ?");
            for ($i = 0; $i < 10; $i++) {
                $random_id = mt_rand(101, 2000);
                $stmt->execute([mt_rand(100, 10000) / 100, $random_id]);
            }
            $pdo->commit();
            $transactions++;
        } catch (Exception $e) {
            $pdo->rollBack();
        }
    }
    $trans_time = microtime(true) - $trans_start;
    $tps = $transactions / $trans_time;
    $metrics['transactions_per_second'] = $tps;
    echo "Completed $transactions transactions in " . round($trans_time, 2) . " seconds ";
    echo "(" . round($tps, 1) . " transactions/sec)\n";
    $score += calculateScore(1 / $tps, 1 / EXCELLENT_THRESHOLDS['transactions'], 1 / GOOD_THRESHOLDS['transactions'], 1.5);

// Cleanup
    echo "\nCleaning up test data...\n";
    $pdo->exec("DROP TABLE test_perf");

// Final score
    $normalized_score = ($max_score > 0) ? min(10, round(($score / $max_score) * 10, 1)) : 0;

echo "\nPerformance Summary:\n";
    foreach ([
        'connection_time' => 'Connection Time:',
        'insert_time' => 'Individual Inserts:',
        'bulk_insert_time' => 'Bulk Inserts:',
        'select_no_index_time' => 'SELECT (no index):',
        'select_indexed_time' => 'SELECT (with index):',
        'complex_query_time' => 'Complex Query:',
        'update_time' => 'UPDATE Operations:',
        'delete_time' => 'DELETE Operations:',
        'transactions_per_second' => 'Transactions/sec:'
    ] as $metric => $label) {
        if (isset($metrics[$metric])) {
            $value = $metrics[$metric];
            $unit = ($metric == 'transactions_per_second') ? ' tps' : 
                   (strpos($label, 'Time') ? ' s' : '');
            echo str_pad($label, 25) . round($value, 4) . $unit . "\n";
        }
    }

echo "\nFinal Performance Score: $normalized_score / 10\n";
    echo "Verdict: ";
    if ($normalized_score >= 9) {
        echo "Excellent performance\n";
    } elseif ($normalized_score >= 7) {
        echo "Good performance\n";
    } elseif ($normalized_score >= 5) {
        echo "Average performance\n";
    } else {
        echo "Below average performance\n";
    }

} catch (\PDOException $e) {
    http_response_code(500);
    echo "Database Error: " . htmlspecialchars($e->getMessage());
} catch (\Throwable $e) {
    http_response_code(500);
    echo "Runtime Error: " . htmlspecialchars($e->getMessage());
}

echo "</pre>";

🔐 Sid Gifari File Manager Pro