We have a big partitioned database in Production (around 7 Gb) on a Windows 2000 SP4 Server with 4 x 2,8 GHz processors and 2,5 Gb of physical memory.
The current cache setting in SQL.INI is 80000 (nothing for sortcache yet) but we have a lot of latencies and many physical reads and writes with 25 users maximum!
The server can start successfully with cache=800000 but I would like to be sure of the SQLBase consistency and stability with this high value.
- What is the maximum supported values of cache and sortcache in SQLBase 8.5.0? What values would you suggest on our server?
- Is it so risky allocating so much cache? Could the cache management overhead be worse than the performance improvement in our case?
- Knowing that SQLBase 8.5 is not multi-threading, what could be the best other settings with our configuration?
Thank you very much.
OK, I'll try to answer your questions. But I'm sure that I'll have a few questions for you along the way. The first one that I will ask is if you really need to have a partitioned database? Starting with SB7.5, the restrictions of 2GB for a single DBS file were lifted and with 8.5 the only file that can't be > 2GB is a backup file (this has been removed entirely with the 11.0.2 release). I would *really* recommend you doing an unload of the partitioned database and then doing a load into a single file. This should speed things up for you just by doing that.
Now, on to the question of memory usage and the cache. There are two settings in the SQLBase server sql.ini file that govern how memory is used. These are the cache= and sortcache= statements. The cache= setting is a permanent memory allocation and is used to hold extra rows from the database whenever requested data is read. Its effectiveness and its setting is highly dependent upon the application mix that is running on the server. If most applications on the server do serial reads, a higher cache setting is desirable. If most do keyed reads you probably won't get a high cache hit
regardless of what you set the cache= to. If this setting is too low there will be more 'real' reads from the disk. If this setting is too high the system will spend more time managing this cache than actually doing processing.
The sortcache= setting is a temporary memory grab of the server memory. It is used when creating indexes and when any sorting of the retrieved data is done, i.e. when doing 'select' statements using 'order by', 'group by' or 'distinct' clauses. It is only used when it's needed and then the memory is released.
Beginning with 8.5, the limitation of 32767 for the cache statement has been removed. The new limit is now 1,000,000, but just because you CAN have a huge cache doesn't mean that you should. I have seen some very strange things that can occur with an overly large cache, including the execution plan changing and indexes no longer being used. Another thing that I have seen is the server spending more time in managing the huge cache and not enough time actually returning data.
Prior to 8.5, the sortcache statement applied only to the Postman's Sort method and was not used when Sort/Merge sorting was done. With 8.5 when using Sort/Merge, the sortcache= statement will be used *for each sort process*. The default if this option is not specified is 2048 and this will automatically expand up to 10,000.
My recommendation for cache would be to start at 50,000 and increase this value up to 150,000 or until performance degrades. Remove the sortcache= statement to use the default and remove any sortlevel statement that may be in the same section.
If you are having performance problems, use SQLConsole to see where your bottlenecks are. I also have a 'SQLBase Performance Toolkit' that I have put together over the years (papers, tips, tricks, utilities, etc.) that I would be willing to send to you privately. Just ask.
Hope this helps!
First of all, thank you very much for your quick and complete reply.
I have to beg your pardon because, yes indeed, the database is not partitioned, only the backups! (What a new bee mistake!!!... )
Unfortunately, our applications are executing heterogeneous OLTP and OLAP queries against the database, and we can see a lot of physical reads and writes with the Windows Task Manager: 70 Gb of I/O read and 7 Gb of I/O write a day!
So the best compromise could be to have a relatively high cache hit to avoid too much physical I/Os, right?
Therefore, I am very surprised reading the following:
"I have seen some very strange things that can occur with an overly large cache, including the execution plan changing and indexes no longer being used."
What could explain these very strange behaviors?
This is a real paradox that the SQLBase optimizer could decide to do so much physical I/Os than logical ones (doing Table Scan or reading from the disk instead of the memory cache) because physical I/Os are around 5 times slower than logical ones!
To avoid this and enforce the optimizer using indexes or specific plans, could we use "Query Hints"?
Else, do you have some documents about the SQLBase internals and core architecture (buffer pools and optimizer management...)?
"Another thing that I have seen is the server spending more time in managing the huge cache and not enough time actually returning data."
Yes, I can "relatively" understand that... But how could we evaluate the limit between both?
Ok. I will try your cache settings. Why "only" up to 150,000 pages (it is far away from the 1,000,000 maximum value) ? Is this an experimented limit with huge databases?
Of course, I am extremely interested in your 'SQLBase Performance Toolkit'. Could you extract my email address from this forum to send it?
Another thing, how could we free up the CPU usage of SQLBase, because it uses only 25% of CPU with our 4 processors?
Thank’s a lot for everything.
>>What could explain these very strange behaviors?<<
An 'overly ambitious' optimizer. 8.5 was the first version that allowed such a large cache and there were some teething problems. Our later releases handle the cache much better.
Sorry, but query hints are not implemented yet. That is something in discussion at the moment. You can, however, see what execution plan has been chosen for a particular query and either modify the query slightly or create an index that will 'help' the optimizer to choose a correct plan. Have a look at the 'Advance Topics Guide' for a more in-depth discussion about working with the optimizer.
>>how could we evaluate the limit between both<<
That's kind of difficult to do. You really need to try different levels and wait to hear the customers give you heaps of praise or complain bitterly about the performance. SQLConsole will give you statistics about the I/Os and cache hits.
>>Is this an experimented limit with huge databases?<<
I work in Tech Support and my speciality is SQLBase. I am speaking from my experience in helping other customers with their performance problems; what works and what doesn't.
>>Could you extract my email address from this forum to send it?<<
Yes, I will do so.
>>how could we free up the CPU usage of SQLBase, because it uses only 25% of CPU with our 4 processors?<<
With the 8.5 release, the SQLBase engine will only use one of the processors. If you are seeing 25% usage, SQLBase is maxed out. All releases are the same until the very next release (11.5 - due out later this year), which will take advantage of multi-processors. This will give you a *HUGE* increase in performance.
Hope this helps!
A little correction: our SQLBase uses up to 25% of CPU, not always 25%
More, the server OS is already set to "Adjust for Background Services".
Anyway, using 1 processor on 4 seems to be a waste of ressources for a dedicated server...
Who is online
Users browsing this forum: [Ccbot] and 0 guests