How many sessions of PDO threads do I need?

by

Quick Answer

The number of PDO (PHP Data Objects) threads you need depends on the specific requirements of your application. Generally, you should use a thread pool with enough threads to handle peak load while avoiding thread starvation or oversubscription. Common thread pool sizes range from 5-25 threads. Benchmarking your application under load is the best way to determine the ideal thread count. Start with 10 threads and adjust up or down based on performance metrics.

What are PDO threads?

PDO threads allow you to make asynchronous database queries from PHP using a thread pool. When you make a query with PDO in asynchronous mode, the query is delegated to a worker thread from the pool instead of blocking the main PHP process. This allows PHP to continue processing other requests while the query runs in the background.

The worker thread handles running the query and fetching the results. Once complete, the results are passed back to the main PHP process. Using threads can significantly improve the overall throughput and responsiveness of database-driven PHP applications.

Benefits of PDO threads

Here are some of the key benefits of using PDO threads:

  • Improves application throughput – More queries can be handled concurrently.
  • Faster response times – Pages return without waiting for slow queries.
  • Better resource utilization – Queries run in parallel in separate threads.
  • Asynchronous programming model – Code is written linearly without callbacks.
  • Built on stable thread-safe PDO – Takes advantage of PDO’s maturity.

By alleviating the bottleneck of synchronous PHP processes, PDO threads can help applications handle more traffic and reduce latency.

When to use PDO threads

PDO threads are most applicable for situations when:

  • Your application makes multiple slow database queries per request.
  • Query latency is causing unacceptable response times.
  • Traffic is intermittent with periods of high load.
  • You want to improve resource usage during blocking I/O.
  • Your application uses MySQL or PostgreSQL databases.

Basically any I/O intensive PHP application dealing with a relational database can benefit from PDO threads. They make it easier to scale up and handle spikes in traffic.

How PDO threads work

PDO threads use a worker thread pool model. Here’s a quick overview:

  • The thread pool is initialized at PDO creation with a max size.
  • Main PHP process queues queries to be handled async by the thread pool.
  • An available worker thread takes the next query and runs it.
  • Once finished, the worker passes results back to main process.
  • Main process now has query results without having to wait.

This cycle repeats as additional queries are made. The thread pool allows queries to execute concurrently while sharing a fixed number of threads.

Sizing the PDO thread pool

So how many threads do you need? There are several factors to consider:

  • Available CPU cores – More threads can utilize more cores, but oversubscribing cores will contention.
  • Type of queries – Simple reads need less threads than complex writes or analytics.
  • Connections per thread – Can reuse threads if you limit connections per thread.
  • Cost per thread – Each thread has memory overhead from stack space.

In general, you’ll want a thread pool large enough to handle peak load without queued requests getting delayed. But not so large that you waste resources on unused threads.

Benchmarking to determine size

The best way to optimize the size of your PDO thread pool is to benchmark with production-like load. Here are the basic steps:

  1. Start with a conservative thread pool size like 10 threads.
  2. Use load testing tools to simulate concurrent users and requests.
  3. Slowly ramp up traffic over time and monitor for bottlenecks.
  4. If latency spikes or queries queue, increment the thread pool size.
  5. Find the smallest size that still handles peak load with low latency.

Continuously measure response times, throughput, CPU usage, and queueing as you adjust the thread count. This will determine the ideal thread pool size for your workload and server resources.

Thread pool sizing guidelines

As a starting point, here are some general PDO thread pool sizing guidelines:

  • 5-10 threads – Light workload with simple queries
  • 10-25 threads – Moderate traffic with complex queries
  • 25-50 threads – High load or complex analytics queries
  • 50-100 threads – Very high traffic and many concurrent connections
  • 100+ threads – Extreme transactional load across multiple servers

Of course your optimal thread count depends on your specific use case. The benchmarks will dial in the best size. Err on the side of too many versus too few.

Other PDO thread tuning tips

In addition to thread pool size, here are some other tips for tuning PDO threads:

  • Use fewer connections per thread for simpler queries
  • Limit connections per thread for complex writes
  • Lower max_execution_time for faster timeout
  • Adjust PHP’s memory_limit for thread stack space
  • Increase database connection limits if hitting limits
  • Tune MySQL/PostgreSQL configuration for concurrent connections

Proper database server configuration is also critical for supporting performant PDO threads.

Monitoring and adjusting at runtime

It’s also good to monitor your PDO thread pool at runtime and adjust the size dynamically based on current load.

For example, you can track metrics like:

  • Active threads
  • Idle threads
  • Queued queries
  • Latency
  • Throughput

If queued queries increase or latency spikes, you may want to automatically increment the thread pool size. This lets you respond to changing load patterns.

You may also want to scale back idle threads during periods of low traffic to minimize resource usage.

Example PDO thread pool usage

Here is some sample code showing how to create a PDO connection with a thread pool:

$pdo = new PDO(
  'mysql:host=localhost;dbname=test', 
  $user, 
  $pass,
  [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_MULTI_STATEMENTS => false,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, 
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4', 
    PDO::ATTR_STRINGIFY_FETCHES => false,
    PDO::ATTR_MAX_CONNECTIONS => 5,
    PDO::ATTR_POOL_SIZE => 20
  ]
);

In this case we are creating a MySQL PDO connection pool with 20 threads and allowing up to 5 connections per thread (for 100 concurrent queries).

You would then issue queries asynchronously:

$stmt = $pdo->prepare('SELECT * FROM table1 WHERE id > ?');

for ($i = 0; $i < 1000; $i++) {
  $id = random_id();
  
  $stmt->executeAsync([$id], function ($stmt) {
    // process row results 
  }); 
}

The executeAsync runs the query in the background freeing up PHP while it completes.

Conclusion

PDO threads allow PHP applications to make non-blocking database queries by delegating work to a thread pool. To maximize performance, the thread pool size should be large enough to handle peak load but not too large to waste resources.

A good starting point is 10-25 threads, but benchmarking under load is the best way to determine the optimal size. Tune the thread count based on latency, throughput, and system resource usage. Monitor at runtime and scale the thread pool up or down as needed. With the right thread pool configuration, PDO threads can significantly improve database performance and scalability.

Related posts:

Leave a Comment