Overview
This article provides PostgreSQL commands to improve database performance and resolve various issues, including HTTP 502 bad gateway errors.
These errors can occur due to systematic errors or operational slowdowns, such as transaction timeouts.
Pre-requisites
Is your DB optimized? If not, see KC.
Steps
Connect to PostgreSQL Instance:
| psql -U jetpatch -h localhost jetpatchdb |
Run the command to list the frequently used and large tables:
| select schemaname as table_schema, relname as table_name, pg_size_pretty(pg_total_relation_size(relid)) as total_size, pg_size_pretty(pg_relation_size(relid)) as data_size, pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as external_size from pg_catalog.pg_statio_user_tables order by pg_total_relation_size(relid) desc, pg_relation_size(relid) desc limit 10; |
VACUUM
The VACUUM command reclaims space still used by data that had been updated. In PostgreSQL, updated key-value tuples are not removed from the tables when rows are changed, so VACUUM should be run periodically.
Before performing VACUUM, stop the Tomcat service on the JetPatch application server:
| systemctl stop tomcat |
Run VACUUM FULL on each large table:
| VACUUM FULL <TABLENAME>; |
Once completed, run the size query again to verify the reduction:
| select schemaname as table_schema, relname as table_name, pg_size_pretty(pg_total_relation_size(relid)) as total_size, pg_size_pretty(pg_relation_size(relid)) as data_size, pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as external_size from pg_catalog.pg_statio_user_tables order by pg_total_relation_size(relid) desc, pg_relation_size(relid) desc limit 10; |
REINDEX
REINDEX rebuilds an index using the data stored in the index's table, replacing the old copy. It reduces index space consumption by writing a new version without dead pages.
Run REINDEX for each table from the list above:
| REINDEX TABLE <TABLENAME>; |
|
Note: On PostgreSQL 12+, use REINDEX CONCURRENTLY to avoid locking the table during the operation:
|
VACUUM VERBOSE
Removes dead tuples and releases space taken by deleted rows.
| VACUUM VERBOSE |
VACUUM ANALYZE
Updates query planner statistics to improve performance and produce better query execution plans.
| VACUUM ANALYZE |
After completing all maintenance steps, start Tomcat on the JetPatch application server:
| systemctl start tomcat |
Additional Steps
If the issue persists, generate the latest manager logs and a database dump for further analysis.
As a temporary workaround, increase the following properties in the JetPatch application configuration file:
| #default 3600 spring.transaction.timeout.sec=7200 #default 30000 spring.datasource.hikari.connectionTimeout=60000 |
This adjustment should only be used as a temporary measure while investigating the root cause. Once a permanent solution is implemented, revert these values.
Comments
0 comments
Please sign in to leave a comment.