Slow database

[Bertrand]

I have a similar problem. VMware 5.1, a application using a Progress DB. The first notice was about backup time:
-> backuping 35 Go took 2h30 when the fileserver (VM under VMware + NetApp Filers FAS2020) was using NFS to access the FAS disk
-> backuping 35 Go took 52/55 minutes when the file server is using iSCSI (NOTE: delay is 15 ms with iSCSI, more than 50 ms with NFS)
-> same backup on a physical disk or with just a copy of the Database and the backup utility took 39 minutes !
There are still slowdown on queries and the backup can last longer.
Bascically, for me (based on my old DB history), a RAID 5 should be faster (mutiple simultaneous access).
Can you explain why RAID-DP is not good for DB access ?
Could you give me some clues about it ?

 

[Rob Fitzpatrick]

Hi Bertrand, welcome to ProgressTalk. In general it is good practice to post a new issue to a separate thread. Also, it is essential to provide detailed information about your environment. The more you tell people here about your situation, the better they can provide you with relevant opinions and help. For example we don't know your operating system or your Progress version.

Many Progress database people, here and elsewhere, will tell you that a NetApp filer is not appropriate for database storage. As the name suggests it is intended for use as a file server and in that capacity it should be fine. From my personal experience with customer systems I relate NetApp storage to substandard database performance. And that doesn't mean it's cheap. I've seen a storage system with 6-figure price tag that was hugely outperformed by an ancient, mid-range rack-mount server with local storage.

I think all of the backup times you quoted are slow. I have a customer with a three-year-old server whose largest DB is about 33 GB. The storage is local to the server; I believe it is RAID 10, 10K or 15K SAS disks. An online backup, to the same volume as the DB, takes less than 9 minutes. And their application is active and doing database I/O 24/7 so the backup isn't even getting the full I/O bandwidth that should be available. The take-away here is that this system is far from optimal and it far outperforms even your best time.

I suggest you read the Progress Database Essentials and Database Administration guides. There are sections on administrative setup, including RAID levels. There is similar information in the Progress knowledge base. Any parity-based RAID level, e.g. 3, 5, 6, 50, 60, DP (this is not an exhaustive list), is sub-optimal for database storage due to the extra expense of writes. It gets much much worse when (not if) a disk fails and the array has to be rebuilt from parity data. Storage vendors try to mask this penalty by selling you expensive cache but that doesn't solve the problem. If your sustained rate of commits exceeds the rate at which the underlying disks can write the data then more and more of the cache will be consumed. When there is no cache available then you are essentially writing straight through to the disks and you experience the true performance level of RAID 5. The client I spoke of previously had their databases on a RAID 50 array and I can tell you performance was awful. When you have RAID 5 on network storage that is potentially accessed by applications on multiple servers, it gets that much worse.

RAID 5 is not optimized for performance. It is optimized for cost, i.e. gigabytes per dollar. In decades past when storage was very expensive, the economics were different. Nowadays storage is cheap and SSDs are gaining a lot of traction in the enterprise. Progress recommends RAID 10 for database storage. If you can't manage that then RAID 1 would be a valid second choice.

You should seriously re-examine your objectives, priorities, and budget. I understand the appeal of virtualization and it can be made to perform well given the correct configuration and hardware. But you need someone who understands virtualization and you need a good storage subsystem. I don't know if you have the former but you definitely lack the latter.

 

[Cringer]

Thanks Rob - I've moved the posts out now into a new thread in the DB Admin section as you can see.

 

[Bertrand]

Hi Bertrand, welcome to ProgressTalk. In general it is good practice to post a new issue to a separate thread. Also, it is essential to provide detailed information about your environment. The more you tell people here about your situation, the better they can provide you with relevant opinions and help. For example we don't know your operating system or your Progress version.

Many Progress database people, here and elsewhere, will tell you that a NetApp filer is not appropriate for database storage. As the name suggests it is intended for use as a file server and in that capacity it should be fine. From my personal experience with customer systems I relate NetApp storage to substandard database performance. And that doesn't mean it's cheap. I've seen a storage system with 6-figure price tag that was hugely outperformed by an ancient, mid-range rack-mount server with local storage.

I think all of the backup times you quoted are slow. I have a customer with a three-year-old server whose largest DB is about 33 GB. The storage is local to the server; I believe it is RAID 10, 10K or 15K SAS disks. An online backup, to the same volume as the DB, takes less than 9 minutes. And their application is active and doing database I/O 24/7 so the backup isn't even getting the full I/O bandwidth that should be available. The take-away here is that this system is far from optimal and it far outperforms even your best time.

