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.

Our software product currently ships on Windows 7 with Postgres 8.3 as its database. On a busy site, there may be 24 automated systems generating 100 rows (x100 columns) per minute, with 3-10 human clients viewing subsets of around 1000 rows – all retrieved at once, with incremental updates querying pk + timestamp every minute or so and retrieving new rows that are pertinent. There are a few auxiliary tables, but this table has the primary activity.

As a first step to a limited multi-master system (to help with geographically separated teams), we implemented an upgrade to 9.3. Performance wasn't the first priority, so it wasn't really profiled. As release time has come, management has decided to drop 9.3 for now citing fear of possible performance degradation and lack of testing resources. I was sure the performance issue was preposterous so I did some PgBench testing.

Using 9.3's pgbench, I alternated between connecting to the local 8.3 and 9.3 installations (diff't port numbers). I've captured my results in this google drive spreadsheet, but the summary is that generally 8.3 beat 9.3. 9.3 only won in raw insert performance.

We have some customization of our postgresql.conf files which I generally kept from 8.3 to 9.3, I'll list the non-default settings

max_connections = 1000
shared_buffers = 320MB
temp_buffers = 80MB
max_prepared_transactions = 50    #8.3 only, 9.3 left at 0 (not sure why)
max_fsm_pages = 204800            #8.3 only, 9.3 doesn't have setting
autovacuum_max_workers = 30

So, is this just the price of progress, or is there something I should be doing in 9.3 to make it excel?

share|improve this question
1  
I know Windows is not the best platform for a database, but telling customers to setup *nix boxes is not a viable solution at this point. –  Thomas Jan 27 at 17:44
3  
You should post this to the Postgres performance mailing list. The general expectation is, that 9.3 should be faster than 8.3. Do both systems have the same cost (xxx_cost) and statistics (default_statistics_target) settings? What about effective_cache_size? –  a_horse_with_no_name Jan 27 at 18:57
    
Same (default) *_costs, different (default) statistics - 10 vs 100, same (default) effective_cache_size. I may try the mailing list if I don't get much traffic here. –  Thomas Jan 27 at 19:04
    
Just to clarify, was this performed on identical hardware and storage? –  Phil Jan 27 at 19:59
2  
10 minutes is nowhere near long enough, you need to average out over several CHECKPOINTs. Your greater stats targets impose a cost where they're not needed. The defaults were raised because people had issues with index selection, but nothing stops you lowering them either per-table or globally. max_connections = 1000 is awful; read wiki.postgresql.org/wiki/Number_Of_Database_Connections . –  Craig Ringer Jan 28 at 8:16

1 Answer 1

up vote 7 down vote accepted

Usually PostgreSQL 9.3 is generally faster then 8.3 - but hard to say what is wrong. Possible sources: problems with IO, wrong PostgreSQL configuration - max_connections = 1000 is probably terribly wrong value, default work_mem is usually too small, hitting hw limits (9.1 and higher should to better use more CPU), wrong testing ...

Other problems can be changes in pgBench. Result of pgBench 8.3 should not be exactly compared with results pgBench 9.3. If you would to have a good numbers, then you have to use pgBench 9.3 for PosgreSQL 9.3 and PostgreSQL 8.3. Don't forget - test should run minimally about 20 munutes (better hours).

Second factor - probably pgBench is not important for your production. pgBench is synthetic tests with disputable value. It is good for initial hw and sw testing, but is bad for configuration precision and database tuning. Much more important is speed of your application - you should to test a speed of slow queries or most often queries. 9.3 has lot of new functionality - better monitoring, more comfortable tools, better optimization for some kind of queries, built-in replication, online comfortable physical backup, much smarted vacuum (faster on large datasets), ...

Increasing of default_statistics_target had overhead in query planning - for extremely fast and simple queries it can be significant. On second hand, higher value decrease a probability of wrong estimation and wrong optimization - that is important for usual non trivial queries - planning is little bit slower (it can be visible in synthetic tests) but usage in production is much more robust. Depends on your application (if it use simple or not simple queries) this configuration variable can be important for you or not.

share|improve this answer
    
I'm using pgBench 9.3 against both 8.3 and 9.3 databases. Test is running for about 10 minutes, so it could be longer. I agree real-world testing is better, pgBench is just easier. I was hoping 9.3 would be clearly faster so I could argue better for shipping it sooner. –  Thomas Jan 27 at 22:15
    
I believe so your numbers strongly depends on used parameters for pgbench. Maybe you hit a physical limits of your hw, and then new software doesn't help. There is lot of factors for new releases - and performance should not be most important: really better monitoring, stronger SQL, and not last - 8.3 is unsupported now. On second hand - maybe these features are not important for you - for some legacy applications - and then you can stay on 8.3. I know so some big companies in car industry did this decision. New releases is important for new application that can use new features. –  Pavel Stehule Jan 27 at 22:21
    
+1 just arguing that 8.3 is now unsupported, and thus will receive no updates going forward. –  xenoterracide Jan 28 at 0:00
    
Pavel, can you elaborate on the following: "Depends on your application (if it use simple or not simple queries)". Did you mean that the simplest queries do not benefit from longer/better planning, but still pay the additional tax for it? –  A-K Jan 28 at 1:17
3  
@AlexKuznetsov - It is effect of higher default_statistics_target. When you increase this value, then you increase time of all manipulation with statistics vectors. For simple queries (where planning and execution is very short) this factor can be detectable (in benchmark like pgbench). In production (less probable) - but it really depends on used queries - but lot of INSERTs without prepared statements can be impacted - but if you use COPY, then all is ok and you never see negative impacts. It is similar like INDEXES - more indexes => maybe faster SELECTs, but surely slower INSERT, UPDATE –  Pavel Stehule Jan 28 at 4:08

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.