Optimizing p99 database query response latency in multi-site Domain-driven architecture (DDD) blocks custom tables
Database Schema Design for High-Throughput Multi-Site WordPress
In a multi-site WordPress installation employing Domain-Driven Design (DDD) principles, where each site might represent a distinct business domain or tenant, optimizing database query performance, particularly for p99 (99th percentile) latency, becomes paramount. This often necessitates moving beyond the default WordPress table structure for specific, high-volume data. We’ll focus on designing and querying custom tables that are sharded or partitioned per site to mitigate cross-site contention and improve read/write throughput.
Consider a scenario where each site manages a large volume of “product reviews.” Storing these in the default `wp_posts` and `wp_postmeta` tables, even with site-specific prefixes, can lead to massive tables that degrade performance as the number of sites and reviews grows. A better approach is to create dedicated tables, potentially sharded by site ID.
Custom Table Structure and Site Sharding
For our product reviews example, we’ll define a custom table structure. The key is to include a `site_id` column to facilitate sharding or partitioning. For simplicity in this example, we’ll assume a single custom table per site, managed via a plugin, but the principles extend to database-level sharding or partitioning.
Let’s assume our plugin creates a table named `wp_site_product_reviews` (where `wp_` is the WordPress table prefix). The structure might look like this:
CREATE TABLE IF NOT EXISTS wp_site_product_reviews (
review_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
product_id BIGINT UNSIGNED NOT NULL,
user_id BIGINT UNSIGNED NULL,
rating TINYINT UNSIGNED NOT NULL,
comment TEXT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_product_id (product_id),
INDEX idx_user_id (user_id),
INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
In a true multi-site sharding scenario, you might have tables like `wp_site_1_product_reviews`, `wp_site_2_product_reviews`, etc., or use a single table with a `site_id` column and database-level partitioning. For this discussion, we’ll abstract the site-specific table access within our PHP code.
Optimizing p99 Latency: Indexing and Query Patterns
The p99 latency is heavily influenced by the worst-case query execution times. This means we need to focus on queries that might be slow under load. Common slow queries involve fetching data for a specific product, especially when sorted by creation date (e.g., for displaying recent reviews).
A critical index for fetching recent reviews for a product would be a composite index on `(product_id, created_at DESC)`. However, since we’re dealing with potentially many sites, we need to ensure our queries are always scoped to the current site. If using separate tables per site, this is implicit. If using a single table with `site_id`, it’s explicit.
Let’s assume we are using a single table `wp_site_product_reviews` and our plugin is aware of the current `site_id`.
Efficiently Fetching Recent Reviews for a Product
To optimize fetching the latest reviews for a given product, we need an index that supports filtering by `product_id` and ordering by `created_at`. A composite index is ideal here. If we also frequently filter by `site_id` (in a single-table approach), it should be the leading column.
-- Assuming a single table with site_id ALTER TABLE wp_site_product_reviews ADD INDEX idx_site_product_created (site_id, product_id, created_at DESC);
With this index, a query to fetch the 10 most recent reviews for product ID 123 on site ID 5 would be highly efficient:
SELECT
review_id,
user_id,
rating,
comment,
created_at
FROM
wp_site_product_reviews
WHERE
site_id = 5 AND product_id = 123
ORDER BY
created_at DESC
LIMIT 10;
The `EXPLAIN` output for this query should show the use of `idx_site_product_created` and a very low `rows` examined count.
PHP Implementation for Custom Table Access
Within your WordPress plugin, you’ll need robust methods to interact with these custom tables, ensuring you always use the correct table name (or correctly filter by `site_id`) and leverage prepared statements for security and performance.
Plugin Class Structure
class My_Product_Reviews_DB {
private $wpdb;
private $table_name;
private $current_site_id;
public function __construct() {
global $wpdb;
$this->wpdb = $wpdb;
// In a multi-site context, determine the current site ID.
// For simplicity, we'll assume it's available.
// In a real scenario, you'd use get_current_blog_id() or similar.
$this->current_site_id = get_current_blog_id();
// If using separate tables per site:
// $this->table_name = $wpdb->prefix . 'site_' . $this->current_site_id . '_product_reviews';
// If using a single table with site_id:
$this->table_name = $wpdb->prefix . 'site_product_reviews';
}
/**
* Get recent reviews for a product.
*
* @param int $product_id The product ID.
* @param int $limit Number of reviews to retrieve.
* @return array An array of review data.
*/
public function get_recent_reviews( int $product_id, int $limit = 10 ): array {
$query = $this->wpdb->prepare(
"SELECT
review_id, user_id, rating, comment, created_at
FROM
{$this->table_name}
WHERE
product_id = %d
ORDER BY
created_at DESC
LIMIT %d"
, $product_id, $limit
);
// If using a single table with site_id, add it to the WHERE clause:
// $query = $this->wpdb->prepare(
// "SELECT
// review_id, user_id, rating, comment, created_at
// FROM
// {$this->table_name}
// WHERE
// site_id = %d AND product_id = %d
// ORDER BY
// created_at DESC
// LIMIT %d"
// , $this->current_site_id, $product_id, $limit
// );
$results = $this->wpdb->get_results( $query );
if ( $this->wpdb->last_error ) {
// Log error appropriately
error_log( "Database error fetching reviews: " . $this->wpdb->last_error );
return [];
}
return $results ?: [];
}
/**
* Add a new product review.
*
* @param array $data Review data (product_id, user_id, rating, comment).
* @return int|false The ID of the newly inserted review, or false on failure.
*/
public function add_review( array $data ) {
$defaults = [
'product_id' => 0,
'user_id' => null,
'rating' => 0,
'comment' => '',
];
$data = wp_parse_args( $data, $defaults );
$insert_data = [
'product_id' => absint( $data['product_id'] ),
'user_id' => $data['user_id'] ? absint( $data['user_id'] ) : null,
'rating' => absint( $data['rating'] ),
'comment' => sanitize_textarea_field( $data['comment'] ),
];
// Add site_id if using a single table approach
// $insert_data['site_id'] = $this->current_site_id;
$format = [
'%d', // product_id
'%d', // user_id (can be NULL)
'%d', // rating
'%s', // comment
];
// If using a single table with site_id, add it to format and insert_data
// $insert_data['site_id'] = $this->current_site_id;
// $format[] = '%d'; // site_id
$success = $this->wpdb->insert(
$this->table_name,
$insert_data,
$format
);
if ( $success === false ) {
// Log error appropriately
error_log( "Database error adding review: " . $this->wpdb->last_error );
return false;
}
return $this->wpdb->insert_id;
}
// ... other methods for updating, deleting, etc.
}
The `get_recent_reviews` method demonstrates how to construct a query that leverages the composite index. Notice the use of `$wpdb->prepare()` for security and the explicit `LIMIT` clause. If using a single table, the `site_id` must be included in the `WHERE` clause and the index definition.
Caching Strategies for Read-Heavy Operations
Even with optimized queries and indexing, read-heavy operations can benefit significantly from caching. For product reviews, common caching strategies include:
- Object Caching: WordPress’s Transients API or external object caches (Redis, Memcached) can store individual review objects or lists of reviews.
- Page/Fragment Caching: Caching entire product pages or specific review sections.
- Database Query Caching: While less common at the application level for dynamic data, some database systems offer query caching.
For our custom table, we can implement object caching for the results of `get_recent_reviews`. A cache key should be unique per product, per site, and per limit/order criteria.
// Inside My_Product_Reviews_DB class
public function get_recent_reviews_cached( int $product_id, int $limit = 10 ): array {
$cache_key = 'product_reviews_' . $this->current_site_id . '_' . $product_id . '_' . $limit;
$cached_reviews = get_transient( $cache_key );
if ( false !== $cached_reviews ) {
return unserialize( $cached_reviews ); // Transients store serialized data
}
$reviews = $this->get_recent_reviews( $product_id, $limit );
if ( ! empty( $reviews ) ) {
// Cache for a reasonable duration, e.g., 1 hour
set_transient( $cache_key, serialize( $reviews ), HOUR_IN_SECONDS );
}
return $reviews;
}
When a new review is added or an existing one is updated/deleted, the corresponding cache entry must be invalidated. This is crucial to prevent stale data.
// In My_Product_Reviews_DB class, after add_review, update_review, delete_review methods:
private function clear_review_cache( int $product_id ) {
$cache_key = 'product_reviews_' . $this->current_site_id . '_' . $product_id . '_' . 10; // Assuming default limit
delete_transient( $cache_key );
// You might need to clear caches for different limits if they are used.
}
// Call clear_review_cache after successful add, update, or delete operations.
// Example in add_review:
// if ( $insert_id ) {
// $this->clear_review_cache( $data['product_id'] );
// return $insert_id;
// }
Database Maintenance and Monitoring
Regular database maintenance is essential for sustained performance. This includes:
- Index Maintenance: Periodically check for fragmented indexes and rebuild or optimize them.
- Table Optimization: Run `OPTIMIZE TABLE` on custom tables, especially after large data purges or bulk operations.
- Slow Query Logging: Configure MySQL/MariaDB to log slow queries and regularly analyze these logs to identify new bottlenecks.
- Performance Monitoring: Utilize tools like New Relic, Datadog, or Prometheus/Grafana to monitor database query times, connection counts, and resource utilization.
For multi-site environments, monitoring must be granular enough to identify issues specific to individual sites or common across all sites.
Example: Enabling Slow Query Log in MySQL
To capture slow queries, you can modify your MySQL configuration. Add or adjust these lines in your `my.cnf` or `my.ini` file:
[mysqld] slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 2 ; Log queries taking longer than 2 seconds log_queries_not_using_indexes = 1 ; Optional: log queries that don't use indexes
After modifying the configuration, restart the MySQL server. You can then analyze the `mysql-slow.log` file using tools like `mysqldumpslow` or `pt-query-digest` from the Percona Toolkit.
Conclusion
Optimizing p99 database query latency in a multi-site DDD WordPress architecture requires a proactive approach to schema design, indexing, efficient query writing, and strategic caching. By moving high-volume, site-specific data into custom tables and meticulously tuning access patterns, developers can ensure a responsive and scalable experience for users across all sites, even under heavy load.