Recommended Cache Size for SQLBase 9.0.1?

[RainerE]

Hi all,

does anyone has experiences with different cache sizes of SQLBase 9.0.1 latest PTF?

We have a 40GB database, multiple users, a service application doing many processings 7 days a week 24 hours a day (including dabase backup and maintenance).

The performance is becoming slower and slower and I'm thinking about tuning SQLBase by increasing the cache size (currently we have set it to 64MB).

In the past I tried to increase the cache size but noticed that this slowed down dabase maintenance (check database, update statistics on database).

Currently I cannot change the application and it's not an option to add indexes (in the early next year, I will do both).

Has anyone experiences with different cache sizes (which?) and SQLBase 9.0.1 (running on a multiprozessor server with windows 2003 and 4GB of RAM) and e.g. those things:
- increase/decrease of performance of selects
- increase/decrease of performance of insert/updates/deletes
- increase/decrease of performance of database backup/check database/update statistics
- increase/decrease of the number of deadlocks (currently we have 2 - 5 the day)
- increase/decrease of the number of timeouts (currently we have 0 the day)
- stability (SQLBase is running 24 hours a day and 7 days a week without restart)

Regars,
Rainer

Here's the server's sql.ini for information:

Code: Select all

[dbntsrv]
dbdir=e:\data\dbs\database
logdir=e:\data\dbs\database
tempdir=c:\temp
servername=PRC2SBL
password=******
cache=64000
centurydefaultmode=1
ANSIJoinSyntax=1
secureapi=1
dbname=Island
DBNAME=ARCPRC2SBL,SQLWS32
DBNAME=OFCPRC2SBL,SQLWS32

[dbntsrv.gui]
MAINWIN=NORM,234,96,930,661 
STATWIN=NORM,0,0,507,249 
DBWIN=NORM,506,1,686,248 
PROCWIN=NORM,-1,249,687,517 
SYSWIN=CLOSED,42,96,462,288 
DISPLEVEL=0 
TIMESTAMPS=1

[dbntsrv.dll]
comdll=sqlws32

[dbntsrv.ws32]
listenport=2155

[win32client]
clientname=PRC2
;clientruntimedir="e:\apps\Gupta\SQLBase901"
secureapi=1

[win32client.dll]
comdll=sqlws32

