I've a list of servers (rows) that I'd like to spread a total load across.
Each has a max load, & a recommended % load threshold to prevent issues. Different servers have different max loads.
(Note... For the non server nerds equally this could work for team members and tasks rather than servers)
My aim is to create a model to spread the load evenly across each server up until it's max threshold. I start with an average across the number of servers I have.
On any one server where the average load is greater than it's threshold (max * recommended %) I want it to only display that maximum load ie not the average. This bit I have sorted.
The tricky but is where I want the remaining 'surplus' on an otherwise loaded server to get added into a remaining bucket of 'load' that then gets added to the original average and applied to the remaining devices.
Small are filled, & larger servers take a greater proportion of total load, possibly even being under utilised.
I can then model the impact of swapping in diff sized servers to my hearts content.
The problem is that I keep getting circular refs when trying to apply that surplus figure!
Possibly, you might be able to use Solver to arrive at a solution. Or, if your model is set up to handle iterative calculations, you could turn on circular references?
How are you measuring server load, is it just a number between 1.0 and n.0, where n is the number of CPUs on a given server? I am having trouble coming up with a mock data sample to match your scenario.
Thanks Iliace. </br> I posted that from my phone last night so let me add some sample data that might help explain this further. </br> We're almost there but what we're missing from your suggestion is we want to spread the 'observed load' across the machines. Eg number of transactions it can handle. </br> So, if we had a total 'observed load' of 50 transactions, then this needs to be spread across enough servers of the right size to take that load. Ie the servers in the model must have a combined storage of at least 50, plus the recommended overhead on each box (threshold). With this I can then see the impact of dropping one of the boxes out of the model. </br> Will the remaining servers still be able to handle that load, or do we run into problems where the distributed load has maxed out all servers and there's still some left as 'undistributed load'. Does that make more sense? </br> Excel 2013 64 bit
A
B
C
D
E
1
Server
Max Load
Threshold
Recommended
Optimal
2
Server1
20
85%
17.0
6.7
3
Server2
10
85%
8.5
6.7
4
Server3
10
85%
8.5
6.7
5
Server4
5
90%
4.5
4.5
6
Server5
5
90%
4.5
4.5
7
Server6
5
90%
4.5
4.5
8
Server7
2
60%
1.2
1.2
9
Server8
2
60%
1.2
1.2
10
Server9
1
60%
0.6
0.6
11
12
Total Load to be distributed (eg workorders / bandwidth / transactions)
Maybe. Check me if I got the logic. Strategy is, distribute "optimal load" to each server first, then distribute the rest as a ratio of excess Recommended over Optimal. Anything above that is either undistributed, or you could use a similar rule to go up to Max Load if desired.
Awesome! Nearly cracked it, thanks soooo much! Couple of questions. What's the logic behind creating the 'optimal' calculation and not just using the recommended max? When I look at the returned values they don't seem to fit the real life scenario. We have a 20 unit max box with the same optimal load as a 10 unit max box? This calculates that bigger boxes would optimally run at 67% load (except the server one which is only using 33%). The other smaller boxes seem to run at their recommended max values. Shouldn't all of them run at their maximums? ie why not just compute the load up to the maximum?
Ok, on my PC today and have been testing this. </br> With our figures, we've got a set of servers where we want to distribute a set of 'units'. Units are based on what is being pushed onto those boxes (eg bandwidth, transactions, work orders... whatever). This is our 'total load to be distributed' and is set to begin with. </br> We want to have enough boxes in that list to make sure the 'Total load to be distributed' fits nicely across the available servers. We don't want too many servers ie a lot of '(undistributed)', and we don't want too few servers so the 'total to be distributed' is greater than the sum('recommended' for each server). So, if there are 9 servers at the moment with 60 units to put across them. Available capacity = sum(recommended max load) = 50.5. We've got an excess of 9.5 so the model shows we need another server or two to handle that. </br> Model works. Great. </br> However, we have a bug..... Try taking the Load to be distributed down to something way smaller than total recommended load.... say to 10. At this point you would expect this to easily fit across the available servers, there to be only 10 units distributed, and lots of 'un-allocated' spare space. You could then drop out a lot of servers and stick with one big one. However with the calculations we still get 45 distributed, rather than a max of 10. </br> So, I think we need to drop optimal, this is redundant as we already have 'max recommended load' (which is the optimal for that server) </br> And need to work out why we're not using the 'units to be allocated' properly. We're using distribution rather than a proportion of the units perhaps? </br> Make sense? </br> </br> Excel 2013 64 bit
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option.
Go back
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar. 2)Click on the toggle to disable it for "mrexcel.com".
Go back
Disable uBlock Origin
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
Go back
Disable uBlock
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
