title: Dynamically re-sizable thread pool
body: <p>I have a following workflow in my application: there can be X requests from users (usually 5-10 simultaneously) who want to search for something in the system (each request is handled in a separate thread).</p>
  
  <p>Each search can be handled in parallel (which I am currently implementing). Threads/CPU usage isn't really the problem here as those tasks aren't CPU intensive. The database is the bottleneck.</p>
  
  <p>Currently I set up a separate DB connection pool only for the search mechanism - with max pool size set to 10. I know that's not much but I can't set it up higher. Now I am trying to figure out how to set the thread pool for each search (per user).</p>
  
  <p>Each request (thread) will spawn a separate thread pool (and in this pool each thread will handle a part of a given user's search). Will setting this thread pool's size to a fixed size (lets say 4) be really problematic if for instance 10 users hit the "search" button at once since it would spawn 10 thread pools with 4 threads each = 40 threads and there are only 10 db connections in the pool? I guess some of the threads would simply be idle and the rest would race to get a connection for the pool but would that really be a huge problem?</p>
  
  <p>If yes then what would be the best course of action:</p>
  
  <ol>
  <li>checking how many thread pools there already are while creating a
  new one and adjusting its (this new pool's) max thread pool size accordingly (say
  there are already 2 pools with 4 threads each then the new one would
  be created with max threads set to 2 and even newer pools with lets say only 1 max thread). This would mean that each next user's search would be substantially slower.</li>
  <li>creating the thread pool with the same max thread pool size (i.e. 4) but implement my own thread pool which would dynamically check how many threads there are in the application and resize maxThreadPoolSize accordingly (in this case for instance all thread pools, 2 old ones and the new one, would be downsized to lets say 3 thread). This would require each thread pool access to some shared object containing information about all thread pools in the application.</li>
  <li>something else?</li>
  </ol>
  
  <p><strong>EDIT:</strong>
  Thanks for all the comments/answers. To clarify why I wanted a thread pool per request - that was done so one user would not use up the whole threadpool. The flow is exactly like this: when a user hits "search" a list of objects is generated (this list can be ranging from 1 item up to thousands), then for each item a DB lookup is performed. Now it is all performed sequentially. After my changes each task handles one lookup (because the search on DB is pretty slow it gives me a really huge boost - I know I could try to do some DB fine tuning but I'm not in charge of it).</p>
  
  <p>The problem is that if I User1 comes and performs a really generic search for X thousand generated items it can take several minutes (or more). So I can have thousands of tasks in the executor from a single user. Then if I have a shared thread pool with lets say max 10 threads (the same number as connection pool) this request will be put in thread pool's Queue. Now if User2 comes and performs his search he will have to wait for User1's search to finish as his search will be put into that same Queue. This is a situation I want to avoid with thread pool per request.</p>
  
  <p>I'm not really that afraid of context switches as each computation can take up to several seconds so they won't occur that often.</p>
  
  <p>Currently I'm thinking about a shared thread pool and a manager to which each user thread would send his data and that manager would then send it to the thread pool whenever there would be an idle thread. This way I could implement it (the manager) to send tasks from different users (i.e. no one user would not dominate the thread pool).</p>
  
  <p>The problem I see with such an approach is that I would need to inform somehow the "parent thread" (meaning user request) that all its tasks were processed by the manager and send it the results somehow.</p>