Performance Tuning

[MPeccorini]

Hi fellows,

I posted some of this questions in a previous thread but the discusion didn't reach these issues. Can you guys help me with this?

First, it is recommended to have communications threads set to 32 if you have 32 clients (for example), if you have more clients you should analyze if it is reasonable to increase the number of threads. Can somebody explain me the situation given the following numbers:
CURRENT PEAK MAXIMUM
CLIENTS 35 48 10000
LICENSES 11 11 50
COMMS THREADS 0 5 16
REMOTE CONNS 20 39 100

I don´t know if I should follow the previous recommendation given that my COMMUNICATIONS THREADS PEAK is just 5.

Finally, is there a way to determine if my CACHE ALLOCATION SIZE is too low. I have found some ways to calculate the recommended but it is based on the size of the databases used. I think that aproach is not the best since not all files from a database are used at the same time. Isn´t there a way to calculate the CACHE HITS/CACHE MISSES ratio for example? or to determine a TOO MUCH I/O RATIO (I don´t know how)?

Thanks a lot for your help and best regards.

Mauricio Peccorini

BTW.: I'm using PSQL 2000i Server and PSQL 2000i WGE as client. The specific problem I'm trying to solve is that even very easy queries (like SELECT some_desc FROM some_catalog WHERE item_id = some_value) have a very high response time (1 or 2 seconds).

 

[Smcmanus2001]

hi there MPeccorini

Here is one suggestion as to what it could be. Someone could have changed the default cache on the engine, I would also check to see if Pervasive utilizes the system Cache, as the usage limit of the Pervasive Cache 2GB, (correct me someone if I am wrong). more if you are on NTFS and ustilize the System cache. But you have to balance it, If you have 2GB of Ram I find the Database slows down if you use more than 60%(1.2GB). There is a Formula which can be dug up on the pervasive site for Cache Calculation.

Hopes this helps.

 

[MPeccorini]

When you say "Someone could have changed the default cache on the engine" you mean REDUCED the default cache or is there an intrinsec problem in CHANGING it?

When you say "check to see if Pervasive utilizes the system Cache" do you recommend using it or not using it?

My server has just 1Gb RAM, it is a database dedicated server and so far I changed the Cache Allocation to 400Mb. It was 200Mb before. However I'm experiencing a different problem today. Before the change, the whole 200Mb where allocated (according to the operating system). Now the ammount allocated is dynamically growing and shrinking (once again according to the O/S). This is making the system run veeeeeeeeeeeery slow.

Just for the record, I increased the Communication Threads to 32 and the Number of Input/Output Threads to 32.

Best regards and thanks for any help you can give me.

Mauricio Peccorini

P.S.: I guess I don't have to tell you how many internal phone calls I'm receiving per minute because of this problem. Not to mention the ammount of emails.

 

[Smcmanus2001]

OK, Some of the details are coming back to me.
On 2000i the combined size of the Level 1 and the Level 2 Cache cannot be bigger then 2GB or it causes problems, seeing as you have only 1GB of ram thats unlikely. System Cache is one Level an Pervasive cache is the other. Go the the Task Manager/Processes menu View and add Virtual Memory Size this will give you a idea if this is a problem, look at NTDBSMGR.exe and check the memory usage.
If not that, then check the Pervasive tools Monitor and communications, this will tell you threads in use, Resource Usage will look at other things that could be hitting a wall.
One other thing, I've found invaluable is the windows performance monitor. Add the Physical Hard Disk Reads and Writes, (I use Current kb/s), and at least CPU %age, and whatever you may find useful. If you see a lot of Hard Disk Reads, it isn't loading from cache. Index Creation looks different, etc.

Hopefully one of these will bear fruit.