[win32client.ws32]
serverpath=PRC2SBL,localhost,2155/*
serverpath=PRC3CON,SVxyz,2155/OFCPRC3CONTRA

[Mike Vandine]

Hi Rainer,

As you know, the cache size to set is highly dependent on the application and how it uses the database. Mostly serial reads will benefit from a large cache setting since more rows from the database will already be in the cache for the next read. If you do a lot of directed reads by key, the odds on the row being in the cache is lower so a large cache would be wasted for this type of access; the server would spend more time managing the cache than actually returning data.

So, it's a trial and error. For 9.0.1, a 64k cache size is a good start. Increase to 128k and see what difference that makes. This users will either applaud or scream. If they applaud, try 256k and see that reaction. If they scream, try 98k.

I will let others comment on their experiences.

Best regards,

Mike

[RainerE]

For all who are interested:

Changing the cache from 64MB to 128MB speeded up 1 special export ,which was running more than 2 hours, by 20%.
Changing the cache to 256MB speeded up this export additional 30%.

All other exports/imports/reports/dialogs/... were not speeded up by changing the cache.
Database backup, update statistics, unload database and check database were not slowed down.

The special export has this poor performance because of a datamodel which is not designed for this export.
To select only the records needed, I had to implement a join between two large tables and the index usage is not good.
So the export has to read many records of table A with a select condition on a date and only keep those records, for which in table B field X has a special value.
Indexes used: On table A a date index which does not work properly for backdated dates. On table B the PK index due to the join.

Next year I will change the data model and adapt the special export. In tests, this speeded up the export very much (< 5 minutes instead of > 2 hours). After this, I will decrease the database cache back to 64MB.

The change will be to redundantly add field X to table A and add an index over X and the date field.
As benefit no join is needed any more and the new index is good enough to select only the records needed.

I think, the larger cache helps in this case, because the records of table A have to be read multiple times.

Regards,
Rainer

[Matterofacts]

Is this thread still open?

We are using Gupta 9 that comes with CamCommerce Retail Office Star app. We had this running on a Windows Server 2008 R2, 1 SATA hard drive, 8GB RAM, Quad Core 2.4GHz non-xeon processor.

We moved it to a VM running in a hyper-v host that has
OS as mirrored 10K rpm SAS drives and RAID 5 set for the VM's (virtual machines) as 8 SATA 7200 rpm drives; 32GB RAM, 2 Xeon processor chips each having six cores at 2.4GHz each totaling 12 cores.

Created a VM with Windows Server 2012 with 16GB and 6 virtual processors. Our reports run slower now then prior on the old server. In talking to the CamCommerce product manager today, he mentioned the cache in the SQL.ini which comes un-configured by default. I am asking for recommendations to increase performance on the new server with Gupta SQLBase v9 running in our VM on the new server.

[Mike Vandine]

Can you please post the sql.ini from your server?

As I said earlier in the post to Rainer:

"As you know, the cache size to set is highly dependent on the application and how it uses the database. Mostly serial reads will benefit from a large cache setting since more rows from the database will already be in the cache for the next read. If you do a lot of directed reads by key, the odds on the row being in the cache is lower so a large cache would be wasted for this type of access; the server would spend more time managing the cache than actually returning data.

So, it's a trial and error. For 9.0.1, a 64k cache size is a good start. Increase to 128k and see what difference that makes. This users will either applaud or scream. If they applaud, try 256k and see that reaction. If they scream, try 98k."

Hope this helps!

[Matterofacts]

Our database size is 8GB. Below is the sql.ini., thanks.

;Network Server SQL.INI
;Star SQL.INI file 4.5
;
[DBNTSRV]
dbname=POS,sqlws32
dbname=SRD,sqlws32
servername=server1,sqlws32
cache=32767
;sortcache=65535
;sortlevel=0
readonly=0
oracleouterjoin=0
logfileprealloc=1
partitions=0
optimizerlevel=2
locktimeout=60
clientcheck=1
ANSIJoinSyntax=1
dbdir=C:\STARData\POS
TEMPDIR=C:\STARData\TEMP
sortcache=65535
sortlevel=0
timezone=-8

[dbntsrv.dll]
comdll=sqlws32

[win32client]
clientname=Server

[win32client.dll]
comdll=sqlws32

[win32client.ws32]
serverpath=SERVER1,127.0.0.1,2155/*
autostartserverpath="C:\Retail STAR\dbntsrv.exe"

[Mike Vandine]

Change cache= to 64000
Remove sortcache=65535 <-- Note that this is there twice, one commented out and one not
Remove sortlevel=0 <-- Note that this is there twice, one commented out and one not

Cheers!

[Matterofacts]

1. Turning those two off and setting the cache to 64000 didn't improve anything noticeable. I suppose the next step would be to increase the cache setting further?

2. And secondly, what about upgrading to SQLBase 11.7 - if I don't have any control over the front end app itself and just the database and the data and I perform an upgrade on the database, will the app that worked with SQLBase v9 32bit work with SQLBase 11.7 32bit or 64bit without modification to the front end app.?

[Mike Vandine]

Yup. Pop the cache= up to 128000.

>>will the app that worked with SQLBase v9 32bit work with SQLBase 11.7 32bit or 64bit without modification to the front end app.?<<

I'm not sure what version the Star application is currently certified for. I don't think they've certified it for 11.7 yet. You definitely need to talk to Cam Commerce.

[Matterofacts]

We'll, Cam will not discuss the question of if it will work in 11.7. They have no intentions of upgrading to it currently and they don't support it. We may however go out on our own and implement it if there is not any modification needed on the front end app. Taking your best guess stab at it, are the procedural calls, queries, etc. to the db from the front end app that worked in v9 SQLBase possibly still going to work in v11.7? And if so, will it have to stay in 32bit or can it be upgraded to SQLBase 64bit?

[Mike Vandine]

Well, I'm not sure about if Cam has a specific SYSADM password (almost certainly) or if they would give it to you to do a conversion, but the database structure had a major change with 11.6 and you have to unload/load the database from older versions to upgrade to 11.6 and above. To unload a database, you need to use the user SYSADM, so you might be stuck...

If you do have the SYSADM password, it wouldn't make any difference whether you use the 32-bit or 64-bit engine. There hasn't been changes to the procedures, etc. so in theory it should all work.

[Matterofacts]

cache, sortcache, sortlevel, oracleouterjoin, optimizerlevel

Where is the documentation that specifies what each of these do?

[Mike Vandine]

All of these settings are documented in the DBA Guide, Chapter 4, with the exception of sortlevel, which was a 'special' setting.

The cache and sortcache, I am attaching a write-up on these settings that gives a bit of history about the changes in meaning that has occurred with the different versions of SQLBase. In essence, you should NOT be using sortcache or sortlevel.

Hope this helps.

[d.richter]

Hello, we have Gupta 9.0.1 without PTF on Windows 2008 R2 in a Hyper-V 2012 Enviroment.
We have performance problems, i can give a lot of memory or cpu, but its the same.

1. What is the optimal CPU and RAM on Hyper-V with 30 Clients, they connect with a erp-software that connect to gupta?
2. Here is the sql.ini, Optimization possible?

; Win32
;--------------------------
; Use this section to specify Win32 client workstation parameters that
; are independent of the communications protocol(s) being used.
;
; This section is for the Win32 client workstations. Here you will specify
; the COMmunication DLL(s) that the Win32 application(s) intend to use.
; Use the following protocol(s) to connect to the specified SQLBase product:
;
; comdll SQLBase target
; ------ --------------
; sqlapipe LOCAL database using Anonymous Pipes
; sqlws32 REMOTE database using TCP/IP (WinSock)
;
[win32client]
clientname=wastel
clientruntimedir="D:\Gupta"

[win32client.dll]
comdll=sqlws32
;comdll=sqlapipe

[win32client.apipe]
autostartserverpath="D:\Gupta\dbntsrv.exe"
serverpath=Server1

[win32client.ws32]
serverpath=Server1,localhost,2155/*

[dbntsrv]
ANSIJoinSyntax=0
AsyncIO=0
dbdir=D:\Gupta
dbname=Island
servername=Server1
centurydefaultmode=1
cache=548462

DBNAME=FIRMA32,SQLWS32
DBNAME=TEST32,SQLWS32
[dbntsrv.gui]

[dbntsrv.dll]
comdll=sqlws32

[dbntsrv.ws32]
listenport=2155

[Mike Vandine]

I would modify the cache= to be cache=128000. You could also try 256000, but with the size you've got now the server will probably be spending more time managing the cache then returning data.

Another thing I would add would be:

[dbntsrv]
readonly=0

This will turn off the global readonly cache and could speed things up.

Best regards,