CresignSys Learn

CresignSys Learn — Lesson 046

Written by

cresignsys

in

MySQL Deep Internals — From SQL Query to Disk

We now go one level deeper than ordinary MySQL administration.

So far:

Browser
 ↓
Nginx
 ↓
PHP-FPM
 ↓
WordPress
 ↓
MySQL

Today:

WordPress
 ↓
SQL
 ↓
MySQL
 ↓
Query processing
 ↓
InnoDB
 ↓
RAM
 ↓
Disk

The goal is to understand what actually happens inside MySQL when WordPress asks for data.

1. Start With One Simple Question

Suppose WordPress asks:

SELECT *
FROM wp_posts
WHERE ID = 100;

What happens?

It does not simply mean:

MySQL → open file → find row

There are several internal stages.

2. The Basic Pipeline

Conceptually:

SQL Query
   ↓
Connection
   ↓
Parser
   ↓
Optimizer
   ↓
Execution
   ↓
Storage Engine
   ↓
InnoDB
   ↓
Buffer Pool / Disk
   ↓
Result

Let’s examine every layer.

3. Step 1 — Connection

PHP needs to communicate with MySQL.

PHP Worker
     ↓
MySQL connection
     ↓
MySQL Server

The connection contains information such as:

username
password
database
host
port/socket

4. Step 2 — SQL Arrives

WordPress sends a query such as:

SELECT ID, post_title
FROM wp_posts
WHERE ID = 100;

MySQL receives this query.

5. Step 3 — Parser

MySQL first parses the SQL.

It identifies:

SELECT
columns
FROM
table
WHERE
condition

Conceptually:

SQL text
   ↓
Parser
   ↓
understood query structure

6. Syntax Error

If the SQL is invalid:

SELEC *
FROM wp_posts;

MySQL can reject it because:

SELEC

is not valid SQL syntax.

The parser is one of the layers involved in identifying this.

7. Step 4 — Query Optimizer

Suppose the query is valid.

MySQL now needs to determine an efficient way to execute it.

This is the job of the:

Query Optimizer

8. Why Do We Need an Optimizer?

Suppose a table contains:

1,000,000 rows

MySQL has multiple possible strategies.

Strategy A

Read every row:

row 1
row 2
row 3
...
row 1,000,000

Strategy B

Use an index:

ID = 100
 ↓
index
 ↓
row 100

The second can be dramatically faster.

9. Query Plan

The optimizer creates a plan for executing the query.

You can inspect a query plan with:

EXPLAIN

Example:

EXPLAIN
SELECT *
FROM wp_posts
WHERE ID = 100;

10. EXPLAIN

EXPLAIN is one of the most important tools for database performance analysis.

It can show information such as:

table
type
possible_keys
key
rows
filtered
Extra

The exact output depends on MySQL version and query.

11. The Key Question

When examining:

EXPLAIN SELECT ...

one important question is:

Is MySQL using an appropriate index?

12. What Is an Index?

Imagine a library with:

1,000,000 books

and you want:

Book ID 783421

Without an index, you might search through many books.

With an index:

783421
  ↓
location
  ↓
book

A database index provides a similar lookup structure.

13. Index Example

Suppose:

wp_posts

has:

ID
post_title
post_content
post_date

and ID is indexed.

Then:

WHERE ID = 100

can efficiently locate the record.

14. Primary Key

A primary key is normally indexed.

For example:

wp_posts
   ↓
ID

The ID uniquely identifies a post row.

15. Why Indexes Matter

Suppose:

1,000 rows

A full scan might be acceptable.

But:

10 million rows

makes inefficient scanning much more expensive.

Indexes can reduce the amount of data MySQL needs to inspect.

16. But Indexes Have a Cost

Indexes consume:

Disk
RAM
CPU
write/update resources

When a row changes, relevant indexes may also need updating.

Therefore:

An index is an optimization, not free storage.

