A DBA's working week · Back to the Database

The short version

Same ten minutes every morning. Service, disk, error log, backup, replication, connections, locks.
Know the paths. MySQL keeps data and logs under C:\ProgramData; PostgreSQL keeps one tree.
Let the counters talk. The sys schema and pg_stat_activity turn "the app is slow" into a named statement.
You will touch about ten settings. SET PERSIST on MySQL, ALTER SYSTEM on PostgreSQL.
Read the log before you restart. A restart erases the evidence.
Keep a rhythm. Daily checklist, weekly restore test, monthly audit, quarterly drill.

The morning checklist

Ten minutes, the same list, by hand for a few weeks before you script it.

Check	MySQL how	PostgreSQL how	Bad looks like
Service up	`Get-Service MySQL84`, then `mysqladmin ping`	`Get-Service postgresql-x64-18`, then `pg_isready`	Stopped; or Running but refusing connections
Disk space	Free space on the drive holding the Data folder; size of the binlogs; `@@tmpdir`	Free space on the data drive; size of `pg_wal` and `log`	Under 15 to 20% free; binlogs growing faster than retention removes them
Error log tail	Last 40 lines of `hostname.err`	Newest file in `data\log`	`[ERROR]` lines; a restart you did not plan; repeated "Aborted connection"
Backup job	Task Scheduler last result 0; today's file exists with a plausible size	Same	A 0-byte dump; "last run: never"; a file the same size as last week's
Replication lag	`SHOW REPLICA STATUS\G`: both `Running` fields Yes, `Seconds_Behind_Source` small	`pg_stat_replication` on the primary	A "No"; lag that climbs all morning; `Last_Error` filled in
Connections	`Threads_connected` against `@@max_connections`	Rows in `pg_stat_activity` against `max_connections`	Creeping toward the ceiling day after day: a leaking pool
Slow query log growth	Size of `hostname-slow.log` since yesterday	Statements logged by `log_min_duration_statement`	A jump after a deploy
Long transactions	`information_schema.INNODB_TRX` ordered by `trx_started`	`pg_stat_activity` where state is `idle in transaction`	Anything older than a few minutes; an old start time with a NULL query is the forgotten transaction, still holding row locks
Locks	`sys.innodb_lock_waits`	`pg_stat_activity` with `wait_event_type = 'Lock'`	Any rows at 9 a.m.

PowerShell

# Is it running, is there room, what did it say last, did the backup run
Get-Service MySQL84, postgresql-x64-18
Get-PSDrive C | Select-Object Used, Free
Get-Content "C:\ProgramData\MySQL\MySQL Server 8.4\Data\$env:COMPUTERNAME.err" -Tail 40
Get-ScheduledTask "shop nightly dump" | Get-ScheduledTaskInfo   # LastTaskResult 0 = ok

For you as a DBA

Your first week is for learning what normal looks like. Write down Threads_connected at 10 a.m., the Data folder size and the nightly dump time, because a number only means something next to yesterday's.

Where things live on Windows

Most incidents end with "read the log". MySQL splits an install in two: programs under Program Files, everything that changes under the hidden C:\ProgramData folder. PostgreSQL keeps one tree, configuration and logs inside the data directory.

What	MySQL 8.4	PostgreSQL 18
Settings file	`C:\ProgramData\MySQL\MySQL Server 8.4\my.ini`	`data\postgresql.conf`
Data directory	`C:\ProgramData\MySQL\MySQL Server 8.4\Data`	`C:\Program Files\PostgreSQL\18\data`
Error log	`Data\<hostname>.err`	`data\log\postgresql-<date>.log`
Slow statements	`Data\<hostname>-slow.log`	The ordinary log files
Persisted settings	`Data\mysqld-auto.cnf` (written by SET PERSIST)	`data\postgresql.auto.conf` (written by ALTER SYSTEM)
Transaction logs	`Data\binlog.000001` … plus `binlog.index`	`data\pg_wal` (never delete files here by hand)
Who may connect	The `mysql` system schema	`data\pg_hba.conf`
Programs	`C:\Program Files\MySQL\MySQL Server 8.4\bin` (not on PATH)	`C:\Program Files\PostgreSQL\18\bin`
Service	`MySQL84`; `MySQL97` for 9.7. A service called just "MySQL" usually means a hand-made ZIP install	`postgresql-x64-18`

