Renting a server or a virtual machine on the network is becoming more accessible. At the time of this writing, renting a powerful 4th nuclear server with unlimited traffic costs only $ 70-100 per month.
For this reason, many companies, webmasters and developers rent a server for several projects instead of using shared hosting.
The advantages are obvious: greater control over the work of applications and the full use of server resources for their projects.
')
How to determine that server performance is not enough and it's time to upgrade or replace? Which component has become a bottleneck?
This article will help you answer the question yourself - is a server required? - using Microsoft Excel PivotTables. Using the described method, it will not be difficult for you to prepare a beautiful illustrated report to management, as well as quickly find bottlenecks in the system. There will be a lot of pictures!
We need two things: data and a tool for analyzing them.
To analyze performance data, we will use the Pivot Tables from Microsoft Excel. As an example of data, the result of the Performance Monitor is recorded in a CSV file. This is a standard Windows Server tool, but the data obtained on any system can be processed in the same way.
Start - data collection
To manage something, you need to measure it. To analyze the performance of the server, we need measurements of performance indicators.
To collect Windows server data, you must first configure Performance Monitor.
When configuring monitoring tools, the intervals between recording performance data counters should be equal to or more than 5 minutes. During the week you will accumulate enough for analysis.
In Windows Server 2008, there are predefined collections for gathering information using Performance Monitor. You can use them by changing the format of writing data to a CSV file, or customize your own.
To assess the basic performance of the server, it is enough to collect information.
- Average Disk Queue - for hard drives
- % Processor Tme - for processor and processes
- Committed Bytes - for RAM
You can also add to the set any other data you deem necessary. To assess the workload, system queues to various system resources — processor, disk — are particularly interesting. When a process needs access to a physical resource, the operating system queues the request. If there are more than 2 elements in the queue, then the resource becomes a bottleneck.
For further analysis, it is better to save the data to a CSV file immediately.
We start the analysis
For analysis, I use Excel 2010 PivotTables. Similar functionality exists in Microsoft Office and earlier versions, as well as in recent OpenOffice.
First you need to load the collected data into the document.
Insert a set of source data for analysis
To insert data, go to the "Data" section, then select "Get external data" and "From text":
Select the file in which the monitoring data is saved and click "Import":
Excel will launch the data import wizard.
Performance Monitor uses a comma as a delimiter. The easiest option is to have the same regional settings on the system where the data was collected and yours.
We will indicate to the master the necessary parameters:
At the last stage, it is important to check that the separator matches the fractional part of the whole. If they differ - depending on the settings, this can be either a period or a comma, then you need to tell Excel which character is used in the imported data.
To do this, select all data columns in the wizard, and click "Details":
Now click "OK" and "Done."
It remains to specify where to put the data in the document:
Excel imported data into a document:
For data processing, we will use a pivot table. To create it, you first need to tell Excel that the characters in the cells in the sheet are a table of related data.
In the 2010 version, just press Ctrl-L. Excel automatically selected all cells filled with data:
The table was colored with beautiful colors, and drop-down lists for filtering data appeared at the top - now the data are treated as a linked table.
To simplify further work, I will remove the server name from the column headings:
Create a pivot table with the schedule
Select "Insert", then "Pivot Chart":
A pivot table with our data and a diagram related to it appeared on the new sheet:
Further analysis will occur like a charm.
Let's look at the number of requests in the queue to the hard disk.
We place time on the fields of axes, and Avg. Disk Queue Length:
In this case, Excel counted the number of measurements.
Change the number to the average for the period:
I also adjusted the title so that the pivot table columns are not too large:
Actually analysis
The graphics immediately show that the hard disk is not a bottleneck - the queue occasionally exceeds one:
The field "Date" can be grouped:
Now you can collapse the pivot table to days:
Immediately visible is the most loaded day of the week:
And this may look like the original report from the working system:
Successes in measurements!