I observed substantiually poorer performance w/ v.17 compared to v.16

[former_SQLA_member1694885]

Dear all,

I was trying to stress a SQL Anywhere (network) server with concurrent INSERT xacts from 20 concurrently running dbisql[c] scripts. The database was created using default settings, pre- populated with a 1M2 row table (the good old IQ megaphone sample data set) and then hammered by generated insert scripts adding the same data set shifted by 12 months using some 390k xacts. the scripts were executed from the same machine.
The machine was the biggest piece of iron I could get hold of (2 sockets XEON, each 6 cores, HT disabled, 96 GB RAM, Win 7 Ultimate 64bit). I used the same 2 SATA spindle BIOS level RAID 0 drive for both sets of test runs. I used 8 cores and 4GB for the dbsrv1x [-ca 0 -c 4g -gnl 24 -gtc 8]. The v.16 software is build 2419. I've upgraded the v.17 software to the latest release I found - 17.0.8.4103 - to make sure I wasn't held up by a fixed issue, but the metrics were pretty much the same as with the older build I had used first.
With v.16 the OS level observation (Windows Task Mgr) came somewhat close to my expectation: The CPU load was not constantly at 100%, but most of the time at least 80%+. Finally, the operation was finished after about 1:25 minutes (dbisql -q) / 1:00 minute (dbisqlc -q) with a dbsrv16 CPU consumption of 266 / 213 seconds (server property ProcessCPU).
With v.17, the Task Mgr showed lower utilization, and the elapsed time was finally about 2:30 minutes (dbisql -q) / 1:52 (dbisqlc -q) with dbsrv17 CPU consumption at 335 / 305 seconds.
Same machine, same disk for DB & scripts, same server start options. The v.16 installation is on an SSD based Windows installation, the v.17 on a VHD (same RAID 0). Temp directories are always on the RAID 0 (no VHD). Other relevant differences I could think of: The v.16 installation uses Bitdefender Internet Security, the v.17 uses MS security essentials. Also, the systems have different software installed besides SQL Anywhere - the SSD used for v.16 has some office stuff (MS Office, Softmaker Office, an Outlook file access utility). The VHD has an (expired) InfoMaker evaluation and OpenClient 15.5 (I had to adjust the PATH variable to get the correct dbisql). I don't see what impact the co- existing software should have, but that may be the result from lack of insight.
Does anybody have a clue why v.17 might be (significantly) slower or maybe less scalable than v.16? I can provide repro scripts in case anybody wants to (try to) reproduce.

TIA for any insights you can share...

Volker

[former_SQLA_member1694885]

To build the pre- populated DB: Extract the Build.zip [https://www.db-tecknowledgy.info/fileshare/repro/171215/Build.zip ] archive to an arbitrary empty directory (not %temp%). Run 0Build.BAT. Enter version number when prompted. Once the job is complete, DBStop the personal server used bui the Build script.
Everything called Miniphone is not used for this purpose (just to confuse the Russians :-).
To run the scripts: Extract the Scripts.zip [https://www.db-tecknowledgy.info/fileshare/repro/171215/Scripts.zip ] archive to an empty directory. Create the appropriate %hostname%.opt file for your machine. I've used 8 of the 12 available cores for DBSrv and set the minimum number of threads to 24 to make sure there are sufficient. (Assuming you use v.17; otherwise replace version number) "DBErase -y Megaphone17.db" if one exists. "copy %temp%\\megaphone17.db ." (that's where 0Build.BAT created the pre- populated database). Start the database using "dbsrv17 @%computername%.opt MegaPhone17.db". Launch the Insert Scripts using
"for %s in (ins*.sql) do start "%~ns" dbisql -q -c dba,sql read %s"
(replace dbisql by dbisqlc if preferred).
Some performance metrics are collected in the shared global temporary table megaphone.gtPerfLog. "select * from megaphone.gtPerfLog order by 1" and restrict to the "interesting" part. GenInsert.SQL was used to generate the the INSERT scripts. You shouldn't need it unless you want a different transaction layout. PerfLog1??.csv are collected metrics from my attempts; PerfLogs.txt is the summary of their content. PerfLogs.xlsx is some enhancement to evaluate the collected data.
16a - v.16 dbisql -q 16b - v.16 dbisqlc -q 17a - v.17 (older build) dbisql -q 17b - v.17 (older build) dbisqlc -q 17c - Insert scripts executed from a different computer via network client 17d - same after firewall adjustments 17e - same with dbisqlc -q 17f - build 4103 with dbisql -q 17g - build 4103 with dbisqlc -q

[former_SQLA_member1694885]

I forgot to mention one more difference that I think shouldn't be relevant. My v.16 installation is a Workgroup Edition (prod license, 5 seats). The v.17 is a Developer Edition.
sa_eng_properties indicates that 2 physical and 8 logical views are utilized on either server.

[VolkerBarth]

Can't comment on those differences, in my tests (with older build numbers) both versions behave rather similar. However, I always use the same machine with parallel installations of the versions I want to compare on the same drive to exclude other possible influences, and I usually run them on bare metal...

[former_SQLA_member1694885]

Hi Volker, it is bare metal. Windows Ultimate VHD boot is not a virtual machine, it's the real physical machine booting from a virtual hard drive.
Anyway, I'm just about to set up a new, kind of clean, boot VHD where I can install both versions together and have them run in the same environment.
Tnx for your comment so far.

[former_SQLA_member1694868]

I can't comment specifically, but we have seen poorer performance at some of our clients who utilize virtual environments. It seems to relate to their specific configuration as we noticed it with versions 12 and 16 as well. I don't have any specifics as to what ends up fixing the issue, so I am interested to see an answer here.