Someone may have moved the data directory to another drive, so ask the server instead of guessing.

Illustration · MySQL

SELECT @@datadir, @@log_error, @@slow_query_log_file, @@log_bin_basename;

Illustration · PostgreSQL

SHOW data_directory;
SHOW config_file;
SHOW hba_file;
SHOW log_directory;   -- 'log', relative to the data directory

Careful

ProgramData is hidden, so people go looking under Program Files, which holds only the binaries, and edit a file the server never reads. A SET PERSIST value in mysqld-auto.cnf overrides the same line in my.ini, so check both files.

What the server can tell you

Thousands of counters exist and you will read about a dozen. Three questions: who is doing what, how busy the server is compared with yesterday, and which statements cost the most.

MySQL	PostgreSQL	What it tells you
`SHOW FULL PROCESSLIST`, `sys.session`	`pg_stat_activity`	One row per connection: user, state, time, statement, whether a transaction is open. Sort by time. A Sleep or `idle in transaction` row with an old transaction is a blocker in waiting, and `idle_in_transaction_session_timeout` can end those sessions for you
`SHOW GLOBAL STATUS`	The `pg_stat_*` cumulative views	Counters since the last restart; the dozen worth knowing are in the next table
`SHOW ENGINE INNODB STATUS`	n/a	A wall of text with two useful sections: LATEST DETECTED DEADLOCK (the last two statements that deadlocked and which was rolled back) and BUFFER POOL AND MEMORY (the hit rate in plain words)
`sys.statement_analysis`	`pg_stat_statements` (an extension: `shared_preload_libraries` and a restart, then `CREATE EXTENSION`)	Every statement shape ranked by total time, with counts, average rows examined and whether it scans whole tables. Where "the app is slow" becomes a named statement
`sys.schema_table_statistics`	`pg_stat_user_tables`	Which tables get the most reads, writes and I/O. On PostgreSQL also sequential scans, dead rows waiting for vacuum, and when autovacuum last ran
`sys.innodb_lock_waits`	`pg_stat_activity` where `wait_event_type = 'Lock'`	Who is waiting on whom right now. The MySQL view hands you ready-made KILL statements in its last two columns
`SHOW REPLICA STATUS\G`	`pg_stat_replication` on the primary	Replication health and lag
`sys.schema_unused_indexes`	n/a	Indexes no query has touched since the server started; only meaningful after weeks of uptime
Slow query log (`slow_query_log`, `long_query_time`)	`log_min_duration_statement`, into `data\log`	Statements over the threshold. MySQL's default of 10 seconds catches almost nothing, so set it to a second or less and read the file weekly
General query log (`SET GLOBAL general_log = 'ON'`)	n/a	Every statement from every connection: the answer to "what exactly does this application send?". It can fill a disk within the hour, so capture what you need, turn it off, delete the file
PMM, or Grafana with `mysqld_exporter`	PMM, or Grafana with `postgres_exporter`	Trends, which counters alone cannot show. Percona Monitoring and Management is free and covers both engines. MySQL Enterprise Monitor reached end of life on 1 January 2025

How busy, compared with yesterday

SHOW GLOBAL STATUS counters are cumulative since the last restart, so the useful figure is the difference between two readings.

