When your application starts slowing down, database queries are often the hidden reason behind performance issues. Tools like phpMyAdmin and MySQL Performance Schema help you look inside the MySQL engine and understand exactly what is happening.
Whether you’re working with Laravel, PHP, WordPress, or any custom backend, Performance Schema is one of the most powerful tools for identifying slow queries, lock waits, and I/O bottlenecks.

This blog explains, in simple language, how to check, enable, and use Performance Schema through phpMyAdmin.

What Is Performance Schema?

MySQL Performance Schema is a built-in performance monitoring framework that provides deep insights into:

• Slow and heavy SQL queries
• Table scans and missing indexes
• Table I/O and file I/O performance
• Lock waits and contention
• Query execution statistics
• Query digestion (pattern-based analysis)

It works at the database engine level, making it more accurate than general logging tools.

🧭 How to Check Performance Schema Status in phpMyAdmin

You can check whether Performance Schema is enabled directly inside phpMyAdmin.

Step 1 — Open phpMyAdmin

Go to your phpMyAdmin dashboard.

Step 2 — Click on the SQL tab

Step 3 — Run this command:

SHOW VARIABLES LIKE 'performance_schema';

Output:

• Value = ON → Performance Schema is enabled
• Value = OFF → It is disabled and must be activated manually

You may also check if the database appears in the left sidebar:

performance_schema

If this database is visible, Performance Schema is active.

🛠️ How to Enable Performance Schema (Windows + Linux)

Performance Schema can only be activated at MySQL startup, so you must edit the configuration file and restart MySQL.

🔵 Enable on XAMPP (Windows)

Step 1 — Open configuration file:

C:\xampp\mysql\bin\my.ini

Step 2 — Add this under the [mysqld] section:

performance_schema = ON
performance_schema_instrument = '%=ON'

Step 3 — Restart MySQL from XAMPP Control Panel

Step 4 — Re-check status using:

SHOW VARIABLES LIKE 'performance_schema';

🔶 Enable on Linux / Ubuntu Server

Step 1 — Edit MySQL config file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Step 2 — Add this:

performance_schema = ON
performance_schema_instrument = '%=ON'

Step 3 — Restart MySQL:

sudo systemctl restart mysql

Step 4 — Verify:

SHOW VARIABLES LIKE 'performance_schema';

📊 How to Use Performance Schema in phpMyAdmin

Once enabled, MySQL creates hundreds of monitoring tables inside the performance_schema database.

Some of the most useful tables are:

• events_statements_summary_by_digest → top slow/heavy queries
• table_io_waits_summary_by_table → which tables cause most I/O
• table_lock_waits_summary_by_table → where locking issues occur
• events_statements_history_long → long history of executed queries

You can inspect any of these through phpMyAdmin just like normal tables.

🧪 Useful Performance Schema Queries (For Developers)

1️⃣ Top 10 Slow Queries

SELECT 
    DIGEST_TEXT,
    COUNT_STAR AS exec_count,
    ROUND(SUM_TIMER_WAIT/1000000000000, 2) AS total_time_sec,
    ROUND(AVG_TIMER_WAIT/1000000000000, 4) AS avg_time_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY total_time_sec DESC
LIMIT 10;

2️⃣ Top Tables Causing I/O Load

SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    COUNT_READ, COUNT_WRITE,
    ROUND(TOTAL_TIMER_WAIT/1000000000000, 2) AS total_time_sec
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
ORDER BY total_time_sec DESC
LIMIT 10;

3️⃣ Table-Level Lock Waits

SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    COUNT_STAR,
    ROUND(SUM_TIMER_WAIT/1000000000000, 2) AS total_time_sec
FROM performance_schema.table_lock_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
ORDER BY total_time_sec DESC
LIMIT 10;

4️⃣ Check Query History

SELECT *
FROM performance_schema.events_statements_history_long
LIMIT 20;

🚦 Performance Schema vs Slow Query Log

Best practice: enable both for maximum visibility.

🎯 Why Developers Should Use Performance Schema

• Understand exactly which queries cause slowdown
• Detect missing indexes and full table scans
• Identify N+1 query issues in frameworks like Laravel
• Fix bottlenecks before they impact real users
• View both query-level and table-level metrics
• Improve application scalability

For multi-service platforms or heavy databases, this is an essential debugging tool.