tag:blogger.com,1999:blog-59155675787072866352024-03-13T09:10:59.310-07:00High Availability MySQLMark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.comBlogger263125tag:blogger.com,1999:blog-5915567578707286635.post-78289461618431320572014-02-22T10:05:00.000-08:002014-02-22T10:05:00.749-08:00codemysql.org?Many startups depend on MySQL. Check out the list of keynote speakers from past and present <a href="http://www.percona.com/live/mysql-conference-2014/home">MySQL User Conferences</a>. There are things to make better and new features to add like better support for dynamic schemas (documents) and auto-sharding. But existing deployments aren't in crisis mode and it is being used for new deployments. Maybe I have an agenda in writing this but anyone writing the opposite is likely to have their own agenda.<br /><br />There is a problem in MySQL land that doesn't get enough attention. When startups using MySQL get really big then they try to hire developers to work on MySQL internals (see Facebook, Google, Twitter, LinkedIn, etc). And I mean <b>try</b> because there is much more demand than supply in the US. There is a lot of talent but most is in other countries. While remote development has been successful in the MySQL community most of that is for traditional software development. Hacking on MySQL at a fast growing startup is far from traditional. You occasionally need to make MySQL better right now to fix serious problems in production. You won't get 6 month development cycles unless you have a large team. This requires a strong relationship with the operations team. This also requires personal experience with operations. That is harder to get while working remotely. Remote development can work. I am an example of that, but I am only 1 hour from the office by plane and I already had strong relationships with the operations team.<br /><br />A great solution is for someone already working at the startup to begin hacking on MySQL. This is great because it grows the supply of MySQL internals expertise and MySQL will get better at a faster rate. My teams at Facebook and Google grew in that manner. The problem is that it can be very hard to grow the team size from 0 to 1. The first person won't have a mentor. If the first person is also new to mature DBMS code then they might be in for a surprise. In my case 9 years between Informix and Oracle increased my tolerance level but not everyone has that background.<br /><br />I think we can make it easy for the first person on the team by providing training and mentorship. A bootcamp in April during/after/before the UC is one way to do that and there can be remote mentorship once per 2 weeks after that. The experts who can teach the class (like gurus from MariaDB) will be in town. Are people interested in this? I don't expect this to be free. If we expect professional training then we need to pay the professionals. But I hope that free training materials can eventually be produced from the effort.<br /><br />Beyond getting paid for professional services there would be other benefits were MariaDB to lead the program. This can increase their community of users and hackers.Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com16tag:blogger.com,1999:blog-5915567578707286635.post-26643672475614495302014-02-15T22:35:00.000-08:002014-02-27T09:45:08.026-08:00Everything is awesomeMy kids watched the new Lego movie today and spent the rest of the day repeating "Everything is amazing". I spent a few hours reading MongoDB documentation to help a friend who uses it. Everything wasn't awesome for all of us. I try to be a pessimist when reading database documentation. If you spend any time near production then you spend a lot of time debugging things that fail. Being less than optimistic is a good way to predict failure.<br />
<div>
<br /></div>
<div>
One source of pessimism is database limits. MongoDB <a href="http://docs.mongodb.org/manual/reference/limits">has a great page</a> to describe limits. It limits index keys to less than 1025 bytes. But this is a great example that shows the value of pessimism. The documentation states that values (MongoDB documents) are not added to the index when the index key is too large. An optimist might assume that an insert or update statement fails when the index key is too large, but that is not the specified behavior.<br />
<br />
As far as I can tell, <a href="https://jira.mongodb.org/browse/SERVER-5290">prior to MongoDB 2.5.5 the behavior</a> was to not add the document to the index when the indexed column exceeded 1024 bytes. The insert or update would succeed but the index maintenance would fail. Queries that used the index after this can return incorrect results.<br />
<br />
A quick search of the interwebs shows that<a href="http://stackoverflow.com/questions/6367759/mongodb-key-too-large-to-index"> people were aware</a> of the problem in 2011. I can reproduce the problem on my Ubuntu 12.10 VM. Why do we tolerate problems like this? Maybe this isn't a big deal and the real problem is that new risks (from a system I don't know much about) are worse than risks in software that I have been using for years. But corruption (either permanent or via incorrect query results) has been a stop the world bug for me -- as in you do nothing else until the problem has been fixed. Why have MongoDB users tolerated this problem for years?<br />
<br />
While it is great that the bug appears to have been fixed, database vendors should understand that FUD takes much more time to go away. See all of the stories about transactions and MySQL that lived on long after InnoDB became viable. And note that some of the MySQL FUD was self-inflicted -- see the <a href="https://dev.mysql.com/doc/refman/5.6/en/ansi-diff-transactions.html">section of the MySQL manual on Atomic Operations</a>.<br />
<br />
Found this code in 2.4.9 to explain how key-too-large is handled. A careful reader might figure out that index maintenance isn't done. Nice optimization. I spoke to one user who doesn't like the old behavior but doesn't want to break apps with the new behavior that fails inserts/updates with too large keys. Indexes on a prefix of a key would help in that case.<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">template< class V ></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">void BtreeBucket<V>::twoStepInsert(DiskLoc thisLoc,<br /> IndexInsertionContinuationImpl<V> &c,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> bool dupsAllowed) const</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> {</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> if ( c.key.dataSize() > this->KeyMax ) {</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> problem() << "ERROR: key too large len:" << c.key.dataSize() </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> << " max:" << this->KeyMax << ' '</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> << c.key.dataSize() << ' '</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> << c.idx.indexNamespace() << endl;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> return; // op=Nothing</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> }</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> insertStepOne(thisLoc, c, dupsAllowed);</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> }</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">/** todo: meaning of return code unclear clean up */</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">template< class V ></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">int BtreeBucket<V>::bt_insert(const DiskLoc thisLoc, const DiskLoc recordLoc,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> const BSONObj& _key, const Ordering &order,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> bool dupsAllowed,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> IndexDetails& idx, bool toplevel) const</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> {</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> guessIncreasing = _key.firstElementType() == jstOID && idx.isIdIndex();</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> KeyOwned key(_key);</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> dassert(toplevel);</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> if ( toplevel ) {</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> if ( key.dataSize() > this->KeyMax ) {</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> problem() << "Btree::insert: key too large to index, skipping "</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> << idx.indexNamespace() << ' ' << key.dataSize()</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> << ' ' << key.toString() << endl;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> return 3;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> }</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> }</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"></span><br />
<br /></div>
Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com10tag:blogger.com,1999:blog-5915567578707286635.post-33239659182498397592014-02-09T19:52:00.002-08:002014-02-09T19:52:26.992-08:00Legacy isn't a dirty wordPardon the rant but the winter storm has kept me in a hotel away from home for a few days. I exchanged email this week with someone pitching a solution to a problem I don't have (MySQL failover). But by "I" I really mean the awesome operations team with whom I share an office. The pitch got off to a bad start. It is probably better to compliment the supposed expertise of the person to whom you are pitching than to suggest they are no different than COBOL hackers working on Y2K problems.<br />
<br />
Unfortunately <b>legacy</b> is a bad word in my world. Going off topic, so is <b>web scale</b>. I hope we can change this. The suggestion that MySQL was a legacy technology was conveyed to me via email, x86, Linux and a laptop. Most of those have been around long enough to be considered legacy technology. DNA and the wheel are also legacy technology. Age isn't the issue. Relevance is determined by utility and efficiency.<br />
<br />Remember that utility is measured from a distance. It is easy to show that one algorithm can do one narrow operation much faster than as implemented in existing products. But an algorithm shouldn't be confused with a solution. A solution requires user eduction, documentation, skilled operations, trust, client libraries, backup, monitoring and more.Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com0tag:blogger.com,1999:blog-5915567578707286635.post-65958896338336997242014-01-28T09:02:00.002-08:002014-01-28T09:02:38.638-08:00Modern databasesWhat is a modern database? We have some terms that wander between marketing and technical descriptions - NewSQL, NoSQL. We have much needed work on write-optimized database algorithms - <a href="http://tokutek.com/">Tokutek</a>, LevelDB, <a href="http://rocksdb.org/">RocksDB</a>, HBase, Cassandra. We also get reports of amazing performance. I think there is too much focus on peak performance and not enough on predictable performance and manageability.<br />
<br />
Building a DBMS for production workloads is hard. Writing from scratch is an opportunity to do a lot better than the products that you hope to replace. It is also an opportunity to repeat many mistakes. You can avoid some of the mistakes by getting advice from someone who has a lot of experience supporting production workloads. I worked at Oracle for 8 years, wrote some good code (new sort!) and fixed a lot of bugs but never got anywhere near production.<br /><br />Common mistakes include insufficient monitoring and poor manageability. Monitoring should be simple. I want to know where something is running and not running (waiting on IO, locks). I also want to drill down by user and table -- user & table aren't just there for access control. I am SQL-centric in what follows. While there are frequent complaints about optimizers making bad choices I can only imagine how much fun it will be to debug load problems when the query plan is hidden away in some external application.<br />
<br />
The best time to think about monitoring is after spending too much time debugging a problem. At that point you have a better idea about the data that would have made things easier. One example of missing monitoring was the lack of disk IO latency metrics in MySQL. In one case not having them made it much easier to not notice that the oversubscribed NFS server was making queries slow via 50 millisecond disk reads.<br />
<br />
Monitoring should be cheap so that it can always be enabled and from this I can understand the average costs and spot changes in load from the weekly push. But I also need to debug some problems manually so I need to monitor both sessions that I know are too slow (get the query plan for a running SQL statement) and to find sessions/statements that are too slow (dump things into the slow query when certain conditions are met). Letting me do EXPLAIN for statements in my session is useful, but I really need to do EXPLAIN from statements in production - if the optimizer uses sampling I want to see the plan they get and if temp tables are involved I have no idea what will be in their temp tables. MariaDB (and MySQL) recently added support to show the query plan for statements that are currently running. This is even more useful when the query plan and performance metrics can be dumped into the slow query log when needed.<br /><br />The goal for monitoring performance problems is to eliminate the use of <a href="http://poormansprofiler.org/">PMP</a>. When I want to understand why a system is running slower than expected I frequently look at thread stacks from a running server. I hope one day that pstack + awk is not the best tool for the job on a modern database. I was debugging a problem with RocksDB while writing this. The symptom was slow performance for a read-only workload with a database that doesn't fit in cache. I have seen the problem previously and was quickly able to figure out the cause -- the block cache used too many shards. Many problems are easy to debug when you have previously experienced them. This can be restated as many problems are expensive to debug for most users because they don't have full time database performance experts.<br />
<br />
The focus on peak performance can be at odds with manageability. The faster way to peak performance is via tuning options and too often these options are static. Restarting a database in production to change an option is a bad idea. Dynamic options are an improvement. Using adaptive algorithms in place of many options is even better. And if you add options, make sure the defaults are reasonable.<br /><br />Predictable performance is part of manageability. How does your modern database behave when confronted with too much load? It helps when you can classify your workload as high-priority and best-effort and then shed load from the best-effort users. Alas this requires some way to distinguish users. In theory you want the hi-pri users to get their work done and then let best-effort users compete for the spare capacity. This requires some notion of SLA for the hi-pri users and spare capacity for the DBMS. These are hard problems and I have not used a great solution for load shedding.<br /><br />This could be a much larger rant/document but I must return to my performance debugging.<br />
Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com6tag:blogger.com,1999:blog-5915567578707286635.post-74594433664805820432014-01-15T10:31:00.001-08:002014-01-15T10:31:47.081-08:00MySQL at FB at the UC<span style="background-color: white; color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13px; line-height: 17px;">The UC schedule has been published and there are several talks from the database teams at Facebook.</span><br />
<ul>
<li><span style="background-color: white; color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13px; line-height: 17px;"><a href="https://www.percona.com/live/mysql-conference-2014/sessions/small-data-and-mysql">Small Data and MySQL</a> by Domas Mituzas- small data is another name for OLTP. Given the popularity of big data we think that small data also deserves attention.</span></li>
<li><span style="background-color: white; color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13px; line-height: 17px;"><a href="https://www.percona.com/live/mysql-conference-2014/sessions/asynchronous-mysql-how-facebook-queries-databases">Asynchronous MySQL</a> by Chip Turner - this describes the work done by Chip's team to implement an async MySQL client API. The feature is in the FB patch, widely used at FB and is integrated with HHVM.</span></li>
<li><span style="background-color: white; color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13px; line-height: 17px;"><a href="https://www.percona.com/live/mysql-conference-2014/sessions/performance-monitoring-scale">Performance Monitoring at Scale</a> by Yoshinori - this will explain how to be effective when monitoring many servers with few people. It is easy to get distracted by false alarms.</span></li>
<li><span style="background-color: white; color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13px; line-height: 17px;"><a href="https://www.percona.com/live/mysql-conference-2014/sessions/mysql-56-facebook-2014-edition">MySQL 5.6 at Facebook</a> by Yoshinori - Yoshi will share many stories about what it took to get 5.6 into production. This included a bit of debugging and performance testing, bug fixes from upstream and a lot of work from the MySQL teams at FB.</span></li>
<li><span style="background-color: white; color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13px; line-height: 17px;"><a href="https://www.percona.com/live/mysql-conference-2014/sessions/global-transaction-id-facebook">Global Transaction ID</a> by Evan, Yoshinori, Santosh - at last global transaction IDs have arrived (for people not using the Google patch). Learn what it took to get this ready for production. </span></li>
<li><span style="background-color: white; color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13px; line-height: 17px;"><a href="https://www.percona.com/live/mysql-conference-2014/sessions/innodb-defragmentation">InnoDB Defragmentation</a> by Rongrong - learn about the work by Rongrong to reduce the amount of space wasted from fragmentation.</span></li>
<li><span style="background-color: white; color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13px; line-height: 17px;"><a href="https://www.percona.com/live/mysql-conference-2014/sessions/under-hood-mysql-pool-scanner-mps">MySQL Pool Scanner</a> by Shlomo</span><span style="background-color: white; color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13px; line-height: 17px;"> - MPS is one of the key tools created by our automation experts (aka operations gurus) that make it possible to manage many machines with few people.</span></li>
</ul>
Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com0tag:blogger.com,1999:blog-5915567578707286635.post-33600049225748045402013-12-19T08:46:00.000-08:002013-12-19T08:46:13.536-08:00Big downtime versus small downtimes<b>Big downtime</b> gets a lot of attention in the MySQL world. There will be some downtime when you replace a failed master. With GTID in MariaDB and MySQL that time will soon be much smaller. There might be lost transactions if you use asynchronous replication. You can also lose transactions with synchronous replication depending on how you define <b>lose</b>. I don't think this gets sufficient appreciation in the database community. If the higher commit latency from sync replication prevents your data service from keeping up with demand then update requests will timeout and requested changes will not be done. This is one form of <b>small downtime</b>. Whether or not you consider this to be a lost transaction it is definitely an example of lousy quality of service.<br />
<br />
My future project, MarkDB, might have a mode where it never loses a transaction. This is really easy to implement. Just return an error on calls to COMMIT.<br />
<br />Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com2tag:blogger.com,1999:blog-5915567578707286635.post-18625258344881217422013-11-19T10:52:00.004-08:002013-11-19T10:53:05.776-08:00Where are the tests?I looked at the <a href="http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-14.html">release notes for 5.6.14</a> and then my bzr tree that has been upgraded to 5.6.14. I was able to find changes in bzr based on bug numbers. However for the 5 changes I checked I did not see any regression tests. For the record, I checked the diffs in bzr for these bugs: 1731508, 1476798, 1731673, 1731284, 1730289.<br />
<br />
I think this is where the MySQL Community team can step up and help the community understand this. Has something changed? Or did the tests <a href="https://mariadb.org/">move over here</a>?Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com2tag:blogger.com,1999:blog-5915567578707286635.post-8502217498081479682013-10-17T20:33:00.003-07:002013-10-17T20:33:17.043-07:00Google search for "mariadb trademark"Google search results for <a href="https://www.google.com/search?source=ig&rlz=&q=mariadb+trademark"><b>mariadb trademark</b></a> are interesting. I forgot so much had been written about this in the past. Did the trademark policy ever get resolved? This discussion started in 2010.<br />
<br />
<ul>
<li><a href="http://openlife.cc/blogs/2010/november/leaving-monty-program-and-mariadb">http://openlife.cc/blogs/2010/november/leaving-monty-program-and-mariadb</a></li>
<li><a href="http://mariadb.com/kb/en/mariadb-trademark-policy">http://mariadb.com/kb/en/mariadb-trademark-policy</a></li>
<li><a href="http://blog.mariadb.org/mariadb-draft-trademark-policy-available">http://blog.mariadb.org/mariadb-draft-trademark-policy-available</a></li>
<li><a href="http://monty-says.blogspot.com/2010/12/proposal-for-mariadb-trademark-policy.html">http://monty-says.blogspot.com/2010/12/proposal-for-mariadb-trademark-policy.html</a></li>
<li><a href="http://blog.mariadb.org/mariadb-and-trademark">http://blog.mariadb.org/mariadb-and-trademark</a></li>
<li><a href="http://www.skysql.com/about/legal/trademarks">http://www.skysql.com/about/legal/trademarks</a></li>
</ul>
Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com2tag:blogger.com,1999:blog-5915567578707286635.post-3905872964962375942013-10-01T12:09:00.003-07:002013-10-01T12:09:49.550-07:00Compression?There aren't many new files under mysql-test for 5.6.14. Is this compression or something else? Many bugs were fixed <a href="http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-14.html">per the release notes</a>.<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">diff --recursive --brief mysql-5.6.13 mysql-5.6.14 | grep "Only in"</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Only in mysql-5.6.14/man: ndb_blob_tool.1</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Only in mysql-5.6.14/mysql-test/include: have_valgrind.inc</span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: x-small;">Only in mysql-5.6.14/support-files: mysql.5.6.14.spec</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Only in mysql-5.6.14/unittest/gunit: log_throttle-t.cc</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Only in mysql-5.6.14/unittest/gunit: strtoll-t.cc</span><br />
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Only in mysql-5.6.13/packaging/rpm-uln: mysql-5.6-stack-guard.patch</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">Only in mysql-5.6.13/support-files: mysql.5.6.13.spec</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com2tag:blogger.com,1999:blog-5915567578707286635.post-90865258202918811082013-10-01T06:57:00.002-07:002013-10-01T06:57:31.078-07:00The Foundation Exists!I have been wondering what the Foundation has been up to. I had high hopes for it and even contributed money but it has been very quiet. Fortunately I learned that it has been busy making decisions, maybe not in public, but somewhere. And at Percona London we will be told <a href="http://www.percona.com/live/london-2013/sessions/whats-new-mariadb-55-and-what-will-happen-mariadb-100">why it forked MariaDB prior to 5.6</a> and reimplemented a lot of features.<br /><br />In other news the <a href="http://www.mysqlperformanceblog.com/2013/10/01/oracle-speakers-percona-live-london-2013/">Percona London</a> lineup looks great and I appreciate that Oracle is part of it.Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com3tag:blogger.com,1999:blog-5915567578707286635.post-82024899753108420302013-09-26T18:50:00.000-07:002013-09-26T18:50:29.809-07:00MySQL 4.1 foreverSomeone I know used to make jokes about their plans to run MySQL 4.0 forever. It wasn't a horrible idea as 4.0 was very efficient and the Google patch added monitoring and crash-proof replication slaves. I spent time this week comparing MySQL 5.7.2 with 5.6.12 and 5.1.63. To finish the results I now have numbers for 4.1.22. I wanted to include 4.0 but I don't think it works good when compiled with a modern version of gcc and I didn't want to debug the problem. The result summary is that 4.1.22 is much faster at low concurrency and much slower at high concurrency. Of course we want the best of both worlds -- 4.1.22 performance at low concurrency and 5.7.2 performance at high. Can we get that?<br /><br />I used sysbench for single-threaded and high concurrency workloads. The database is cached by InnoDB. All of the QPS numbers are in previous posts except for the 4.1.22 results. I only include the charts and graphs here as the differences between 4.1.22 and modern MySQL stand out.<br />
<h4>
Single thread results</h4>
For all of the charts the results for 4.1.22 are at the top. The first result is for a workload that fetches 1 row by primary key via SELECT. MySQL 4.1.22 is much better than modern MySQL.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-xBST2OgFFC4/UkThpNESX2I/AAAAAAAAAr0/wSzljc_2-F4/s1600/chart_1+(1).png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="198" src="http://3.bp.blogspot.com/-xBST2OgFFC4/UkThpNESX2I/AAAAAAAAAr0/wSzljc_2-F4/s320/chart_1+(1).png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
The next result is for a workload that fetches 1 row by primary key via HANDLER. MySQL 4.1.22 is still the best but the difference is smaller than for SELECT.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-o3OO9HBmJwA/UkTibyIxaVI/AAAAAAAAAr8/rvyi5-M0bog/s1600/chart_2+(1).png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="198" src="http://4.bp.blogspot.com/-o3OO9HBmJwA/UkTibyIxaVI/AAAAAAAAAr8/rvyi5-M0bog/s320/chart_2+(1).png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
The last result is for a workload that updates 1 row by primary key. The database uses reduced durability (no binlog, no fsync on commit). Modern MySQL has gotten much slower. Bulk loading a database with MySQL might be a lot slower than it was in 4.1.22.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-KrapJ8kfz40/UkTiqQxiqvI/AAAAAAAAAsE/WZ9kR7pNPoY/s1600/chart_3+(1).png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="198" src="http://3.bp.blogspot.com/-KrapJ8kfz40/UkTiqQxiqvI/AAAAAAAAAsE/WZ9kR7pNPoY/s320/chart_3+(1).png" width="320" /></a></div>
<h4>
Concurrent results</h4>
MySQL 4.1.22 looked much better than modern MySQL on the single-threaded results. It looks much worse on the high-concurrency workloads and displays a pattern that was well known back in the day -- QPS collapses once there are too many concurrent requests.<br /><br />
Here is an example of that pattern for SELECT by primary key.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-KUoNHVuppLI/UkTjn679vfI/AAAAAAAAAsM/CTHTA0hD6XE/s1600/chart_7.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="198" src="http://1.bp.blogspot.com/-KUoNHVuppLI/UkTjn679vfI/AAAAAAAAAsM/CTHTA0hD6XE/s320/chart_7.png" width="320" /></a></div>
<br />
This is an example of the collapse for fetch 1 row by primary key via HANDLER.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-og61eIiYT2I/UkTj4mECQxI/AAAAAAAAAsU/6djhsI1rBYE/s1600/chart_8.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="198" src="http://3.bp.blogspot.com/-og61eIiYT2I/UkTj4mECQxI/AAAAAAAAAsU/6djhsI1rBYE/s320/chart_8.png" width="320" /></a></div>
<br />
The final example of the collapse is for UPDATE 1 row by primary key. Note that 5.1.63 with and without the Facebook patch also collapses.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/--i29Ld78F2Q/UkTj4pnmtWI/AAAAAAAAAsY/lDYorwNv0bU/s1600/chart_9.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="198" src="http://2.bp.blogspot.com/--i29Ld78F2Q/UkTj4pnmtWI/AAAAAAAAAsY/lDYorwNv0bU/s320/chart_9.png" width="320" /></a></div>
<br />Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com5tag:blogger.com,1999:blog-5915567578707286635.post-45695410580652228712013-09-26T10:29:00.000-07:002013-09-26T10:29:48.151-07:00Group commit is good in 5.6 and 5.7<div class="separator" style="clear: both; text-align: left;">
Many of my write-intensive benchmarks use reduced durability mode (fsync off, binlog off) because that was required to understand whether other parts of the server might be a bottleneck. Fortunately real group commit exists in 5.6 and 5.7 and it works great. Results here compare the performance between official 5.1, 5.6 and 5.7 along with Facebook 5.1. I included FB 5.1 because it was the first to have group commit and the first to use that in production. But <a href="http://mysqlmusings.blogspot.in/2012/06/binary-log-group-commit-in-mysql-56.html">the official version of real group commit</a> is much better, <a href="https://www.facebook.com/note.php?note_id=10150261692455933">as is the MariaDB version</a>. Performance for the same workload <a href="http://mysqlha.blogspot.com/2013/09/concurrent-sysbench-performance-for.html">without group commit is here</a>.<br /><br />I compared 5 binaries:</div>
<div class="separator" style="clear: both; text-align: left;">
</div>
<ul>
<li>orig5612.gc - MySQL 5.6.12 with group commit</li>
<li>orig572.gc - MySQL 5.7.2 with group commit</li>
<li>fb5163.gc - MySQL 5.1.63 and the FB patch with group commit</li>
<li>fb5163.nogc - MySQL 5.1.63 and the FB patch without group commit</li>
<li>orig5163 - MySQL 5.1.63 without group commit</li>
</ul>
<br />
<div class="separator" style="clear: both; text-align: left;">
This graphs displays performance for an update-only workload. The client is 8 sysbench processes that run on one host with mysqld on another host. The total number of clients tested was 8, 16, 32, 64, 128 and 256 where the clients are evenly divided between the sysbench processes. The test database was cached by InnoDB and 8 tables with 8M rows each were used. The clients were evenly divided between the 8 tables. Each transaction is an auto-commit UPDATE that changes the non-indexed column for 1 row found by primary key lookup. The binlog was enabled and InnoDB did fsync on commit. The test server has 24 CPUs with HT enabled and storage is fast flash.</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-6qT84Hhcly0/UkRsN6jaJtI/AAAAAAAAAqk/fnPe51bNiiA/s1600/chart_10.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="198" src="http://1.bp.blogspot.com/-6qT84Hhcly0/UkRsN6jaJtI/AAAAAAAAAqk/fnPe51bNiiA/s320/chart_10.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
This table has all of the results from the test.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<table cellpadding="0" cellspacing="0" dir="ltr" style="font-family: arial,sans,sans-serif; font-size: 13px; table-layout: fixed;"><colgroup><col width="120"></col><col width="120"></col><col width="120"></col><col width="120"></col><col width="120"></col><col width="120"></col><col width="120"></col></colgroup><tbody>
<tr style="height: 17px;"><td style="background-color: white; border: 1px solid rgb(204, 204, 204); direction: ltr; font-size: 100%; overflow: hidden; padding: 0px 3px; vertical-align: bottom;">binary</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">8</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">16</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">32</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">64</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">128</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">256</td></tr>
<tr style="height: 2px;"><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; font-size: 100%; overflow: hidden; padding: 0px 3px; vertical-align: bottom;">orig5612.gc</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">11108</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">17728</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">28789</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">39533</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">47708</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">51110</td></tr>
<tr style="height: 17px;"><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; font-size: 100%; overflow: hidden; padding: 0px 3px; vertical-align: bottom;">orig572.gc</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">11021</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">17583</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">27133</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">37145</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">43736</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">46717</td></tr>
<tr style="height: 17px;"><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; font-size: 100%; overflow: hidden; padding: 0px 3px; vertical-align: bottom;">fb5163.gc</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">8051</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">14709</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">22486</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">28882</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">31743</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">31888</td></tr>
<tr style="height: 17px;"><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; font-size: 100%; overflow: hidden; padding: 0px 3px; vertical-align: bottom;">fb5163.nogc</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">7457</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">6784</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">6722</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">6689</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">6810</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">6537</td></tr>
<tr style="height: 17px;"><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; font-size: 100%; overflow: hidden; padding: 0px 3px; vertical-align: bottom;">orig5163</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">6942</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">7125</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">6957</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">6598</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">6648</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">6568</td></tr>
</tbody></table>
<br />
For comparison I include these results from tests that disabled the binlog and fsync on commit. In these tests performance of 5.1.63 collapses under concurrency. I did not debug the cause but using the binlog and fsync on commit improved performance. Note also that 5.6 and 5.7 can do ~70k TPS in a reduced durability configuration and ~50k in a durable configuration. So durability costs about 2/7 of the peak.<br />
<br />
<table cellpadding="0" cellspacing="0" dir="ltr" style="font-family: arial,sans,sans-serif; font-size: 13px; table-layout: fixed;"><colgroup><col width="120"></col><col width="120"></col><col width="120"></col><col width="120"></col><col width="120"></col><col width="120"></col></colgroup><tbody>
<tr style="height: 17px;"><td style="border: 1px solid rgb(204, 204, 204); direction: ltr; padding: 0px 3px; vertical-align: bottom;">binary</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">8</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">16</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">32</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">64</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">128</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">fb5163.noahi</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">28543</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">41978</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">31758</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">14480</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">10208</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5163.noahi</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">27714</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">47582</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">35231</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">14936</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">10308</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">fb5612.noahi</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">25935</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">47862</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">69679</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">75730</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">71983</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.noahi</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">26920</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">51842</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">73288</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">78757</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">72966</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.psdis</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">27138</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">50902</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">71711</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">77208</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">71674</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.psen</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">26576</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">48000</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">69451</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">75729</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">70466</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.noahi</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">26089</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">48382</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">73190</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">83373</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">75456</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.psdis</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">25090</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">48368</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">71795</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">82348</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">75060</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.psen</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">25375</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">45751</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">69154</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">79023</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">70585</td></tr>
</tbody></table>
Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com0tag:blogger.com,1999:blog-5915567578707286635.post-41505661564107702862013-09-25T12:19:00.000-07:002013-09-25T12:19:29.060-07:00Concurrent sysbench performance for MySQL 5.7.2These are results for sysbench with a cached database and concurrent workload. All data is in the InnoDB buffer pool and I used three workloads (select only, handler only, update only) as <a href="http://mysqlha.blogspot.com/2013/09/mysql-572-single-threaded-performance.html">described here</a>. The summary is that MySQL sustains much higher update rates starting with 5.6 and that improves again in 5.7. Read-only performance also improves but to get a huge increase over 5.1 or 5.6 you need a workload with extremely high concurrency.<br />
<br />
The tests used one server for clients and another for mysqld. Ping between the hosts takes ~250 microseconds. The mysqld host has 24 CPUs with HT enabled. Durability was reduced for the update test -- binlog off, no fsync on commit and host storage was fast for the writes/fsyncs that had to be done. The names used to describe the binaries is <a href="http://mysqlha.blogspot.com/2013/09/mysql-572-single-threaded-performance.html">described here</a>. Each test was repeated for 8, 16, 32, 64 and 128 concurrent clients.<br />
<br />
You might also notice there is a performance regression in the FB patches for MySQL 5.6. I am still trying to figure that out. The regression is less than the one in 5.6/5.7 when the PS is enabled but I hope we can get per-table and per-user resource monitoring with less overhead.<br />
<h4>
SELECT by PK</h4>
<table cellpadding="0" cellspacing="0" dir="ltr" style="font-family: arial,sans,sans-serif; font-size: 13px; table-layout: fixed;"><colgroup><col width="120"></col><col width="120"></col><col width="120"></col><col width="120"></col><col width="120"></col><col width="120"></col></colgroup><tbody>
<tr style="height: 17px;"><td style="border: 1px solid rgb(204, 204, 204); direction: ltr; padding: 0px 3px; vertical-align: bottom;">binary</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">8</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">16</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">32</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">64</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">128</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">fb5163.noahi</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">31228</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">59099</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">128500</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">184677</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">192537</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5163.noahi</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">32450</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">67192</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">126999</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">183784</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">193457</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">fb5612.noahi</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">28996</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">58856</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">118444</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">168934</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">175622</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.noahi</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">33207</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">59713</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">124882</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">176286</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">184590</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.psdis</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">27963</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">63654</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">123344</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">174108</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">180259</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.psen</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">29192</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">57937</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">116613</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">160917</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">164578</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.noahi</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">30053</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">62649</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">121101</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">171441</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">180280</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.psdis</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">30835</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">62925</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">117282</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">165293</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">171528</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.psen</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">31869</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">58030</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">117433</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">156647</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">160074</td></tr>
</tbody></table>
<h4>
HANDLER by PK</h4>
<table cellpadding="0" cellspacing="0" dir="ltr" style="font-family: arial,sans,sans-serif; font-size: 13px; table-layout: fixed;"><colgroup><col width="120"></col><col width="120"></col><col width="120"></col><col width="120"></col><col width="120"></col><col width="120"></col></colgroup><tbody>
<tr style="height: 17px;"><td style="border: 1px solid rgb(204, 204, 204); direction: ltr; padding: 0px 3px; vertical-align: bottom;">binary</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">8</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">16</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">32</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">64</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">128</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">fb5163.noahi</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">34613</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">73636</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">156946</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">239452</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">207223</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5163.noahi</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">38014</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">83000</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">152202</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">223349</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">133286</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">fb5612.noahi</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">34552</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">83458</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">152313</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">243776</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">266989</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.noahi</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">36064</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">84524</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">158341</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">246033</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">276491</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.psdis</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">38537</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">71292</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">159299</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">242109</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">272497</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.psen</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">34997</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">82608</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">151322</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">228510</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">249636</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.noahi</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">33260</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">73790</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">161773</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">242909</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">280488</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.psdis</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">34244</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">71770</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">151687</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">239340</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">272236</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.psen</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">37723</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">72841</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">153221</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">226125</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">248008</td></tr>
</tbody></table>
<h4>
UPDATE by PK</h4>
<table cellpadding="0" cellspacing="0" dir="ltr" style="font-family: arial,sans,sans-serif; font-size: 13px; table-layout: fixed;"><colgroup><col width="120"></col><col width="120"></col><col width="120"></col><col width="120"></col><col width="120"></col><col width="120"></col></colgroup><tbody>
<tr style="height: 17px;"><td style="border: 1px solid rgb(204, 204, 204); direction: ltr; padding: 0px 3px; vertical-align: bottom;">binary</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">8</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">16</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">32</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">64</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">128</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">fb5163.noahi</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">28543</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">41978</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">31758</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">14480</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">10208</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5163.noahi</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">27714</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">47582</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">35231</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">14936</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">10308</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">fb5612.noahi</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">25935</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">47862</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">69679</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">75730</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">71983</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.noahi</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">26920</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">51842</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">73288</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">78757</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">72966</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.psdis</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">27138</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">50902</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">71711</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">77208</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">71674</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.psen</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">26576</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">48000</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">69451</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">75729</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">70466</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.noahi</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">26089</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">48382</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">73190</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">83373</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">75456</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.psdis</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">25090</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">48368</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">71795</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">82348</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">75060</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.psen</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">25375</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">45751</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">69154</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">79023</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; padding: 0px 3px; text-align: right; vertical-align: bottom;">70585</td></tr>
</tbody></table>
Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com3tag:blogger.com,1999:blog-5915567578707286635.post-65704626204387891352013-09-25T09:57:00.001-07:002013-09-25T09:57:39.353-07:00Concurrent connection create performance for MySQL 5.7.2I used sysbench to measure the performance for concurrent clients connecting and then running a query. Each transaction in this case is one new connection followed by a HANDLER statement to fetch 1 row by primary key. Connection create is getting faster in 5.6 and even more so in 5.7. But enabling the performance schema with default options significantly reduces performance. See <a href="http://bugs.mysql.com/bug.php?id=70018">bug 70018</a> if you care about that.<br /><br />There are more details on my test setup in <a href="http://mysqlha.blogspot.com/2013/09/single-threaded-connect-performance.html">previous</a> <a href="http://mysqlha.blogspot.com/2013/09/mysql-572-single-threaded-performance.html">posts</a>. For this test clients and server ran on separate hosts and ping takes ~250 usecs between them today. Eight sysbench processes were run on the client host and each process created between 1 and 16 connections to mysqld. The database is cached by InnoDB and the clients were divided evenly between the tables. Each table has 8M rows.<br /><br />
These are results in TPS for 8, 16, 32, 64 and 128 concurrent clients. Each transaction is connect followed by a HANDLER fetch. The binaries orig572.psen and orig5612.psen use the performance schema with default options for MySQL 5.7.2 and 5.6.12. Throughput is much worse compared to the same code without the PS. All binary names are <a href="http://mysqlha.blogspot.com/2013/09/mysql-572-single-threaded-performance.html">explained here</a>.<br />
<br />
<table cellpadding="0" cellspacing="0" dir="ltr" style="font-family: arial,sans,sans-serif; font-size: 13px; table-layout: fixed;"><colgroup><col width="120"></col><col width="120"></col><col width="120"></col><col width="120"></col><col width="120"></col><col width="120"></col></colgroup><tbody>
<tr style="height: 17px;"><td style="border: 1px solid rgb(204, 204, 204); direction: ltr; padding: 0px 3px; vertical-align: bottom;">binary</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; direction: ltr; padding: 0px 3px; text-align: right; vertical-align: bottom;">8</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; border-top: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">16</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; border-top: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">32</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; border-top: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">64</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; border-top: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">128</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">fb5163.noahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">4041</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">8084</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">16323</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">16380</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">16066</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5163.noahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">4026</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">7848</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">15587</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">15912</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">15741</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">fb5612.noahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">4004</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">7425</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">23125</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">24570</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">24688</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.noahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">4027</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">7601</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">26155</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">28021</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">28091</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.psdis</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">4008</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">7643</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">25640</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">27517</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">27631</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.psen</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">4205</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">9366</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">21197</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">21456</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">21592</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.noahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">4172</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">9248</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">28613</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">39451</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">39721</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.psdis</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">4025</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">7612</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">27600</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">37963</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">38044</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.psen</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">4001</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">7870</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">18437</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">22982</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">23240</td></tr>
</tbody></table>
<br />And this chart has data for some of the binaries.<br /><div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-80NyS5Zx6w8/UkMVG-Gul_I/AAAAAAAAAp4/uuSBYkNT1LQ/s1600/chart_6.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="198" src="http://3.bp.blogspot.com/-80NyS5Zx6w8/UkMVG-Gul_I/AAAAAAAAAp4/uuSBYkNT1LQ/s320/chart_6.png" width="320" /></a></div>
<div>
<br /></div>
Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com1tag:blogger.com,1999:blog-5915567578707286635.post-6901328050332954892013-09-24T20:19:00.000-07:002013-09-24T20:19:25.233-07:00Single-threaded connect performanceI used sysbench to understand the changes in connection create performance between MySQL versions 5.1, 5.6 and 5.7. The test used single-threaded sysbench where each query created a new connection and then selected one row by PK via HANDLER. The database was cached by InnoDB and both the single client thread and mysqld ran on the same host. The tests were otherwise the same as <a href="http://mysqlha.blogspot.com/2013/09/mysql-572-single-threaded-performance.html">described in a previous post</a>.<br /><br />The summary is that connection create has gotten faster in MySQL 5.6 and 5.7 but enabling the performance schema with default options reduces that by about 10% for a single threaded workload. <a href="http://bugs.mysql.com/bug.php?id=70018">Bug 70018</a> is open to reduce this overhead. The <a href="http://bugs.mysql.com/bug.php?id=68514">memory consumed per increment of max_connections</a> by the PS might also be interesting to you.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-atv-v3D-bUI/UkJUqgfeAGI/AAAAAAAAApo/4JlnUkLv42w/s1600/chart_4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="198" src="http://4.bp.blogspot.com/-atv-v3D-bUI/UkJUqgfeAGI/AAAAAAAAApo/4JlnUkLv42w/s320/chart_4.png" width="320" /></a></div>
<table cellpadding="0" cellspacing="0" dir="ltr" style="font-family: arial,sans,sans-serif; font-size: 13px; table-layout: fixed;"><colgroup><col width="120"></col><col width="120"></col></colgroup><tbody>
<tr style="height: 17px;"><td style="border: 1px solid rgb(204, 204, 204); direction: ltr; padding: 0px 3px; vertical-align: bottom;">binary</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">QPS</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">fb5163.noahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">2087</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5163.noahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">2122</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">fb5612.noahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">2656</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.noahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">2775</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.psdis</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">2706</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.psen</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">2468</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.noahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">2687</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.psdis</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">2611</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.psen</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">2427</td></tr>
</tbody></table>
Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com1tag:blogger.com,1999:blog-5915567578707286635.post-26541330891609085562013-09-24T19:31:00.002-07:002013-09-24T19:31:43.995-07:00MySQL 5.7.2 single threaded performance needs improvementThis <a href="http://mysqlha.blogspot.com/2013/05/mysql-56-single-threaded-performance.html">isn't a new message</a> but single-threaded performance continues to get worse in 5.7.2. There have been regressions from 5.1 to 5.6 and now to 5.7. I skipped testing 5.5. On the bright side there is <a href="http://bugs.mysql.com/bug.php?id=68825">progress on a bug</a> I opened for this and MySQL seems to be very interested in making things better. The regressions for UPDATE and SELECT are much worse than for HANDLER so I assume the optimizer accounts for much of the new overhead.<br />
<br />
The performance schema with default instrumentation appears to have a higher overhead than the Facebook patch. The critical monitoring for the FB patch is per-user and per-table statistics. While it is always nice to reduce the size of the FB patch, and switching back to the PS for that would reduce it, I don't think that will happen until the PS becomes more efficient.<br />
<br />
The graphs below have results for 5.1 at the top, 5.6 in the middle and 5.7 on the bottom. This makes it easier to see the regressions over time.<br /><br />I tested 3 workloads using sysbench: select, handler and update. The <b>select</b> workload fetches all columns in one row by primary key using SELECT. The <b>handler</b> workload does the same using HANDLER instead of SELECT. The <b>update</b> workload updates a non-indexed column in 1 row by primary key. For all of the tests the database was cached by InnoDB. The tests used 1 sysbench process and 1 table (sbtest1) and all processes ran on the same server. Only one client connection (1 thread) was used during each test. Durability was reduced for the update test -- no binlog, no fsync on commit<br />
<br />
MySQL 5.1.63, 5.6.12 and 5.7.2 were tested in several configuration -- with/without the adaptive hash index (AHI below) and with/without the performance schema (PS below). When the PS is enabled only the default options are used. The results use the following binary names:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<ul>
<li>fb5163.noahi - 5.1.63, Facebook patch, AHI off</li>
<li>fb5163.ahi - 5.1.63, Facebook patch, AHI on</li>
<li>orig5163.noahi - 5.1.63, AHI off</li>
<li>orig5163.ahi - 5.1.63, AHI on</li>
<li>fb5612.noahi - 5.6.12, Facebook patch, AHI off</li>
<li>fb5612.ahi - 5.6.12, Facebook patch, AHI on</li>
<li>orig5612.noahi - 5.6.12, AHI off, PS not compiled</li>
<li>orig5612.ahi - 5.6.12, AHI on, PS not compiled</li>
<li>orig5612.psdis - 5.6.12, AHI off, PS compiled but disabled</li>
<li>orig5612.psen - 5.6.12, AHI off, PS compiled & enabled</li>
<li>orig572.noahi - 5.7.2, AHI off, PS not compiled</li>
<li>orig572.ahi - 5.7.2, AHI on, PS not compiled</li>
<li>orig572.psdis - 5.7.2, AHI off, PS compiled but disabled</li>
<li>orig572.psen - 5.7.2, AHI off, PS compiled & enabled</li>
</ul>
<br />
<h4>
Fetch 1 row by SELECT via PK</h4>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-P22UZnj-V8I/UkJG176smAI/AAAAAAAAApM/jskPL5TNoNs/s1600/chart_1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="198" src="http://1.bp.blogspot.com/-P22UZnj-V8I/UkJG176smAI/AAAAAAAAApM/jskPL5TNoNs/s320/chart_1.png" width="320" /></a></div>
<div>
<table cellpadding="0" cellspacing="0" dir="ltr" style="font-family: arial,sans,sans-serif; font-size: 13px; table-layout: fixed;"><colgroup><col width="120"></col><col width="120"></col></colgroup><tbody>
<tr style="height: 17px;"><td style="border: 1px solid rgb(204, 204, 204); direction: ltr; padding: 0px 3px; vertical-align: bottom;">binary</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">QPS</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">fb5163.noahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">10087</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">fb5163.ahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">10918</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5163.noahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">10230</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5163.ahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">10614</td></tr>
<tr style="height: 17px;"><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; font-size: 100%; overflow: hidden; padding: 0px 3px; vertical-align: bottom;">fb5612.noahi</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">9070</td></tr>
<tr style="height: 17px;"><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; font-size: 100%; overflow: hidden; padding: 0px 3px; vertical-align: bottom;">fb5612.ahi</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">9607</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.noahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">9412</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.ahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">9511</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.psdis</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">9334</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.psen</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">8702</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.noahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">9128</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.ahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">9607</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.psdis</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">8573</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.psen</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">8572</td></tr>
</tbody></table>
</div>
<div>
<br /></div>
<div>
Fetch 1 row by PK via HANDLER</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-LpPth_DWo_M/UkJIHBOTRbI/AAAAAAAAApQ/NqAgnyvm0UE/s1600/chart_2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="198" src="http://2.bp.blogspot.com/-LpPth_DWo_M/UkJIHBOTRbI/AAAAAAAAApQ/NqAgnyvm0UE/s320/chart_2.png" width="320" /></a></div>
<div>
<table cellpadding="0" cellspacing="0" dir="ltr" style="font-family: arial,sans,sans-serif; font-size: 13px; table-layout: fixed;"><colgroup><col width="120"></col><col width="120"></col></colgroup><tbody>
<tr style="height: 17px;"><td style="border: 1px solid rgb(204, 204, 204); direction: ltr; padding: 0px 3px; vertical-align: bottom;">binary</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">QPS</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">fb5163.noahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">14560</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">fb5163.ahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">14832</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5163.noahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">14679</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5163.ahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">15535</td></tr>
<tr style="height: 17px;"><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; font-size: 100%; overflow: hidden; padding: 0px 3px; vertical-align: bottom;">fb5612.noahi</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">14532</td></tr>
<tr style="height: 17px;"><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; font-size: 100%; overflow: hidden; padding: 0px 3px; vertical-align: bottom;">fb5612.ahi</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">14908</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.noahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">15068</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.ahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">14638</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.psdis</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">13840</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.psen</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">14433</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.noahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">13960</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.ahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">14799</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.psdis</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">14434</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.psen</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">13697</td></tr>
</tbody></table>
</div>
<h4>
Update 1 row by PK</h4>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-zPNwzsx_-2U/UkJIlkIQneI/AAAAAAAAApY/KFzfe_8VIwk/s1600/chart_3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="198" src="http://4.bp.blogspot.com/-zPNwzsx_-2U/UkJIlkIQneI/AAAAAAAAApY/KFzfe_8VIwk/s320/chart_3.png" width="320" /></a></div>
<div>
<table cellpadding="0" cellspacing="0" dir="ltr" style="font-family: arial,sans,sans-serif; font-size: 13px; table-layout: fixed;"><colgroup><col width="120"></col><col width="120"></col></colgroup><tbody>
<tr style="height: 17px;"><td style="border: 1px solid rgb(204, 204, 204); direction: ltr; padding: 0px 3px; vertical-align: bottom;">binary</td><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; border-top-color: rgb(204, 204, 204); border-top-style: solid; border-top-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">QPS</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">fb5163.noahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">7947</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">fb5163.ahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">8130</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5163.noahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">8184</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5163.ahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">8273</td></tr>
<tr style="height: 17px;"><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; font-size: 100%; overflow: hidden; padding: 0px 3px; vertical-align: bottom;">fb5612.noahi</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">6569</td></tr>
<tr style="height: 17px;"><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; font-size: 100%; overflow: hidden; padding: 0px 3px; vertical-align: bottom;">fb5612.ahi</td><td style="background-color: white; border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; font-size: 100%; overflow: hidden; padding: 0px 3px; text-align: right; vertical-align: bottom;">6587</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.noahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">6813</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.ahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">6893</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.psdis</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">6613</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig5612.psen</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">6395</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.noahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">6350</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.ahi</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">6306</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.psdis</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">6131</td></tr>
<tr style="height: 17px;"><td style="border-bottom-color: rgb(204, 204, 204); border-bottom-style: solid; border-bottom-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; border-left-width: 1px; border-right-color: rgb(204, 204, 204); border-right-style: solid; border-right-width: 1px; direction: ltr; padding: 0px 3px; vertical-align: bottom;">orig572.psen</td><td style="border-bottom: 1px solid #ccc; border-right: 1px solid #ccc; padding: 0 3px; text-align: right; vertical-align: bottom;">5984</td></tr>
</tbody></table>
</div>
Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com2tag:blogger.com,1999:blog-5915567578707286635.post-16338085942391797362013-09-24T16:18:00.000-07:002013-09-24T16:21:53.171-07:00MySQL 5.7.2 and linkbench -- InnoDB is getting betterI used <a href="https://www.facebook.com/notes/facebook-engineering/linkbench-a-database-benchmark-for-the-social-graph/10151391496443920">linkbench</a> to compare MySQL/InnoDB 5.1, 5.6 and 5.7. After a few improvements to linkbench and to the InnoDB my.cnf variables I was able to get much better QPS than before (about 1.5X better). I was ready to try 5.7 because it reduces contention from the <a href="http://dom.as/2011/07/03/innodb-index-lock/">per-index latch</a>. All tests below use reduced durability (no binlog, no fsync on commit) and more details on the my.cnf options are at the end of this page. The tests were very IO-bound as the databases were ~600GB at test start prior to fragmentation and the InnoDB buffer pool was 64GB.<br />
<br />
The summary is that 5.7.2 has better performance than 5.6 and 5.1 and much less mutex contention. The test server has 32 cores with HT enabled and a fast flash device. InnoDB was doing about 40,000 page reads & writes per second.<br />
<br />
<ul>
<li>11281 QPS -> MySQL 5.1.63</li>
<li>23079 QPS -> MySQL 5.6.12</li>
<li>24710 QPS -> MySQL 5.7.2</li>
</ul>
<br />
<h4>
Mutex contention for 5.7.2</h4>
This was collected using the performance schema during an 1800 second test run with 64 client connections. The nsecs_per column is the average number of nanoseconds per attempt to lock the mutex or rw-lock. The seconds column is the total number of seconds attempting to lock it.<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">+---------------------------------------------+---------+--------+</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| event_name |nsecs_per| seconds|</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">+---------------------------------------------+---------+--------+</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| wait/synch/rwlock/innodb/index_tree_rw_lock | 19543.3 | 3456.1 |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| wait/synch/mutex/innodb/log_sys_mutex | 2071.3 | 385.8 |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| wait/synch/rwlock/innodb/hash_table_locks | 165.7 | 184.5 |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| wait/synch/mutex/innodb/fil_system_mutex | 328.3 | 113.6 |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| wait/synch/mutex/innodb/redo_rseg_mutex | 1766.4 | 84.9 |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| wait/synch/rwlock/sql/MDL_lock::rwlock | 430.2 | 73.9 |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| wait/synch/mutex/innodb/buf_pool_mutex | 264.5 | 72.7 |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| wait/synch/rwlock/innodb/fil_space_latch | 27216.1 | 53.8 |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| wait/synch/mutex/sql/THD::LOCK_query_plan | 167.0 | 50.7 |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| wait/synch/mutex/innodb/trx_sys_mutex | 394.9 | 41.5 |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">+---------------------------------------------+---------+--------+</span><br />
<h4>
Mutex contention for 5.6.12</h4>
<div>
This was collected using the performance schema during an 1800 second test run with 64 client connections. The total number of seconds stalled for index_tree_rw_lock and buf_pool_mutex is much higher compared to 5.7.2.</div>
<div>
<br /></div>
<div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">+---------------------------------------------+--------------------+</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| event_name | nsecs_per| secs |</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">+---------------------------------------------+----------+---------+</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| wait/synch/rwlock/innodb/index_tree_rw_lock | 144148.0 | 24491.5 |</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| wait/synch/mutex/innodb/buf_pool_mutex | 5439.9 | 1531.8 |</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| wait/synch/mutex/innodb/log_sys_mutex | 1821.6 | 349.7 |</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| wait/synch/rwlock/innodb/hash_table_locks | 112.4 | 240.9 |</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| wait/synch/mutex/innodb/fil_system_mutex | 234.9 | 83.2 |</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| wait/synch/rwlock/sql/MDL_lock::rwlock | 373.1 | 66.1 |</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| wait/synch/mutex/innodb/trx_sys_mutex | 332.3 | 50.6 |</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| wait/synch/mutex/sql/THD::LOCK_thd_data | 159.6 | 46.7 |</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| wait/synch/mutex/innodb/os_mutex | 190.3 | 42.9 |</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">| wait/synch/mutex/sql/LOCK_table_cache | 325.4 | 35.9 |</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">+---------------------------------------------+------------+-------+</span></div>
</div>
<div>
<br /></div>
<h4>
my.cnf options</h4>
<div>
This lists the my.cnf options for 5.7.2 and 5.6.12</div>
<div>
<br /></div>
<div>
<blockquote class="tr_bq">
table-definition-cache=1000<br />
table-open-cache=2000<br />
table-open-cache-instances=1<br />
max_connections=2000<br />
key_buffer_size=200M<br />
metadata_locks_hash_instances=256<br />
query_cache_size=0<br />
query_cache_type=0<br />
skip_log_bin<br />
max_allowed_packet=16000000<br />
innodb_buffer_pool_size=64G<br />
innodb_log_file_size=1900M<br />
innodb_buffer_pool_instances=8<br />
innodb_io_capacity=16384<br />
innodb_lru_scan_depth=2048<br />
innodb_checksum_algorithm=CRC32<br />
innodb_flush_log_at_trx_commit=2<br />
innodb_thread_concurrency=0<br />
innodb_flush_method=O_DIRECT<br />
innodb_max_dirty_pages_pct=80<br />
innodb_file_format=barracuda<br />
innodb_file_per_table<br />
innodb_adaptive_hash_index=0<br />
innodb_doublewrite=0<br />
innodb_flush_neighbors=0<br />
innodb_use_native_aio = 1</blockquote>
<div>
<br /></div>
</div>
Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com17tag:blogger.com,1999:blog-5915567578707286635.post-8611970320230544062013-09-19T16:33:00.000-07:002013-09-19T16:33:41.839-07:00Conferences and NetworkingSeveral members of the small data team at FB will be <a href="http://mysqlha.blogspot.com/2013/08/mysql-connect.html">at MySQL Connect</a> this weekend. It would be interesting to learn that someone else <a href="https://www.facebook.com/notes/facebook-engineering/linkbench-a-database-benchmark-for-the-social-graph/10151391496443920">has used Linkbench</a>. I use it in addition to sysbench. After some effort tuning InnoDB and a few changes to the source I was able to almost double the Linkbench QPS but I really need MySQL 5.7 as the per-index latch for InnoDB indexes is the primary bottleneck.<br />
<br />
In addition to networking at conferences, I recently spent a day looking at networking in MySQL 5.1 and 5.6. A good overview is the output from strace -c -p $PID where $PID is a thread busy with sysbench read-only queries for a cached database. Below I describe the results from MySQL 5.1.63 and 5.6.12 using official MySQL and the Facebook patch. Each result is from a sample of about 10 seconds (give or take a few seconds).<br />
<h3>
Official MySQL 5.1.63</h3>
This strace output is from official MySQL 5.1.63. There are two interesting things in these results. The first is frequent calls to sched_setparam and all of them return an error. That is <a href="http://bugs.mysql.com/bug.php?id=35164">bug 35164</a> which was fixed in MySQL 5.6. Removing the calls in 5.1 improved performance by about 0.3% on my test server. That isn't a big deal but I am happy the code is gone. The second interesting result is the high number of calls to fcntl. I filed <a href="http://bugs.mysql.com/bug.php?id=54790">feature request 54790</a> asking for them to be removed. There were a big problem for performance on older Linux kernels that used a big kernel mutex for some of the fcntl processing. <a href="https://www.facebook.com/notes/mysqlfacebook/using-pmp-to-double-mysql-throughput-part-1/404965725932">See this post</a> for details on the impact. This is not a performance problem on the kernels I have been using recently.<br />
<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">% time seconds usecs/call calls errors syscall</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">------ ----------- ----------- --------- --------- ----------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> 40.30 0.964307 17 57447 17100 read</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> 36.58 0.875208 22 40348 40348 sched_setparam</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> 11.30 0.270419 8 34199 fcntl</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> 9.12 0.218152 11 20174 write</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> 2.51 0.060160 20 3021 621 futex</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> 0.19 0.004601 29 156 sched_yield</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">------ ----------- ----------- --------- --------- ----------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">100.00 2.392847 155345 58069 total</span><br />
<h3>
Facebook 5.1.63</h3>
This strace output is from the Facebook patch for MySQL 5.1.63. It still has the frequent errors from calls to sched_setparam. But instead of too many calls to fcntl it has too many calls to setsockopt. That was a good tradeoff on some Linux kernels as described <a href="https://www.facebook.com/notes/mysqlfacebook/using-pmp-to-double-mysql-throughput-part-1/404965725932">in this post</a> but it doesn't matter on recent kernels.<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">% time seconds usecs/call calls errors syscall</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">------ ----------- ----------- --------- --------- ----------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> 46.15 0.673638 42 15851 read</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> 28.17 0.411157 26 15850 15850 sched_setparam</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> 11.73 0.171289 11 15851 setsockopt</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> 6.93 0.101176 13 7925 write</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> 4.61 0.067359 26 2628 615 futex</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> 2.41 0.035137 39 896 sched_yield</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">------ ----------- ----------- --------- --------- ----------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">100.00 1.459756 59001 16465 total</span><br />
<h3>
MySQL 5.6.12</h3>
This result is the same for both official MySQL and the Facebook patch. Hooray, the calls to sched_setparam are gone! There are many calls to recvfrom that get errors. I assume these are the non-blocking calls that return no data. There are also many calls to poll. I prefer to see fewer calls to poll and hacked on MySQL to do blocking calls to recv. That made the poll calls go away but didn't have a significant impact on performance. Perhaps it will help in the future when other bottlenecks are removed.<br />
<br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">% time seconds usecs/call calls errors syscall</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">------ ----------- ----------- --------- --------- ----------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> 52.61 1.743483 108 16120 poll</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> 33.75 1.118461 59 19055 sendto</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> 9.71 0.321903 6 54229 16121 recvfrom</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> 3.92 0.130002 19 6846 1716 futex</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> 0.01 0.000353 118 3 sched_yield</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">------ ----------- ----------- --------- --------- ----------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">100.00 3.314202 96253 17837 total</span><br />
<h3>
Server-side network reads</h3>
The pattern for server-side network reads is to first do a non-blocking read and if that doesn't return the expected amount of data then a read with timeout is done. I don't have all of the history behind the design decision but my guess is that there had to be support for interrupting reads on shutdown. The implementation of read with timeout has changed over time and it can be hard to figure out some of the code unless you look at the preprocessor output.<br />
<br />
<ul>
<li>in official MySQL 5.1.63 read with timeout was usually implemented by doing a blocking read and then the alarm thread would unblock the thread when the timeout was reached or on shutdown. There was also an option to not use the alarm thread (see -DNO_ALARM). This suffered from frequent calls to fcntl which was a problem when Linux required a big kernel mutex to process them.</li>
<li>in the Facebook patch for MySQL 5.1.63 the code was changed to set a read timeout on the socket via setsockopt and this worked with -DNO_ALARM.</li>
<li>in MySQL 5.6 the recv call is used to read data from a socket and the code does non-blocking reads and then does a wait with timeout via poll until there is more data.</li>
</ul>
<h3>
Reference</h3>
<span style="font-size: small;"><span style="font-weight: normal;">I frequently refer to my old blog posts to research problems so I pasted some of the network stack that is used during server-side socket reads. The call stack is:</span></span><br />
<span style="font-size: small;"><span style="font-family: Courier New, Courier, monospace;">my_net_read()</span></span><br />
<span style="font-size: small;"><span style="font-family: Courier New, Courier, monospace;">-- net_read_packet()</span></span><br />
<span style="font-family: 'Courier New', Courier, monospace; font-size: small;">---- net_read_packet_header()</span><br />
<span style="font-family: Courier New, Courier, monospace;">------ net_read_raw_loop()</span><br />
<span style="font-family: Courier New, Courier, monospace;">---- net_read_raw_loop() to get body</span><br />
<h3>
<span style="font-size: small; font-weight: normal;">And the interesting code for net_read_raw_loop() is listed below:</span><br />
<span style="font-weight: normal;"><br /></span><span style="font-family: Courier New, Courier, monospace; font-size: x-small;">net_read_raw_loop has:</span></h3>
<div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> while (count)</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> {</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> size_t recvcnt= vio_read(net->vio, buf, count);</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* VIO_SOCKET_ERROR (-1) indicates an error. */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> if (recvcnt == VIO_SOCKET_ERROR)</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> {</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* A recoverable I/O error occurred? */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> if (net_should_retry(net, &retry_count))</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> continue;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> else</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> break;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> }</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* Zero indicates end of file. */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> else if (!recvcnt)</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> {</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> eof= true;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> break;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> }</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> count-= recvcnt;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> buf+= recvcnt;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> }</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><b>vio_read is:</b></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">size_t vio_read(Vio *vio, uchar *buf, size_t size)</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">{</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> ssize_t ret;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> int flags= 0;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* If timeout is enabled, do not block if data is unavailable. */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> if (vio->read_timeout >= 0)</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> flags= VIO_DONTWAIT; </span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">/* this is VIO_DONTWAIT == MSG_DONTWAIT </span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> and with tracing all calls to mysql_socket_recv have read_timeout > 0 and use MSG_DONTWAIT */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> while ((ret= mysql_socket_recv(vio->mysql_socket, (SOCKBUF_T *)buf, size, flags)) == -1)</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> {</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> int error= socket_errno;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* The operation would block? */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> if (error != SOCKET_EAGAIN && error != SOCKET_EWOULDBLOCK)</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> break;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* Wait for input data to become available. */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> if ((ret= vio_socket_io_wait(vio, VIO_IO_EVENT_READ)))</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> break;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> }</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> DBUG_RETURN(ret);</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">}</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">int vio_socket_io_wait(Vio *vio, enum enum_vio_io_event event)</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">{</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> int timeout, ret;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> DBUG_ASSERT(event == VIO_IO_EVENT_READ || event == VIO_IO_EVENT_WRITE);</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* Choose an appropriate timeout. */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> if (event == VIO_IO_EVENT_READ)</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> timeout= vio->read_timeout;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> else</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> timeout= vio->write_timeout;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* Wait for input data to become available. */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> switch (vio_io_wait(vio, event, timeout))</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> {</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> case -1:</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* Upon failure, vio_read/write() shall return -1. */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> ret= -1;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> break;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> case 0:</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* The wait timed out. */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> ret= -1;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> break;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> default:</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* A positive value indicates an I/O event. */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> ret= 0;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> break;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> }</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> return ret;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">}</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">int vio_socket_io_wait(Vio *vio, enum enum_vio_io_event event)</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">{</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> int timeout, ret;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> DBUG_ASSERT(event == VIO_IO_EVENT_READ || event == VIO_IO_EVENT_WRITE);</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* Choose an appropriate timeout. */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> if (event == VIO_IO_EVENT_READ)</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> timeout= vio->read_timeout;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> else</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> timeout= vio->write_timeout;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* Wait for input data to become available. */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> switch (vio_io_wait(vio, event, timeout))</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> {</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> case -1:</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* Upon failure, vio_read/write() shall return -1. */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> ret= -1;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> break;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> case 0:</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* The wait timed out. */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> ret= -1;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> break;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> default:</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* A positive value indicates an I/O event. */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> ret= 0;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> break;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> }</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> return ret;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">}</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">int vio_socket_io_wait(Vio *vio, enum enum_vio_io_event event)</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">{</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> int timeout, ret;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> DBUG_ASSERT(event == VIO_IO_EVENT_READ || event == VIO_IO_EVENT_WRITE);</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* Choose an appropriate timeout. */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> if (event == VIO_IO_EVENT_READ)</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> timeout= vio->read_timeout;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> else</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> timeout= vio->write_timeout;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* Wait for input data to become available. */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> switch (vio_io_wait(vio, event, timeout))</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> {</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> case -1:</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* Upon failure, vio_read/write() shall return -1. */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> ret= -1;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> break;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> case 0:</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* The wait timed out. */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> ret= -1;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> break;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> default:</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* A positive value indicates an I/O event. */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> ret= 0;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> break;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> }</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> return ret;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">}</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">int vio_io_wait(Vio *vio, enum enum_vio_io_event event, int timeout)</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">{</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> int ret;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> short DBUG_ONLY revents= 0;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> struct pollfd pfd;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> my_socket sd= mysql_socket_getfd(vio->mysql_socket);</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> memset(&pfd, 0, sizeof(pfd));</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> pfd.fd= sd;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* Set the poll bitmask describing the type of events.</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> The error flags are only valid in the revents bitmask. */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> switch (event)</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> {</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> case VIO_IO_EVENT_READ:</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> pfd.events= MY_POLL_SET_IN;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> revents= MY_POLL_SET_IN | MY_POLL_SET_ERR | POLLRDHUP;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> break;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> case VIO_IO_EVENT_WRITE:</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> case VIO_IO_EVENT_CONNECT:</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> pfd.events= MY_POLL_SET_OUT;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> revents= MY_POLL_SET_OUT | MY_POLL_SET_ERR;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> break;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> }</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* Wait for the I/O event and return early in case of error or timeout */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> switch ((ret= poll(&pfd, 1, timeout)))</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> {</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> case -1:</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> break; /* return -1 on error */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> case 0:</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* Set errno to indicate a timeout error. */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> errno= SOCKET_ETIMEDOUT;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> break;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> default:</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* Ensure that the requested I/O event has completed. */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> DBUG_ASSERT(pfd.revents & revents);</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> break;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> }</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> DBUG_RETURN(ret);</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">}</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">static inline ssize_t </span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">inline_mysql_socket_recv(MYSQL_SOCKET mysql_socket, SOCKBUF_T *buf, size_t n, int flags)</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">{</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> ssize_t result;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> /* Non instrumented code */</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> result= recv(mysql_socket.fd, buf, IF_WIN((int),) n, flags);</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"><br /></span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;"> return result;</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace; font-size: x-small;">}</span></div>
</div>
Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com1tag:blogger.com,1999:blog-5915567578707286635.post-90627280858356263412013-09-08T09:40:00.000-07:002013-09-08T09:40:10.301-07:00Big and small data at XLDBMy co-workers will speak about big and small data at <a href="http://conf-slac.stanford.edu/xldb-2013/">XLDB</a>. Jeremy Cole and the Tokutek founders are also speaking. I hope to learn many interesting things there including my fate (<a href="http://gigaom.com/2011/07/07/facebook-trapped-in-mysql-fate-worse-than-death/">1</a>, <a href="http://dom.as/2011/07/08/stonebraker-trapped/">2</a>, <a href="http://www.theregister.co.uk/2011/07/13/mike_stonebraker_versus_facebook/">3</a>, <a href="http://gigaom.com/2011/12/06/facebook-shares-some-secrets-on-making-mysql-scale/">4</a>), whether I am doing things right or wrong and what <a href="http://conf-slac.stanford.edu/xldb-2013/abstracts#JBecla">database technology might be used</a> by future extremely large science experiments. Oh, you probably missed it but we are doing it all wrong. See the slides/abstract from <a href="http://db.csail.mit.edu/nedbday13/program.html">NEDS 2013</a> on "The Traditional Wisdom is All Wrong". Excessively strong claims without any attempt to understand web-scale data management doesn't make a great paper.<br />
<br />
<ul>
<li><a href="http://conf-slac.stanford.edu/xldb-2013/abstracts#HFiskandDMituzas">Small Data at Peta Scale</a> by Domas Mituzas and Harrison Fisk</li>
<li><a href="http://conf-slac.stanford.edu/xldb-2013/abstracts#RMurthy">Beyond Hadoop - Building the Analytics Infrastructure at Facebook</a> by Ravi Murthy</li>
<li><a href="https://conf-slac.stanford.edu/xldb-2013/abstracts#JCole">The MySQL Ecosystem at Scale</a> by Jeremy Cole</li>
<li><a href="http://conf-slac.stanford.edu/xldb-2013/tutorials#pmC">Data Structures and Algorithms for Big Databases</a> by Michael Bender and Bradley Kuszmaul</li>
</ul>
Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com5tag:blogger.com,1999:blog-5915567578707286635.post-36459318209214647672013-08-12T18:44:00.002-07:002013-08-12T18:44:29.045-07:00MySQL Connect<span style="background-color: white; color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13.333333015441895px; line-height: 18px;">The Facebook MySQL teams are presenting at <a href="http://www.oracle.com/mysqlconnect/index.html">MySQL Connect</a>:</span><br />
<ul>
<li><span style="background-color: white; color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13.333333015441895px; line-height: 18px;"><a href="https://oracleus.activeevents.com/2013/connect/speakerDetail.ww?PERSON_ID=07BEFD9B358D74E979D890D36069ADAD">Lots and lots of small data</a> by Harrison Fisk</span><span style="background-color: white; color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13.333333015441895px; line-height: 18px;"> </span></li>
<li><span style="background-color: white; color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13.333333015441895px; line-height: 18px;"><a href="https://oracleus.activeevents.com/2013/connect/speakerDetail.ww?PERSON_ID=BBEF5FCC7AB4A48C2E595F25F3B2F1A0">MySQL 5.6 at Facebook</a> by Yoshinori Matsunobu</span></li>
<li><span style="background-color: white; color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13.333333015441895px; line-height: 18px;"><a href="http://www.oracle.com/mysqlconnect/keynotes/index.html">Panel session on MySQL 5.6</a> with Mark Callaghan</span></li>
</ul>
<span style="background-color: white; color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif; font-size: 13.333333015441895px; line-height: 18px;"><br />A lot of work has been done by the teams this year and even more is planned for the future. We have a lot more people helping to make MySQL better. That is a nice change. Now we just need to get the new people to speak & write about their work in public.</span>Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com1tag:blogger.com,1999:blog-5915567578707286635.post-7962091143413274742013-05-18T15:03:00.000-07:002013-05-18T15:03:02.246-07:00MySQL 5.6, InnoDB and fast storageI used a simple workload with sysbench to determine the rate at which InnoDB can read blocks from disk. The workload is read-only and each query fetches 1 row by PK. The workload was IO-bound with a 2G InnoDB buffer pool and 32G database. Storage was fast courtesy of buffered IO and enough RAM to cache the database in the OS filesystem cache.<br /><br />Using MySQL 5.6.11 and InnoDB with a few hacks the peak throughput was about 240,000 QPS and 210,000 block reads/second. The test server has 32 cores (16 physical cores, 32 logical cores with HT enabled). This is a great result that can probably be even better. Contention on fil_system->mutex was the bottleneck and I think that can be improved (see feature request <a href="http://bugs.mysql.com/bug.php?id=69276">#69276</a>). I wonder if 400,000 block reads/second is possible?<br />
<br />
A few years back, in 2009 or 2010, I ran similar tests using a server with 8 physical cores. I think HT was disabled. Using MySQL 5.1 with the Facebook patch I was able to get about 40,000 QPS and 35,000 block reads/second. It is good to see software advance to keep up with hardware.Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com9tag:blogger.com,1999:blog-5915567578707286635.post-39513313561587607112013-05-14T12:54:00.000-07:002013-05-15T08:06:31.978-07:00MySQL 5.6: single-threaded performance regressionsI ran single-threaded performance tests to compare MySQL using releases 4.0.30, 5.0.85, 5.1.63 and 5.6.11. On my simple tests 4.0.30 is almost 1.5X faster than 5.6.11. I think it is important to reduce these regressions. Maybe this is an area in which the forks (MariaDB, Percona) will lead the way? I previously opened <a href="http://bugs.mysql.com/bug.php?id=68825">bug 68825</a> for this and will update it with the results I report here. <a href="http://www.mysqlperformanceblog.com/2013/03/27/why-mysql-performance-at-low-concurrency-is-important/">Peter</a> and <a href="http://mysqlha.blogspot.com/2013/03/mysql-56-single-threaded-read-only.html">I have written</a> about this previously. <a href="http://bugs.mysql.com/bug.php?id=69236">Bug 69236</a> is also open for this now.<br />
<br />
I used most of the advice from a <a href="http://mysqlha.blogspot.com/2013/04/mysql-56-incomplete-guide-to-avoiding.html">previous blog post</a> to build & configure 5.6 for peak performance and that included disabling the performance schema at compile time. All binaries were compiled with the same version of gcc and used jemalloc. For 4.0, 5.0 and 5.6 the built-in version of InnoDB. For 5.1 the plug-in was used.<br />
<br />
Two of the tests below report the time in seconds to reload a table and a lower value is better. The other test reports QPS for sysbench point queries and a higher value is better. To avoid (or add) confusion from looking at one graph where less is better and another where more is better I list a value for <b>relative performance</b> below and use that in the graphs. For relative performance a larger value is better and the result for 4.0.30 is the baseline. For the other binaries performance was always worse and the relative performance is less than 1.<br />
<br />
I also ran the sql-bench tests from the 4.0.30 release with all MySQL versions. The total time to run all tests was 789, 921, 911 and 1093 seconds for 4.0.30, 5.0.85, 5.1.63 and 5.6.11. Much of the 5.6 regression was from the <b>create</b> tests but there were significant regression for many tests.<br />
<br />
<b>Bulk load</b><br />
<br />
The first workload is bulk load. I used mysqldump to dump all rows for a sysbench table with 16M rows. For the important configuration variables: the InnoDB buffer pool was 64G, the doublewrite buffer was disabled, the binlog was disabled and I set innodb_flush_log_at_trx_commit=2. The test was repeated using two files created by mysqldump. The first used <b>--opt</b> and the second used <b>--opt --skip-extended-insert</b>.<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">Time to reload 16M rows</span><br />
<span style="font-family: Courier New, Courier, monospace;">Input from mysqldump --opt</span><br />
<span style="font-family: Courier New, Courier, monospace;">Relative-perf is Seconds-for-4.0.30 / Seconds</span><br />
<span style="font-family: Courier New, Courier, monospace;"> Seconds Relative-perf</span><br />
<span style="font-family: Courier New, Courier, monospace;">4.0.30 143 1</span><br />
<span style="font-family: Courier New, Courier, monospace;">5.0.85 178 0.80</span><br />
<span style="font-family: Courier New, Courier, monospace;">5.1.63 176 0.81</span><br />
<span style="font-family: Courier New, Courier, monospace;">5.6.11 203 0.70 -> no perf-schema</span><br />
<span style="font-family: Courier New, Courier, monospace;">5.6.11 210 0.68 -> perf-schema</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-tOOfa_S26vs/UZKR2xEioTI/AAAAAAAAAak/wUYoYDs8Ob8/s1600/chart_5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="228" src="http://2.bp.blogspot.com/-tOOfa_S26vs/UZKR2xEioTI/AAAAAAAAAak/wUYoYDs8Ob8/s320/chart_5.png" width="320" /></a></div>
<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">Time to reload 16M rows</span><br />
<span style="font-family: Courier New, Courier, monospace;">Input from mysqldump --opt --skip-extended-insert</span><br />
<span style="font-family: Courier New, Courier, monospace;">Relative-perf is Seconds-for-4.0.30 / Seconds</span><br />
<span style="font-family: Courier New, Courier, monospace;"> Seconds Relative-perf</span><br />
<span style="font-family: Courier New, Courier, monospace;">4.0.30 966 1</span><br />
<span style="font-family: Courier New, Courier, monospace;">5.0.85 1150 0.84</span><br />
<span style="font-family: Courier New, Courier, monospace;">5.1.63 1201 0.80</span><br />
<span style="font-family: Courier New, Courier, monospace;">5.6.11 1274 0.76</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-dswqe5vU-Hg/UZKR23JgI1I/AAAAAAAAAag/UjqOVqQOrmY/s1600/chart_4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="228" src="http://1.bp.blogspot.com/-dswqe5vU-Hg/UZKR23JgI1I/AAAAAAAAAag/UjqOVqQOrmY/s320/chart_4.png" width="320" /></a></div>
<br />
One source of overhead in 5.6 is the mysql client. For the first set of tests below that took 2 to 3 minutes the mysql client used 10 seconds of CPU time for 4.0.30 versus 26 seconds for 5.6. Why does it need 3X more CPU time? From CPU profile reports there is a lot more time in utf8 functions. I added results for 5.6.11 with perf-schema enabled & disabled at compile time. The table below has the CPU time (user + system) from the <b>mysql</b> client during the reload using the output from <b>mysqldump --opt</b>. There is a big regression from 4.0 to 5.1 mostly due to conversions to utf8. There is another regression from 5.1 to 5.6. I don't know why.<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">5.6 user 0m25.121s sys 0m0.629s -> 25.8 seconds</span><br />
<span style="font-family: Courier New, Courier, monospace;">5.1 user 0m19.884s sys 0m0.747s -> 20.6 seconds</span><br />
<span style="font-family: Courier New, Courier, monospace;">5.0 user 0m18.700s sys 0m0.853s -> 19.6 seconds</span><br />
<span style="font-family: Courier New, Courier, monospace;">4.0 user 0m8.994s sys 0m0.923s -> 9.9 seconds</span><br /><br /><span style="font-family: Times, Times New Roman, serif;">My server was using these character sets:</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<pre class="note" style="background-color: white; font-size: 12px; line-height: 19px; margin-left: 8px; white-space: pre-wrap; word-wrap: break-word;"><span style="font-family: Courier New, Courier, monospace;">character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_filesystem binary
character_set_results latin1
character_set_server latin1
character_set_system utf8</span></pre>
<br />
<br />
<br />
<br />
<b>Point lookup</b><br />
<br />
The next workload is point queries from sysbench. These use auto-commit and fetch 1 row by primary key per query. The buffer pool was warmed prior to the test and cached all rows but the adaptive hash index was cold.<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">Queries per second from sysbench</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">Relative-perf is QPS / QPS-for-4.0.30</span><br />
<span style="font-family: Courier New, Courier, monospace;"> QPS Relative-perf</span><br />
<span style="font-family: Courier New, Courier, monospace;">4.0.30 14898 1</span><br />
<span style="font-family: Courier New, Courier, monospace;">5.0.85 12247 0.82</span><br />
<span style="font-family: Courier New, Courier, monospace;">5.1.63 10538 0.71</span><br />
<span style="font-family: Courier New, Courier, monospace;">5.6.11 10334 0.69</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-ShyRrAof2nw/UZKUqJjY4MI/AAAAAAAAAa4/cAAPtHNrZkI/s1600/chart_6.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="228" src="http://1.bp.blogspot.com/-ShyRrAof2nw/UZKUqJjY4MI/AAAAAAAAAa4/cAAPtHNrZkI/s320/chart_6.png" width="320" /></a></div>
Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com4tag:blogger.com,1999:blog-5915567578707286635.post-41559960419785003962013-05-09T08:10:00.000-07:002013-05-09T08:10:06.813-07:00MySQL 5.6: flushing potentialFor some workloads the InnoDB performance model is very simple. It must flush dirty pages as fast as storage will allow. With a few more bug fixes InnoDB in MySQL 5.6 should be able to do that. The performance model becomes a bit more complicated when you consider that there are two reasons for sustaining a high rate of flushing dirty pages.<br />
<br />
<b>flush list</b><br />
<br />
The flush list maintains pages in the order in which they were made dirty (see below for the fine print). This list only has dirty pages. Good reasons to flush dirty pages from the <a href="http://dev.mysql.com/doc/innodb/1.1/en/glossary.html#glos_flush_list">flush list</a> include:<br />
<br />
<ol>
<li>The percentage of dirty pages in the buffer pool exceeds <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_max_dirty_pages_pct">innodb-max-dirty-pages-pct</a>. These writes are scheduled by a background thread (srv_master_thread prior to 5.6, buf_flush_page_cleaner_thread in 5.6).</li>
<li>A dirty page was modified a long time ago and uses a redo log file that will soon be rotated. Fuzzy checkpoint code schedules such pages to be written in the background before the log rotate happens to avoid blocking the rotate. Read more about fuzzy checkpoints <a href="http://www.xaprb.com/blog/2011/01/29/how-innodb-performs-a-checkpoint/">here</a>, <a href="https://www.facebook.com/note.php?note_id=408059000932">here</a> and <a href="http://dev.mysql.com/doc/innodb/1.1/en/innodb-performance-adaptive_flushing.html">here</a>. These writes are scheduled by background & foreground threads. In 5.1 the asynchronous writes scheduled by foreground threads weren't really async. I don't know if that is still true in 5.6. See <a href="http://bugs.mysql.com/bug.php?id=55004">bug 55004</a> for details.</li>
</ol>
<div>
<div>
This is the source-code description of oldest_modification field used by the flush_list:</div>
<div>
<blockquote class="tr_bq">
<i>log sequence number of the START of the log entry written of the oldest modification to this block which has not yet been flushed on disk; zero if all modifications are on disk.</i></blockquote>
</div>
</div>
<b>LRU list</b><br />
<br />
The <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html">LRU lis</a>t maintains all pages, dirty & clean, in referenced order from most-recent to least-recent. When data is read from disk into a page the page is take from either the free list which is frequently empty. When the free list is empty pages are taken from the tail of the <a href="http://dev.mysql.com/doc/innodb/1.1/en/glossary.html#glos_lru">LRU</a>. When pages at the tail of the LRU are dirty they must be flushed first and work is done to schedule flushes for such pages as they approach the tail of the LRU. Prior to 5.6 most of these flushes would be scheduled by foreground threads via buf_flush_free_margin. The buf_flush_free_margin code wasn't efficient and has been replaced in 5.6. I also replaced it in the Facebook patch for 5.1 via <a href="http://mysqlha.blogspot.com/2013/02/mysql-56-is-much-faster-on-io-bound.html">innodb_fast_free_list</a> but my patch is no longer needed. The big change in 5.6 is that most of this flushing should now be done by a background thread - buf_flush_page_cleaner_thread.<br />
<br />
<b>Configuration</b><br />
<b><br /></b>
In MySQL 5.6 the rate of flushing for the LRU and flush lists should be determined by <a href="http://mysqlha.blogspot.com/2013/05/configuring-innodb-for-mysql-56.html">configuration variables</a> - innodb_io_capacity for the flush list and innodb_lru_scan_depth for the LRU. This is a big change from earlier MySQL releases because innodb_lru_scan_depth is new and prior to 5.6 <i><b>furious flushing</b></i> would frequently be done.<br />
<br />
In MySQL 5.6 these variables are the peak page write rates per buffer pool instance and with the exception of shutdown these rates should not be exceeded. Prior to 5.6 InnoDB would flush as fast as possible when needed and that rate could exceed innodb_io_capacity. I use furious flushing to describe this.<br />
<br />
This is good news for getting predictable performance assuming InnoDB is able to sustain these configured rates. There are a few more bugs to get fixed in 5.6 before that is true. Fortunately the bugs are getting fixed quickly and many are fixed in 5.6.12. But I wish there were but one variable to set the page flush rate rather than two.<br /><br /><b>Benchmarks</b><br /><br />There are a few basic performance tests to determine whether InnoDB can sustain the desired page flush rates. I use sysbench with a workload that updates 1 row by primary key per transaction and then run it for two configurations -- cached and IO-bound. The doublewrite buffer is enabled but fsync-on-commit and the binlog are disabled. The workload has no data contention. The workload & configuration make it as easy as possible for InnoDB to flush pages. If it can't keep up here then it will have more trouble elsewhere.<br />
<br />
<b>Cached</b><br />
<br />
The cached configuration uses a buffer pool much larger than the test tables. For this workload all of the flushing should be done from the flush list. Whether InnoDB needs to sustain the maximum page flush rate (innodb_buffer_pool_instances X innodb_io_capacity) is determined by the size & number of the redo logs and the rate at which redo logs are written. For the tests reported here I did not vary the size & number of redo logs to confirm the peak flush rate could be sustained.<br /><br />
These list the rate of updates/second. One interesting result is that for both 4.0.30 and 5.6.11 the rate at high-concurrency with all data in one table is better than the rate for a workload that uses 8 tables. I hope that problem will be fixed in 5.6.12 (see bugs <a href="http://bugs.mysql.com/bug.php?id=68658">68658</a>, <a href="http://bugs.mysql.com/bug.php?id=68588">68588</a>, <a href="http://bugs.mysql.com/bug.php?id=68555">68555</a> and <a href="http://bugs.mysql.com/bug.php?id=45892">45892</a>). Note also that the rates at low-concurrency are better (about 1.2X here) for 4.0.30. I hope that we can undo that change. My builds & configurations use most of <a href="http://mysqlha.blogspot.com/2013/04/mysql-56-incomplete-guide-to-avoiding.html">my advice</a> for getting peak performance from 5.6.<br /><br />
<br />
<span style="font-family: Courier New, Courier, monospace;">updates/second using 1 table</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 1 2 4 8 16 32 clients</span><br />
<span style="font-family: Courier New, Courier, monospace;">5411 10342 20268 36473 19616 9718 4.0.30</span><br />
<span style="font-family: Courier New, Courier, monospace;">4372 8347 16757 34656 60680 70662 5.6.11</span><br />
<br />
<div>
<br /></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">updates/second using 8 tables</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;"> 8 16 32 64 128 256 clients</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">33580 18501 9875 7367 6659 6012 4.0.30</span></div>
<div>
<span style="font-family: Courier New, Courier, monospace;">34328 25716 24633 23828 23790 23114 5.6.11</span></div>
<br />
<br />
<br />
<br />
<div>
<b>IO-bound</b></div>
<br />
<br />The IO-bound configuration uses a buffer pool much smaller than the test tables and most of the flushing should be done from the LRU list. The buffer pool is 2G and the tables are ~32G total. The peak flush (innodb_buffer_pool_instances X innodb_lru_scan_depth) is one limit on the peak update rate.<br /><br />I ran IO-bound tests to compare MySQL 4.0.30 and 5.6.11 and was disappointed by the results for 5.6.11 as the rate was much less than the peak LRU flush rate. I filed bug 69170 and learned it was already fixed in 5.6.12. The results below are the rate of updates/second for 4.0.30, 5.6.11 and 5.6.11 with my hack to fix bug 69170. The 5.6.11 configuration used innodb_buffer_pool_size=2G, innodb_buffer_pool_instances=4 and innodb_lru_scan_depth=4000. <br /><br />For both the 1 table and 8 table configurations the peak LRU flush rate should be 16,000 and I want the peak rate of updates/second to be close to 16,000 when concurrency grows. Because of bug 69170 it was far from the peak for the 1 table case and my hack fixes that. So I expect that InnoDB in 5.6.12 will be able to sustain much higher QPS for workloads that are bound by LRU flushing. The results with 5.6.11 for 8 tables are much worse than for 1 table and I think this suffers from the bugs listed in the cached section above. I will wait for 5.6.12 to confirm. <br /><br />
<span style="font-family: Courier New, Courier, monospace;">updates/second using 1 table</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 1 2 4 8 16 32 clients</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">2535 3865 5554 6729 6759 5825 4.0.30</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">2818 3740 5026 5359 5557 5525 5.6.11</span><br />
<span style="font-family: 'Courier New', Courier, monospace;">3070 5893 10940 15420 15514 13628 5.6.11+hack</span><br />
<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">updates/second using 8 tables</span><br />
<span style="font-family: 'Courier New', Courier, monospace;"> 8 16 32 64 128 256 clients</span><br />
<span style="font-family: Courier New, Courier, monospace;">4626 4522 4253 4076 3972 3941 4.0.30</span><br />
<span style="font-family: Courier New, Courier, monospace;">7565 6234 6109 6144 6125 6271 5.6.11</span><br />
<br />
Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com1tag:blogger.com,1999:blog-5915567578707286635.post-89113997617971946462013-05-08T07:27:00.001-07:002013-05-08T10:06:27.929-07:00Configuring InnoDB for MySQL 5.6: innodb_io_capacity, innodb_lru_scan_depth<a href="http://draft.blogger.com/Linkbench%20results%20are%20here.%20MySQL%205.6.11%20needs%20a%20few%20improvements.%20Results%20for%205.6.12%20should%20be%20better.%20And%20don't%20forget%20to%20set%20both%20innodb_io_capacity%20and%20innodb_lru_scan_depth.%20As%20innodb_lru_scan_depth%20is%20new%20in%205.6%20many%20people%20won't%20set%20it%20when%20they%20increase%20innodb_io_capacity,%20so%20LRU%20page%20flushing%20won't%20be%20done%20at%20the%20needed%20rate.%20Even%20more%20interesting%20is%20that%20%22furious%20flushing%22%20is%20not%20done%20in%205.6%20as%20it%20was%20prior%20to%205.6.%20So%20InnoDB%20will%20%20%20http://www.mysqlperformanceblog.com/2013/05/08/mysql-and-percona-server-in-linkbench-benchmark">Percona published</a> Linkbench results. It looks like they set <a href="http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_io_capacity">innodb_io_capacity</a> to a large value but did not increase <a href="http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_lru_scan_depth">innodb_lru_scan_depth</a>. I think that is a mistake. This is a mistake that I have made and that I hope you can avoid. innodb_io_capacity limits the number of pages flushed per second from the flush list. innodb_lru_scan_depth limits the number of pages flushed per second from the LRU list. Perhaps it would have been better to derive both flush rates from innodb_io_capacity. These are approximate limits on the number of page writes per buffer pool instance per second rather than on the number of page writes per second. When innodb_buffer_pool_instances=8, innodb_io_capacity=2000 and innodb_lru_scan_depth=1000 then InnoDB can flush about 8 X 2000 pages per second from the flush list and 8 X 1000 pages per second from the LRU.<br />
<br />
It is now very important to set both of these variables to the IO rate you want InnoDB to maintain. Prior to 5.6 this was much less important as InnoDB did "furious flushing" and the main background thread could greatly exceed innodb_io_capacity when there was work to be done.<br />
<br />
--<br />
<br />
The rest of this was posted to mysqlperformanceblog.com but looks like it is held up by spam filters. Maybe it has too many URLs. One day spam filters will discover the notion of relevance for URLs.<br />
<br />
<br />
For 5.6 you need to set innodb_lru_scan_depth to a value similar to innodb_io_capacity. They should have made LRU page flushing a function of innodb_io_capacity, but they didn’t and a lot of people are going to waste time and lose performance because of this. I am one of those people.<br />
<br />
When MySQL 5.6 spends a lot of time in buf_LRU_scan_and_free_block you are not going to have good performance results. Prior to 5.6 foreground threads were responsible for moving pages to the free list via buf_flush_free_margin. I did a lot of work for the innodb_fast_free_list option to fix the stalls and that change makes 5.1 competitive with 5.6 (http://mysqlha.blogspot.com/2013/02/mysql-56-is-much-faster-on-io-bound.html) but that patch can’t be used in 5.6.<br />
<br />
In 5.6 a background thread, buf_flush_page_cleaner_thread, should be moving all pages to the free list. Foreground threads can still do it, but the server is much less efficient when they must do it. And when they call buf_LRU_scan_and_free_block they are trying to do it.<br />
<br />
I filed a few bugs for this and I hope most of them will be fixed in 5.6.12. They should make a difference. I think Oracle might be aware of some of these problems prior to my bug reports — but it is hard to know what goes on behind the large wall between us and them.<br />
http://bugs.mysql.com/bug.php?id=68658<br />
http://bugs.mysql.com/bug.php?id=68588<br />
http://bugs.mysql.com/bug.php?id=68555<br />
http://bugs.mysql.com/bug.php?id=68481<br />
<br />
I am not sure this one will be fixed. Too bad, as Percona has a fix for it – http://bugs.mysql.com/bug.php?id=45892Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com0tag:blogger.com,1999:blog-5915567578707286635.post-33343542898992695772013-05-07T13:13:00.000-07:002013-05-07T13:13:17.664-07:00MySQL 5.6 versus 4.0 for a read-only workloadI compared the performance of MySQL 5.6.11 versus 4.0.30 using a read-only workload with sysbench. Performance was much better for 5.6.11 in most cases. At low-concurrency MySQL 4.0 was a bit faster. MySQL 5.6.11 was faster at high-concurrency and when doing many page reads per second. The product has improved a lot since I started using MySQL.<br />
<br />
I followed most of <a href="http://mysqlha.blogspot.com/2013/04/mysql-56-incomplete-guide-to-avoiding.html">my advice</a> on building and configuring MySQL 5.6 for peak performance. I used two test servers - one for the sysbench clients and the other for mysqld. Each server has 16 real cores and 32 with HT enabled. Each test server has 144 GB of memory and fast storage that can do ~150,000 16 KB page reads/second.<br />
<br />The tests selected 1 row by primary key per query and all queries used auto-commit mode. Tests were run in IO-bound and cached modes. For cached tests all of the tables were in the InnoDB buffer pool at test start, but the adaptive hash index might not be warm. For the IO-bound tests only a subset of the tables fit in the InnoDB buffer pool. Direct IO was used to avoid caching by the OS. The buffer pool was 64 GB for the cached tests and 2 GB for the IO-bound tests. The full my.cnf files are at the end of this post.<br /><br />One group of tests used 1 table and was run for 1, 2, 4, 8, 16 and 32 concurrent clients. The other group of tests used 8 tables and was run for 8, 16, 32, 64, 128 and 256 concurrent clients. The database size was 4 GB for the 1 table tests and 32 GB for the 8 table tests. Thus for the IO-bound tests about 50% of the table is in cache for the 1-table test and about 7% is in cache for the 8-tables test.<br />
<br />
<b>IO bound<br /></b><br />
MySQL 5.6.11 was much faster than 4.0.30 except at low concurrency. Some of the changes that make this possible include multiple buffer pool instances and a more efficient LRU page replacement algorithm.<br />
<br /><span style="font-family: Courier New, Courier, monospace;">QPS, 1-table</span><br />
<br />
<span style="font-family: Courier New, Courier, monospace;"> 1 2 4 8 16 32 clients</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 3224 6231 12705 26193 55477 92301 5.6.11</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 3579 7160 13418 12646 10769 10184 4.0.30</span><br />
<span class="Apple-tab-span" style="white-space: pre;"><span style="font-family: Courier New, Courier, monospace;"><br /></span></span>
<span class="Apple-tab-span" style="white-space: pre;"><span style="font-family: Courier New, Courier, monospace;">QPS, 8-tables </span></span><br />
<span style="font-family: Courier New, Courier, monospace;"> 8 16 32 64 128 256 clients</span><br />
<span style="font-family: Courier New, Courier, monospace;">22676 44121 71019 79787 79082 78269 5.6.11</span><br />
<span style="font-family: Courier New, Courier, monospace;">17168 16520 14990 14852 14767 14696 4.0.30</span><br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-gQPxIzaG9RI/UYlZyh2jeQI/AAAAAAAAAZY/bihHmSjbqco/s1600/chart_1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="228" src="http://4.bp.blogspot.com/-gQPxIzaG9RI/UYlZyh2jeQI/AAAAAAAAAZY/bihHmSjbqco/s320/chart_1.png" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-9x-aOknNDg0/UYlZyrjsjZI/AAAAAAAAAZk/rt4gKGiXxm0/s1600/chart_2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="228" src="http://1.bp.blogspot.com/-9x-aOknNDg0/UYlZyrjsjZI/AAAAAAAAAZk/rt4gKGiXxm0/s320/chart_2.png" width="320" /></a></div>
<br />
<b>Cached<br /></b><br />
Had I run the 1-table test for more than 32 clients I expect that the difference between 5.6.11 and 4.0.30 would have been significant as it was in the 8-table test. At low-concurrency 4.0.30 is faster.<br />
<b><br /></b>
<br />
<span style="font-family: Courier New, Courier, monospace;">QPS, 1-table</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 1 2 4 8 16 32 clients</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 5005 10065 20178 39595 83888 154852 5.6.11</span><br />
<span style="font-family: Courier New, Courier, monospace;"> 6130 11868 24391 45856 106598 133349 4.0.30</span><br />
<span class="Apple-tab-span" style="white-space: pre;"><span style="font-family: Courier New, Courier, monospace;"> </span></span><br />
<span class="Apple-tab-span" style="white-space: pre;"><span style="font-family: Courier New, Courier, monospace;">QPS, 8-tables </span></span><br />
<span style="font-family: Courier New, Courier, monospace;"> 8 16 32 64 128 256 clients</span><br />
<span style="font-family: Courier New, Courier, monospace;">39895 76498 153421 227335 245511 244313 5.6.11</span><br />
<span style="font-family: Courier New, Courier, monospace;">49603 101307 97728 77737 75790 75500 4.0.30</span><br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-O2Pir8L25bI/UYlZysxcdKI/AAAAAAAAAZc/KCTQ3ZCswWI/s1600/chart_3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="228" src="http://4.bp.blogspot.com/-O2Pir8L25bI/UYlZysxcdKI/AAAAAAAAAZc/KCTQ3ZCswWI/s320/chart_3.png" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-4fDdC1aBqUI/UYlZyw4g-jI/AAAAAAAAAZg/5_a-I6A8vdQ/s1600/chart_4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="228" src="http://2.bp.blogspot.com/-4fDdC1aBqUI/UYlZyw4g-jI/AAAAAAAAAZg/5_a-I6A8vdQ/s320/chart_4.png" width="320" /></a></div>
<b>Configuration</b><br />
<br />
This is the my.cnf file for 4.0.30:<br />
<br />
<blockquote class="tr_bq">
[mysqld]<br />innodb_log_file_size=1900M<br />innodb_flush_method=O_DIRECT<br />innodb_max_dirty_pages_pct=80<br />innodb_flush_log_at_trx_commit=2<br />innodb_thread_concurrency=32<br />innodb_buffer_pool_size= --> 64G or 2G<br />max_connections=2000<br />table_cache=2000<br />key_buffer_size=200M<br />query_cache_size=0<br />query_cache_type=0</blockquote>
<br />
This is the my.cnf file for 5.6.11<br />
<br />
<blockquote class="tr_bq">
innodb_log_file_size=1900M<br />innodb_doublewrite=1<br />innodb_flush_method=O_DIRECT<br />innodb_max_dirty_pages_pct=80<br />innodb_file_format=barracuda<br />innodb_file_per_table<br />innodb_io_capacity=8000<br />innodb_lru_scan_depth=8000<br />innodb_checksum_algorithm=CRC32<br />metadata_locks_hash_instances=256<br />innodb_flush_log_at_trx_commit=2<br />innodb_thread_concurrency=32<br />innodb_buffer_pool_size=64G<br />innodb_buffer_pool_instances=8<br />table-definition-cache=1000<br />table-open-cache=2000<br />table-open-cache-instances=1<br />max_connections=2000<br />key_buffer_size=200M<br />skip_log_bin<br />query_cache_size=0<br />query_cache_type=0</blockquote>
<br />
<div>
<br /></div>
<br />
Mark Callaghanhttp://www.blogger.com/profile/09590445221922043181noreply@blogger.com6