Counter	What it counts	Bad looks like
`Threads_connected`	Open connections right now	Creeping toward `max_connections` every day
`Threads_running`	Connections executing a statement this instant	Tens on a small server: everyone waits on CPU, disk or one lock
`Questions`	Statements received	A sudden drop with connections steady (something upstream broke); a jump (a deploy, or a loop)
`Innodb_buffer_pool_read_requests` vs `Innodb_buffer_pool_reads`	Logical page reads, and the ones that missed the cache and went to disk	Misses growing at more than about 1% of requests on a warmed-up server: the buffer pool is too small
`Innodb_row_lock_waits`, `Innodb_row_lock_time`	How often transactions waited for a row lock, and for how long	Climbing through the day: long transactions or hot rows
`Created_tmp_disk_tables`	Internal temporary tables that spilled to disk	Rising with every report: big sorts and GROUP BY, or a missing index
`Slow_queries`	Statements over `long_query_time`	Anything after a deploy that was zero before
`Aborted_connects`	Failed connection attempts	Dozens: a wrong password in some config, or a scanner

Configuration you will actually touch

MySQL has several hundred system variables and you will change about ten. SET GLOBAL changes the running server and is forgotten at the next restart. SET PERSIST also writes the value to mysqld-auto.cnf, so it survives restarts. Editing my.ini takes effect only at startup. A few variables can be set only when the data directory is initialized.

Setting	Default	What it does, and how it changes
`innodb_buffer_pool_size`	128 MB	The cache for data and indexes; the single most important number. Dynamic. Up to about 80% of RAM on a dedicated server, rounded to chunk multiples
`max_connections`	151	Ceiling on client connections. Dynamic. Every connection costs memory, so fix the leaking pool before raising it
`innodb_redo_log_capacity`	100 MB	Disk space for the redo log; replaces `innodb_log_file_size` since 8.0.30. Dynamic. Larger for write-heavy servers
`sql_mode`	Strict	Strictness (STRICT_TRANS_TABLES, ONLY_FULL_GROUP_BY and friends). Dynamic. Do not loosen it to make a legacy app "work"; fix the app
`log_bin`, `binlog_expire_logs_seconds`	ON, 30 days	Binary logging and how long to keep the files. `log_bin` needs a restart; retention is dynamic
`slow_query_log`, `long_query_time`	OFF, 10 s	The slow log and its threshold. Dynamic
`bind_address`	`*` (all)	Which network interfaces the server listens on. Restart. Use `127.0.0.1` for a server nobody else should reach (chapter 14)
`lower_case_table_names`	1 on Windows	Table names are case-insensitive, so a dump from a Linux server that has both `Orders` and `orders` will not load. Initialization only
`time_zone`	SYSTEM	Default zone for sessions and DATETIME conversions. Dynamic, but named zones such as `'Asia/Dubai'` fail with ERROR 1298 until you load the time zone tables from the MySQL downloads site, because Windows has no zoneinfo database. Do it on install day
`admin_address`, `admin_port`	Unset, 33062	A separate listener for you when the normal one is full. Restart

my.ini

[mysqld]
# The lines a DBA edits; everything else is the Configurator's.
innodb_buffer_pool_size=4G
innodb_redo_log_capacity=1G
max_connections=300
slow_query_log=ON
long_query_time=1
bind_address=127.0.0.1
datadir=D:/mysql/data
# Paths: forward slashes, or doubled backslashes

A typo here stops the service with "System error 1067", so change one thing at a time, restart, and read the .err log. performance_schema.variables_info says where a running value came from: EXPLICIT is an option file, PERSISTED is mysqld-auto.cnf.

PostgreSQL One file, and the same habit: ALTER SYSTEM SET writes to postgresql.auto.conf and SELECT pg_reload_conf() applies it. The context column of pg_settings says which settings still need a restart, and pending_restart says which are waiting for one.

Setting	Default	What it does
`shared_buffers`	128 MB	The cache. The manual suggests starting near 25% of RAM, since PostgreSQL also leans on the operating system's file cache. Restart
`work_mem`	4 MB	Memory per sort or hash, per query, so it multiplies by connections
`max_connections`	100	Ceiling on connections, three of them reserved for superusers. Restart
`autovacuum_*`	On	Reclaims dead rows; tune per table for the hot tables
`log_min_duration_statement`	Off	Logs statements slower than this into `data\log`. PostgreSQL's slow log

