DB2 tablespace configuration and site performance

[MarkW]

Default Confluence installation on the DB2 database created a single tablespace. Due to size issues for the table space, it was rebuilt from a singler tablespace to the follow:

Data001_16k - 16k page size - DMS - contains all non-system tables except for attachments and attachmentdata
Index001_16k - 16k page size - DMS - contains all non-system table indexes except for attachments and attachmentdata indexes
Attachment - 16k page size - DMS - contains attachments and attachmentdata tables - 60 GB lob file and growing
Attachment_IDX - 16k page size - DMS - contains attachments and attachmentdata indexes
Temporary_16k - 16k page size - DMS
There is a 16k page size bufferpool for tables, another for indexes, and another for temporary processing.

This caused a degredation in performance to the site. Anything utilizing decendents, page trees, or displaying children took extraordinary amount of time to load. Some pages simply never returned. After limping along for a week we performed another rebuild to reset as close to possible the previous single tablespace setup. Here are the current settings:

Tablespaces per the app:
USERSPACE1	- 4k page size - SMS - contains all non-system tables except for the attachmentdata table.    
		- This is where the app installed all DB2 items.
		- This tablespace is created by DB2 at database creation time.

Tablespaces per dbosg:
ATTACHMENTDATA - 16k page size - DMS - contains table/lob data for the CWIKIUSR.ATTACHMENTDATA table.
ATTACHMENTDATA_IDX - 16k page size - DMS - contains index data for the CWIKIUSR.ATTACHMENTDATA table.
TEMP001_16K - 16k page size - tablespace for temporary processing (sorts, etc) of tables in 16k tablespaces.

Bufferpools:
IBMDEFAULTBP - for use by tables in 4k tablespaces - set back to -2 per original specifications
DATA001_16K - for use by tables in 16k tablespaces - set to 40000
INDEX001_16K - for use by indexes in 16k tablespaces - set to 40000
TEMP001_16K - for use by temporary 16k tablespace - set to 40000

The site is back to running normally again. Has anyone experienced any performane issues in DB2 or possibly any other database when splitting out the tablespaces?

[Rodrigo Girardi Adami]

Hi Mark,

I could find this answer regarding DB2 opened for so long that I wanted to let you know that Confluence doesn't support DB2 anymore on newer versions. Probably there were some problems that caused the deprecation of this specific DB.

I recommend to stick with one of these database vendors for newer versions of confluence: Oracle 11g, MSSQL server, Mysql and Postgres.

Cheers,

Rodrigo

[MarkW]

Hi Rodrigo, We had converted from DB2 to Oracle so the question no longer applies. Thanks!