Question
· 16 hr ago

Poor database write performance

Hello,

I have created this script that does lot of writes to a single global. DB write performance is much slower than expected (compared to another similar systems).

set rec = "..." //fill it with something
set time = $piece($horolog,",",2)
while(($piece($horolog,",",2)-time) < 30) //30 seconds
    set ^A($System.Util.CreateGUID()) = rec
}

I have notified the following : 

  • CPU usage does not reach 100% on a single core (eg: 25% of total CPU usage should be seen on a 4 cores system). Instead, much lower CPU usage is shown (with some drops to 0% from time to time). It looks like process has to wait for I/O completion before proceeding. Removing the set statement in the while loop above (only keeping CreateGUID) allow to reach 100% single core usage.
  • In Process Monitor, it writes to the database using mostly 8KB blocks. Even if database is defined to use 8KB blocks, IRIS is usually able to batch multiple writes at same time (giving a better performance). The write to WIJ is done using 256KB blocks (as expected). 

I have another IRIS server with exact same specs, and it behave as expected : 

  • CPU reach 100% usage on one core (as expected).
  • It performs writes using blocks bigger than 8KB, followed by a lot of writes using 512KB blocks at the end (before another journal / database cycle occurs). The slow server does not have such 512KB writes.

What I have tried :

  • restarting the IRIS instance
  • using another database (like TEMP). One theory is that "slow" database would be heavily fragmented but it's not the case.
  • killing the ^A global before or writing to another global

Additional info : 

  • databases are the same : encrypted, 8KB block size. Both have similar size (around 1.5TB) and free space left (a few percent). It should be plenty of space to store ^A nodes (not expansion needed).
  • journaling is enabled on both systems.
  • global buffer cache : both are set to 4095MB
  • both systems are using Windows Server 2012 and Hyper-V. CPU frequency is similar.
  • both systems are using Sophos. Maybe there is an exclusion rule for D:\ drive made for one of them. But AFAIK that should not explain the "8KB only" writes.
Product version: IRIS 2023.1
$ZV: IRIS for Windows (x86-64) 2023.1.3 (Build 517) Wed Jan 10 2024 13:51:33 EST
Discussion (7)3
Log in or sign up to continue

Out of interest:

  • Are both instances running on the same version of Windows including architecture?
  • Are both instances using the same license type for IRIS?
    • I'm not sure if one using a community/temp license vs a "full" license could have this effect, but thought it's worth asking.
  • If you call $System.Util.CreateGUID() without writing it to the database, does the CPU still hit 100% on the single core (just thinking this could point to the CreateGUID call being the bottleneck instead of the DB write)
  • Both systems are using Windows Server 2012 R2 Standard and Hyper-V (with same very similar CPU). 
  • Both systems are using a core license.
  • CreateGUID is not the bottleneck for sure. This is something I have checked very early. Removing the write to the global (keeping CreateGUID) will allow CPU to reach 100%. The effect of using a GUID (versus a incremental ID) is to spread out the global node writes, which might affect performance. But that not the explanation, because then both systems should be affected.

I have edited OP to reflect those details.
I have tested this on 4 systems (all very similar), and only one behave like that (slow DB writes).

Hi,
Couple of things to check.

Is there any difference in Server design? .e.g. number of disks, scsi controllers, volume/storage distribution etc
Is the VM definition the same? e.g. storage driver versions (generic scsi controller vs hyperV SCSI controller)
Is the OS on the host and in HyperV the same? 
Is the storage provider design the same? 
Is the IRIS config the same (i.e. cpf file), especially are below settings present?

[config]
wduseasyncio=1
asyncwij=8

I guess both IRIS versions are the exactly the same build although i never heard that to affect disk performance.