12.02 Performance Tuning

Why this matters

A slow SimpleRisk install produces user friction (form submissions take 30 seconds; pages don't load), which produces lower-quality data (users abandon mid-submission), which produces a worse program. Performance is a quality issue, not just a comfort issue.

Most performance issues have known causes. PHP without opcache loads and parses every PHP file on every request; PHP-FPM with too few workers blocks under load; a database without the right indexes performs full-table scans on common queries; a 50-million-row audit_log slows every audit-trail render. The fixes are well-understood; the work is identifying which fix matches your install's bottleneck.

The honest scope to know up front: most installs don't need tuning. SimpleRisk's defaults work for installs up to several hundred users with thousands of risks. Tuning matters when an install grows beyond that, when specific operations get slow, or when monitoring surfaces specific bottlenecks. Don't tune speculatively; measure first.

Before you start

Have these in hand:

A baseline measurement of the slow operation. "The risk register loads in 12 seconds with 5,000 risks" is something you can act on; "it feels slow" is not.
OS-level access to the SimpleRisk server, the web server, and the database server.
Monitoring in place to compare before/after. See Monitoring SimpleRisk.
A non-production environment to test tuning changes. Production isn't where you experiment with PHP opcache settings.
Awareness that some "slow" is application-design, not configuration. A list endpoint that returns 50,000 rows will be slow regardless of tuning; the fix is filtering, not tuning.

Tuning order

Tune in this order; later items depend on earlier ones being right.

1. PHP opcache

Single biggest performance lever. Without opcache, PHP parses every .php file on every request — a typical SimpleRisk page touches dozens of files. With opcache, parsed bytecode is cached.

Verify opcache is enabled:

php -m | grep -i opcache

If missing, install (apt install php8.3-opcache on Debian/Ubuntu, or your distribution's equivalent).

Configure opcache in php.ini:

[opcache]
opcache.enable = 1
opcache.memory_consumption = 256
opcache.interned_strings_buffer = 16
opcache.max_accelerated_files = 20000
opcache.validate_timestamps = 1
opcache.revalidate_freq = 60

memory_consumption=256 is generous; smaller installs can use 128 MB. max_accelerated_files=20000 accommodates SimpleRisk's file count plus vendor libraries. validate_timestamps=1 with revalidate_freq=60 means PHP checks for file changes every 60 seconds (changes during an upgrade get picked up promptly). For production with no in-flight changes, you can set validate_timestamps=0 for slightly better performance.

Restart PHP-FPM after config changes:

systemctl restart php8.3-fpm

Verify cache is being used: load some pages, then check the opcache stats via a tool like opcache-status (https://github.com/rlerdorf/opcache-status) or phpinfo()'s opcache section.

2. Database indexes

SimpleRisk's standard schema includes the indexes the application's standard queries need. Performance issues from missing indexes typically come from:

Custom queries (reports, integrations) that filter on columns without indexes.
Large tables (audit_log, debug_log) whose default indexes are sufficient for application use but slow for full-table aggregations.
Schema modifications that added columns without indexes.

To identify missing indexes:

-- Enable the slow query log
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- After capturing slow queries, analyze with mysqldumpslow:
-- mysqldumpslow -s t /var/log/mysql/slow.log

For each slow query, run EXPLAIN to see if it's using an index. If type is ALL (full table scan) and the query has a WHERE clause, an index on the WHERE column probably helps:

-- Example: index on audit_log.timestamp for time-range queries
CREATE INDEX idx_audit_log_timestamp ON audit_log (timestamp);

Add indexes carefully — they speed reads but slow writes. For low-write tables (read-heavy), more indexes are fine; for write-heavy tables (audit_log itself is high-write), be selective.

3. PHP-FPM worker count

PHP-FPM uses a pool of worker processes; concurrent requests above the worker count queue. For SimpleRisk with many concurrent users, the default worker count may be insufficient.

In /etc/php/8.3/fpm/pool.d/www.conf (or your distribution's equivalent):

pm = dynamic
pm.max_children = 50
pm.start_servers = 5
pm.min_spare_servers = 5
pm.max_spare_servers = 35
pm.max_requests = 500

max_children=50 allows 50 concurrent PHP requests. Tune based on memory (each worker uses 64-256 MB depending on the request) and observed concurrency.

To estimate the right count: monitor pm.status (enable pm.status_path = /status and query it) for actual worker utilization. If active processes regularly hits total processes, you're saturated; increase.

4. Database connection pool

PHP's MySQL extension creates a connection per request by default. For high-concurrency installs, persistent connections (PDO with PDO::ATTR_PERSISTENT) reuse connections across requests.

SimpleRisk's database configuration in simplerisk/includes/config.php may expose this; check the install version. Persistent connections reduce connection overhead but require careful tuning of MySQL's max_connections to avoid exhaustion.

5. SimpleRisk-specific tuning

Within SimpleRisk:

Risk-list page size: large registers may have a configurable per-page limit. Smaller page sizes load faster.
Disable unused Extras: each active Extra adds load (cron jobs, hooks). Deactivate Extras you don't use.
Audit-trail rendering: the per-record audit trail decodes HTML markup; for risks with extensive history, this can be slow. Consider archiving old audit entries.
Custom field count: each custom field adds form-rendering and storage overhead. Don't define fields you don't use.

6. Database query patterns

Some operations are inherently expensive:

Full risk list with joins: pulling every risk plus every team/owner/category join is slow at scale. Filter aggressively.
Cross-table aggregations (counts across the audit log, the risk table, the test table): pre-compute via materialized views or scheduled summary tables for installs with very large data.
Encryption/decryption (when the Encryption Extra is active): each encrypted-field read or write uses CPU. Bulk operations may be slower than non-encrypted equivalents.

For chronically-slow specific operations, profile in a non-production environment to find the actual bottleneck.

7. Web server tuning

Apache or nginx tuning is largely off-the-shelf. For SimpleRisk:

HTTP/2 (if not already on) — reduces request overhead; modern browsers benefit.
Compression (gzip, brotli) — reduces response size for HTML and CSS.
Static file caching — simplerisk/css/, simplerisk/js/, simplerisk/images/ should have aggressive cache headers (configurable in the web server).
Keepalive — reduce connection setup overhead.

These are standard web server tunings; consult your web server's documentation.

8. Database server tuning

MySQL tuning varies by version and workload. Common levers:

innodb_buffer_pool_size — the most impactful single setting. Set to 60-80% of available RAM on a dedicated database server.
innodb_log_file_size — larger log files mean fewer checkpoint flushes; better for write-heavy workloads.
max_connections — must be higher than the sum of all PHP-FPM workers across all app servers, plus headroom.
query_cache — typically disabled in modern MySQL (the cache had scalability issues); leave at the default.

For installs not using a managed database service, the default my.cnf is conservative; tuning produces real gains.

9. CDN for static assets

For installs with users distributed geographically, a CDN (Cloudflare, AWS CloudFront, Azure CDN) caches static assets near users:

Configure SimpleRisk's static asset paths (/css/, /js/, /images/) to be CDN-cached.
Set appropriate cache headers (long max-age for versioned files; short max-age for HTML).

For small or single-region installs, a CDN adds complexity without much gain; skip it.

10. Profiling for specific bottlenecks

When standard tuning doesn't resolve a specific slow operation:

Xdebug profiling — enable Xdebug's profiler temporarily; profile the specific request; identify the slow function.
MySQL slow query log — capture queries taking longer than N seconds; analyze with mysqldumpslow or pt-query-digest.
APM tools (New Relic, Datadog APM, AppDynamics) — instrument SimpleRisk to see per-request breakdown by function and query.

Profiling tells you what's actually slow; tune that, not what you assume is slow.

Common pitfalls

A handful of patterns recur with performance tuning.

Tuning without measuring. "I changed something; it feels faster" isn't measurement. Capture baseline; tune; capture after; compare.
Over-tuning opcache memory. A 4 GB opcache for a small install just wastes RAM. 128-256 MB is typically plenty.
Adding indexes promiscuously. Each index slows writes; for write-heavy tables, indexes are a tradeoff, not a free win.
Tuning PHP-FPM worker count without considering memory. 200 workers × 256 MB each = 50 GB; if your server has 16 GB, you'll OOM.
Disabling opcache validate_timestamps in production without ensuring upgrades reload PHP. New code won't be picked up until manual cache clear or PHP restart.
Treating slow queries as a tuning problem when the query design is wrong. A query that returns 50,000 rows is slow because of the rows, not the indexes. Filter at the application layer.
Not coordinating database tuning with the database administrator. Changes to innodb_buffer_pool_size require restart and affect every database on the server.
Ignoring write-heavy operations as a performance issue. Reads aren't always the bottleneck. Mass risk imports, bulk audit-log inserts, and similar writes can slow more than reads.
Forgetting that the Encryption Extra adds CPU cost. A historically-fast install that gets slow after activation may be CPU-bound on encryption. Vertical scale or dedicated encryption hardware (HSMs, AES-NI) helps.
Tuning the application but ignoring infrastructure issues. Underprovisioned VM, slow disks, network bottlenecks, contention from co-tenant workloads — these matter regardless of application tuning.
Tuning PHP-FPM in a multi-app-server deployment without coordinating. Each server's worker count affects database load; coordinate.
Not capturing baseline metrics before upgrades. "The new version is slower" claims need baselines to be debuggable.

Reference

Permission required: OS access for PHP / web server / database tuning.
Implementing files (PHP): /etc/php/8.3/php.ini, /etc/php/8.3/fpm/pool.d/www.conf (or your distribution's paths).
Implementing files (DB): /etc/mysql/my.cnf (or distribution-specific path).
Implementing files (Web): /etc/nginx/nginx.conf or /etc/apache2/apache2.conf.
Profiling tools: Xdebug profiler; MySQL slow query log; pt-query-digest (Percona Toolkit); APM tools (New Relic, Datadog APM).
Key opcache settings: opcache.enable, opcache.memory_consumption, opcache.max_accelerated_files, opcache.validate_timestamps, opcache.revalidate_freq.
Key PHP-FPM settings: pm.max_children, pm.start_servers, pm.min_spare_servers, pm.max_spare_servers, pm.max_requests.
Key MySQL settings: innodb_buffer_pool_size (most impactful), innodb_log_file_size, max_connections.
External dependencies: None for tuning itself; APM tools for advanced profiling.