17. WordPress and Indexes

WordPress tables contain indexes designed around common access patterns.

You can inspect them using:

SHOW INDEX FROM wp_posts;

This shows index information for the table.

18. Storage Engine

After the optimizer determines a plan, MySQL relies on a storage engine.

For modern WordPress installations, the important engine is:

InnoDB

19. InnoDB

InnoDB is the storage engine responsible for storing and retrieving table data and indexes.

Conceptually:

MySQL
 ↓
InnoDB
 ↓
tables + indexes

20. Why InnoDB Matters

InnoDB provides major database capabilities including:

Transactions
Crash recovery
Row-level locking
Indexes
Buffer pool
Redo logging
Undo information

These are fundamental to reliable database operation.

21. InnoDB Does Not Read Every Byte From Disk

This is one of the most important performance concepts.

MySQL tries to keep frequently needed data in RAM.

The main mechanism is:

InnoDB Buffer Pool

22. Buffer Pool

Think of the buffer pool as:

RAM
 ↓
frequently used database pages

Instead of repeatedly reading from disk:

Disk → RAM → query

MySQL can often use:

RAM → query

which is much faster.

23. Simple Analogy

Imagine your desk and a warehouse.

Desk
=
RAM
Warehouse
=
Disk

If you repeatedly need the same document, you keep it on your desk.

You don’t walk to the warehouse every time.

The buffer pool works somewhat like the desk.

24. Buffer Pool Flow

Conceptually:

SQL Query
   ↓
InnoDB
   ↓
Buffer Pool
   │
   ├── Data already in RAM?
   │       ↓
   │      YES
   │       ↓
   │    use it
   │
   └── NO
           ↓
         Disk
           ↓
        load page
           ↓
       Buffer Pool

25. Cache Hit

If the required page is already in memory:

Buffer Pool
 ↓
FOUND

This is much faster than disk access.

26. Cache Miss

If it isn’t:

Buffer Pool
 ↓
NOT FOUND
 ↓
Disk read
 ↓
Memory

This introduces additional I/O latency.

27. Why RAM Matters

For a WordPress server:

More useful RAM
 ↓
larger useful caches
 ↓
fewer disk reads
 ↓
potentially better performance

But RAM isn’t the only performance factor.

CPU, storage, queries, indexes, PHP-FPM, and application behavior all matter.

28. SSD vs HDD

Storage speed also matters.

Traditional HDD:

mechanical movement
 ↓
slower random access

SSD/NVMe:

electronic flash storage
 ↓
much faster random access

Modern VPS infrastructure commonly uses SSD/NVMe-backed storage, but the exact storage architecture depends on the provider.

29. Database Pages

InnoDB doesn’t think primarily in terms of:

one row at a time

It organizes data into fixed-size pages.

A common InnoDB page size is:

16 KB

unless configured differently.

30. Why Pages?

Suppose you need one row.

The database can load the relevant page containing that row into memory.

Conceptually:

Disk
│
├── Page
├── Page
├── Page
└── Page

The buffer pool stores these pages.

31. Page Flow

Disk
 ↓
InnoDB page
 ↓
Buffer pool
 ↓
SQL operation

When a page changes:

Application
 ↓
modify page in memory
 ↓
eventually persist changes

The details involve InnoDB’s logging and flushing mechanisms.

32. Dirty Page

Suppose a page is loaded into memory.

WordPress changes some data.

The in-memory page is modified.

It is now called a:

Dirty page

Conceptually:

Clean page
 ↓
modify
 ↓
Dirty page

33. Why Not Immediately Write Everything to Disk?

Constantly writing every tiny modification directly to disk could be inefficient.

Instead, InnoDB uses buffering and logging mechanisms to balance performance and durability.

34. Redo Log

One critical component is the:

Redo Log

Its purpose is related to durability and crash recovery.

Conceptually:

Change
 ↓
Redo information
 ↓
