|
We have been investigating performance issues, particularly relating to validation, with a client who has a 75GB database running on v10.0.1. (planned to be upgraded to 16 but not immediately) The server is virtualized, connected to an EMC SAN via Fibre Channel Over Ethernet (FCoE) running on a 10 Gb fibre network using Brocade switches. 11 GB of 18 GB of the RAM is allocated to cache. Performance of the validation is approx 20% compared with a desktop PC (5 hours v 1 hour). We have other users of a similar configuration but with actual FiberChannel who get far better performance. The database is built on 4k pages (like all our customers' databases). The ratio of table pages to extension pages is approximately 80:20 - I don't know if it's relevant but the same measure for the most important \ heavily used tables is approx 90:10. The provider of the infrastructure is recommending changing the page size to 32k or 64k as an aid to disk I/O performance, but given what people have said on the forum before I am concerned as to what this would do to the the efficiency of the usage of the cache etc. Can anyone give any guidance? Many thanks. |
|
This is mostly a WAG, but it's easier to type an answer than a comment :)... A database that size might have indexes that could benefit from an 8K page size, but it is doubtful that the page size will improve physical disk I/O by the amount you want. Methinks the server aleady does I/O in much larger chunks than the page size already, as mentioned here:
There aren't a lot of (any?) public success stories extolling the virtues of giant page sizes, so you are unlikely to find anyone telling you to "go ahead, listen to the vendor, ignore this exhortation on the same page as above":
Have you tried ALTER DATABASE CALIBRATE? 1
Thanks Breck - that all makes sense. Yes we have tried ALTER DATABASE CALIBRATE on this one - no effect - unlike some others where the result has been VERY beneficial (especially now I can calibrate the TEMPORARY dbspace as well :) http://sqlanywhere-forum.sap.com/questions/23844 ).
(21 Jan '15, 08:58)
Justin Willey
I've now worked out what WAG means. Over here it's "Wives and Girlfriends" of grossly overpaid football (soccer) players, notorious for their poor fashion sense / lifestyles choices!
(21 Jan '15, 16:02)
Justin Willey
Replies hidden
|
One idea: (given the poor 20% performance figure you site) You might want to compare the low level file copy performance too. {there is always a chance that some link, layer or component is not performing up to spec. there}
Also, the SAN's level of sustained performance may not match the peak bandwidth numbers you are working from. The recommendation from EMC (or the vendor/provider) does sounds like boiler-plate for SANs. Did they base that upon any real metrics that actually show that small I/Os dominate there?
Otherwise, has there been any recent REORGanziation/rebuilding of this database? If not you might want to check the degree of fragmentation for your largest tables.
And are all the accesses to the SAN just for the SYSTEM dbspace? {goes to competition for multiple, interleaved accesses and/or possible distractions from 'pushing the bandwidth'}
Validation can be quite expensive in a number of ways even when everything is running at its peak. (let me not count the possbile ways) In addition to insufficient cache scenarios, fragmentation can increase the required number of physical I/Os required to read the same amount of required page data (compared to clustered/compacted data), as can blob accesses (as you surmised). It may be important to break down the types of accesses that are dominating the slowness here.
You may want to start by narrowing down the performance barrier by table object (noting size, complexity and degree of fragmentation) first and see where that leads you.
I'm leaving off before I end up WAGging into any boggy blob aspects of this now . . .
HTH
Thanks Nick - that is really helpful. EMC have been sent a load of log files and that may produce a more specific recommendation.
Do you have any suggestions about how to check "low level file copy performance"? Do you mean just doing some basic copying files of varying sizes - or something more sophisticated?
I'll also try your idea of table by table validation and compare that with the table size and see how the rate varies.
I tend towards
copy /b-ing some large (hopefully not too fragmented) file tonul:to test for sustained read performance. Since you are validating databases, copying the SYSTEM dbspace .db file might do just fine for this.Then you can compare that time to a local copy, and compare that based upon file sizes. Do check for external fragmentation at the same time.
Effects due to internal fragmentation would not be captured in that test (of course).
{In addition} ... watching the performance of a validation using a script (filled with calls to sa_validate) may tell you what objects are most effected.
O\W me not so sophisticated. You can probably buy very sophisticated (and expen$ive) system management software to do that and more but it sometimes is sufficient to do that.
The provider has setup a server with local disks (RAID 10) so we can do a comparison (the OS is still virtualised). I thought I'd better do a basic check on disk speeds first so we know what we are comparing.
I got hold of the ATTO disk benchmarker utility (from http://www.attotech.com/disk-benchmark/) and some interesting things emerged. On the default settings the read transfer rate never got above 13 MB/sec (under 2 for 4KB files), compared with 201 MB/sec (78 for 4KB files) on a server I have here with RAID 1 (15k) disks.
When I switched off the Direct I/O option (which I as understand it ignores buffering etc) the providers machine gave 2.9 GB/s max (562 MB/s for 4K files) compared with 2.6 GB/s max (489 MB/sec for 4K files) on my server.
So with direct I/O my machine was say 15 times faster, without the direct I/O the providers machine was about 1.1 times faster than mine.
I am right out of my depth here, but I presume that when validating, the whole point is to check what's on disk not in a cache somewhere, so does the validation process in some way force actual disk reads? In that case then maybe the virtualisation is getting in the way somehow?
If you are validating the production server, the pages will all eventually be in cache. Whether or not they need to be reread depends upon their status in cache. Of course if dbvalid is autostarting the .db then all pages of any importantance will be read. Not certain how this question pertains to slower performance though . . .