I suggest you read the Progress Database Essentials and Database Administration guides. There are sections on administrative setup, including RAID levels. There is similar information in the Progress knowledge base. Any parity-based RAID level, e.g. 3, 5, 6, 50, 60, DP (this is not an exhaustive list), is sub-optimal for database storage due to the extra expense of writes. It gets much much worse when (not if) a disk fails and the array has to be rebuilt from parity data. Storage vendors try to mask this penalty by selling you expensive cache but that doesn't solve the problem. If your sustained rate of commits exceeds the rate at which the underlying disks can write the data then more and more of the cache will be consumed. When there is no cache available then you are essentially writing straight through to the disks and you experience the true performance level of RAID 5. The client I spoke of previously had their databases on a RAID 50 array and I can tell you performance was awful. When you have RAID 5 on network storage that is potentially accessed by applications on multiple servers, it gets that much worse.

RAID 5 is not optimized for performance. It is optimized for cost, i.e. gigabytes per dollar. In decades past when storage was very expensive, the economics were different. Nowadays storage is cheap and SSDs are gaining a lot of traction in the enterprise. Progress recommends RAID 10 for database storage. If you can't manage that then RAID 1 would be a valid second choice.

You should seriously re-examine your objectives, priorities, and budget. I understand the appeal of virtualization and it can be made to perform well given the correct configuration and hardware. But you need someone who understands virtualization and you need a good storage subsystem. I don't know if you have the former but you definitely lack the latter.

 

[Bertrand]

Thanks Rob...
I did the infrastructure installation and setup and another company (software development) did the installation/configuration of OpenEdge + the installation of the software.
Basically, today, the customers are noticing slow accesses, not on a regular basis, and the other criteria is the backup time.
We don't have other criteria.
So I assumed that they are not using Promon or other tools, to make a more accurate diagnosis.

To answer to your questions:

• The platform is ESX VM 5.1
• The OS is Win2008 R2 64 bits
• The DB is OpenEdge 10.2B
• The VM is 8 vProc / 20 Go RAM

We do install a VM infrastructure because the initial request was a reliable and non-interruptible solution for the main application (based on Progress OpenEdge), the file server, the mail server. Nothing more precise.

We did request to the software company a description of the infrastructure and the answer was a description of a physical machine we do translate in our VM envt.

I did notice the point about RAID 5 or RAID DP. We will try to build another aggregate with 2 disks connected using RAID1, dedicated to the DB.

But what drove me nuts was those facts:

• copy of the DB + DB backup utility on a single "new" machine (same OS): 14 minutes backup (correct based on your comment in the previous Post)
• copy of the machine itself (clone): same value
• re-installation of a brand new machine + DB + software application used by the customer + copy of the living DB: .....32 minutes
• same machine but used in production: 50/60 minutes
• Average disk delay used today: 14,9 ms
• Average disk delay used in the first installation: 30/45 ms
• but...same feeling from the customer (no metrics provided by the software company).

Where the differences ? Explaining such differences of backup time ? The lack of performance increases with a delay divided by two or more.

 

[Rob Fitzpatrick]

Thanks Rob...
I did the infrastructure installation and setup and another company (software development) did the installation/configuration of OpenEdge + the installation of the software.
Basically, today, the customers are noticing slow accesses, not on a regular basis, and the other criteria is the backup time.
We don't have other criteria.
So I assumed that they are not using Promon or other tools, to make a more accurate diagnosis.

When you say "they are not using Promon" do you mean the customer or the vendor?

To answer to your questions:

• The platform is ESX VM 5.1
• The OS is Win2008 R2 64 bits
• The DB is OpenEdge 10.2B
• The VM is 8 vProc / 20 Go RAM

• Is it 32-bit or 64-bit OpenEdge ?
• Which service pack of 10.2B?
• Are the vCPUs or RAM "overprovisioned" or dedicated to this VM?

I did notice the point about RAID 5 or RAID DP. We will try to build another aggregate with 2 disks connected using RAID1, dedicated to the DB.

You should make this a priority. I think trying to tune or optimize an application on slow storage is a waste of effort.
Can you not afford the storage to create a 4-disk RAID 10 array?

copy of the DB + DB backup utility on a single "new" machine (same OS): 14 minutes backup (correct based on your comment in the previous Post)

I wouldn't say a 14-minute backup of a 35 GB DB is "correct". I would say it is "less horrible" than the numbers posted previously. It is still slow in my opinion.

re-installation of a brand new machine + DB + software application used by the customer + copy of the living DB: .....32 minutes

I don't know how long installing the machine and software application should take so this isn't a meaningful metric for me.

same machine but used in production: 50/60 minutes

I don't know what you mean. What activity took 50 to 60 minutes? Was it 50 or 60? That's a significant difference.

Average disk delay used today: 14,9 ms
Average disk delay used in the first installation: 30/45 ms

How are you measuring "disk delay"?

but...same feeling from the customer (no metrics provided by the software company).

Do you mean the customer still complains of slow application performance? Investigation of that problem involves a lot more than just looking at the storage and database. However just as you need a strong foundation to build a house, you need to get the storage and database configured well and performing well even before you run the application and measure its performance.

Run this simple batch file in a proenv session:

@echo off
rem simple BI grow test
echo.
echo %time%: Creating a sports database...
prodb sports sports
echo.
echo %time%: Setting the BI cluster size to 32 MB...
call proutil sports -C truncate bi -bi 32768
echo.
echo %time%: Growing the BI by 4 additional clusters (256 MB total)...
call proutil sports -C bigrow 4
echo.
echo %time%: Removing the database...
echo y | prodel sports
del sports.st
echo.
echo %time%: Done.

Your elapsed time in seconds should be a small single-digit number. This is by no means a comprehensive test and is not a substitute for proper benchmarking but it will give you some idea of what your disks can do in terms of writes. However most applications are read-heavy so you have to test that as well, e.g. how long it takes to run probkup or dbanalys. For comparison, on the client system I mentioned earlier (33 GB DB) a dbanalys runs in a little under two and a half minutes.

When you get into the question of whether the database is well-configured there are more things to consider. For starters you have to look at the database structure, the startup parameters for the database broker(s), and the BI and AI configuration.

• What is the database block size?
  
• What is the BI block size and cluster size and AI block size?
• Is after imaging enabled?
  
• Are there any objects in the schema area?
• Are all tables and indexes in Type II storage?
• Are records per block and blocks per cluster configured appropriately for the storage areas?
• When was the last database dump and load?
• What is the percentage utilization of the most frequently-used indexes?
  
• How do the clients connect to the database -- WAN connection? LAN connection? Self-service? A mix of these?

There are many more questions that can be asked about database configuration but this is a start.

You mentioned application performance. There are many more factors that come into play here and which may extend beyond the database server. These things include client startup parameters, the location of the code and the means by which it is accessed, the length and content of the propath, the suitability of the indexes for the queries being performed, and the general efficiency of the code.

However it sounds like these last items lie outside of your area of responsibility. Where is the software vendor in all of this? Do they not have an obligation to investigate their client's complaints of poor application performance?

 

[Bertrand]

When you say "they are not using Promon" do you mean the customer or the vendor?

• Is it 32-bit or 64-bit OpenEdge ?
• Which service pack of 10.2B?
• Are the vCPUs or RAM "overprovisioned" or dedicated to this VM?

You should make this a priority. I think trying to tune or optimize an application on slow storage is a waste of effort.
Can you not afford the storage to create a 4-disk RAID 10 array?
Pretty hard. I know it is more efficient. But we were asked to do the best ratio price/performance/volume. That's why we did use RAID DP. I'll try to figure out how we might rebuild the array without losing data of course and...without stopping production. W have very narrow windows for tests and reconfiguration.

I wouldn't say a 14-minute backup of a 35 GB DB is "correct". I would say it is "less horrible" than the numbers posted previously. It is still slow in my opinion.

I don't know how long installing the machine and software application should take so this isn't a meaningful metric for me.
Sorry for that !! I meant DB backup time...

I don't know what you mean. What activity took 50 to 60 minutes? Was it 50 or 60? That's a significant difference.
Sorry again, it was still DB backup time.

How are you measuring "disk delay"? I'm using both NetApp and VM tools. The results are consistent using both ways. Just as an example, when using NFS, the max delay was (I know it was awful) 150 ms, the average 60 ms. When using iSCSI, the max delay is 40 ms and the average is 14 ms. That's why, even though I'm not a DB expert, I'm very confused: the user should experience better response time, it the slowliness was strictly connected to the infrastructure.

Do you mean the customer still complains of slow application performance? Investigation of that problem involves a lot more than just looking at the storage and database. However just as you need a strong foundation to build a house, you need to get the storage and database configured well and performing well even before you run the application and measure its performance.

Run this simple batch file in a proenv session:

@echo off
rem simple BI grow test
echo.
echo %time%: Creating a sports database...
prodb sports sports
echo.
echo %time%: Setting the BI cluster size to 32 MB...
call proutil sports -C truncate bi -bi 32768
echo.
echo %time%: Growing the BI by 4 additional clusters (256 MB total)...
call proutil sports -C bigrow 4
echo.
echo %time%: Removing the database...
echo y | prodel sports
del sports.st
echo.
echo %time%: Done.

Your elapsed time in seconds should be a small single-digit number. This is by no means a comprehensive test and is not a substitute for proper benchmarking but it will give you some idea of what your disks can do in terms of writes. However most applications are read-heavy so you have to test that as well, e.g. how long it takes to run probkup or dbanalys. For comparison, on the client system I mentioned earlier (33 GB DB) a dbanalys runs in a little under two and a half minutes.

When you get into the question of whether the database is well-configured there are more things to consider. For starters you have to look at the database structure, the startup parameters for the database broker(s), and the BI and AI configuration.

• What is the database block size? I'll try to get the info asap
  
• What is the BI block size and cluster size and AI block size? idem
• Is after imaging enabled? idem
  
• Are there any objects in the schema area? idem
• Are all tables and indexes in Type II storage? idem
• Are records per block and blocks per cluster configured appropriately for the storage areas? idem
• When was the last database dump and load? idem
• What is the percentage utilization of the most frequently-used indexes? idem
  