persistent log

If a crash occurs, InnoDB can use redo information during recovery.

35. Simple Example

Suppose:

Post title:
Hello

changes to:

Hello World

InnoDB updates internal structures and records appropriate redo information.

If the system crashes before every modified data page reaches its final disk location, recovery can use the redo log to help restore committed changes.

36. Undo Information

InnoDB also maintains undo information.

Undo supports operations such as:

rollback
consistent reads
transaction processing

Conceptually:

Current data
+
history/undo information

37. Redo vs Undo

Remember:

REDO
=
help recover/reapply committed changes after failure

UNDO
=
help roll back changes / provide older versions for transactional consistency

The exact internal mechanics are more sophisticated, but this mental model is useful.

38. Transaction Example

Suppose an application performs:

Operation 1
Operation 2
Operation 3

inside a transaction.

Then:

COMMIT

means the transaction is finalized.

If it needs to be cancelled:

ROLLBACK

can reverse its changes where transaction semantics permit.

39. WordPress Doesn’t Usually Mean One SQL Query

A WordPress page request can trigger many database queries.

For example:

Request
 ↓
WordPress bootstrap
 ↓
settings queries
 ↓
plugin queries
 ↓
theme queries
 ↓
post query
 ↓
metadata queries
 ↓
taxonomy queries
 ↓
...

Therefore page performance can depend heavily on database behavior.

40. Plugin Effect

Suppose you install:

Plugin A
Plugin B
Plugin C
Plugin D

A page request might cause more database work.

Conceptually:

No plugins
 ↓
fewer queries

Many plugins
 ↓
potentially more queries

But the number of queries alone isn’t enough to judge performance; query complexity and execution time matter too.

41. Query Count vs Query Time

Imagine:

100 queries × 1 ms
=
100 ms

versus:

5 queries × 500 ms
=
2500 ms

The second can be much slower despite fewer queries.

42. Slow Query

A slow query might be caused by:

Missing index
Poor query design
Large dataset
Bad join strategy
Heavy sorting
Large result set
Resource contention

43. EXPLAIN

Suppose:

SELECT *
FROM wp_posts
WHERE post_status = 'publish';

You can examine:

EXPLAIN
SELECT *
FROM wp_posts
WHERE post_status = 'publish';

The plan can help determine whether MySQL scans too much data.

44. Full Table Scan

A full table scan means MySQL examines a large portion or all of the table.

Conceptually:

wp_posts
 ↓
row 1
row 2
row 3
...
row 1,000,000

This isn’t always bad.

For some queries, scanning is the most efficient strategy.

45. Don’t Fear “ALL” Automatically

When reading EXPLAIN, seeing:

type = ALL

often indicates a full scan.

But whether it is actually a problem depends on:

table size
query purpose
rows examined
result size
alternative indexes

A full scan of 10 rows isn’t a crisis.

A full scan of 100 million rows may be very different.

46. rows

The rows estimate in EXPLAIN gives an indication of how many rows MySQL expects to examine.

Lower isn’t automatically better in every circumstance, but excessive row examination is often a performance warning.

47. Index Selectivity

An index is especially useful when it can narrow the search significantly.

Imagine:

1,000,000 rows

and an indexed field identifies only:

1 row

Very selective.

But if an indexed field has only:

2 possible values

such as:

yes/no

it may be less useful depending on the query and data distribution.

48. Composite Index

An index can contain multiple columns.

Example:

INDEX (A, B)

This is a:

Composite index

The order matters.

(A, B)

is not equivalent to:

(B, A)

for all query patterns.

49. Why Index Order Matters

Suppose:

INDEX (country, city)

This is especially useful for queries that start with:

country

and then potentially use:

city

The exact optimizer behavior depends on the query and statistics.

50. Don’t Add Random Indexes

A common beginner mistake is:

“The database is slow, so I’ll add indexes everywhere.”

That can make writes more expensive and increase storage usage.

