Tuning LiveUpdate Administrator 2.x's PostgreSQL Database
Article ID: 177632
Updated On:
Products
Issue/Introduction
LUA 2.1 and LUA 2.2 use a open source object-relational database management system (RDBMS) called PostgreSQL for the storage and maintenance of data used by LUA. This data includes source server, failover server, updates to be downloaded, and download schedules. The version that is currently in use, PostgreSQL 8.2.9, does not have Autovacuum enabled by default or multiple autovacuum workers, and so there may be concerns about how best to tune and maintain the database.
Symptoms
Poor database performance can be indicated by failures in LUA 2.x to distribute content, freezes or crashes in the product, slow processing or other symptoms. PostgreSQL ships with its settings tuned for wide compatibility. All customers are recommended to tune their PostgreSQL settings after a successful installation in order to maximize performance.
Resolution
Note: The document below contains advanced details on tuning PostgreSQL. Rather than create their own, most LUA 2.x administrators can successfully use the configuration files attached to the article on LiveUpdate Administrator 2.2 Performance Tuning
Version
LUA 2.1 shipped with PostgreSQL 8.1. The version used in LUA 2.2 contained superior performance management features (see http://www.postgresql.org/about/featurematrix for details). To ensure that all of the most recent enhancements and improvements are available- including database tuning and maintenance improvements- Symantec Technical Support recommends upgrading to the latest available release of LUA 2.x if you have not already done so.
Tuning postgresql.conf
PostgreSQL Global User Configurations (GUCs) are the configurable parameters for the product. As of October 2008, PostgreSQL has 187 GUCs, most of which are server configuration settings. Most need never be adjusted by the database administrator, but there are a handful that are useful for performance tuning.
The main database server parameters are held in a file named postgresql.conf. The default location in LUA 2.x is in C:\Program Files\Symantec\LiveUpdate Administrator\pgsql\data (on a 64-bit server: C:\Program Files (x86)\Symantec\LiveUpdate Administrator\pgsql\data. A full default copy of LUA 2.2's postgresql.conf is attached, in case no backup copy was saved before undocumented changes were made, and now need to be reversed.
PostgreSQL ships with its settings tuned for wide compatibility. Changing several key GUCs should result in noticeable performance improvements within one day.
1. shared_buffers
shared_buffers determines how much memory is dedicated to PostgreSQL use for caching data. Some sources recommend setting shared_buffers up to 25% of a system's RAM.
By default, LUA 2.2 has a value of shared_buffers = 32MB. A setting of 512MB has been effective. Some larger customers have increased this value up to 1024MB and seen great improvements.
2. temp_buffers
temp_buffers are used only for holding temporary tables in memory.
As LUA 2.x does not rely heavily upon these tables, some customers have lowered this value to 2MB as part of their tuning.
3. work_mem
work_mem sets the maximum memory to be used for query workspaces.
By default, this GUC is commented out. Setting work_mem to 256MB has resulted in improvement for some customers.
4. max_fsm_pages
Sets the maximum number of disk pages for which free space is tracked. This value should be set to the maximum number of data pages you expect to be updated or deleted between vacuums.
A value of 20000 has worked well for some customers.
5. wal_buffers
wal_buffers sets the number of disk-page buffers in shared memory for WAL.
A value of 256Kb should be sufficiently large. for healthy LUA 2.x usage
6. checkpoint_segments
checkpoint_segments sets the maximum distance in log segments between automatic WAL checkpoints. Checkpoint warnings appear in the logs when this is not set sufficiently high. Recommended values range from 16 to 128, with greater hard drive space being required the higher the value is set.
By default, LUA 2.2 has the checkpoint_segments commented out (#) so has a default of 3. Some larger customers have increased this value to 30 and seen great improvements.
7. effective_cache_size
effective_cache_size Sets the planner's assumption about the size of the disk cache. That is, the portion of the kernel's disk cache that will be used for PostgreSQL data files. This GUC tells the PostgreSQL query planner how much RAM is estimated to be available for caching data, in both shared_buffers and in the filesystem cache. effective_cache_size should be set to how much memory is leftover for disk caching after taking into account what's used by the operating system, dedicated PostgreSQL memory, and other applications. Some sources recommend setting effective_cache_size at 50% of a system's RAM. On a server used solely for running LUA 2.2, this might represent most of the system's physical memory.
By default, LUA 2.2 has effective_cache_size commented out, so it uses a default value of 128MB. Some larger customers have increased this value up to 2048MB and seen great improvements.
After changes are made to postgresql.conf, the LUA PostgreSQL service must be restarted. Rebooting the server will effectively restart all services and clean our many cache and temporary files.
Periodic maintenance
To maintain the database performance:
Stop “LUA Apache Tomcat” service. Do NOT stop “LUA PostgreSQL” service. Open a command window and run the following commands. Allow each to complete before beginning the next. Each may require several minutes.
- 1. Analyze the database
vacuumdb -z -h 127.0.0.1 -p 7072 -U lua -d lua >out_lua.txt 2>&1
2. Verify the database
vacuumdb -v -h 127.0.0.1 -p 7072 -U lua -d lua >out_lua_vdb.txt 2>&1
3. Reindex the database
reindexdb -h 127.0.0.1 -p 7072 -U lua -d lua >out_lua_vdb_idx.txt 2>&1
4. Re analyze and re verify the database after the reindex
vacuumdb -zv -h 127.0.0.1 -p 7072 -U lua -d lua >out_lua_vdb_idx_vdb.txt 2>&1
Verify the database using the following command:
- vacuumdb -fzv -h 127.0.0.1 -p 7072 -U lua -d lua >out_lua_vdb_idx_vdb_f.txt 2>&1
Automatically vaccuuming tables
PostgreSQL 8.2.9 has Autovacuum capabilities, but these are not enabled by default. Changing the following values in postgresql.conf should result in a healthier database over time.
- autovacuum = on
autovacuum_naptime = 60
autovacuum_vacuum_threshold = 500
autovacuum_analyze_threshold = 250
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
random_page_cost = 3.5
maintenance_work_mem = 16384
After changes are made to postgresql.conf, the LUA PostgreSQL service must be restarted. Rebooting the server will effectively restart all services and clean our many cache and temporary files.
References
Performance Optimization http://wiki.postgresql.org/wiki/Performance_Optimization
VACUUM: http://www.postgresql.org/docs/8.2/interactive/sql-vacuum.html
VACUUMDB: http://www.postgresql.org/docs/8.1/static/app-vacuumdb.html
REINDEXDB: http://www.postgresql.org/docs/8.1/static/app-reindexdb.html
Technical Information
The LUA PostgreSQL service runs under the Local System account. The default path to executable is C:/Program Files (x86)/Symantec/LiveUpdate Administrator/pgsql/bin/pg_ctl.exe runservice -N "LUA PostgreSQL" -D "C:/Program Files (x86)/Symantec/LiveUpdate Administrator/pgsql/data" This should not be altered without good cause.
Attachments
Feedback
