« MySQL Performance: Some results comparing InnoDB log size impact @dbSTRESS benchmark | Main | MySQL Performance: MySQL 5.4 and other InnoDB engines on 32cores AMD server & dbSTRESS »

Thursday, 04 June, 2009

PostgreSQL Performance: VARCHAR and FX Class scheduler impact @dbSTRESS benchmark

Since my last post about PostgreSQL performance on dbSTRESS benchmark  I've received several interesting comments about what may be improved with PostgreSQL. Currently I was interested on the following two:

  • use VARCHAR instead of CHAR -- within a tested database schema I've used CHAR columns intentionally to simplify space management as all manipulated data during the test have a fixed length. But seems in PostgreSQL management of CHAR values have an additional overhead comparing to VARCHAR as it'll always try to add trailing spaces at the end of the value...

So, I was curious to try both of them :-) with 8.4 beta1


Using VARCHAR

Main surprise for me here was that once all data were loaded as VARCHAR, then all PREPARED statement started to work more than 100x times slower... - PREPARE itself is worked as usual, but EXECUTE took too much time - I'm supposing there is a bug and not presenting here any results with prepared statements. However, all tests with not-prepared statements worked well.


Solaris Scheduler: FX Class

Finishing previous tests I've got a feeling there are some scheduler issues may enter in game when injector and database are running on the same server - in my case I've always separated them by running within an isolated processors sets. But there was no way to create 2 proper processor sets on 32cores server to test 32cores configuration :-) so at the end I've tested 24 cores, and planned to continue investigation on a bigger server once I'll have it. However my mind continued to turn a question: What if it's just because I did not use FX class scheduler?.. How to miss an occasion to try when it's coming? ;-)


Results

Initial graph labeling was explained here .

Some comments on additional labeling currently used:

  • all tests are having the same base configuration (4096M buffer pool, etc. etc. as before)
  • prep --> prepared statements were used
  • buf4096 --> statements were not prepared
  • FX --> PG was switched to FX Class during execution
  • vchar --> VARCHAR datatype was used instead of CHAR

Read-Only Workload


Read-Write Workload


Summary

  • it's quite curious to see in some cases a negative impact on Read+Write workload by switching to FX Class scheduling - it make me think there are probably some of postgres processes should have a higher priority then others to keep things working well.

  • on the same time Read+Only workload feels better under FX scheduling :-)

  • And it's true, when it worked VARCHAR performs better than CHAR even if only fixed length values are used! Will keep in mind for the next time :-)

Any comments are welcome! :-)

Posted by Dimitri at 21:59
Categories: PostgreSQL, Solaris, Tools/ dbSTRESS
blog comments powered by Disqus
Note: if you don't see any "comment" dialog above, try to access this page with another web browser, or google for known issues on your browser and DISQUS..