Proper database optimization starts with:

measure
 ↓
identify slow query
 ↓
EXPLAIN
 ↓
understand access pattern
 ↓
optimize
 ↓
measure again

51. MySQL RAM

MySQL uses RAM for several purposes:

InnoDB buffer pool
connections
sort buffers
temporary structures
internal caches
other server memory

Therefore:

MySQL RAM usage

is more than simply:

buffer pool

52. Buffer Pool Sizing

On a dedicated database server, the InnoDB buffer pool can often occupy a large fraction of available memory.

But your server is not a dedicated MySQL server.

You also run:

Nginx
PHP-FPM
WordPress
OS
other services

Therefore you cannot simply allocate almost all RAM to MySQL.

53. Shared VPS

Your server might look like:

RAM
│
├── Linux
├── Nginx
├── PHP-FPM
├── MySQL
├── SSH
├── monitoring
└── other services

So resource planning must consider the entire stack.

54. PHP-FPM vs MySQL Memory

Suppose:

PHP-FPM
 ↓
many workers
 ↓
high RAM usage

At the same time:

MySQL
 ↓
large buffer pool
 ↓
high RAM usage

Together they can exhaust the server.

55. Swap

Linux may use swap when memory pressure occurs.

Conceptually:

RAM
 ↓
memory pressure
 ↓
swap
 ↓
disk

Swap is much slower than RAM.

It can help prevent immediate process termination in some situations, but heavy swapping can severely hurt performance.

56. OOM Killer

If the Linux system runs critically short of memory, the kernel may invoke the:

Out-Of-Memory Killer

Conceptually:

RAM exhausted
 ↓
kernel attempts recovery
 ↓
process may be killed

This is why poor PHP-FPM/MySQL memory planning can cause apparently random service failures.

57. Check Memory

On Ubuntu:

free -h

This gives a quick overview.

You can also use:

htop

if installed.

58. Check Disk

df -h

Remember:

RAM
≠
Disk

They are completely different resources.

59. Check MySQL Processes

ps aux | grep mysqld

or:

ps aux | grep mariadbd

depending on the database software.

60. MySQL Status

Inside MySQL:

SHOW STATUS;

There are many metrics.

You can also query specific status variables.

61. Connection Count

A useful metric:

SHOW STATUS LIKE 'Threads_connected';

This tells you the current number of connected client threads.

62. Maximum Connections

You can inspect:

SHOW VARIABLES LIKE 'max_connections';

This is a limit on concurrent client connections.

Again, setting it extremely high isn’t automatically good.

Each connection can consume resources.

63. Too Many Connections

If MySQL reaches its connection limit:

PHP-FPM
 ↓
new connection
 ↓
MySQL
 ↓
too many connections
 ↓
failure

WordPress may then report database connection errors.

64. Classic WordPress Error

A famous WordPress message is:

Error establishing a database connection.

Possible causes include:

MySQL stopped
Wrong database credentials
Wrong DB host
Network/socket problem
Too many connections
Database overload
Corruption
Resource exhaustion

65. Diagnostic Sequence

If WordPress reports a database connection error:

1. Is MySQL running?
2. Is the database present?
3. Does the database user exist?
4. Are credentials correct?
5. Can the user access the database?
6. Is MySQL listening?
7. Is the socket available?
8. Is the server overloaded?
9. Are connections exhausted?

66. Check MySQL

sudo systemctl status mysql

Then:

sudo journalctl -u mysql -n 100

67. Test Database Login

If appropriate:

mysql -u wordpress_user -p wordpress_db

You’ll be prompted for the password.

Do not place database passwords directly into shell history unnecessarily.

68. Check Database Exists

Inside MySQL:

SHOW DATABASES;

Then:

USE wordpress_db;

69. Check Tables

SHOW TABLES;

If you see:

wp_posts
wp_users
wp_options
...

the WordPress database is present.

