|
When I run SELECT DB_EXTENDED_PROPERTY('IOParallelism','SYSTEM'); on my PC (1 processor 16 cores, RAID 1 HHDs) I get a value of 64. On a client's server (2 processors, 48 cores, v fancy EMC all SSD SAN, connected by fiber channel) I get a value of 1. This latter doesn't change if ALTER DATABASE CALIBRATE PARALLEL READ;is run. Is this expected and does it matter? This is a busy server with around 900 concurrent users, so I wouldn't be expecting a lot of hugely parallel query plans to be being chosen, but will other things be effected? I can't find a lot in the docs about IOParallelism beyond "Returns the estimated number of simultaneous I/O operations supported by the dbspace." One would think that a high number would be good - reducing queueing, but I suspect it's not that simple. |
Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- numbered list: 1. Foo 2. Bar
- to add a line break simply add two spaces to where you would like the new line to be.
- basic HTML tags are also supported
Question tags:
question asked: 28 Jul '15, 09:11
question was seen: 1,792 times
last updated: 28 Jul '15, 14:38
The calculation of IOParallelism is done using a actual test of the read speeds (as opposed to inspecting the physical disk topology). If bringing pages in to cache in parallel isn't significantly faster then sequentially, the IOParallelism number could be lower then the number of striped disks. How big is the database file? If the SAN is caching the whole thing, then the parallel read probably wouldn't be any faster.
The database is around 100GB. The db server has almost that much cache. Also - as I understand it, the SAN will more or less cache everything as well, so that fits with what you are saying.
At this point a total ignorance warning on my part is required :)
I don't understand your point about the SAN cache. What this possibly means is that I don't really understand what this stat really represents. I had imagined that it dealt with how many simultaneous read requests the server reckoned it is best make to the disk system. However fast the cache of the SAN, isn't it always going to be faster to to have quite a few requests happening at once rather than queuing them one at a time (assuming that there are lots of requests waiting for data and that the disk system is capable of supporting it)?
With the amount of cache, we see very little in the way of disk reads most of the time. However we sometimes have periods of high disk activity, often related to temporary file usage and it's those that I was wondering if the IOParallelism would impact.
(FWIW, this is just an educated guess. Someone with deeper understand of the dtt model can correct me.)
My understanding is, I/O parallelism is really designed to determine how many separate rotational devices are behind the logical volume. Normally, when running the calibration on rotational media, the time to read a bunch of random pages would be absolutely dominated by the hard drive seek time. The time to bring random pages into the database server cache from the SAN cache is probably very small and mostly overhead (but no ~50ms seek time for each page). Even without the in-memory cache, it would probably be very hard to speculate about the number of SSDs in a RAID array by reading random pages. They're just too fast.
Regarding whether this number even matters for your system: I'm pretty sure I/O parallelism is only used to determine the suitability of parallel index scans (with index pages on the disk) when building an access plan. I wouldn't expect it to slow down temporary file access.