PostgreSQL recommendations
The database is an important part of BMC Client Management, especially in highly distributed environments. For each of the three basic types of database-Web Application (Web), Online Transaction Processing (OLTP), and Data Warehousing (DW)-the hardware component priorities are different. Client Management works like a web application with web services; it behaves like an OLTP application when integrating all information reported by devices; and it performs like a DW application for report generation, aggregating the results and displaying inventory views.
This topic provides recommendations for the appropriate database configuration for PostgreSQL 9.1 in highly distributed environments:
Disk layout
When you run the Create_TS.pgsql.sql
script, which is provided for tablespace creation on your database, BMC recommends that you segregate the tablespaces on different disks, as shown in the following table:
Tablespaces for tables
Small | Large | Static |
---|---|---|
BCM_DATA | BCM_EL | BCM_VMCKB |
BCM_VM | BCM_INV | BCM_ESIDKB |
BCM_WQ | BCM_DELTAINV | BCM_PATCH |
— | BCM_VMINV | — |
— | BCM_ESIDDATA | — |
— | BCM_SCAP | — |
Tablespaces for indexes
Small | Large | Static |
---|---|---|
BCM_INDEX | BCM_ELINDEX | BCM_VMCKBINDEX |
BCM_VMINDEX | BCM_INVINDEX | BCM_ESIDKBINDEX |
BCM_WQINDEX | BCM_DELTAINDEX | BCM_PATCHINDEX |
— | BCM_VMINVINDEX | — |
— | BCM_ESIDDATAINDEX | — |
— | BCM_SCAPINDEX | — |
Recommended minimum disk layout
Disk | Rmp/s | File system | Purpose |
---|---|---|---|
1 | 10 K / 15 K | ext3 | / SYSTEM - POSTGRESQL |
1 | 10 K / 15 K | ext3 | / PG_XLOG - WALL |
1 | 10 K / 15 K | ext3 | / TABLESPACES FOR TABLES |
1 | 10 K / 15 K | ext3 | / TABLESPACES FOR INDEXES |
1 | 10 K / 15 K | ext3 | / TEMPORARY TABLESPACES |
Recommended best disk layout
Disk | Rmp/s | File system | Purpose |
---|---|---|---|
1 | 10 K / 15 K | ext3 | / SYSTEM - POSTGRESQL |
1 | 10 K / 15 K | ext3 | / PG_XLOG - WALL |
1 | 10 K / 15 K | ext3 | / TABLESPACES FOR SMALL TABLES |
1 | 10 K / 15 K | ext3 | / TABLESPACES FOR LARGE TABLES |
1 | 10 K / 15 K | ext3 | / TABLESPACES FOR STATIC TABLES |
1 | 10 K / 15 K | ext3 | / TABLESPACES FOR SMALL INDEXES |
1 | 10 K / 15 K | ext3 | / TABLESPACES FOR LARGE INDEXES |
1 | 10 K / 15 K | ext3 | / TABLESPACES FOR STATIC INDEXES |
1 | 10 K / 15 K | ext3 | / TEMPORARY TABLESPACES |
Recommended RAID implementation
Disk | Rmp/s | RAID level | Location | File system | Purpose |
---|---|---|---|---|---|
2 | 10 K / 15 K | 1 | /sda | ext3 | / SYSTEM |
2 | 10 K / 15 K | 10 | /sdb | ext3 | / POSTGRESQL + TABLESPACES |
2 | 10 K / 15 K | 1 | /sdc | ext3 | / PG_XLOG -WALL |
2 | 10 K / 15 K | None | /sde | ext3 | / TABLESPACE TEMP |
Storage capacity sizing
When sizing storage capacity for a PostgreSQL system, you need to consider the following PostgreSQL database components:
- Database files
- Transaction log files
- Backup
Database element | Space requirement | 20,000 nodes | 50,000 nodes | 100,000 nodes | 150,000 nodes | 200,000 nodes |
---|---|---|---|---|---|---|
Database files | DB size | 20 GB | 50 GB | 100 GB | 150 GB | 200 GB |
Transaction log "Wall" | DB size * 0.1 | 2 GB | 5 GB | 10 GB | 15 GB | 20 GB |
Backup | DB size * 0.2 | 4 GB | 10 GB | 20 GB | 30 GB | 40 GB |
Total | 26 GB | 65 GB | 130 GB | 145 GB | 260 GB |
System tuning for write-heavy operations
You can tune your system for better performance for the following write-heavy operations:
File access times
Applying the noatime
attribute can significantly improve the file I/O performance of your server. Edit the /etc/fstab file and add the noatime
attribute as follows:
/dev/sdb1 on / PostgreSQL ext3 rw,noatime
/dev/sdc1 on / Pg_xlog ext3 rw,noatime
/dev/sdd1 on / Tablespaces for tables ext3 rw,noatime
/dev/sde1 on / Tablespaces for indexes ext3 rw,noatime
# repeat for each additional disk
Read ahead
Modifying the Read-Ahead
parameter can improve the read performance of a disk.
Type # blockdev --getra /dev/sda to check the Read-Ahead
value of an individual disk. The default value is generally 256.
To permanently increase this value to 4096, edit the /etc/rc.local
file by adding the following block to the end of the file:
blockdev --setra 4096 /dev/sda
blockdev --setra 4096 /dev/sdb
blockdev --setra 4096 /dev/sdc
# repeat for each additional disks
Read caching and swapping
Modifying the following parameters increases database performance:
Parameter | Recommendation |
---|---|
| Decrease this value to 5 to make flushes more frequent but result in fewer I/O spikes. |
| Decrease this value to 10 to make flushes more frequent but result in fewer I/O spikes. |
| Deactivate swapping to eliminate the tendency of the kernel to move processes out of physical memory and onto the swap disk. |
To permanently change these parameter values, edit the /etc/sysctl.conf
file as follows:
vm.dirty_background_ratio = 5
vm.dirty_ratio = 10
vm.swappiness = 0
To set the values without rebooting, run the sysctl -p
command after saving and exiting the sysctl.conf
file.
Shared memory and semaphores
A large PostgreSQL installation can quickly exhaust various operating system resource limits. The following example shows how to increase the shared memory to 16 GB of RAM:
# sysctl -w kernel.shmmax=8420048896
# sysctl -w kernel.shmall=2055676
# sysctl -w kernel.sem = 250 32000 32 128
PostgreSQL configuration
PostgreSQL can easily be tuned using the third-party-tool pgtune . It is licensed under a standard three-clause BSD license. pgtune works by taking an existing postgresql.conf file as an input and making changes to it based on the amount of RAM in your server and a suggested workload. pgtune then outputs a new file with suggestions.
The following example is for a server with 16 GB of RAM and a suggested workload of 100 simultaneous connections to the server.
Enter the following command line:# ./pgtune -i $PGDATA/postgresql.conf -o $PGDATA/postgresql.conf.pgtune --type MIXED --connections=100
The values recommended by pgtune are appended at the end of the postgresql.conf.pgtune file, for example:#------------------------------------------------------------------------------ # pgtune run on 2013-07-08 # based on 16445412 KB RAM, platform Linux #------------------------------------------------------------------------------ default_statistics_target = 100 maintenance_work_mem = 960MB checkpoint_completion_target = 0.9 effective_cache_size = 11GB work_mem = 80MB wal_buffers = 16MB checkpoint_segments = 32 shared_buffers = 3840MB
Add the following additional values at the end of the postgresql.conf.pgtune file:
bgwriter_lru_maxpages = 1000 bgwriter_lru_multiplier = 4.0 random_page_cost = 2.0 cpu_tuple_cost = 0.03 log_autovacuum_min_duration = 0 autovacuum_max_workers = 5 autovacuum_vacuum_cost_delay = 10ms
Run the following commands to apply the new parameters and values to the original postgresql.conf file by renaming the postgresql.conf.pgtune pgtune file to postgresql.conf and then restarting PostgreSQL:
mv $PGDATA/postgresql.conf $PGDATA/postgresql.conf.sav mv $PGDATA/postgresql.conf.pgtune $PGDATA/postgresql.conf chown postgres:postgres $PGDATA/postgresql.conf chmod 0644 $PGDATA/postgresql.conf service postgresql restart
Comments
Log in or register to comment.