70. Check WordPress Options

A useful diagnostic:

SELECT option_name, option_value
FROM wp_options
WHERE option_name IN ('siteurl', 'home');

This helps confirm important URL configuration values.

71. Why URL Configuration Matters

Suppose the site was originally:

https://oldsite.com

and you migrate it to:

https://newsite.com

but database values still reference:

oldsite.com

you can experience:

redirect problems
incorrect asset URLs
login problems
mixed-content issues

Migration therefore involves both:

files
+
database

72. Database Backup

For WordPress hosting, one of the most important operational principles is:

A website backup must include both filesystem data and database data.

Conceptually:

Backup
│
├── Website files
│
└── MySQL database

73. Why Copying public/ Is Not Enough

Suppose you copy:

/storage/websites/example.com/public/

You have the PHP code and uploads.

But you don’t necessarily have:

posts
users
settings
menus
plugin configuration

Those are in the database.

74. Database Restore

A logical backup might look like:

wordpress.sql

Restoration conceptually:

SQL dump
 ↓
MySQL
 ↓
database
 ↓
WordPress

A proper disaster-recovery plan should test this process periodically.

75. Database and Hosting Automation

Your hosting platform can eventually automate:

Create website
 ↓
Create database
 ↓
Create database user
 ↓
Grant permissions
 ↓
Generate wp-config.php
 ↓
Install WordPress

This is a major part of a hosting control panel.

76. Your Hosting Platform Architecture

You are gradually building toward:

CresignSys Hosting Platform
│
├── Domain management
├── DNS
├── Website directories
├── Nginx
├── SSL
├── PHP-FPM
├── PHP versions
├── MySQL
├── Database users
├── WordPress
├── Backups
├── Logs
└── Monitoring

Each of these is now becoming understandable as an individual layer.

77. The Complete Internal Path

Let’s trace one WordPress database request extremely deeply.

Browser
 ↓
Nginx
 ↓
PHP-FPM
 ↓
PHP worker
 ↓
WordPress
 ↓
SQL query
 ↓
MySQL connection
 ↓
SQL parser
 ↓
Query optimizer
 ↓
Execution plan
 ↓
InnoDB
 ↓
Index
 ↓
Buffer pool
 ↓
Data page
 ↓
RAM

If the required page isn’t in memory:

Buffer Pool
 ↓
Disk I/O
 ↓
Storage
 ↓
page loaded
 ↓
RAM
 ↓
InnoDB
 ↓
WordPress

78. Now Think About Performance

A page can become slow at many different points:

DNS
 ↓
Network
 ↓
TLS
 ↓
Nginx
 ↓
PHP-FPM queue
 ↓
PHP execution
 ↓
WordPress plugin
 ↓
MySQL query
 ↓
Disk I/O

Therefore:

“The website is slow” is not a diagnosis.

It is only a symptom.

79. Performance Diagnosis

The correct approach is:

Measure
   ↓
Find slow layer
   ↓
Measure that layer
   ↓
Find bottleneck
   ↓
Fix bottleneck
   ↓
Measure again

This principle will become extremely important as you manage more websites.

80. Four Major Resources

For your VPS, constantly think about:

CPU
RAM
Disk I/O
Network

A fifth practical resource is:

Database/application capacity

although it ultimately consumes the underlying resources.

81. CPU Bottleneck

Could look like:

PHP
 ↓
CPU 100%

Possible causes:

expensive plugin
heavy PHP computation
many simultaneous requests
bad application code
bot traffic

82. RAM Bottleneck

Could look like:

RAM
 ↓
nearly exhausted
 ↓
swap/OOM

Possible causes:

too many PHP workers
large MySQL memory allocation
memory-heavy plugins
many concurrent requests

83. Disk Bottleneck

Could look like:

Disk I/O
 ↓
high latency

Possible causes:

database writes
backups
logs
cache operations
large file operations
swap

