Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am developing a somewhat big web application, which uses data which is aggregated using a crawler. The crawler is constantly writing to the database and updates many tables at the same time. For the frontend it is not important to have the very latest data available all the time, but the response time is critical for the user experience. The frontend accesses the same database (read-only), but is really slow as long as the crawler is active. I suspect that unfortunate locks on the database cause this, as the server still has many CPU cores idling and iostat also shows that the HDDs aren't fully utilized yet.

I know that I could probably resolve this issue by creating a master/slave setup using another database server, which is replicated periodically, but I wonder if I could achieve the same performance jump in another way:

Does it make sense to replicate/mirror a database (mirror all the tables needed for the application) within the same database-server-instance? If so, what would be the smartest way to do so?

(I am using postgreSQL 8.4.17 if that makes any difference in this case)

share|improve this question
1  
Don't guess, measure. Is pg_stat_activity showing lots of queries in waiting state? Are there lots of un-granted locks in pg_locks? Does perf tell you anything useful about what the system is doing? etc. Also: if performance is a concern, why stay on such an old version? –  Craig Ringer May 14 at 12:11
    
Okay, thanks for the pointers. It seems there aren't any locks preventing the queries from being fast, nor any waiting queries. But one thing I haven't tried is to use connection pooling, so maybe the problem lies there. In any case, thanks again! I'll report back. –  devsnd May 14 at 15:23
    
Hm, ok. How many concurrent connections are you using then? max_connections value? –  Craig Ringer May 15 at 1:23
    
no, actually the number of connections directly from postgres should suffice, but opening a new connection for every request is more expensive than having a pool. But I don't know if this will be noticable at all... –  devsnd May 15 at 11:31

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.