As we know, one of the most important config for InnoDB is the innodb_buffer_pool_size, it basically store the innodb data and indexes in memory, when MySQL receives a query and the InnoDB pages involved on that query are stored in the buffer, it does not need to go to the disk to return the result, which is much faster (memory speed vs disk speed).
As it is stored in memory, every time you restart your MySQL server it starts with a clean/empty buffer pool and usually it take some time to warm-up the buffer.
To speed up this process, we can configure 2 variables that will dump and reload the pages reference stored in the buffer, this is a new functionality added on MySQL 5.6 (it was presented on previous versions of Percona
This is part of the ongoing work on improving the transaction life cycle management. In 5.7.2 we split the transaction list into two. The read-only transaction list and the read-write transaction list. There was another “virtual” list, the auto-commit non-locking read-only (AC-NL-RO) transaction list. The change in 5.7.2 was that by default a transaction was treated as read only and added to the read-only transaction list. Only when it was determined that the transaction was going to do an update we removed the transaction from the read-only list and moved it to the read-write transaction list. This initial add to the the read-only list forced the acquisition of the trx_sys_t::mutex. Acquiring the mutex during transaction start/begin has a cost. Promoting a transaction from read-only to read-write we had to acquire the trx_sys_t::mutex to add to the read-write[Read more...]
In the last 10 years I have worked a lot with replication systems, and I have developed a keen interest in the topic of multiple masters in a single cluster. My interest has a two distinct origins:
The double nature of[Read more...]
If the performance of your MySQL servers is important to you, performance tuning is key.
In the 4-day MySQL Performance Tuning course, you learn to analyze and optimize the performance of the MySQL Database, using the tools necessary for monitoring, evaluating and tuning.
You can take this course as a:
This week we discuss test data sets. Ear Candy is errata from Episode 165 on Galera Cluster, and At the Movies is "Runaway Complexity in Big Data"
Test Data Sets
Episode 81, where we talked about different benchmark tools
Sysbench - general version
Percona's sysbench tests
Factors: # tables, concurrency, data set size
Episode 143, where we did an ear candy featuring Morgan Tocker's article on estimating MySQL's working set with INFORMATION_SCHEMA
As you probably already know, in MySQL 5.7.3 release, InnoDB Memcached reached a record of over 1 million QPS on read only workload. The overview of the benchmark and testing results can be seen in an earlier blog by Dimitri. In this blog, I will spend sometime on the detail changes we have made to achieve this record number.
First thanks to Facebook’s Yoshinori with his bug#70172 that brought our attention to this single commit read only load test. We have been focussing on operation with large batch size. This bug prompted us to do a series of optimization on single commit read only queries and these optimizations eliminated almost all major bottlenecks from the[Read more...]
This week we discuss fine-tuning Galera Cluster. In this week's ear candy we talk about recent changes to mysqldump and locking; At the Movies presents "Using Amazon Web Services for MySQL at Scale".
In some application scenarios (e.g. PHP applications) client connections have very short durations, maybe only executing a single query. This means that the time spent processing connects and disconnects can have a large impact on the overall performance.
In MySQL 5.6 we started working on optimizing the code handling connects and disconnects. And this work has accelerated in MySQL 5.7. In this blog post I will first show the results we have achieved and then describe what we have done to get them.
The graph below shows a comparison of the most recent 5.5 and 5.6 releases as well as the 5.7.2 and 5.7.3 milestones. We measured the number of queries per second (QPS) where each client executes a single query (point select) before disconnecting. For each server[Read more...]
This week we talk about how to install and set up Galera Cluster. Ear Candy talks about the new MySQL repos from Oracle and what to know about using them; At the Movies is Michael Stonebreaker talking about how to process today's big data transactional processing needs.
I took a quick look at MariaDB 10.0 single-treaded performance (simple read-only sysbench). One thing immediately leaps to the eye, and I thought it worthy of mention. It contains an important lesson about the use of synchronisation primitives and in particular "atomic operations" in MariaDB (and MySQL).
I am using the Linux
perf tool on this
sysbench --num-threads=1 --test=oltp --oltp-test-mode=simple --oltp-read-only --oltp-skip-trxLook at the top offender in the output from
1,54% mysqld mysqld [.] set_thread_state_v1The only thing this does is set a string for SHOW PROCESSLIST (and the like) about what the thread is doing. And we are spending a whopping 1.5% of the total time doing this.
And why? That becomes clear when looking[Read more...]
Or I could place in the title – “Yes, we done it!”
After reaching 500K QPS in Read-Only on SQL queries, it was natural to expect a much higher performance level from InnoDB Memcached Plugin which is by-passing all SQL related layers.. However the story is not simple, and yet far from finished
While for today we have already our first “preview” results showing that we’re able to reach over 1,000,000 Query/sec level with the latest MySQL 5.7 code:
If you are responsible for a MySQL Database, you make choices based on your priorities; cost, security and performance.
To learn more about improving performance, take the MySQL Performance Tuning course.
In this 4-day instructor-led course you will learn practical, safe and highly efficient ways to optimize performance for the MySQL Server. It will help you develop the skills needed to use tools for monitoring, evaluating and tuning MySQL.
You can take this course via the following delivery methods:Training-on-Demand:
Q2: Does extended secondary keys only work with InnoDB?
No, it should work with any storage engine that uses the primary key columns as reference to the row, which means most storage engines with clustered primary keys. I say [Read more...]
During our experiments I came upon a few TokuDB variables of interest; if you are using TokuDB you might want to look into these:
This is a boundary on the number of seconds an ANALYZE TABLE will operate on each index on each partition on a TokuDB table.
That is, if tokudb_analyze_time = 5, and your table has 4 indexes (including PRIMARY) and 7 partitions, then the total runtime is limited to 5*4*7 = 140 seconds.
Default in 7.1.0: 5 seconds
Similar to innodb_buffer_pool_size, this variable sets the amount of memory allocated by TokuDB for caching pages. Like InnoDB the table is clustered within[Read more...]
Like TokuDB, InfiniDB is now a fully open source server product. In the past infiniDB was “almost open source”. The open source version was an old release with no access to the advance functions like MPP multi-server execution. This is no more the case. With InfiniDB 4 the open source version is the latest release [...]
There are two basic performance analysis methodologies you can use for most performance issues. The first is the resource-oriented USE Method, which provides a checklist for identifying common bottlenecks and errors. The second is the thread-oriented TSA Method, for identifying issues causing poor thread performance. I summarized the TSA Method in my Stop The Guessing talk at Velocity conf this year, and it is also covered in the Applications chapter of my[Read more...]
This week we discuss features of Percona Server 5.6 compared to MySQL 5.6. Ear Candy is about pt-upgrade, and At the Movies is a set of lightning talks.
DB Hangops - every other Wednesay at noon Pacific time
Upcoming MySQL events (http://www.mysql.com/news-and-events/events/)
Percona Live London 2013 is happening Monday November 11th and Tuesday November 12th, 2013 at the Millenium Gloucester Conference Center
MySQL Connect 2013 has been a great edition. There was of course a lot of nice announcements of improvements in the the core MySQL server technology. One of the major announcement that received a lot of buzz was MySQL Fabric. MySQL Fabric is an infrastructure component aimed at simplifying construction of a highly available, sharded, [...]
It all started with a goal to make InnoDB temporary tables more effective. Temporary table semantics are blessed with some important characteristics that can help us simplify lot of operations.
On September 21st, during the opening keynote at MySQL Connect 2013, Tomas Ulin disclosed the release of MySQL 5.7.2. This is a milestone release that includes several new features. Unlike the Previous one, which was just a point of pride, where Oracle was stating its continuous commitment to releasing new versions of MySQL. In MySQL 5.7.2, we see several new features:
“Will you expand the next webcast to 90 minutes? This one was too interesting to last only one hour” was one of the questions we got during the “Tuning MySQL for Great Product Performance: The Fundamentals, Updated for MySQL 5.6” (http://www.mysql.com/news-and-events/web-seminars/tuning-mysql-for-great-product-performance-the-fundamentals-updated-for-mysql-5-6/) webinar on August 27th. The engineers on Q&A got a lot of good (and more technical) questions during the webcast. For those of you who were unable to join us live, I’ve posted the questions and answers below, and you can listen to a recording of the webinar and get a .pdf of slides at this link. (http://www.mysql.com/news-and-events/web-seminars/tuning-mysql-for-great-product-performance-the-fundamentals-updated-for-mysql-5-6/)
The webinar was created specifically for the software and
In talking to existing MongoDB users and TokuMX evaluators, I’ve often heard that the performance of MongoDB is very good as long as your working data set fits in RAM. The story continues that if your working data set grows to be larger than the RAM on your server, the built-in sharding capabilities of MongoDB allow you to scale horizontally.[Read more...]
The MySQL engineering team constantly works to bring you huge performance improvements with each new release of MySQL. Here are four ways to help you get the most from these improvements
Tap into Sveta Smirnova's MySQL performance expertise on October 1st 2013 at 10am Central European Time, by attending the 1-day virtual seminar, Troubleshooting MySQL Performance with Sveta Smirnova. Sveta starts with basics, working towards more advanced cases that DBAs usually need years of experience to identify or solve. Click here to learn more about this seminar and to register for the event.
This is the first in a series of posts describing my impressions of converting a large OLAP server to TokuDB. There's a lot to tell, and the experiment is not yet complete, so this is an ongoing blogging. In this post I will describe the case at hand and out initial reasons for looking at TokuDB.
Disclosure: I have no personal interests and no company interests; we did get friendly, useful and free advice from Tokutek engineers. TokuDB is open source and free to use, though commercial license is also available.
We have a large and fast growing DWH MySQL setup. This data warehouse is but one component in a larger data setup, which includes Hadoop, Cassandra and more. For online dashboards and most reports, MySQL is our service. We populate this warehouse mainly via Hive/Hadoop. Thus, we have an hourly load of data from Hive, as[Read more...]
The talk is on YouTube at Are You Getting the Best Out of Your MySQL Indexes? There are also PDF slides.
From the official conference description, if you want to know more:
MySQL indexes are often used to make performance better. However, they can make performance suffer if you are not using them properly. Oracle ACE Director Sheeri Cabral explains the pitfalls to avoid with indexes and how to utilize compound indexes to maximize index availability with the least amount of write overhead.
*I know I have not[Read more...]