• How do the clients connect to the database -- WAN connection? LAN connection? Self-service? A mix of these? LAN (at least, for that question, I'm clearly in my job, so I can answer. FOr remote sites, we are using TSE sessions)

There are many more questions that can be asked about database configuration but this is a start.

You mentioned application performance. There are many more factors that come into play here and which may extend beyond the database server. These things include client startup parameters, the location of the code and the means by which it is accessed, the length and content of the propath, the suitability of the indexes for the queries being performed, and the general efficiency of the code.

However it sounds like these last items lie outside of your area of responsibility. Where is the software vendor in all of this? Do they not have an obligation to investigate their client's complaints of poor application performance?

 

[Bertrand]

When you say "they are not using Promon" do you mean the customer or the vendor?

• Is it 32-bit or 64-bit OpenEdge ?
• Which service pack of 10.2B?
• Are the vCPUs or RAM "overprovisioned" or dedicated to this VM?

You should make this a priority. I think trying to tune or optimize an application on slow storage is a waste of effort.
Can you not afford the storage to create a 4-disk RAID 10 array?

I wouldn't say a 14-minute backup of a 35 GB DB is "correct". I would say it is "less horrible" than the numbers posted previously. It is still slow in my opinion.

I don't know how long installing the machine and software application should take so this isn't a meaningful metric for me.

I don't know what you mean. What activity took 50 to 60 minutes? Was it 50 or 60? That's a significant difference.

How are you measuring "disk delay"?

Do you mean the customer still complains of slow application performance? Investigation of that problem involves a lot more than just looking at the storage and database. However just as you need a strong foundation to build a house, you need to get the storage and database configured well and performing well even before you run the application and measure its performance.

Run this simple batch file in a proenv session:

@echo off
rem simple BI grow test
echo.
echo %time%: Creating a sports database...
prodb sports sports
echo.
echo %time%: Setting the BI cluster size to 32 MB...
call proutil sports -C truncate bi -bi 32768
echo.
echo %time%: Growing the BI by 4 additional clusters (256 MB total)...
call proutil sports -C bigrow 4
echo.
echo %time%: Removing the database...
echo y | prodel sports
del sports.st
echo.
echo %time%: Done.

Your elapsed time in seconds should be a small single-digit number. This is by no means a comprehensive test and is not a substitute for proper benchmarking but it will give you some idea of what your disks can do in terms of writes. However most applications are read-heavy so you have to test that as well, e.g. how long it takes to run probkup or dbanalys. For comparison, on the client system I mentioned earlier (33 GB DB) a dbanalys runs in a little under two and a half minutes.

When you get into the question of whether the database is well-configured there are more things to consider. For starters you have to look at the database structure, the startup parameters for the database broker(s), and the BI and AI configuration.

• What is the database block size?
  
• What is the BI block size and cluster size and AI block size?
• Is after imaging enabled?
  
• Are there any objects in the schema area?
• Are all tables and indexes in Type II storage?
• Are records per block and blocks per cluster configured appropriately for the storage areas?
• When was the last database dump and load?
• What is the percentage utilization of the most frequently-used indexes?
  
• How do the clients connect to the database -- WAN connection? LAN connection? Self-service? A mix of these?

There are many more questions that can be asked about database configuration but this is a start.

You mentioned application performance. There are many more factors that come into play here and which may extend beyond the database server. These things include client startup parameters, the location of the code and the means by which it is accessed, the length and content of the propath, the suitability of the indexes for the queries being performed, and the general efficiency of the code.

However it sounds like these last items lie outside of your area of responsibility. Where is the software vendor in all of this? Do they not have an obligation to investigate their client's complaints of poor application performance?

 

[tamhas]

Well, you've quoted it twice ... were you planning on responding at some point?

 

[oli]

My two cents...

This remark might sounds trivial in regard of Rob's analysis, but since your first notice was about backups duration, are they running through a scheduled task? If so, keep in mind that Windows Server 2008 uses Task Scheduler (2.0). Tasks are defined by default with a low I/O priority (i.e. 7: BELOW_NORMAL_PRIORITY_CLASS).
In order to change the task's priority, you have to export the task definition, edit the generated .xml file, modify the priority (set it between 4 and 6), and then re-import the task.

 

[Bertrand]

Rob, I'm sorry. When I was talking about times, it was backup times.
- new VM (iSCSI disks) + new installation W2008R2 +copy of the production DB => backup time = 15 minutes
- new VM (iSCSI disks) + new installation W2008R2 + copy of the production DB + installation of the application => backup time = 32 minutes
- same VM (iSCSI disks) + application running on production => backup time = 55 minutes
Extensive tests and metrology on disks show a access time (average) less that 15 ms

- old VM (NFS disks) + + application running on production => backup time = 2 hours and 30 minutes
access time (average) about 40 ms

So my first main question was: DB access should be at least twice faster (even though it s RAID DP and then still not as efficient as it should be) ?

I did the test you do request. Hereafter are the results:
20:03:58,14: Creating a sports database...

DÚbut de session Procopy pour l'utilisateur Administrateur sur CON:. (451)
La base de donnÚes a ÚtÚ copiÚe depuis P:\Progress\DLCV10~1\sports. (1365)
Fin de la session Procopy. (334)

20:03:59,65: Setting the BI cluster size to 32 MB...
OpenEdge Release 10.2B06 as of Mon Mar 19 19:15:43 EDT 2012
La taille du cluster avant image (BI) est Úgale Ó 32768 kb. (1620)

20:03:59,82: Growing the BI by 4 additional clusters (256 MB total)...
OpenEdge Release 10.2B06 as of Mon Mar 19 19:15:43 EDT 2012

20:04:19,96: Removing the database...
The following files will be deleted:
.\sports.b1
.\sports.d1
.\sports_7.d1
.\sports_8.d1
.\sports_9.d1
.\sports_10.d1
.\sports_11.d1
P:\Progress\Wrk\sports.lg
P:\Progress\Wrk\sports.db
Please confirm by entering y and RETURN: Deleting:
.\sports.b1
.\sports.d1
.\sports_7.d1
.\sports_8.d1
.\sports_9.d1
.\sports_10.d1
.\sports_11.d1
P:\Progress\Wrk\sports.lg
P:\Progress\Wrk\sports.db

20:04:20,31: Done.

For the other info:
OpenEdge release:
Product Name : Progress
Install Path : P:\Progress\Dlcv102b64
Version : 10.2B
Service Pack : 06
Temp. Fix : 00
Build : 1613

Windows release:
Nom de l'hôte: VM-BDD
Nom du système d'exploitation: Microsoft Windows Server 2008 R2 Standard
Version du système: 6.1.7601 Service Pack 1 version 7601

Fabricant du système d'exploitation: Microsoft Corporation
Configuration du système d'exploitation: Serveur membre
Type de version du système d'exploitation: Multiprocessor Free
Propriétaire enregistré: Utilisateur Windows
Organisation enregistrée:
Identificateur de produit: 00477-OEM-8421517-06893
Date d'installation originale: 26/01/2014, 12:20:24
Heure de démarrage du système: 03/02/2014, 15:55:39
Fabricant du système: VMware, Inc.
Modèle du système: VMware Virtual Platform
Type du système: x64-based PC
Processeur(s): 2 processeur(s) installé(s).
[01] : Intel64 Family 6 Model 45 Stepping 7 GenuineIntel ~2000 MHz
[02] : Intel64 Family 6 Model 45 Stepping 7 GenuineIntel ~2000 MHz
Version du BIOS: Phoenix Technologies LTD 6.00, 22/06/2012
Répertoire Windows: C:\Windows
Répertoire système: C:\Windows\system32
Périphérique d'amorçage: \Device\HarddiskVolume1
Option régionale du système: fr;Français (France)
Paramètres régionaux d'entrée: fr;Français (France)
Fuseau horaire: (UTC+01:00) Bruxelles, Copenhague, M
adrid, Paris
Mémoire physique totale: 20 479 Mo
Mémoire physique disponible: 7 202 Mo
Mémoire virtuelle : taille maximale: 40 957 Mo
Mémoire virtuelle : disponible: 25 477 Mo
Mémoire virtuelle : en cours d'utilisation: 15 480 Mo
Emplacements des fichiers d'échange: C:\pagefile.sys
Domaine: bigmat.local
Serveur d'ouverture de session: \\VM-BDD

RAM is dedicated and not overprovisionned. In the 1st installation, we dedicated 32 Go RAM but the machine never used it, so we decided when we did build the new VM to configure 20 Go RAM.

For the other info,

• the connection is over LAN (remote offices are connected through TSE sessions, so acting over LAN)
• don't know how to get the other informations. Which config file should I read to give you those info

We are working on how to re-organize the info to either provide a RAID 1 infrastructure or, may be, a RAID10. It is not that simple because we must deal without any extension (no additionnal investment

 

[Rob Fitzpatrick]

Your post seems to have cut off mid-sentence; was that the end of it or is there more?

So you answered a few of my questions. It seems you should steer clear of your NFS mounts as you are getting horrible performance there. I am not clear on where the production database is currently mounted; is it on the iSCSI disks or on the NetApp filer accessed via NFS?

What is the full probkup command you are using?

The sports database BI grow test took 22 seconds which is very slow. The customer system I mentioned runs it in 5 seconds.

I see that OpenEdge is installed on your P: drive. Is this a network install? That's another practice I associate with bad performance. From the directory name I assume this is 64-bit OpenEdge.

You said you previously had 32 GB of RAM but "the machine" never used it. What about the database? What are your startup parameters? Does a VM with 20 GB of RAM allow you enough buffer cache to satisfy all your application reads and still have empty buffers? You can see empty buffers in promon R&D | 1 | 7.

This doesn't make any sense:

- new VM (iSCSI disks) + new installation W2008R2 +copy of the production DB => backup time = 15 minutes
- new VM (iSCSI disks) + new installation W2008R2 + copy of the production DB + installation of the application => backup time = 32 minutes

The only difference between the two lines above is "+ installation of the application". The application is not involved with a database backup. You need to investigate this further to determine the difference between these two VMs (I assume they are different VMs). They should have identical backup times.

Regarding your statement that you must deal with this problem with no additional investment, I don't see that. You are spending time on this, aren't you? Time costs money just like hard drives cost money and it seems you have already invested a fair bit of time already and you aren't done yet. Addressing this problem will cost someone money no matter what. I like to see money spent where it will do the most good. Buying and configuring fast storage is a more worthy investment than trying at length to troubleshoot slow storage, in my opinion.

 

[Rob Fitzpatrick]

In Windows you can work with the database in a "proenv" command prompt. A shortcut to proenv is available from the OpenEdge folder under the Start menu.

Run this command and put the output within CODE tags:
proutil databasename -C describe
This command shows several pieces of information, including database block size, BI block size, BI cluster size, AI block size (if after imaging is enabled), and enabled database features

The database structure file is in the database directory and has the name databasename.st. Update it with this command:
prostrct list databasename
and then post the contents of the structure file within CODE tags.
This will show information about your storage areas, whether they are Type II (in other words, whether their blocks per cluster value is greater than 1), and their records per block values.

Some of the other information I asked about is available in a database analysis report. If you don't have a recent one then you can run one with this command:
proutil databasename -C dbanalys -Bp 10 > outputfile
This report shows an analysis of your database, storage area by storage area. It is broken into sections. The RECORD BLOCK SUMMARY section gives you information about your tables. The INDEX BLOCK SUMMARY section gives you information about your indexes. All of your ABL application tables are in the PUB schema; their table and index line items will be prefixed with "PUB." in the report. All Progress system table and index names begin with "_". Look at the top of the Schema Area output in the table section and the index section. There should not be any table or index names that begin with "PUB.".

Database startup parameters, at least some of them, can be found in the database log file. It is located in the database directory and its name is databasename.lg. View the file in a text editor, go to the bottom, and then search upwards for this message:

(333)  Multi-user session begin.

Then search downwards for this message:

(10471) Database connections have been enabled.

Copy all of the messages in between and paste them here in CODE tags.

Go into promon, screen 5 (Activity). Copy the screen contents and paste them here in CODE tags.

 

[TomBascom]

NetApp will probably not tell you this. They will fill your head with exciting stories and bold promises.

"RAID DP" is NetApp's variety of RAID 6. RAID 6 is very similar to RAID 5 -- except that there are 2 parity disks instead of 1. This is because as disks have become larger the likelihood of multiple simultaneous failures has increased. An extra parity disk means that write operations are 150% as expensive as they are with RAID 5 -- instead of writing to the data disk and the parity disk you are now writing to the data disk and *two* parity disks.

Read performance is not improved at all and since the need to mask poor write performance with RAM is increased the benefit that you get from any given amount of cache is reduced -- so read performance may even go down if you sized your RAM cache the same.

By choosing a SAN or a NAS you optimize for:

• Storage administrator convenience (one SAN to rule them all, or in NetApp's case, one NAS to rule them all).
• "Space" -- which is the only technical metric bean-counters ever seem to be able to understand.
• Sales weasel commissions.

You are specifically compromising performance. Any shared device is a performance compromise. It can only ever be as fast as a dedicated device if nobody else is using it. By externalizing storage you are also almost certainly introducing latency in whatever technology connects it to multiple hosts. To cover up these deficiencies vendors add lots and lots of RAM cache and other hardware (i.e. "storage processors") plus fancy proprietary implementations of common algorithms (calling "RAID 6" "RAID DP"...) Under light loads when the device is brand-new and nobody else is using it yet it might even seem to "work" reasonably well.

But all of that effort to mask the downsides costs money and increases costs. If you are running a large data center with lots of miscellaneous non-performance oriented users of storage it may still be sensible for those applications. But that still doesn't make it the right choice for databases that need good performance.

Dedicated storage devices for databases that need good performance can actually be relatively inexpensive. You'd be amazed at how cost effective and performant a couple of SSD drives can be.

 

[Bertrand]

The 2 cents remark does make sense to explain why the installation of the application, which should start some jobs, processes etc. had an impact in a decrease of the backup performance.

I fully agree with your performances assertions and conclusions. But, if the customer does require a non-stop production for the application, 14 hours a day, 6 day a week, with a fail over time less than few minutes, if I do externalize storage and use VMs, I'm able to manage that.

I might use the local storage (the VM are hosted on machines having RAID1 600 G disks) but in such a case, a physical machine failure mean a production stop and a DB restore to the previous backup. If I did run two VMs on separate physical machines, might it be possible to synchronize them using OpenEdge fail over mechanism ?

 

[Rob Fitzpatrick]

I might use the local storage (the VM are hosted on machines having RAID1 600 G disks) but in such a case, a physical machine failure mean a production stop and a DB restore to the previous backup.

It sounds like you are not using after imaging.

 

[Bertrand]

Promon Option 5:

   [FONT=Courier New]Enter your selection: 5
♀Activity  - Sampled at 03/17/14 18:25 for 1004:52:27.

Event                 Total   Per Sec   Event                 Total   Per Sec
      Commits     79269804      21.9            Undos       171706       0.0
Record Updates     82473315      22.8     Record Reads  69782014113   19289.9
Record Creates     14330178       4.0   Record Deletes     11329658       3.1
    DB Writes     77235668      21.4         DB Reads   1928710485     533.2
    BI Writes     12083142       3.3         BI Reads     10358465       2.9
    AI Writes            0       0.0
  Record Locks   3270161577     904.0     Record Waits          871       0.0
  Checkpoints       170785       0.0    Buffs Flushed       364426       0.1

Rec Lock Waits    0 %    BI Buf Waits      0 %    AI Buf Waits      0 %
Writes by APW    99 %    Writes by BIW    88 %    Writes by AIW     0 %
Buffer Hits      99 %    Primary Hits     99 %    Alternate Hits  100 %
DB Size          27 GB       BI Size     947 MB       AI Size       0 K
FR chain                    11 blocks   RM chain                    2 blocks

Shared Memory   9505M        Segments      1

75 Servers, 47 Users (0 Local, 47 Remote, 3 Batch),2 Apws[/FONT]

 

[Bertrand]

OpenEdge Release 10.2B06 as of Mon Mar 19 19:15:43 EDT 2012[/FONT]

[FONT=Courier New]OpenEdge Database Description

Database Name               : P:\Progress\Wrk\xxxxx\BD\yyyyyyy
Version                     : 150.0
Block Size                  : 4096
Largest Cluster             : 8
Create Date                 : Mon May 21 16:44:42 2012
Last Open Date              : Mon Feb 03 21:33:06 2014
Prior Open Date             : Mon Feb 03 21:33:06 2014
Schema Change Date          : Tue Mar 11 08:39:57 2014

Before Imaging information
  Block Size                : 8192
  Cluster Size (16K Units)  : 32
  Last Open Date            : Mon Feb 03 21:33:13 2014[/FONT]

[FONT=Courier New]Backup Information
  Last Full Backup Date     : Mon Mar 17 12:15:16 2014
  Last Incremental Backup   : *** Not yet performed ***[/FONT]

[FONT=Courier New]Database Features[/FONT]

[FONT=Courier New]  ID   Feature                            Active  Details
  ----  ---------------------------------  ------  -------
    5  Large Files                        Yes
    9  64 Bit DBKEYS                      Yes
    10  Large Keys                         Yes
    11  64 Bit Sequences                   Yes

 

[Bertrand]

OpenEdge Release 10.2B06 as of Mon Mar 19 19:15:43 EDT 2012
Avertissement : Un autre utilisateur utilise cette base de donnÚes en modification.
AccÚder Ó la base avec -RO pourrait engender des rÚsultats inattendus. (1531)
Area Name: Control Area, Type 6, Block Size 4096, Extents 1, Records/Block 32, Cluster Size 1
 N░ ext 1, Type VARIABLE, Taille 32 Ko, Nom : P:\Progress\Wrk\xxxxxx\BD\yyyyyyyy.db

Nom Area : Primary Recovery Area, Type 3, Taille du bloc 8192, Extents 2
 N░ ext 1, Type FIXED   , Taille 128000 Ko, Nom : P:\Progress\Wrk\xxxxxx\BD\VOL_BI\yyyyyyyy.b1
 N░ ext 2, Type VARIABLE, Taille 841856 Ko, Nom : P:\Progress\Wrk\xxxxxx\BD\VOL_BI\yyyyyyyy.b2

Area Name: Schema Area, Type 6, Block Size 4096, Extents 1, Records/Block 32, Cluster Size 1
 N░ ext 1, Type VARIABLE, Taille 67904 Ko, Nom : P:\Progress\Wrk\xxxxxx\BD\yyyyyyyy.d1

Area Name: FicArt, Type 6, Block Size 4096, Extents 1, Records/Block 16, Cluster Size 8
 N░ ext 1, Type VARIABLE, Taille 10155648 Ko, Nom : P:\Progress\Wrk\xxxxxx\BD\VOL_FICART\yyyyyyyy_7.d1

Area Name: CfgAppl, Type 6, Block Size 4096, Extents 1, Records/Block 32, Cluster Size 8
 N░ ext 1, Type VARIABLE, Taille 286400 Ko, Nom : P:\Progress\Wrk\xxxxxx\BD\VOL_CFGAPPL\yyyyyyyy_8.d1

Area Name: FicStable, Type 6, Block Size 4096, Extents 1, Records/Block 16, Cluster Size 8
 N░ ext 1, Type VARIABLE, Taille 297408 Ko, Nom : P:\Progress\Wrk\xxxxxx\BD\VOL_FICSTABLE\yyyyyyyy_9.d1

Area Name: FicChangeant, Type 6, Block Size 4096, Extents 1, Records/Block 32, Cluster Size 8
 N░ ext 1, Type VARIABLE, Taille 427456 Ko, Nom : P:\Progress\Wrk\xxxxxx\BD\VOL_FICCHANGEANT\yyyyyyyy_10.d1

Area Name: FicStat, Type 6, Block Size 4096, Extents 1, Records/Block 64, Cluster Size 8
 N░ ext 1, Type VARIABLE, Taille 951296 Ko, Nom : P:\Progress\Wrk\xxxxxx\BD\VOL_FICSTAT\yyyyyyyy_11.d1

Area Name: FicDevCde, Type 6, Block Size 4096, Extents 1, Records/Block 8, Cluster Size 8
 N░ ext 1, Type VARIABLE, Taille 759488 Ko, Nom : P:\Progress\Wrk\xxxxxx\BD\VOL_FICDEVCDE\yyyyyyyy_12.d1

Area Name: FicBlFac, Type 6, Block Size 4096, Extents 1, Records/Block 8, Cluster Size 8
 N░ ext 1, Type VARIABLE, Taille 4296448 Ko, Nom : P:\Progress\Wrk\xxxxxx\BD\VOL_FICBLFAC\yyyyyyyy_13.d1

Area Name: FicAch, Type 6, Block Size 4096, Extents 1, Records/Block 32, Cluster Size 8
 N░ ext 1, Type VARIABLE, Taille 1699200 Ko, Nom : P:\Progress\Wrk\xxxxxx\BD\VOL_FICACH\yyyyyyyy_14.d1

Area Name: FicMvt, Type 6, Block Size 4096, Extents 1, Records/Block 16, Cluster Size 8
 N░ ext 1, Type VARIABLE, Taille 4793536 Ko, Nom : P:\Progress\Wrk\xxxxxx\BD\VOL_FICMVT\yyyyyyyy_15.d1

Area Name: FicIdxMvt, Type 6, Block Size 4096, Extents 1, Records/Block 32, Cluster Size 8
 N░ ext 1, Type VARIABLE, Taille 4908608 Ko, Nom : P:\Progress\Wrk\xxxxxx\BD\VOL_FICIDXMVT\yyyyyyyy_16.d1

Area Name: FicArtModele, Type 6, Block Size 4096, Extents 1, Records/Block 32, Cluster Size 8
 N░ ext 1, Type VARIABLE, Taille 64 Ko, Nom : P:\Progress\Wrk\xxxxxx\BD\VOL_FICARTMODELE\yyyyyyyy_17.d1

Area Name: FicIdxArtModele, Type 6, Block Size 4096, Extents 1, Records/Block 32, Cluster Size 8
 N░ ext 1, Type VARIABLE, Taille 320 Ko, Nom : P:\Progress\Wrk\xxxxxx\BD\VOL_FICIDXARTMODELE\yyyyyyyy_18.d1

Area Name: FicEntrepot, Type 6, Block Size 4096, Extents 1, Records/Block 32, Cluster Size 8
 N░ ext 1, Type VARIABLE, Taille 1344 Ko, Nom : P:\Progress\Wrk\xxxxxx\BD\VOL_FICENTREPOT\yyyyyyyy_19.d1

Nom Area : After Image Area 1, Type 7, Taille du bloc 8192, Extents 1
 N░ ext 1, Type VARIABLE, Taille 128 Ko, Nom : P:\Progress\Wrk\xxxxxx\BD\VOL_AI\yyyyyyyy.a1

Nom Area : After Image Area 2, Type 7, Taille du bloc 8192, Extents 1
 N░ ext 1, Type VARIABLE, Taille 128 Ko, Nom : P:\Progress\Wrk\xxxxxx\BD\VOL_AI\yyyyyyyy.a2

Nom Area : After Image Area 3, Type 7, Taille du bloc 8192, Extents 1
 N░ ext 1, Type VARIABLE, Taille 128 Ko, Nom : P:\Progress\Wrk\xxxxxx\BD\VOL_AI\yyyyyyyy.a3

Nom Area : After Image Area 4, Type 7, Taille du bloc 8192, Extents 1
 N░ ext 1, Type VARIABLE, Taille 128 Ko, Nom : P:\Progress\Wrk\xxxxxx\BD\VOL_AI\yyyyyyyy.a4

 

[Bertrand]

Last answer for today (except if I'm courageous tonight): the P: drive, even though accessed through a NAS using iSCSI is seen as a local mapping, not a network mapping

Thanks for your help. I'm really far far far from expert (of course ...) but I'm starting seeing what can be done for enhancing performances.