84. Network Bottleneck

Could occur when:

large downloads/uploads
high traffic
many connections

consume network capacity.

85. Database Bottleneck

Could be:

MySQL
 ↓
slow queries
 ↓
PHP waits
 ↓
Nginx waits
 ↓
Browser waits

This is why understanding MySQL internals matters to web hosting.

86. One More Important Concept: Concurrency

Suppose:

1 visitor

requests a page.

Easy.

Now:

100 visitors

arrive simultaneously.

The architecture becomes:

100 HTTP requests
        ↓
      Nginx
        ↓
PHP-FPM workers
        ↓
WordPress
        ↓
MySQL connections/queries

Every layer must handle concurrency.

87. PHP-FPM Queue

If all PHP workers are busy:

Request
 ↓
PHP-FPM
 ↓
no worker available
 ↓
wait

This increases response time.

88. MySQL Queue/Contention

Similarly, if MySQL is overloaded:

PHP workers
 ↓
database requests
 ↓
MySQL contention
 ↓
queries take longer

So increasing PHP-FPM workers can sometimes make an overloaded MySQL server even busier.

89. This Is Why Tuning Must Be Holistic

Don’t think:

Website slow
 ↓
increase PHP workers

Instead:

Website slow
 ↓
measure
 ↓
PHP?
MySQL?
Disk?
CPU?
Network?
Application?

Then make the appropriate change.

90. MySQL’s Deepest Simplified Model

Remember:

SQL
 ↓
Parser
 ↓
Optimizer
 ↓
Execution
 ↓
InnoDB
 ↓
Index/Data
 ↓
Buffer Pool
 ↓
Disk when needed

And for changes:

Write
 ↓
memory/data structures
 ↓
redo logging
 ↓
eventual page flushing
 ↓
persistent storage

This is a simplified conceptual model, not a complete description of every internal operation.

91. What You Should Know Now

You have progressed from:

"WordPress is a website"

to:

Browser
 ↓
DNS
 ↓
IP
 ↓
TCP
 ↓
TLS
 ↓
HTTP
 ↓
Nginx
 ↓
FastCGI
 ↓
PHP-FPM
 ↓
PHP
 ↓
WordPress
 ↓
SQL
 ↓
MySQL
 ↓
InnoDB
 ↓
Buffer Pool
 ↓
Disk

That is a substantial part of the foundation of modern PHP hosting.

Lesson 046 Summary

The key concepts are:

Parser
=
understands SQL syntax

Optimizer
=
chooses an execution strategy

EXPLAIN
=
shows query execution plan

Index
=
helps locate data efficiently

InnoDB
=
major MySQL storage engine

Buffer Pool
=
RAM area used to cache InnoDB data/index pages

Dirty Page
=
modified in-memory page not yet fully persisted

Redo Log
=
supports durability/recovery

Undo
=
supports rollback and consistent transactional behavior

Transaction
=
logical group of database operations

The most important mental model:

WordPress
   ↓
SQL
   ↓
MySQL
   ↓
Optimizer
   ↓
InnoDB
   ↓
Buffer Pool
   ↓
Disk

Next Lesson — 047

Linux Networking From Zero — What Actually Happens Between Your VPS and the Internet

We will go below Nginx and learn:

Network interface
IP address
MAC address
Ethernet
ARP
Subnet
Gateway
Routing table
TCP
UDP
Ports
Sockets
NAT
Firewall
IPv4
IPv6

Then connect it directly to your Oracle Cloud VPS:

Internet
   ↓
Public IP
   ↓
VNIC
   ↓
Subnet
   ↓
Route table
   ↓
Security rules
   ↓
Ubuntu
   ↓
Nginx
   ↓
Website

This will explain why a domain can resolve correctly but a website can still be unreachable, and how DNS, Oracle Cloud networking, Ubuntu firewall, ports 80/443, Nginx, and the browser all fit together.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

More posts