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.
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
|Querying user location using Google Play Services, Google Maps, and SQL||6||471|
|Postgres function that loops over array using each value to query database and add record to result||4||3,436|
|Access record sets in a Postgresql DB from Django - nothing appearing||5||219|
|Copy error Amazon Redshift loading from S3||11||244|
Join the community of 500,000 technology professionals and ask your questions.