Conceptual challenges between DBAs and Storage technicians and Developers.
Visit my group in LinkedIn to find more:
https://www.linkedin.com/groups/8151826
Storage for DBAs:
As a rule of thumb, pretty much any storage system can be characterised by three fundamental properties:
Latency is a measurement of delay in a system; so in the case of storage it is the time taken to respond to an I/O request. It’s a term which is frequently misused – more on this later – but when found in the context of a storage system’s data sheet it often means the average latency of a single I/O.
Latency figures for disk are usually measured in milliseconds; for flash a more common unit of measurement would be microseconds.
Latency describes the time required for a sub-system to process a single data request or transaction. With flash storage, read latency includes the time it takes to navigate through the various network connectivity. Once this process is completed, latency also includes the time it takes find the required data blocks and to prepare to transfer data.
IOPS (which stands for I/Os Per Second) represents the number of individual I/O operations taking place in a second.
IOPS figures can be very useful, but only when you know a little bit about the nature of the I/O such as its size and randomicity.
If you look at the data sheet for a storage product you will usually see a Max IOPS figure somewhere, with a footnote indicating the I/O size and nature.
Bandwidth (also variously known as throughput) is a measure of data volume over time – in other words, the amount of data that can be pushed or pulled through a system per second.
Throughput figures are therefore usually given in units of MB/sec or GB/sec.
As the picture suggests, these properties are all related.
It’s worth understanding how and why, because you will invariably need all three in the real world. It’s no good buying a storage system which can deliver massive numbers of IOPS, for example, if the latency will be terrible as a result.
The throughput is simply a product of the number of IOPS and the I/O size:
Throughput = IOPS x I/O size
So 2,048 IOPS with an 8k blocksize is (2,048 x 8k) = 16,384 kbytes/sec which is a throughput of 16MB/sec.
The latency is also related, although not in such a strict mathematical sense. Simply put, the latency of a storage system will rise as it gets busier. We can measure how busy the system is by looking at either the IOPS or Throughput figures, but throughput unnecessarily introduces the variable of block size so let’s stick with IOPS.
We can therefore say that the latency is proportional to the IOPS:
Latency ∝ IOPS
The proportional (∝) symbol suggests a direct relationship, but actually the latency of a system usually increases exponentially as it nears saturation point.
IOPS isn't latency, but related to this.
IOPS is the number of operations per second;
as the number of IOPS requested from the device increases the latency will increase.
Imagine your disk is 2 IOPS.
If you send 2 requests simultaneously, this will average 1 second to complete.
If you send 20 requests near simultaneously, this will take ten seconds to complete, so any extra requests will have a latency of 10 seconds, because they are waiting for the others to complete.
We can see this if we plot a graph of latency versus IOPS – a common way of visualising performance characteristics in the storage world. The graph shows the SPC benchmark results for an HP 3PAR storage disk system.
See how the response time seems to hit a wall of maximum IOPS?
Beyond this point, latency increases rapidly without the number of IOPS increasing.
Even though there are only six data points on the graph it’s pretty easy to visualise where the limit of performance for this particular system is.
Sometimes the term Latency is frequently misused.
The SPC performance graph is actually plotting response time and not latency.
These two terms, along with variations of the phrase I/O wait time, are often used interchangeably when they perhaps should not be.
According to Wikipedia, “Latency is a measure of time delay experienced in a system“.
If your database needs, for example, to read a block from disk then that action requires a certain amount of time.
And DB want to read more than one block that doesn't be sequential (no ordering blocks=fragmented related blocks ) this time goes more and more.
Also consider concurrency sessions and all of them need read a shared block, and adding locking database mechanisms....
The time taken for the action to complete is the response time.
If your user session is subsequently waiting for that I/O before it can continue (a blocking wait) then it experiences I/O wait time which Oracle will make to one of the regular wait events such as db file sequential read.
The latency is the amount of time taken until the device is ready to start reading the block, i.e not including the time taken to complete the read.
In the disk world this includes things like the seek time (moving the actuator arm to the correct track) and the rotational latency (spinning the platter to the correct sector), both of which are mechanical processes (and therefore slow).
You can review my previous post in Database Box LinkedIn group about type of disks and i/o concept if need more information.
When you first began working for a storage vendor you found the intricacies of the terminology confusing and I suppose it’s no different to people entering the database world for the first time.
I began to realise that there is often a language barrier in I.T. as people with different technical specialties use different vocabularies to describe the same underlying phenomena.
For example, a storage person might say that the array is experiencing “high latency” while the database admin says that there is “high User I/O wait time“.
The OS admin might look at the server statistics and comment on the “high levels of IOWAIT“, yet the poor user trying to use the application is only able to describe it as “slow“.
Therefore, it’s the application and its users that matter most, since without them there would be no need for the infrastructure.
So with that in mind, let’s finish off this post by attempting to translate the terms above into the language of applications.
Translating Storage Into Application
Earlier we defined the three fundamental characteristics of storage. Now let’s attempt to translate them into the language of applications:
Latency is about application acceleration.
If you are looking to improve user experience, if you want screens on your ERP system to refresh quicker, if you want release notes to come out of the warehouse printer faster…then latency is critical.
It is extremely important for highly transactional (OLTP) applications which require fast response times.
Examples include call centre systems, CRM, trading systems, e-Business, core banking systems, etc where real-time data is critical and the high latency of spinning disk has a direct negative impact on revenue.
IOPS is for application scalability.
IOPS are required for scaling applications and increasing the workload, which most commonly means one of three things:
- in the OLTP space, increasing the number of concurrent users;
- in the data warehouse space increasing the parallelism of batch processes,
- in the consolidation / virtualisation space increasing the number of database instances located on a single physical platform (i.e. the density).
This last example is becoming ever more important as more and more enterprises consolidate their database estates to save on operational and licensing costs.
Bandwidth / Throughput is effectively the amount of data you can push or pull through your system.
Obviously that makes it a critical requirement for batch jobs or datawarehouse, type workloads where massive amounts of data need to be processed in order to aggregate and report, or identify trends.
Increased bandwidth allows for batch processes to complete in reduced amounts of time or for Extract Transform Load (ETL) jobs to run faster.
And every DBA that ever lived at some point had to deal with a batch process that was taking longer and longer until it started to overrun the window in which it was designed to fit…
Finally,
The above “explanations” is just a rough guid to better understanding essential concepts.
the real message is to remember that I/O is driven by applications.
Data sheets tell you the maximum performance of a product in ideal conditions, but the reality is that your applications are unique to your organization so only you will know what they need.
If you can understand what your I/O patterns look like using the three terms above, you are halfway to knowing what the best storage solution is for you.
☑️My experiences:
When working as a DBA with a storage support team as a coworker, and you faced a slowing on disk, you can see that IOPS graph provided by storage man is healthy stats and he/she could not see any delay or pressure on disks, but you have slow at sometimes, especially when you are slow on Redo logs, because of sequential mechanisms of redo vectors this type of object in database need a separated and exactly isolated disk with preferred speed and minimal latency, at this situation your problem is not available high IOPS disks, instead actually you need lower IOPS but heavy and heay sequential small IOPS for redo generation and you need to convince her/him to replace high IOPS disks with a more Throughput and minimum latency disk by adding disks to Raid group(add spindle) for example use 4 disks in raid 10 instead of 2 disk.
IOPS vs. Throughput
To summarize the difference between throughput vs. IOPS, IOPS is a count of the read/write operations per second, but throughput is the actual measurement of read/write bits per second that are transferred over a network. To visualize this in a typing context, IOPS would be similar to words per second (where words can be different lengths, but only whole words are being measured), and throughput would be characters per second (where characters are the indivisible component). You can also think about this contrast as IOPS vs. read/write speed.
IOPS =Count of read/write operations per second
Throughput = Count of read/write bits per second (bps)
What’s The Best Storage Performance Metric?
This can be a tricky question to answer because storage requirements can vary between environments, and can even be different based on the specific application. A high-performing storage environment can mean many different things. One thing we can confidently say (based on the content of this article) is that the different between IOPS vs. throughput is frequently asked about.
Sometimes high throughput or high IOPS mean success but may not tell the full story. That is why we recommend using latency in addition to IOPS and throughput.
🟥 I wrote 3 sequential post about hardware concepts and challenges for DBA in LinkedIn (Database Box Group) that always are in collaboration with storage team and concurrently with applications developers so we meed deep knowledge of hardware concepts to achieve better performance and discovering issues in every layer as a DBA responsibilities.
Best regards,
Alireza Kamrani
Senior RDBMS Consultant.
