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.

Trying to figure out how I should expect my database to perform. Basically, I need to determine when the server I have set up is reaching its performance limits - which would help me plan server scaling better.

This question aims more towards ways that I can calculate or estimate (really, any idea of this would be good) expected performance. This should ideally help me come up with a formula I can run based on several factors (like record size, number of rows, etc), instead of a subjective assumption based on a particular server / DB.

So, what is a good way to determine expected performance on a PostgreSQL server?

Thanks very much!

share|improve this question
    
I expect this question is going to be harder to answer, really you're more worried about the actual performance of the server, assuming that the database is properly indexed. –  jcolebrand Mar 19 '13 at 2:49
    
Yes, in a way. I'm more interested in the overall performance / throughput of the server as a whole, when executing its function of serving data using PostgreSQL. For example, the checkpoints for WAL might be too frequent - or something like that, which can affect the performance of the DB server in its function as a data repository. –  Juan Carlos Coto Mar 20 '13 at 16:45

1 Answer 1

up vote 2 down vote accepted

I think determining the expected performance is a lot harder than planning for capacity. My guess is that is what you are really trying to do so I will go over what I consider to be the most important rules for OLTP workloads here.

  1. You want the working data set to fit in memory if possible. Calculating performance where this is not the case depends on how often the disks are hit and what sort of disk I/O. If you can't fit all of it in memory, at least you should try to fit the most commonly used data, and all commonly used indexes in memory.

  2. You want to have available at least one core per two concurrent queries. Better is one core per concurrent query.

Both of these are a little tricky to calculate and they depend on row size, number of pages in the table, number of dead tuples in the table, which in turn depends partly on the number of concurrent updates.

Beyond this, performance will slowly degrade, hopefully gracefully. Estimating performance when these are or aren't met is very difficult because it is very fact specific.

share|improve this answer
    
Thanks! That's a good answer. Do you have any idea what resources I could look into (manuals, websites, etc) that will help me estimate / study these elements? –  Juan Carlos Coto Mar 19 '13 at 17:34
1  
Juan-- Take a look at packtpub.com/postgresql-90-high-performance/book –  gsiems Mar 20 '13 at 22:23
    
@gsiems Will do, thanks! You could add this as an answer, too. –  Juan Carlos Coto Mar 31 '13 at 0:13

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.