Friday, 02 October, 2009
Last week I've participated in several Performance Conferences and Trainings in Kiev. Events mainly were dedicated to the latest improvements within MySQL performance, as well Solaris and Oracle tuning / performance analyzing and best practices. The week was very hot :-) but I've enjoyed every day discussing with creative and smart people! People hungry on knowledge and ready to stay 2 hours more overnight outpassing all allowed timing - it was a real pleasure to share all I know! :-))
So, I'd like to thank all participants! As well Sun@Kiev and i-Klass teams who organized and prepared all these events! I may only wish to see such events more and more often, and don't forget to invite me again! :-))
NOTE : similar conference about MySQL performance improvement will be organized soon in Paris - so if you're interesting in (and speaking French :-)) - please, contact me ahead, we'll need to know the size of the auditorium to be able to have seats for everybody :-))
After all that said, here are few images from the "road notes" and my slides or other materials used by me during presentations (as promised)...
Kiev Polytechnic Institute
25 years ago I came here expecting to be accepted as a new student.. And
spent 6 years of my life to study maths, computers, programming, as well
many other topics (even if some of them were here only to develop your
brain and did not have any real usage :-)) I was invited to discuss
about of databases scalability and the latest improvement made in MySQL
performance. The conference was prepared to celebrate the Software
Freedom Day in KPI ...
After so many years, entering the Main campus building was so impressive.. The Main Auditorium in this building should still remember so many famous names ... Did I expect one day to find myself on the podium and presenting my work to others?.. :-)) Initial stress was very high.. but discussing and presenting to students was so fun - that in few minutes I've got a feeling I'm speaking with old friends, and everything goes just fine :-))
MySQL Performance conference @i-Klass
i-Klass is very well placed in Kiev near and face to the one of the most famous and beautiful Church in Kiev - Kiev Pechersk Lavra - simply must to visit if you're traveling in Kiev :-)) BTW, there is also a famous museum of micro miniatures (the world's smallest chess board you may see on following photo). Also, did I say you the food is absolutely excellent in Kiev?.. ;-)
i-Klass is a very good partner of Sun, and for such event they prepared their bigger conference hall accepting near 350 persons. The event itself was very successful as nobody left till the end :-)) as well a big part of participants preferred to stay one or two hours more again but do not miss the last uncovered topics :-) As well I was surprised to discover Percona presence even in Kiev! :-)) And I'm still waiting for photos from this event :-))
Solaris Performance Monitoring & Analyze @i-Klass
Two next days covered Solaris performance and Oracle-oriented system tuning. We expected 20 persons for each event as practice lab supposed to have a Solaris desktop on the desk and there was not too much place in the room. But finally we got 45 persons every day and most of people came with their own laptop running Solaris! :-))
I've enjoyed all the time during these trainings and was happy to see so many strong engineers loving Sun technology and proud of their choices! :-) Some partners and customers I've already met during their benchmarks in Paris :-) All other are always welcome to test and improve performance of their applications in our Sun Solution Centers !!!
Also, one of the fun events was a direct connection with our Paris office and presenting live (with my colleague Matthieu) the demo of Automated Dynamic Reconfiguration with LDOMs! It was absolutely fantastic seating in two different locations and work all together as a one single team! Seems we made a new step in our activity :-))
Note : Last day due Internet connection problems we did not finish our stress testing with Oracle in Solaris zones - all who still want to participate in the remote testing please contact me before next Tuesday, because on Tuesday we'll replay live all programmed tests! :-))
- Solaris Performance slides
- Pools & Zones Overview slides
- LDOMs Training slides by Maria AVILA (Sun Solution Center)
Dynamic Reconfiguration (ADR) via dim_STAT slides
Thanks again to all participants and see you next time! :-))
Thursday, 04 June, 2009
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! :-)
Monday, 18 May, 2009
Last month when I've published my results obtained with PostgreSQL 8.3.7 on db_STRESS benchmark I was surprised they were much lower comparing to MySQL, as well some signs alarmed me there is something goes wrong with PostgreSQL... So, once it was possible, I took my time and prepared another testing on the same M5000 server I published MySQL results last week.
I would say I discovered a lot of new things benchmarking PostgreSQL ! I'm not kidding :-) And without going too much in detail, the main gain seems to me of MySQL over PostgreSQL was a lower cost on executing a single query. The query in question was the second SELECT in db_STRESS.
SELECT-2 execution time:
- MySQL 5.4: 0.44ms
- PostgreSQL 8.4: 1.3ms
- PostgreSQL 8.4 prepared statement: 0.98ms
Again, if PostgreSQL being slower on a single query scaled much more far on say 24 cores - it'll be not really a problem. But the result on 24 cores is only slightly better than on 16, and still lower comparing to MySQL... Of course, it's only true since MySQL 5.4 :-)
However, PostgreSQL made a big progress and performs very well on the mixed workload (Read+Write). Since 8.3 version it has a kind of "delayed" checkpoint - instead to flush all modified data at one (like it was in 8.2 and earlier) it has an option to say "I give you N sec to flush all data, don't need to stress, but finish work on time!" - and it works very well! :-)
Here are some PostgreSQL results on db_STRESS (as usual, X-axis representing a number of concurrent users (sessions), Y-axis representing a TPS level) - oh, and "prep" in name means prepared statements, buf4096 - means buffer pool set to 4096MB, etc. (all test descriptions are explained in the final report):
As usually, all other details you may find in the full report: http://dimitrik.free.fr/db_STRESS_PostgreSQL_837_and_84_May2009.html
Any comments are welcome! :-)
Wednesday, 22 April, 2009
In case you also want to replay the same dbSTRESS test scenarios I've posted results yesterday, I've added into the test kit a shell script which may be used as a Reference Scenario.
Tuesday, 21 April, 2009
Forget to say, I've also tested PostgreSQL 8.3.7 during the last benchmark serie with dbSTRESS!
A big surprise - if two years ago on the same workload PostgreSQL was two times faster (see: http://dimitrik.free.fr/db_STRESS_BMK_Part2_ZFS.html ), now it's MySQL 5.4 outperforming PostgreSQL!
Read-Only workload: MySQL is near two times faster now! (13.500
TPS vs ~7.000 TPS for PostgreSQL)
- Read+Write workload: MySQL performs as well or better (7.000-8.000 TPS vs 6.000-7.000 TPS for PostgreSQL)