Safar Booking Theme: Debugging MariaDB Locking and PHP Latency

We deployed the Safar – Hotel & Resort Booking WordPress Theme on a cluster of three nodes running Debian 12, each equipped with an AMD EPYC 7003 series processor and 64GB of DDR4 ECC RAM. The stack is relatively standard: Nginx 1.24 (acting as a reverse proxy), PHP 8.2-FPM with the Zend OPcache enabled, and a separate MariaDB 10.11 instance for the database layer. The Safar theme handles complex booking logic—date range checks, seasonal pricing, and room availability—through a series of AJAX-heavy interactions.

The specific issue identified was a 450ms jitter during the safar_check_availability AJAX request. This latency did not occur during peak traffic, nor was it accompanied by CPU spikes. It was a consistent, low-level stutter that affected the user experience during the room selection phase.

The Diagnostic Path: Packet Analysis and Xdebug Profiling

To isolate whether the delay originated in the network stack or the application code, I initiated a tcpdump session on the application server, capturing traffic on port 3306.

tcpdump -i eth0 -n -s 0 port 3306 -w db_traffic.pcap

Analyzing the .pcap file in Wireshark revealed that the application server was waiting for a Response packet from the database for approximately 380ms after sending a specific SELECT query. The query in question targeted the wp_safar_bookings table, attempting to join room metadata with availability dates.

Simultaneously, I enabled Xdebug 3.2 on a staging clone of the environment, specifically using the profiler to generate a Cachegrind file. The flamegraph showed that 62% of the execution time was spent within the Safar_Booking_Engine::validate_date_range() method. This method was performing a nested loop to compare requested check-in dates against existing entries in the database.

Database Schema and Locking Contention

The Safar theme, while robust in its front-end presentation, relies on a database schema where the availability data is stored in a single table without composite indexes for the room_id and booking_date columns. When a user queries for availability, the engine runs a search across all historical records.

In a standard Download WooCommerce Theme environment, you might expect standard table structures, but Safar uses custom post types for "Rooms" and a custom table for "Bookings." The lack of an index meant MariaDB was performing a full table scan for every availability check.

I examined the InnoDB storage engine status:

SHOW ENGINE INNODB STATUS;

The output indicated a high number of READ COMMITTED locks being held during the INSERT phase of the booking process, which was intermittently blocking the SELECT queries from the availability checker. Even though the system was not under heavy load, the row-level locking was causing a queue. This is a common bottleneck in hotel themes where the "search" and "reserve" actions hit the same data blocks.

PHP-FPM and Session Overhead

Further investigation into the PHP-FPM logs showed that pool www was occasionally hitting its max_children limit, but not because of request volume. Instead, the processes were lingering in a CLOSE_WAIT state. The Safar theme utilizes the native WordPress WP_Session class, which, on this specific configuration, was configured to store session data in the database.

Every time the availability engine was called, the PHP process had to lock the session row in the wp_options table. If the database was already slow due to the unindexed booking queries, the session lock would hold up the PHP worker, preventing it from returning to the pool. This created a cascading delay.

Refining the SQL Logic

The immediate fix required a twofold approach: optimizing the database indexes and refactoring the PHP date validation logic. I added a composite index to the custom booking table:

ALTER TABLE `wp_safar_bookings` ADD INDEX `idx_room_date` (`room_id`, `check_in_date`, `check_out_date`);

This reduced the query execution time from 380ms to 4ms. However, the PHP logic in Safar_Booking_Engine still had to process the returned data. The original theme code used DateTime::diff() inside a foreach loop that iterated through every day of the requested stay. For a 14-day booking, this was 14 iterations per existing reservation.

I refactored this to use a SQL-based date overlap check, which is significantly more efficient than PHP-level iteration. Instead of fetching all bookings and comparing them in PHP, I moved the logic to the WHERE clause:

SELECT count(*) FROM wp_safar_bookings 
WHERE room_id = %d 
AND NOT (check_out_date <= %s OR check_in_date >= %s)

Buffer Pool and Memory Tuning

With the queries optimized, I turned to the MariaDB configuration. The innodb_buffer_pool_size was set to 1GB, which was too small for the growing booking table and its new indexes. I increased this to 48GB (roughly 75% of total RAM) to ensure the entire working set stayed in memory. I also adjusted the innodb_log_file_size to 2GB to reduce the frequency of checkpoint flushes during high-volume reservation periods.

On the PHP side, the memory limit per script was set to 256MB. Profiling showed that the Safar_Booking_Engine was consuming 45MB per request due to the way it loaded room attributes into a global object. By implementing a lazy-loading pattern for the room attributes—only fetching the specific metadata required for availability (e.g., "max_occupancy")—I reduced the per-request memory footprint to 18MB.

Nginx and Upstream Keepalives

To further squeeze out the latency, I adjusted the Nginx upstream configuration. By default, Nginx opens a new connection to the PHP-FPM socket for each request. Under the Safar theme's AJAX-heavy workflow, the TCP handshake overhead becomes measurable.

I enabled keepalives for the upstream:

upstream php-fpm {
    server unix:/run/php/php8.2-fpm.sock;
    keepalive 32;
}

This keeps 32 connections open to the PHP-FPM pool, eliminating the SYN/ACK cycle for subsequent AJAX requests. This change alone reduced the TTFB (Time to First Byte) by another 15ms.

Final Cache Strategy

The Safar theme does not natively support object caching for its availability data because the data is highly dynamic. However, we implemented a 30-second Redis cache for the availability results. While 30 seconds sounds short, it is sufficient to handle the "rapid click" behavior of users exploring different date ranges for the same resort.

I used the wp_cache_set and wp_cache_get functions within the safar_check_availability hook, using a key composed of the room_id and the MD5 hash of the date range. This effectively offloaded 70% of the repetitive queries to memory.

Technical Implementation Snippet

Ensure your MariaDB configuration avoids the common mistake of over-allocating the query cache, which can lead to global mutex contention. In modern versions, it is better to rely on the InnoDB buffer pool.

# MariaDB Tuning for Safar Theme
[mysqld]
innodb_buffer_pool_size = 48G
innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_buffer_pool_instances = 8
max_connections = 500

# PHP-FPM Pool Adjustment
pm = static
pm.max_children = 100
pm.max_requests = 1000

Do not use the DateTime object inside large loops if a simple timestamp comparison can achieve the same result. The overhead of object instantiation in PHP 8.2 is reduced, but at the scale of a hotel booking search, it still accumulates.