Toluu

MySQL Performance Blog

Everything about MySQL Performance

Tom Basil (formerly MySQL’s Director of Support) Joins PerconaYesterday

Percona has a new Chief Operating Officer! It is Tom Basil, who was Employee #11 at MySQL and led the Support organization there for almost eight years. He was the founding Director of Support at MySQL, and built it into a team of about 60 people all across the world. Tom was legendary as a manager at MySQL, and we’re really looking forward to his experience at Percona.

It sure is funny how things work out. When Peter and Vadim were at MySQL, Tom was their boss for 4 years. Now, Tom works for them! And Tom actively recruited me for a position at MySQL Support almost exactly a year ago, but I joined Percona instead. Perhaps we were meant to work together.

So, why did he resign from Sun/MySQL and come join us? In his own words, slightly paraphrased,

• Vision. Tom knows that we have replicated the vision of MySQL’s own founders Monty and David, which was what drew him to work at MySQL in the first place. We’re building our business organically, focusing on our customers’ practical needs. We’re doing it bootstrap-style, with no venture capital or hard revenue targets.
• Virtual. We have a distributed workforce of some of the best and brightest performance gurus in the world.
• Values. We’re fiercely loyal to our employees and their families. And we care about our customers.

I could probably find some more words starting with V, but that seems to sum up what Tom has told me. His main regret is leaving beh

MySQL 5.0, 5.1 and Innodb Plugin CPU EfficiencyYesterday

We’ve recently done benchmarks comparing different MySQL versions in terms of their CPU efficiently in TPC-C like Workload. We did it couple of weeks ago so MySQL 5.0.67, MySQL 5.1.29 and Innodb Plugin 1.0.1 were used which are not very recent, though we do not think results will differ a lot with today versions.

Results are as follows:

The system was 2* Quad Core Xeon E5310, CentOS 5, Data stored on ramfs. We controlled number of cores used with /sys/devices/system/cpu/cpuX/online Maximum performance for each number of cores was taken though it was reached with number of sessions matching number of cores. Just 1 “Data warehourse” was used to keep data small.

As you can see there is some gain for MySQL from read-write lock split patch (found in Percona Builds) though it is not very significant for this workload. To isolate effect of this patch we only use this patch not full patch set in testing.

MySQL 5.1 is 4% slower than MySQL 5.0 with two cores and just 2% slower with 8 cores, thus showing a bit better scalability.

MySQL 5.1 plugin (compiled in) is further 3% slower compared to MySQL 5.1 with 2 cores and about 6% slower with 8 cores, meaning regression from plugin increases with number of cores.

If you wou

Presentations from OpenSQLCamp PublishedDecember 2

We have now published presentations from OpenSQLCamp at Percona Presentation pages

Percona MySQL Patches is a great presentation to see Percona Patches for MySQL in action, showing how you can use them to get more understanding of your server load and improve server performance, as well as how they can improve performance all together.

Sphinx full-text search engine is presentation about Sphinx Search Engine talking a bit about architecture, performance and new features in Sphinx 0.9.9

---

Entry posted by peter | No comment

Add to:

Computing 95 percentile in MySQLNovember 29

When doing performance analyzes you often would want to see 95 percentile, 99 percentile and similar values. The "average" is the evil of performance optimization and often as helpful as "average patient temperature in the hospital".

Lets set you have 10000 page views or queries and have average response time of 1 second. What does it mean ? Really nothing - may be one page view was 10000 seconds and the rest was in low milliseconds or may be you had every single page view taking 1 second, which are completely different.

You also do not really care about average performance - the goal of good user experience is majority of users to have good experience and average is not a good fit here. Defining your response time goal in 95 or 99 percentile is much better. Say you say 99 percentile response time should be one second, this means only 1 percent of queries/page views are allowed to take more than that. For larger systems defining (increasing) response times for 99.9 or even 99.99 percentile numbers often make sense.

It also often makes sense to define response time goals separately for different transactions - the AJAX widget response time requirements may be very different from the slow search page.

So you have defined your response time in terms of 95/99 percentile and get your logs in the table, so how to get the data if MySQL only provides you the avg:

PLAIN TEXT

MySQL for Hosting Providers - how do they manage ?November 28

Working with number of hosting providers I always wonder how do they manage to keep things up given MySQL gives you so little ways to really restrict how much resources single user can consume. I have written over a year ago about 10+ ways to crash or overload MySQL and since that people have come to me and suggested more ways to do the same.

This is huge hole in MySQL design, thinking little about users isolations and resource quotas and interesting enough I have not seen significant changes in fresh our MySQL 5.1 GA or even something major on the roadmap for future MySQL versions. May be Drizzle will give it a thought ? This surely would help adoption by (especially low end) Hosting Providers and remember this exactly where a lot of kids start to develop their first sites and play with web technologies.

So how do the hosting providers manage to host hundreds of users on single server with single MySQL server ? Well people just seems to be nice and not looking to crash MySQL/DOS server on purpose but rather cause most of the issues unintentionally by running bad queries or installing bad software.

It is good people are nice but it may not be comforting to know you stay up just because nobody wants bring you down rather than because your systems are solid and designed to prevent such abuse.

The systems which I see people implementing are ty