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...
- Jignesh published some interesting observations by using FX class scheduler on Solaris
So, I was curious to try both of them :-) with 8.4 beta1
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? ;-)
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
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
Any comments are welcome! :-)
blog comments powered by DisqusNote: 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..