Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a backup routine that replicates the production database onto our staging server. It works without issues, but the staging database becomes unbearably slow after I restore from one of the snapshots. Slow to the point that my web server just times out and 502's.

I checked through to see what could be causing this, and I've isolated it to two large tables. We have a certain sessions table with several large TEXT fields in it. We're storing some unstructured data and are currently holding it in TEXT fields. Once I trim that table down (by deleting many of the rows), the staging environment accelerates and begins acting normally again.

I've gone through the routine of pg_reindexing the database, and have tried VACUUMing, but it's not making much of a difference. It's still really slow. Pages that don't need to access the two large tables run normally; pages that do don't load.

In short, I'm looking for help to answer two questions.

  1. Is there a better way of doing database duplication from production to staging if my database dump is > 130 MB that will not cause this issue? I was under the impression that 130MB is not a very large database. I suspect it's all in the large session table with the TEXT fields that I spoke about earlier.

  2. Is there any way I can optimize this table? Are there any tweaks that I can make after pg_restoring? I'm afraid that I'm going to have to restore from one of these backups on a rainy day and that it's going to affect the whole site's performance.

Thanks in advance.

share|improve this question

migrated from stackoverflow.com Feb 1 '11 at 11:07

This question came from our site for professional and enthusiast programmers.

1  
Can you show us the results from EXPLAIN ANALYZE for a slow query on these tables? And what about configuration settings? work_mem, shared_buffers, maintenance_work_mem and some others, need some tweaking. 130MB is a small database, the whole database should be in RAM. –  Frank Heikens Feb 1 '11 at 7:24
add comment

1 Answer

When you're vacuuming, are you ANALYZE-ing?

Reindex shouldn't help: when you pg_restore all those indexes are being created from scratch anyway.

VACUUM by itself won't do anything: a newly restored database doesn't contain any dead tuples to get rid of.

Run ANALYZE across the entire database after a restore, this updates the statistics, allowing the query optimiser to generate efficient query plans.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.