 |
| 17 Database Tuning |
|---|
| Back to TOC |
A number of parameters affect the performance behaviour and scalability of the cego database system. In the following these parameters are explained in more detail and how to set them up in an appropriate way. Since the change of the parameters is a sophisticated ( and critical ) task, a native database user or admin should not change it.
Some of the parameters have to set up at compile time and so they are constants within the executable cego program. To change the parameter value, you have to do a recompilation of the source code. The second set of paramters can be set up in the database xml configuration file. Both kind of parameters are described now.
17.1 Incompile parameters
All incompile tuning parameters are located in the source code header file
| File Management | ||
|---|---|---|
| Parameter | Meaning | Default Value |
| FILMNG_MAXDATAFILE | Maximum number of managed datafiles | 100 |
| Table Management | ||
| Parameter | Meaning | Default Value |
| TABMNG_MAXTABSET | Maximum number of managed tablesets | 30 |
| TABMNG_MAXINDEXVALUE | Maximum length of an index value ( in byte ) | 1000 |
| TABMNG_MAXJOINLEVEL | Maximum number of allowed subselects | 10 |
| LOGMNG_RECOVERY_DELAY | Waiting delay for next logfile to recover | 2 |
| Datatype Management | ||
| Parameter | Meaning | Default Value |
| MAX_INT_LEN | Max outut format integer len | 10 |
| MAX_FLOAT_LEN | Max outut format float len | 40 |
| MAX_DOUBLE_LEN | Max outut format double len | 45 |
| MAX_DECIMAL_LEN | Max outut format decimal len | 30 |
| MAX_FIXED_LEN | Max outut format fixed len | 30 |
| MAX_SMALLINT_LEN | Max outut format smallint len | 6 |
| MAX_TINYINT_LEN | Max outut format tinyint len | 4 |
| MAX_DATETIME_LEN | Max outut format datetime len | 30 |
| MAX_BOOL_LEN | Max outut format bool len | 5 |
| MAX_NULL_LEN | Max outut format null value len | 4 |
| MAX_OBJNAME_LEN | Max object name len | 10 |
| MAXSTRINGLEN | Max string len | 10000 |
| MAX_USERNAMELEN | Max username len | 15 |
| MAX_PASSWORDLEN | Max password len | 15 |
| RESERVED_BTREE_FLOATLEN | Reserved space in btree entries for type fixed and bigdecimal | 30 |
| STATICFIELDBUF | Statically reserved buffer for field values | 20 |
| Lock Management | ||
| Parameter | Meaning | Default Value |
| LCKMNG_NUM_DATAFILE_SEMA | Number of semaphores allocated for file lock operations | 10 |
| LCKMNG_NUM_BUFFERPOOL_SEMA | Number of semaphores allocated for buffer pool lock operations | 30 |
| LCKMNG_NUM_RECORD_SEMA | Number of semaphores allocated for record lock operations | 10 |
| LCKMNG_NUM_SYSPAGE_SEMA | Number of semaphores allocated for system page lock operations | 10 |
| LCKMNG_NUM_DATAPAGE_SEMA | Number of semaphores allocated for data page lock operations | 10 |
| LCKMNG_NUMLOCKS | Number of parallel locks per thread | 10 |
| FH_LOCKTIMEOUT | FileHandler lock timeout ( msec ) | 100000 |
| BP_LOCKTIMEOUT | BufferPool lock timeout ( msec ) | 10000 |
| TS_LOCKTIMEOUT | Transaction Manager lock timeout ( msec ) | 80000 |
| XS_LOCKTIMEOUT | XML Space lock timeout ( msec ) | 80000 |
| DBM_LOCKTIMEOUT | Database Manager lock timeout ( msec ) | 80000 |
| Naming Management | ||
| Parameter | Meaning | Default Value |
| TABMNG_PIDX_SUFFIX | Tablename suffix for primary index tables | _pidx |
| SYSSUFFIX | Datafile suffix for system datafile | .sys |
| SYSSUFFIX | Datafile suffix for temp datafiles | .temp |
| OSPACE | Name prefix for ordering tables | ospace |
| Buffer Pool Management | ||
| Parameter | Meaning | Default Value |
| BUPMNG_MAXFIXTRIES | Search range to look for an appropriate page slot | 25 |
| BUPMNG_MAXPAGEDELETE | Maximimum number of pages for release before forcing a checkpoint | 500 |
| BUPMNG_MINFREERATIO | Space reservation in data pages for free space management ( in percent ) | 10 |
| BUPMNG_ALIGNMENT | Datapointer alignment ( required form some system architectures) | sizeof(int) |
| Network Management | ||
| Parameter | Meaning | Default Value |
| NETMNG_SELECT_TIMEOUT | msg timeout for select system call (in usec) | 1500000 |
| NETMNG_RECV_TIMEOUT | msg timeout for recv system call (in sec) | 180 |
| NETMNG_MSG_BUFLEN | msg buffer size for recv ( in byte ) | 8192 |
| NETMNG_COLPREFIX | prefix sign for unique col identification | c |
| NETMNG_MAXTUPLECOUNT | Number of tuples to send within one msg | 10 |
| Authorization Management | ||
| Parameter | Meaning | Default Value |
| CEGOSALT | Salt key for password authorization | "$1$hgz" |
17.2 Configuration parameters
The configuration parameters can be set up in the database xml configuration file. They have to be set up database wide as attributes in the DATABASE tag
| Lock Management | ||
|---|---|---|
| Parameter | Meaning | Default Value |
| NUMDATAFILESEMA | Number of datafile semaphores | 11 |
| NUMBUFFERPOOLSEMA | Number of bufferpool semaphores | 31 |
| NUMRECSEMA | Number of row record semaphores | 31 |
| NUMSYSPAGESEMA | Number of system page semaphores | 31 |
| NUMDATAPAGESEMA | Number of data page semaphores | 31 |
| NUMIDXPAGESEMA | Number of index page semaphores | 17 |
| NUMRBPAGESEMA | Number of rollback page semaphores | 17 |
| MAXFIXTRIES | Maximum number of tries to fix a bufferpool page | 25 |
| MAXPAGEDELETE | Maximum number of pages to release before forcing a checkpoint | 500 |
| MAXSENDLEN | Network message chunk len for a db thread sending result data | 8192 |
| SELECTTIMEOUT | Socket select timeout value for all thread pools in usec, can be used to decrease idle CPU load | 3000000 |
| QUEUEDELAY | Delay for each thread if no requests are available in usec, can be used to throttle down number of incoming connections and to decrease idle CPU load | 50000 |
| NUMLOCKTRIES | Maximum number of tries to achieve any lock | 3 |
| RECLOCKTIMEOUT | Timeout value to achieve any record lock ( in msec ) | 10000 |
| PAGELOCKTIMEOUT | Timeout value to achieve any page lock ( in msec ) | 10000 |
| FILELOCKTIMEOUT | Timeout value to achieve any file lock ( in msec ) | 10000 |
| POOLLOCKTIMEOUT | Timeout value to achieve any pool lock ( in msec ) | 300000 |
Sample
<?xml version="1.0"?> <!DOCTYPE CEGO_DB_CONFIG> <DATABASE NAME="cegodb" PAGESIZE="4096" ADMINPORT="2000" LOGPORT="3000" DATAPORT="2200" NUMRECSEMA="281" NUMSYSPAGESEMA="53" NUMDATAPAGESEMA="281" NUMIDXPAGESEMA="281" NUMRBPAGESEMA="281" NUMDATAFILESEMA="53" NUMBUFFERPOOLSEMA="31"> </DATABASE> |