Maintenance that keeps it healthy

InnoDB does most of its own housekeeping, so the chores are few.

ANALYZE TABLE: Refreshes optimizer statistics. Cheap. Run it when a plan goes strange after a bulk load or mass delete.
OPTIMIZE TABLE: Rebuilds a table to return space after mass deletes. Heavy, and needs free disk about the size of the table. Never on a schedule.
CHECK TABLE: Verifies structure when you suspect corruption. Slow; on a copy if you can.
Binary logs: Retention (binlog_expire_logs_seconds, 30 days) purges them for you. Deleting the files in Explorer leaves binlog.index pointing at ghosts (chapter 15).
Patch upgrades: Quarterly (January, April, July, October): back up, stop the service, run the newer MSI over the old one, start the service. mysql_upgrade is gone, removed in 8.4.
Major upgrades: A ladder with no skipped rungs and no way back: 8.0 to 8.4, 8.4 to 9.7, 9.7 to 26.x. Run util.checkForServerUpgrade(), back up, rehearse on a copy.
Windows patch reboots: A restart you scheduled is maintenance; the same restart at 2 a.m. from Windows Update is an incident.
PostgreSQL vacuum: MVCC leaves the old version of every changed row behind (chapter 9). Autovacuum is on by default; check n_dead_tup and last_autovacuum in pg_stat_user_tables. Every table must be vacuumed once per two billion transactions or the server stops accepting writes. VACUUM FULL takes an exclusive lock, so it is a downtime tool.
PostgreSQL upgrades: Minor releases install over the existing installation. Major versions (17 to 18) use pg_upgrade on the data directory.

The incident playbook

These nine cover most of a first year. Add a row each time something new happens.

Incident	First look	Then	Avoid
Disk full	`Get-PSDrive`; which folder grew: binlogs, the slow or general log, tmp, one runaway table?	Free space that is safe to free: purge binlogs the backups no longer need, rotate old logs, move the general log. Binlog and MyISAM writes wait and retry every minute; InnoDB writes fail outright until space returns	Deleting binlog files in Explorer; deleting anything in `#innodb_redo` or `pg_wal`
Too many connections (ERROR 1040)	Connect anyway: the server keeps one extra slot for an account with CONNECTION_ADMIN, and the admin port if you configured one	`SHOW PROCESSLIST`; find the leak (hundreds of Sleep rows from one host), KILL the idle ones, fix the pool. Raise `max_connections` only with memory to spare	Granting CONNECTION_ADMIN to app accounts; they will burn the reserved slot
A runaway query	Process list ordered by Time; `EXPLAIN FOR CONNECTION id` shows its plan	`KILL QUERY id` stops the statement and keeps the connection; `KILL CONNECTION id` drops both. A big UPDATE rolls back and that takes about as long as it ran	Killing a large ALTER halfway and expecting it to stop instantly
Blocking session	`sys.innodb_lock_waits`; `performance_schema.metadata_locks` for "Waiting for table metadata lock"	Kill the blocker with the `sql_kill_blocking_connection` column; it is usually an idle session with an open transaction that will otherwise hold its row locks until `innodb_lock_wait_timeout` (50 seconds) raises ERROR 1205. Then find out why it stayed open	Raising `innodb_lock_wait_timeout` as a "fix"
Replication stopped	`SHOW REPLICA STATUS\G`: the two Running fields, `Last_IO_Error`, `Last_SQL_Error`	Fix the cause (network, a duplicate key, disk), then `START REPLICA`. A diverged replica is rebuilt from a backup (chapter 15)	Skipping errors blindly to make the Yes come back
Service will not start	Error 1067 means "read the `.err` log first". PostgreSQL: newest file in `data\log`, then Event Viewer	Usual causes: a typo in `my.ini`, the port in use (`netstat -ano \| findstr 3306`), disk full, permissions, antivirus locking the data folder, a stale `postmaster.pid`	Reinstalling before reading the log
Suspected corruption	The `.err` log mentions page checksums or InnoDB corruption; PostgreSQL 18 has data checksums on by default and `pg_checksums` to verify offline	Restore from backup onto a copy first. If you must, walk the `innodb_force_recovery` ladder from 1 to 3 to get a dump out; 4 and above can damage the files permanently	Running production on force recovery for days; heroics instead of a restore
Forgot the root password	Steps below	Reset it, then rotate it into a password manager and a login path	Leaving the init file with the clear-text password on disk
"The app is slow"	Is it the server (`Threads_running`, buffer pool misses, disk queue) or one statement (slow log, `statement_analysis`)?	Plan changed: `ANALYZE TABLE`. Missing index: chapter 10. Lock: the blocking row above. Buffer pool too small: raise it online	Restarting the service as the first move; it erases the evidence

