La version Française est disponible ici: Programmation linéraire appliquée à la virtualisation
Operational research is a mathematical discipline providing tools for decision support. Among the problem that can be solved using these techniques are combinatorial problems. An example of this type of problem: sizing a virtualization infrastructure.
This time we won't use
LibreOffice. With the linear solver integrated into
LibreOffice suite, it becomes very simple and almost easy to solve this problem. This is obviously just one example among many other of what we can do with
A company wants to deploy a web platform as virtual machines. These virtual machines will have of course different functions:
Master Database and
Company's technical teams have conducted tests of the future web platform and identified needs in terms of
memory. In addition, the company does not want to acquire new types of hardware. Therefore we have to host the web platform on existing servers. We have 3 types of servers with different abilities in terms of
memory, all known from technical teams again after tests. In addition, technical teams have found threshold effects, which degrade server's performances. These threshold effects are also shown.
The goal is to determine the number of each type server we have to buy while minimizing costs.
x3 the number of servers from
SRV3 sort to acquire.
For each criterion (
memory), it is understood that total need for virtual machines have to be covered by the contribution of physical servers. We apply threshold for each capacity item, ie only 60% capacity
I/O disque, 60% capacity
CPU and 95% of the
memory are actually "available".
This give us 3 constraints:
disk I/Oconstraint: 540
Right values represent the virtual machines global need.
The total cost for this platform will depend on how many of each type to buy. The cost calculation is actually quite simple:
- Monthly cost:
x1(5500/36 + 55) +
x2(3000/36 + 40) +
x3(2000/36 + 30)
- 36 month cost:
x1(5500 + 55*36) +
x2(3000 + 40*36) +
x3(2000 + 30*36)
Once the basics of the problem, we can solve it. In this case, we will use
OpenLibreOffice, specifically Calc. You can retrieve the Calc workbook used for this exercise.
Cells in light green can, and should, be modified by you. This will allow you to play several scenarios.
Cells containing the information
need of virtual machines and
server capacity can also be modified. Obviously, it will enroll representative values of your hardware and / or virtual machines.
However, do not change anything outside of
step 1, except if you exactly know what you do:
Step 2 consolidates the needs and capabilities,
Step 3 gives you the information
Number of physical servers. As in
step 4, you guessed it gives you the final result with the cost of your infrastructure. All uses formulas, beware if you want to change any of them.
LibreOffice Solver usage
Tools > Solver menu, as showed on capture below:
In opened frame,
cellule cible indicates our target, ie final cost. It's
F24 cell. As we have to minize cost, you can choose
Minimum. The cells that can be modified are physical servers number, ie cells
E24. Finally, our constraints are:
F17cell have to be lower or equal than
F18cell have to be lower or equal than
F19cell have to be lower or equal than
We're almost there. One last detail to be: make sure that the number of server to be positive on the one hand, and especially an integer. I doubt that 2.3215 server will be of any use, nor to you, nor to the CFO:). To do this, click
Options and tick the two boxes as shown below:
Once done, you can ask for a solution clicking
Solve. If everything goes well, you got the result:
You noticed that the sheet has been updated. Click
Keep the result to close the frame:
You are then able to announce the investment necessary for the production launch of the platform:
- 8 servers type
- 16 servers de type
- 14 servers de type
Total cost is 4833,33 € per month, ie 174 000 € for 36 month.
You can also test different scenarios, forfor example to test the impact of adding RAM to a server type . To do this, edit the cell in
step 1 and repeat the optimization. Each time, the optimizer will give you the combination which will be the lowest cost ... Magic math!
Another example, how to "neutralize" a type of physical server? Simply by setting its capacity to 0! :)
Of course, asking manually for optimisation can be tedious. One solution: Macros. The optimizer is a component of LibreOffice among others, and it is possible to control it from a macro. You can then update the sheet with a simple click of a button. But that's another story.
This example is obviously and deliberately, simplified. Many other parameters come into play. But now that you know how, feel free to adapt the worksheet. As an exercise, you can search for the ideal distribution of virtual machines on different servers.