SETUP:
We have a .Net application that is distributed across 6 local servers with a local database (ORACLE), 1 main server and 1 load balancing machine. Requests are sent to the load balancer, which redirects incoming requests to one of 6 local servers. At certain intervals, data is collected on the main server and redistributed to 6 local servers in order to be able to make decisions with complete data.
Each local server has a cache component that caches incoming requests based on different parameters (location, incoming parameters, etc.). With each request, the local server decides whether to go to the database (ORACLE) or get a response from the cache. However, in both cases, the local server must receive the database in order to do 1 insert and 1 update for each request.
Problem:
On peak day, each local server receives 2000 requests per second, and the system begins to slow down (CPU: 90%). I am trying to increase capacity before adding another local server to the mix. After running some benchmarks, the bottleneck, as always, is the inevitable 1 insert and 1 update for each query in the database.
TRAINING METHODS
To reduce the frequency, I created a Windows service, which is located between the database and the .NET application. It contains a pipe server and receives each insert and update from the main .NET application and saves them in a Hashtable. Then, at a certain time interval, the new service is sent to the database once to make inserts and updates in batch mode. It was about accessing the database less frequently. Although this had a positive effect, it did not benefit the system as far as I expected. Most of the processor load comes from oracle.exe in the form of queries per second.
I am trying to avoid access to the database as much as I can, and the only way to avoid the database seems to increase the cache hit ratio, different from the above solution that I tried. The cache hit ratio is about 81% at present. Since each local computer has its own cache, I actually miss a lot of cached requests. When two similar requests are redirected to different servers, the second request cannot benefit from the cached result of the first.
I do not have much experience in system architecture, so I would be grateful for any help in this issue. Any suggestions on various caching or tuning architectures or any tools are welcome.
Thanks in advance, I hope I put my question clear.