MySQL Performance Schema (4)
This is #4 in a series of blog postings about MySQL Performance Schema.
What does the new PERFORMANCE SCHEMA cost?
In my last three postings, I’ve shown shown lots of monitoring / diagnostic new features, and made it obvious there’s a lot more. At this point any wise viewer will wonder whether such a marvel can really be free. Well, it is, in the two senses of the word: “free like GPL” and “free like borshch”.
The “free like GPL” sense is easy to explain. Anybody can download the source as soon as we put it in the public trees, i.e. “soon”. Naturally Enterprise people can expect far more besides the server code (more about that later), but everybody gets the server code.
The “free like borshch” phrase evokes an East European beet soup (see http://en.wikipedia.org/wiki/Borscht) which is cheap enough that a cook can just throw it in with the rest of the dinner for no visible extra charge. The overhead of Performance Schema is low according to some early tests.
Disclaimer #1: these are not in-the-field tests, nothing is in the field.
Disclaimer #2: the feature is still being expanded and fixed constantly.
Disclaimer #3: I’m biased and my current role is as a tout.
Nevertheless I think people will wonder so I spew what I’ve swallowed so far.
It seems it should be way way less than 1 microsecond per instrumentation. The instrumenting code itself is currently taking way less than 1000 cycles, which was our initial goal — if it had taken more, we would have abandoned most of the summaries. This measurement is outside the server in loops, which means it evades caching or contention effects. So I call it “theoretical”. I can’t provide something more accurate, because the measurer doesn’t measure itself. As the old Latin phrase goes: “Quis custodiet ipsos custodes?” Who will watch the watchers? Nobody.
There are no mutexes.
Well, I should say for full disclosure that at time of writing there’s a single mutex having to do with one of the file-io summary counters, but we regard that as a bug, it will disappear. This means that wrong results will appear often enough that everyone will notice, and we’ll say “we regret that but that’s a cost of doing business in a low-overhead world”.
The default timer is a cycle counter.
It’s possible for a user to override the default and ask for timings in (say) microseconds instead of cycles, thus avoiding all the obscure gotchas of using a cycle counter (which I’ll talk about in excruciating detail in a later blog post). But users who do that will notice severe performance degradation over the long term.
When they complain, we’ll say “”we regret that but that’s a cost of doing business in a low-overhead world”.
There is no disk io.
All output is to memory. We have considered using a trace or log file (see the “Trace” section in the WL#2360 description), and I recall well that one of the early objections to WL#2360 was that it gave low priority to tracing. We’ll see whether that becomes an issue in the future.
My own comparison of a non-perf with a perf.
On a 1,000,000-row MyISAM table, to create an index, I need 5.62 seconds using the main-tree code which doesn’t support performance schema yet, I need 5.19 seconds using the performance-schema-tree code with every instrument turned on. Whoops. Something’s wrong with the test, eh? Oh well, it doesn’t show things getting worse.
Kelly Long and DBT2.
Kelly’s another person who deserves an honourable mention for services to the Performance Schema cause. He’s done a lot of testing and performance-improvement work for Sun/MySQL out of his Colorado labs. One item that he did back in December 2008 looked like this:
DBT2 test by Kelly Long December 1 2008
8 16 32 64 128 192
----- ----- ----- ----- ----- -----
IN 15096 18049 21259 20494 19886 19293
OUT 15680 21277 22183 21025 20474 19174
Overhead 3.72% 15.17% 4.17% 2.53% 2.87% -0.62%
From sourceforge.net: “This Database Test Suite [DBT2] aims to create database workload test kits used to simulate heavy user loads for OLTP, Decision Support, and e-commerce database transactions.” Kelly did the test with AMD 8-way Dual Core. Looking at the column labelled ‘8′: With 8 simultaneous connections, with performance schema compiled in and all instruments on, MySQL was doing 15096 transactions per second. With performance schema not compiled in, MySQL was doing 15680 transactions per second. Calculating 100*(1-(15096/15680)) gives 3.72 which is the percentage overhead. It goes up and down as one increases the number of connections. Bizarrely, one of the measurements shows MySQL getting faster with Performance Schema in. Although effects like that will disappear, this looks okay so far. Some community contacts said they’d be happy with 20% if they could get complete statistics.
So we think we’ll be able to recommend to many/most users: turn it on most of the time. By default all performance schema instrumentation will be DISABLED, because it’s dangerous to make it a default when it’s new, we expect to encounter embarrassments. However, we don’t think performance considerations alone would justify turning Performance Schema off.
February 11th, 2009 at 4:28 am
What does it mean that there is no mutex? lockless data structures or just some other type of mutual exclusion solution?
February 11th, 2009 at 7:02 pm
There are no “mutual exclusion” solutions that would work well with the amount of data that might pour through, so we wanted to avoid adding new synchronization mechanisms of any kind. Our solution is to avoid guarantees of consistency.