Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.
1. We have determined that the database needs to be VAACUMed and ANALYZEd. VACUUM is a postgres routine maintenance command which essentially defragments your database. It needs to be run every 1 billion transactions. You are running at 5 transactions/sec right now, and have processed about 468,000,000 transactions since the last VACUUM or initdb, so my guess is that VACUUM has never been run on this data. While a backup of the database consumes 29mb, the size of the data directory is over 700mb. This lends some support to my vacuum hypothesis. VACUUM is also necessary for ANALYZE to run. ANALYZE updates the query planner for the actual data layout, so if one does not run ANALYZE, one can expect query performance to drop over time. From my research, I expect a performance increase when this routine maintenence has been accomplished.
The database has not been tuned to the size of the system. Almost all settings are at the default, which are not optimal for a 4GB system. With some further research, I can change the tuning parameters to make the database faster. This may not be necessary if #1 makes performance good enough. If necessary, it should be possible to tune the database so the entire data set fits in memory.
Postgres is currently at version 8.0.3. This version was released 2005-05-09. I recommend that this be upgraded, at a minimum to 8.0.26, the last bugfix release in the 8.0 stream. The upgrade may require reindexing of some tables and may be conducted in place, however it may be less troublesome to dump the database, initdb, and restore.
|Syntax how to select past 7 days from PostgreSQL table||4||66|
|Automate postgres backups on windows and move it to linux box||15||110|
|Java array passed to SQL where clause||23||93|
|Postgresql create field from "as " then reuse it but it say does not exist||2||57|