The password reset gets its own steps. This is the manual's Windows procedure.

Stop the service.
Elevated PowerShell: Stop-Service MySQL84, or whatever services.msc shows.
Write the reset file.
Create C:\mysql-init.txt with one line: ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPass!';

Start the server by hand with that file.

From cmd.exe, with the doubled backslashes the manual insists on.

PowerShell

cd "C:\Program Files\MySQL\MySQL Server 8.4\bin"
mysqld --defaults-file="C:\\ProgramData\\MySQL\\MySQL Server 8.4\\my.ini" --init-file=C:\\mysql-init.txt --console

Test it.
In a second window: mysql -u root -p.
Swap back to the service.
mysqladmin -u root -p shutdown stops the hand-started server; then Start-Service MySQL84.
Delete the file.
C:\mysql-init.txt holds the password in clear text.

PostgreSQL is gentler, and the Linux sudo -u postgres advice does not apply here. Back up pg_hba.conf, switch the host lines for 127.0.0.1/32 and ::1/128 from scram-sha-256 to trust, connect with psql -U postgres, run ALTER USER postgres PASSWORD '...', then put scram-sha-256 back. The file is re-read for every new connection, so the trust window opens when you save.

Ask the AI

Name the engine and the exact error. "MySQL 9.7 on Windows 11: ERROR 1040 'Too many connections'. Show which user and host hold the most connections, and generate a KILL statement for each one sleeping more than ten minutes."

Change management

Every schema change arrives as a migration: a file with the DDL, in version control, reviewed, run the same way in test as in production. Before DDL on a big table, take a backup, because MySQL cannot roll DDL back and an online ALTER on a 200 GB table needs 200 GB of free space. Keep a runbook and a log of what changed, when, by whom and how to undo it.

A weekly rhythm

Rhythm	What
Every day	The morning checklist; skim the error log; glance at the dashboard for anything that changed shape
Every week	Restore last night's backup into a scratch instance and count rows (a backup you have not restored is a guess); review the slow query log and `sys.statement_analysis`; check autovacuum and replication lag trends
Every month	Capacity: disk growth rate, buffer pool misses, connections at peak. User audit: `SHOW GRANTS` for every account, remove leavers (chapter 14). Apply the quarter's patch release on test first
Every quarter	Upgrade planning: which series are you on, when does its support end, what does the upgrade checker say. A full restore drill with point-in-time recovery. Reread and fix the runbook

Remember

Boring, regular, written down. The checklist finds problems while they are small, the restore test proves the backups are real, and the runbook means you do not have to remember any of it at 2 a.m.

Go deeper

MySQL sys schema
Every view and its columns.
PostgreSQL cumulative statistics views
The pg_stat views.
Forcing InnoDB recovery
Read it before you need it.
MySQL upgrade paths
Which version moves to which.
PostgreSQL routine vacuuming
Vacuum and the wraparound limit.

The morning checklist

Where things live on Windows

What the server can tell you

How busy, compared with yesterday

Configuration you will actually touch

Maintenance that keeps it healthy

The incident playbook

Change management

A weekly rhythm

Go deeper

Quick check