idea Bring DBTUNE Template File Back in Data Management Ideas

Bring DBTUNE Template File Back

MarceloMarques — Tue, 18 Oct 2022 22:06:58 GMT

I am a database administrator responsible to setup enterprise geodatabases that store very large datasets (terabytes), the best practice is to separate tables and indexes to improve performance, this reduces database datafiles fragmentation and contention, thus database i/o improves which leads to better performance, also improves maintenance tasks such as sde compress (versioning), rebuilding indexes and gather new statistics. In order to accomplish this I use a custom DBTUNE file.

1) DBTUNE documentation

If you look the 10.2.2 documentation for PostgreSQL it has an example on how to configure a custom dbtune keyword.

B_STORAGE              "TABLESPACE userdata" 
B_INDEX_ROWID          "WITH (FILLFACTOR = 60)TABLESPACE userdata" 
B_INDEX_USER          "WITH (FILLFACTOR = 75)TABLESPACE userdata" 
BLK_STORAGE                "TABLESPACE rasterdata" 
BLK_INDEX_COMPOSITE        "WITH (FILLFACTOR = 90)USING INDEX TABLESPACE" 
A_INDEX_RASTER             "WITH (FILLFACTOR = 75) TABLESPACE rasterdata" 
B_INDEX_RASTER             "WITH (FILLFACTOR = 75) TABLESPACE rasterdata" 
BND_STORAGE                "TABLESPACE rasterdata" 
BND_INDEX_COMPOSITE        "WITH (FILLFACTOR = 90)USING INDEX TABLESPACE" 
RAS_STORAGE            "TABLESPACE rasterdata" 
RAS_INDEX_ID           "WITH (FILLFACTOR = 90)USING INDEX TABLESPACE rasterdata"

Notice that for some keywords we have to use the " USING INDEX" syntax spite of the keyword name to have the word "INDEX", this because the dbtune keyword is used to create a table and an index as well.

10.2.2 was the last release of the ArcSDE Binaries Installer.

2) DBTUNE template file

After the "ArcSDE_for_PostgreSQL_on_Windows_1022_140610.exe" was installed we had a full dbtune template that we could use to fill up with the tablespaces name .

3) Remarks

It seems that this knowledge was dropped in the more recent versions of the documentation.

If possible consider to add this important info about the dbtune keywords back into the documentation (Oracle, SQL Server, PostgreSQL) and continue to provide a dbtune template file going forward, a good place to find it would be in the ArcGIS installation under the Database Support folder.

Re: Bring DBTUNE Template File Back

George_Thompson — Mon, 04 May 2020 11:50:48 GMT

Here is a way to do it, supported: Alter configuration keywords—Geodatabases in PostgreSQL | Documentation

Is that what you are looking for?

Re: Bring DBTUNE Template File Back

BillFox — Mon, 04 May 2020 18:34:42 GMT

Have not heard that term in a long time.

Is your environment virtual and on SAN gear?

Re: Bring DBTUNE Template File Back

MarceloMarques — Tue, 05 May 2020 19:08:45 GMT

I am a very experienced Geodatabase Administrator that has been using DBTUNE for over 20 years, this idea is to bring that forgotten knowledge back into the current documentation and to continue to provide a dbtune template file that is so useful for the geodatabase administrators. I talk more about this here "Mapping & Charting Solutions (MCS) Enterprise Databases Best Practices".

Re: Bring DBTUNE Template File Back

MarceloMarques — Tue, 05 May 2020 19:14:07 GMT

The best practice to separate tables and indexes applies for virtual and SAN storages. I talk more about this in my best practices guides books, check it out Mapping & Charting Solutions (MCS) Enterprise Databases Best Practices.

In particular this might be of interest DBTUNE configuration keywords and disk configuration.

Re: Bring DBTUNE Template File Back

George_Thompson — Tue, 05 May 2020 19:15:00 GMT

Ah ok, I see. I can understand that and appreciate the clarification. I can say that many people tried to toy with the file and created some interesting problems along the way.